Oracle创建用户及表空间

1. 用户

创建用户:

sql> create user <用户名> IDENTIFIED BY <用户密码> default tablespace <表空间名称> temporary tablespace TEMP profile DEFAULT;

temporary tablespace TEMP –指定临时表空间默认TEMP

password expire; –密码过期需要重设密码,意思是当你用这个新建立的密码过期用户首次登录后，系统会提示你重新输入新密码，不然会拒绝你登陆，重设新密码后就可以登录，该用户的密码就是你新设的密码，相当于首次登录修改密码这样的选项

修改用户：

1. 修改密码：

sql> alter user <用户名> identified by "<密码>";

2. 修改用户缺省表空间：

sql> alter user <用户名> default tablespace <表空间名称>;

3. 修改用户临时表空间

sql> alter user <用户名> temporary tablespace <临时表空间名称>;

4. 强制用户修改密码：

sql> alter user <用户名> password expire;

5. 将用户加锁：

sql> alter user <用户名> account lock;		--加锁
sql> alter user <用户名> account unlock;	--解锁

删除用户:

sql> drop user <用户名> CASCADE;

将用户及其所建实体全部删除

监视用户:

1. 查询用户会话信息：

sql> select username, sid, serial#, machine from v$session;

2. 删除用户会话信息：

sql> alter system kill session \'sid, serial#\';

3. 查询用户执行SQL：

sql> select user_name, sql_text from v$open_cursor;

2. 权限

权限说明：

1. 系统权限：系统规定用户使用数据库的权限。（系统权限是对用户而言)。

DBA: 拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。
RESOURCE:拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。
CONNECT:拥有Connect权限的用户只可以登录Oracle，不可以创建实体，不可以创建数据库结构。

对于普通用户：授予connect, resource权限。
对于DBA管理用户：授予connect，resource, dba权限。

2. 实体权限：某种权限用户对其它用户的表或视图的存取权限。（是针对表或视图而言的）。

实体权限分类：select, update, insert, alter, index, delete, all

all –包括所有权限
execute –执行存储过程权限

用户授权

1. 将表的操作权限授予全体用户：

sql> grant all on product to public; 
--public表示是所有的用户，这里的all权限不包括drop。

2. 指定用户授权：

sql> grant connect,resource,dba to <用户名>,<用户名2>;

3. 级联用户授权：

sql> grant connect to <用户名> with admin option;

4. 实体权限收回：

sql> revoke select, update on product from <用户名>; 
--传递的权限将全部丢失。

with admin option –被授予该权限的用户有权将某个权限(如create any table)授予其他用户或角色，取消是不级联的。给一个用户授予系统权限带上with admin option 时，此用户可把此系统权限授予其他用户或角色，但收回这个用户的系统权限时，这个用户已经授予其他用户或角色的此系统权限不会因传播无效，如授予A系统权限create session with admin option,然后A又把create session权限授予B,但管理员收回A的create session权限时，B依然拥有create session的权限，但管理员可以显式收回B create session的权限，即直接revoke create session from B.

with grant option –权限赋予/取消是级联的，对象授权时，被授予的用户也可把此对象权限授予其他用户或角色，不同的是但管理员收回用with grant option授权的用户对象权限时，权限会因传播而失效，如：grant select on <表名> to A with grant option;，A用户把此权限授予B，但管理员收回A的权限时，B的权限也会失效，但管理员不可以直接收回B的SELECT ON TABLE 权限。

其他说明：

DBA用户可以操作全体用户的任意基表(无需授权，包括删除)

1. 用户可以查询到的所有表：

sql> select owner, table_name from all_tables

2. 查询用户创建的表：

sql> select table_name from user_tables;

3. 获权可以存取的表(被授权的)

sql> select grantor, table_schema, table_name, privilege from all_tab_privs;

4. 授出权限的表(授出的权限)

sql> select grantee, owner, table_name, privilege from user_tab_privs; 

3. 角色

角色：角色是一组权限的集合，将角色赋给一个用户，这个用户就拥有了这个角色中的所有权限。
预定义角色：是在数据库安装后，系统自动创建的一些常用的角色。

1. 角色所包含的权限可以用以下语句查询：

sql> select * from role_sys_privs where role=\'<角色名>\';

常用角色说明：

1．CONNECT, RESOURCE, DBA
这些预定义角色主要是为了向后兼容。其主要是用于数据库管理。oracle建议用户自己设计数据库管理和安全的权限规划，而不要简单的使用这些预定角色。将来的版本中这些角色可能不会作为预定义角色。

2．DELETE_CATALOG_ROLE， EXECUTE_CATALOG_ROLE， SELECT_CATALOG_ROLE
这些角色主要用于访问数据字典视图和包。

3．EXP_FULL_DATABASE， IMP_FULL_DATABASE
这两个角色用于数据导入导出工具的使用。

4．AQ_USER_ROLE， AQ_ADMINISTRATOR_ROLE
AQ:Advanced Query。这两个角色用于oracle高级查询功能。

5． SNMPAGENT
用于oracle enterprise manager和Intelligent Agent

6．RECOVERY_CATALOG_OWNER
用于创建拥有恢复库的用户。关于恢复库的信息，参考oracle文档《Oracle9i User-Managed Backup and Recovery Guide》

7．HS_ADMIN_ROLE
A DBA using Oracle\’s heterogeneous services feature needs this role to access appropriate tables in the data dictionary.

管理角色

1. 建立角色：

sql> create role <角色名>;

2. 授权给角色：

sql> grant create any table,create procedure to <角色名>;

3. 授权角色给用户：

sql> grant <角色名> to <用户名>;

4. 查看角色所包含的权限：

sql> select * from role_sys_privs;

5. 创建带有密码的角色(在生效带有密码的角色时必须提供密码)：

sql> create role <角色名> identified by <密码>;

6. 修改角色，是否需要密码：

sql> alter role <角色名> not identified;
sql> alter role <角色名> identified by <密码>;

7. 设置当前用户要生效的角色：

注：角色的生效是，假设用户a有b1,b2,b3三个角色，那么如果b1未生效，则b1所包含的权限对于a来讲是不拥有的，只有角色生效了，角色内的权限才作用于用户，最大可生效角色数由参数MAX_ENABLED_ROLES设定；在用户登录后，oracle将所有直接赋给用户的权限和用户默认角色中的权限赋给用户。

--使指定角色生效
sql> set role <角色名1>,<角色名2>;
--使用带有密码的角色生效
sql> set role <角色名> identified by <密码>;
--使用该用户的所有角色生效
sql> set role all;
--设置所有角色失效
sql> set role none;
--除<角色名>外的该用户的所有其它角色生效。
sql> set role all except <角色名>;
--查看当前用户的生效的角色。
sql> select * from SESSION_ROLES;

8. 修改指定用户，设置其默认角色：

sql> alter user <用户名> default role <角色名>;
sql> alter user <用户名> default role all except <角色名>;

9. 删除角色：

sql> drop role <角色名>;
--角色删除后，原来拥用该角色的用户就不再拥有该角色了，相应的权限也就没有了。

其他说明

1. 无法使用WITH GRANT OPTION为角色授予对象权限

2. 可以使用WITH ADMIN OPTION 为角色授予系统权限,取消时不是级联

4. Oracle 数据字典

1. 动态数据字典是以v$xxx开始的数据字典，在数据库中约有150个左右，这些数据字典反映数据库动态运行状况，在不同时间查询会得到不同的结果。

2. DBA_*：DBA数据字典是以DBA_xxx表示，该数据字典存储数据库结构，查询DBA数据字典可以反映数据库结构设置，管理磁盘空间和表空间、事务与回退段、用户与表空间等信息。

3. USER_*：用户数据字典是以USER_xxx表示，这些数据字典反应用户所创建的实体信息。如，USER_TABLES、USER_VIEWS，数据库管理员具有操作全体用户所有实体的权限，可以查询这类数据字典，了解用户所创建实体状况，必要时可以将用户创建的不正确的实体删除。

4. ALL_*：ALL_xxx类数据字典，表示用户所创建的实体及用户有权可以存取的实体。

常用查询

1. 查询所有用户：

sql> select * from dba_user; 
sql> select * from all_users; 
sql> select * from user_users; 

2. 查看用户系统权限：

sql> select * from dba_sys_privs; 
sql> select * from all_sys_privs; 
sql> select * from user_sys_privs; 

3. 查看用户对象权限：

sql> select * from dba_tab_privs;
sql> select * from all_tab_privs;
sql> select * from user_tab_privs;

4. 查看所有角色：

sql> select * from dba_roles;

5. 查看用户所拥有的角色：

sql> select * from dba_role_privs;
sql> select * from user_role_privs;

6. 查看当前用户的缺省表空间：

sql> select username,default_tablespace from user_users; 

7. 查看某个角色的具体权限：

sql> grant connect,resource,create session,create view to <角色名>;

8. 查看”RESOURCE”具有那些权限：

sql> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=\'RESOURCE\';