oracle 第08章 用户、权限、角色管理

目录

一、用户管理
1.查看用户
2.创建用户
3.修改用户
4.删除用户
5.查看资源文件
6.创建资源文件
7.应用资源文件
8.修改资源文件
9.删除资源文件
二、权限管理
1.权限分类
2.查看系统权限
3.授予系统权限
4.回收系统权限
5.查看对象权限
6.授予对象权限
7.回收对象权限
三、角色管理
1.查看角色
2.创建角色
3.修改角色
4.赋予角色权限
5.赋予用户角色
6.默认角色
7.禁止和激活角色
8.回收和删除角色
9.oracle预定义角色

一、用户管理
1.查看用户

SQL> desc dba_users;
SQL> col username for a25;
SQL> col account_status for a20;
SQL> select username,account_status,created from dba_users where username='JANE';

2.创建用户
【语法】

CREATE USER username
IDENTIFIED {BY password |EXETERNALLY}
[DEFAULT TABLESPACE tablespace] 
[TEMPORARY TABLESPACE temptablespace] 
[QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace 
[,QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace 
[PROFILES profile_name] 
[PASSWORD EXPIRE] 
[ACCOUNT LOCK or ACCOUNT UNLOCK]

CREATE USER username：用户名，一般为字母数字型和“#”及“_”符号。 
IDENTIFIED BY password：用户口令，一般为字母数字型和“#”及“_”符号。 
IDENTIFIED EXETERNALLY：表示用户名在操作系统下验证，该用户名必须与操作系统中所定义的用户名相同。 
IDENTIFIED GLOBALLY AS 'CN=user'：用户名由Oracle安全域中心服务器验证，CN名字表示用户的外部名。 
[DEFAULT TABLESPACE tablespace]：默认的表空间。 
[TEMPORARY TABLESPACE tablespace]：默认的临时表空间。 
[QUOTA [integer K[M] ] [UNLIMITED] ] ON tablespace：用户可以使用的表空间的字节数。 
[PROFILES profile_name]：资源文件的名称。 
[PASSWORD EXPIRE]：立即将口令设成过期状态，用户再登录前必须修改口令。 
[ACCOUNT LOCK or ACCOUNT UNLOCK]：用户是否被加锁，默认情况下是不加锁的。

【例子】

#创建用户jane
SQL> create user jane identified by american default tablespace users temporary tablespace temp quota 10m on users password 
expire;
SQL> select username,account_status,created from dba_users;
#查看用户jane
SQL> conn sys/oracle as sysdba;
SQL> col username for a10;
SQL> col default_tablespace for a10;
SQL> col temporary_tablespace for a10;
SQL> select username,expiry_date,default_tablespace,temporary_tablespace,created from dba_users where username='JANE';
#查看用户jane的表空间配额
SQL> select tablespace_name,username,max_bytes from dba_ts_quotas where username='JANE';
#赋予用户jane权限
SQL> grant create session,resource to jane;
SQL> conn jane/american;

3.修改用户

#修改默认表空间配额
SQL> alter user jane quota 20m on users;
SQL> select tablespace_name,username,max_bytes from dba_ts_quotas where username='JANE';
#创建表空间newtbs
SQL> create tablespace newtbs logging datafile '/u01/app/oracle/oradata/orcl/newtbs.dbf' size 50m autoextend on next 50m 
maxsize 20480m extent management local;
#修改用jane的默认表空间为newtbs
SQL> alter user jane default tablespace newtbs quota unlimited on newtbs;
#查看修改后用户jane的默认表空间
SQL> select username,tablespace_name,max_bytes from dba_ts_quotas where username='JANE';
#回收用户jane在表空间USERS的使用权
SQL> alter user jane quota 0 on users;
SQL> select username,tablespace_name,max_bytes from dba_ts_quotas where username='JANE';

4.删除用户

SQL> drop user jane;

5.查看资源文件

SQL> desc dba_profiles;
SQL> col profile for a20;
SQL> col resource_name for a20;
SQL> col resource_type for a10;
SQL> col limit for a20;
SQL> select * from dba_profiles;

6.创建资源文件
【语法】

CREATE PROFILE profile
LIMIT { resource_parameters
| password_parameters
}
[ resource_parameters
| password_parameters
]... ;

<resource_parameters>
{ { SESSIONS_PER_USER
| CPU_PER_SESSION
| CPU_PER_CALL
| CONNECT_TIME
| IDLE_TIME
| LOGICAL_READS_PER_SESSION
| LOGICAL_READS_PER_CALL
| COMPOSITE_LIMIT
}
{ integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA
{ integer [ K | M ] | UNLIMITED | DEFAULT }
}

< password_parameters >
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME
}
{ expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION
{ function | NULL | DEFAULT }
}

profile：配置文件的名称。Oracle数据库以以下方式强迫资源限制：
1．如果用户超过了connect_time或idle_time的会话资源限制，数据库就回滚当前事务，并结束会话。用户再次执行命令，数据库则返回一个错误，
2．如果用户试图执行超过其他的会话资源限制的操作，数据库放弃操作，回滚当前事务并立即返回错误。用户之后可以提交或回滚当前事务，必须结束会话。
提示：可以将一条分成多个段，如1小时(1/24天)来限制时间，可以为用户指定资源限制，但是数据库只有在参数生效后才会执行限制。
Unlimited：分配该profile的用户对资源使用无限制，当使用密码参数时，unlimited意味着没有对参数加限制。
Default：指定为default意味着忽略对profile中的一些资源限制，Default profile初始定义对资源不限制，可以通过alter profile命令来改变。

Resource_parameter部分
Session_per_user：指定限制用户的并发会话的数目。
Cpu_per_session：指定会话的CPU时间限制，单位为百分之一秒。
Cpu_per_call：指定一次调用（解析、执行和提取）的CPU时间限制，单位为百分之一秒。
Connect_time：指定会话的总的连接时间，以分钟为单位。
Idle_time：指定会话允许连续不活动的总的时间，以分钟为单位，超过该时间，会话将断开。但是长时间运行查询和其他操作的不受此限制。
Logical_reads_per_session：指定一个会话允许读的数据块的数目，包括从内存和磁盘读的所有数据块。
Logical_read_per_call：指定一次执行SQL（解析、执行和提取）调用所允许读的数据块的最大数目。
Private_sga：指定一个会话可以在共享池（SGA）中所允许分配的最大空间，以字节为单位。（该限制只在使用共享服务器结构时才有效，会话在SGA中的私有空间包括私有的SQL和PL/SQL，但不包括共享的SQL和PL/SQL）。
Composite_limit：指定一个会话的总的资源消耗，以service units单位表示。Oracle数据库以有利的方式计算cpu_per_session， 
connect_time，logical_reads_per_session和private-sga总的service units

Password_parameter部分：
Failed_login_attempts：指定在帐户被锁定之前所允许尝试登陆的的最大次数。
Password_life_time：指定同一密码所允许使用的天数。如果同时指定了password_grace_time参数，如果在grace period内没有改变密码，则密码会失效，连接数据库被拒绝。如果没有设置password_grace_time参数，默认值unlimited将引发一个数据库警告，但是允许用户继续连接。
Password_reuse_time和password_reuse_max：这两个参数必须互相关联设置，password_reuse_time指定了密码不能重用前的天数，而 
password_reuse_max则指定了当前密码被重用之前密码改变的次数。两个参数都必须被设置为整数。
1．如果为这两个参数指定了整数，则用户不能重用密码直到密码被改变了password_reuse_max指定的次数以后在password_reuse_time指定的时间内。
如：password_reuse_time=30，password_reuse_max=10，用户可以在30天以后重用该密码，要求密码必须被改变超过10次。
2．如果指定了其中的一个为整数，而另一个为unlimited，则用户永远不能重用一个密码。
3．如果指定了其中的一个为default，Oracle数据库使用定义在profile中的默认值，默认情况下，所有的参数在profile中都被设置为unlimited，如果没有改变profile默认值，数据库对该值总是默认为unlimited。
4．如果两个参数都设置为unlimited，则数据库忽略他们。
Password_lock_time：指定登陆尝试失败次数到达后帐户的缩定时间，以天为单位。
Password_grace_time：指定宽限天数，数据库发出警告到登陆失效前的天数。如果数据库密码在这中间没有被修改，则过期会失效。
Password_verify_function：该字段允许将复杂的PL/SQL密码验证脚本做为参数传递到create profile语句。Oracle数据库提供了一个默认的脚本，但是自己可以创建自己的验证规则或使用第三方软件验证。 对Function名称，指定的是密码验证规则的名称，指定为Null则意味着不使用密码验证功能。如果为密码参数指定表达式，则该表达式可以是任意格式，除了数据库标量子查询

【例子】

#创建资源文件scott_prof
SQL> create profile scott_prof limit sessions_per_user 10 cpu_per_session 10000 idle_time 40 connect_time 120;
#查看资源文件scott_prof
SQL> col profile for a20;
SQL> col resource_name for a25;
SQL> col limit for a20;
SQL> select * from dba_profiles where profile='SCOTT_PROF' order by limit;
#创建密码文件password_prof
SQL> create profile password_prof limit failed_login_attempts 5 password_life_time 90 password_reuse_time 15 
password_grace_time 3;
#查看密码文件password_prf
SQL> col profile for a20;
SQL> col resource_name for a25;
SQL> col limit for a20;
SQL> select * from dba_profiles where profile='PASSWORD_PROF' order by limit;

7.应用资源文件

SQL> alter user scott profile scott_prof;
SQL> select username, profile from dba_users where username ='SCOTT';
SQL> alter user scott profile default;
SQL> select username, profile from dba_users where username ='SCOTT';

8.修改资源文件

#修改资源文件password_prof
SQL> alter profile password_prof limit failed_login_attempts 3 password_life_time 60 password_grace_time 7;
#查看资源文件password_prof
SQL> col profile for a15;
SQL> col resource_name for a25;
SQL> col resource_type for a8;
SQL> col limit for a10;
SQL> select * from dba_profiles where profile='PASSWORD_PROF' and resource_type ='PASSWORD' order by limit;

9.删除资源文件

SQL> drop profile scott_prof;
SQL> drop profile password_prof;

二、权限管理
1.权限分类
权限：执行特殊SQL语句或访问其他用户拥有的对象的权利。
系统权限：允许用户执行一个或一类特殊数据库操作，如创建数据库、创建用户、创建与维护表空间以及管理会话等。
对象权限：用户维护数据库对象的权利，如维护表、视图、序列号、存储过程、函数等。

2.查看系统权限

SQL> conn system/oracle;
SQL> col grantee for a10;
SQL> col privilege for a25;
SQL> select * from dba_sys_privs;

3.授予系统权限
【语法】

GRANT {system_privilege | role}
[, { system_privilege |role } ] ......
TO    { user | role |PUBLIC }
[, { user | role | PUBLIC }] .......
[ WITH ADMIN OPTION ]

【例子】

#创建新用户jane
SQL> create user jane identified by abc#123;
#赋予权限
SQL> grant create session,create table,select any table to jane;
#查看用户jane拥有的系统权限
SQL> conn system/oracle;
SQL> col grantee for a10;
SQL> col privilege for a25;
SQL> select * from dba_sys_privs where grantee = 'JANE';
#创建用户larry
SQL> create user larry identified by abc#456;
#查看用户larry的系统权限
SQL> select * from dba_sys_privs where grantee = 'LARRY';
#用户jane赋予用户larry系统权限，权限不够
SQL> conn jane/abc#123;
SQL> grant create session,select any talbe to larry;
#回收用户jane的所有权限
SQL> conn system/oracle;
SQL> revoke create session,select any table,create table from jane;
#赋予用户jane系统权限并允许继续授权
SQL> grant create session,select any table,create table to jane with admin option;
#查看用户jane的系统权限信息
SQL> select * from dba_sys_privs where grantee = 'JANE';
#用户jane赋予用户larry系统权限
SQL> conn jane/abc#123;
SQL> grant create session,select any table to larry;
#查看用户larry的系统权限
SQL> conn system/oracle;
SQL> select * from dba_sys_privs where grantee = 'LARRY';
#以用户larry登录数据库
SQL> conn larry/abc#456;
SQL> select * from scott.dept;
#将部分系统权限赋予所有用户
SQL> conn system/oracle;
SQL> grant create session,select any table to public;

4.回收系统权限
【语法】

REVOKE {system_privilege | role }
[, {system_privilege | role } ] ......
FROM { user | role | PUBLIC }
[, {user | role | PUBLIC } ] ......

【例子】

#查询用户jane和larry的系统权限
SQL> col grantee for a10;
SQL> col privilege for a25;
SQL> col admin_option for a15;
SQL> select * from dba_sys_privs where grantee in ('JANE','LARRY') order by grantee;
#回收用户larry的系统权限
SQL> conn system/oracle;
SQL> revoke create session,select any table from larry;
#查询用户larry的系统权限
SQL> select * from dba_sys_privs where grantee = 'LARRY';
#为用户larry赋予系统权限并带with admin option选项
SQL> grant create session,select any table to larry with admin option;
#创建用户sophie
SQL> create user sophie identified by abc#123;
#用户larry向用户sophie授予系统权限
SQL> conn larry/abc#123;
SQL> grant create session,select any table to sophie;
#查询用户sophie的系统权限
SQL> conn system/oracle;
SQL> col grantee for a10;
SQL> col privilege for a25;
SQL> col admin_option for a15;
SQL> select * from dba_sys_privs where grantee = 'SOPHIE';
#用户jane回收用户larry的select any table权限
SQL> conn jane/abc#123;
SQL> revoke select any table from larry;
#以用户sophie登录数据库并查询表信息
SQL> conn sophie/abc#123;
SQL> select * from scott.dept;
#回收授予所有用户的系统权限
SQL> conn system/oracle;
SQL> revoke create session,select any table form public; 

5.查看对象权限

SQL> col table_name for a10;
SQL> col grantor for a15;
SQL> col privilege for a10;
SQL> select * from user_tab_privs_made;

6.授予对象权限
【语法】

GRANT { object_privilege [ (column_list )] 
[, object_privilege [ (column_list )] ] ...
|ALL [ PRIVILEGE] }
ON [schema. ] object
TO    { user | role | PUBLIC } [, { user | role | PUBLIC } ] ...
[WITH GRANT OPTION]

GRANT:	 　　　　　　　　 授权关键字
object_privilege:	对象权限
column_list:	 　　　　对象权限操作的列的列表
all:	 　　　　　　　　 将当前用户的某个数据库对象的所有权限赋予新用户
on object:	 　　　　说明具体的数据库对象，如表、存储过程
with grant option:	新用户可以继续授权

【例子】

#把scott用户的emp表的update权限赋予新用户larry
SQL> conn scott/oracle;
SQL> grant update on emp to larry with grant option;
#查看scott用户表对象的授权信息
SQL> col table_name for a10;
SQL> col grantor for a15;
SQL> col privilege for a10;
SQL> select * from user_tab_privs_made;
#把表对象dept的列的update权限赋予用户sophie
SQL> grant update (dname,loc) on dept to sophie;
#使用数据字典user_col_privs_made查看相关列的权限赋予信息
SQL> col column_name for a10;
SQL> select * from user_col_privs_made;

7.回收对象权限
【语法】

REVOKE { object_privilege [, object_privilege ] ...
|ALL [PRIVILEGE ] }
ON [schema.] object
FROM {user | role |PUBLIC }
[, {user | role | PUBLIC } ]...
[CASCADE CONSTRAINTS ]

【例子】

#回收用户larry对emp表的update权限
SQL> conn scott/oracle;
SQL> revoke update on emp from larry;
#验证是否成功回收赋予用户larry的对象权限
SQL> select * from user_tab_privs_made where grantee = 'LARRY';
#回收用户sophie对dept表的列操作权限
SQL> revoke all on dept from sophie;

三、角色管理
角色是各种权限的集合。
1.查看角色

SQL> select * from dba_roles;

2.创建角色
【语法】

CREATE ROLE role_name [NOT IDENTIFIED|IDENTIFIED {BY password | EXTERNALLY |GLOBALLY |USING package }]

role_name:	 角色名称
NOT IDENTIFIED:	 在激活角色时不需要密码验证
IDENTIFIED:	 在激活角色时需要密码验证
BY password:	 设置激活角色时的验证密码
USING package:	 创建应用角色，该角色只能由应用通过授权的package激活
EXTERNALLY:	 说明角色在激活前，必须通过外部服务如操作系统或第三方服务授权
GLOBALLY:	 当使用SET ROLE激活角色时，用户必须通过企业路径服务授权来使用角色

【例子】

#创建角色mk_clerk，该角色在激活时不需要密码
SQL> create role mk_clerk;
#创建角色at_clerk，该角色在激活时需要密码
SQL> create role at_clerk identified by rmb;
#创建角色manage，该角色使用外部服务标识
SQL> create role manager identified by externally;
#通过数据字典dba_roles来查看角色信息
SQL> select * from dba_roles where role in ('MK_CLERK','AT_CLERK','MANAGER');

3.修改角色
【语法】

ALTER ROLE role_name [NOT IDENTIFIED|IDENTIFIED {BY password | EXTERNALLY |GLOBALLY |USING package }]

【例子】

#修改角色mk_clerk使用外部服务验证
SQL> alter role mk_clerk identified by externally;
#修改角色at_clerk不需要密码验证
SQL> alter role mk_clerk not identified;
#修改角色manager需要密码验证
SQL> alter role manager identified by rmb;
#通过数据字典dba_roles来查看角色信息
SQL> select * from dba_roles where role in ('MK_CLERK','AT_CLERK','MANAGER');

4.赋予角色权限
【语法】

GRANT 权限 | 角色 TO 角色名

【例子】

#为角色at_clerk赋予权限
SQL> grant create session,select any table,create view to at_clerk;
#验证角色at_clerk的权限信息
SQL> select * form role_sys_privs where role = 'AT_CLERK';
#将权限和角色at_clerk授予角色manager
SQL> grant create any table,at_clerk to manager;
#查看角色manager具有的系统权限
SQL> select * from role_sys_privs where role = 'MANAGER';
#通过数据字典dba_role_privs查看角色授予信息
SQL> select * from dba_role_privs where granted_role = 'AT_CLERK';

5.赋予用户角色
【语法】

GRANT role [,role ] ... 
TO {user | role |public} | [, {user |role |public }] ...
[WITH ADMIN OPTION]

【例子】

#创建用户clerk和mymanager
SQL> create user clerk identified by 123;
SQL> create user mymanager identified by 123;
#验证是否成功创建用户
SQL> select username,created from dba_users where username in ('CLERK','MYMANAGER');
#将角色manager赋予用户mymanager
SQL> grant manager to mymanager with admin option;
#验证manager角色授予用户信息
SQL> select * from dba_role_privs where granted_role = 'MANAGER';
#使用被赋予manager角色的用户mymanager登录数据库
SQL> conn mymanager/123;
#查看用户mymanager的会话级权限
SQL> select * from session_privs;
#在mymanager模式下再将角色manager赋予用户clerk
SQL> grant manager to clerk;
#使用新用户clerk登录数据库
SQL> conn clerk/123;
#查看clerk用户的角色信息
SQL> select * fromm user_role_privs;

6.默认角色

#将角色at_clerk赋予用户clerk
SQL> conn system/oracle;
SQL> grant at_clerk to clerk;
#查看用户clerk被赋予的角色信息
SQL> select * from dba_role_privs where grantee = 'CLERK';
#将角色manager设置为非默认角色
SQL> alter user clerk default role all except manager;
#验证角色manager是否为非默认角色
SQL> select * from dba_role_privs where grantee = 'CLERK';
#将用户clerk的所有角色设置为非默认角色
SQL> alter user clerk default role none;
#使用system用户登录数据库并查看用户clerk的角色信息
SQL> select * from dba_role_privs where grantee = 'CLERK';
#将用户clerk的角色at_clerk设置为默认角色
SQL> alter user clerk default role at clerk;
#验证是否将角色at_clerk设置为用户clerk的默认角色
SQL> select * from dba_role_privs where grantee = 'CLERK';
#将赋予用户的所有角色设置为默认角色
SQL> conn system/oracle;
SQL> alter user clerk default role all;
SQL> select * from dba_role_privs where grantee = 'CLERK';

7.禁止和激活角色
禁止角色是回收角色具有的权限，激活角色是赋予用户角色的权限。

#查看用户clerk的用户权限
SQL> conn clerk/123;
SQL> select * from session_privs;
#禁止用户的所有角色
SQL> set role none;
#在禁止所有角色后查询用户权限
SQL> select * from session_privs;
#激活用户clerk的角色at_clerk
SQL> set role at_clerk;
#查询用户clerk是否具有角色at_clerk的权限
SQL> select * from session_privs;
#激活设置了密码验证的角色MANAGER
SQL> set role_manager identified by rmb;
#查看用户clerk的权限
SQL> select * from session_privs;

8.回收和删除角色

#查看创建的角色信息
SQL> conn system/oracle;
SQL> select * from dba_role where role in ('AT_CLERK','MANAGER');
#查看角色at_clerk和manager赋予的用户信息
SQL> select * fro dba_role_privs where granted_role in ('AT_CLERK','MANAGER') order by granted_role;
#回收用户clerk的at_clerk角色
SQL> revoke at_clerk from clerk;
#验证是否正确回收用户clerk的角色at_clerk
SQL> select * from dba_role_privs where granted_role = 'AT_CLERK';
#删除角色at_clerk
SQL> drop role at_clerk;
#验证角色at_clerk是否删除
SQL> select * from dba_roles where role in ('AT_CLERK','MANAGER');
#将角色授予所有用户
SQL> grant manager to public;
#查看角色manager的赋予用户信息
SQL> select * from dba_role_privs where granted_role in ('MANAGER','PUBLIC');
#回收授予PUBLIC的MANAGER角色
SQL> revoke manager from public;
#查询是否成功回收授予PUBLIC的角色manager
SQL> select * from dba_role_privs where granted_role in('MANAGER','PUBLIC');

9.oracle预定义角色
AQ_ADMINISTRATOR_ROLE: 管理QUEUE的管理员角色
CONNECT: 连接数据库权限
DBA: 数据库管理员权限
EXP_FULL_DATABASE: 导出数据库权限
IMP_FULL_DATABASE: 导入数据库权限
JAVADEBUGPRIV: 调试Java程序权限
MGMT_USER: 创建会话和创建触发器权限
OEM_ADVISOR: 执行OEM顾问的权限
OLAP_DBA: 执行联机事务处理时的DBA权限
OLAP_USER: 执行联机事务处理时的USER权限
RECOVERY_CATALOG_OWNER: 恢复数据字典
RESOURCE: 创建一系列数据库对象的权限
SCHEDULER_ADMIN: 管理各种调度的权限，如创建任务、执行程序等

#使用system用户登录数据库
SQL> conn system/oracle as sysdba;
#查询角色exp_full_database的权限信息
SQL> select * from role_sys_privs where role = 'EXP_FULL_DATABASE' order by privilege;

参考资料

[1] 林树泽.Oracle 11g R2 DBA操作指南[M].北京：清华大学出版社，2013

[2] Oracle Profile 使用详解

[3] Oracle系统中的profile

[4] Oracle Profile 使用详解

[5] Oracle 用户管理

[6] Oracle用户、权限、角色管理

[7] Oracle 用户权限管理方法

[8] oracle用户权限、角色管理详解

[9] ORACLE用户权限管理

[10] oracle用户权限、profile及角色的管理

[11] Oracle--用户管理与权限分配

[12] Oracle用户权限表的管理方法

[13] Oracle用户与权限学习总结

[14] Oracle数据库的权限、用户、角色管理

[15] oracle用户创建及权限设置

[16] Oracle新建用户、角色，授权，建表空间的sql语句

[17] Oracle权限管理详解(原创)

[18] Oracle权限

[19] Oracle 系统权限详细列表

[20] Oracle用户权限

[21] Oracle系统权限的分类

[22] Oracle用户、授权、角色管理

[21] Oracle 角色及权限

[22] Oracle角色

[23] Oracle角色

[24] oracle角色管理

[25] 如何查看oracle用户具有的权限和角色

[26] 如何查看某个角色被授予的权限Oracle

[27] Oracle的用户、角色和权限