1、角色新建
SQL> create role clerk;
Role created
SQL> create role sales;
Role created
SQL> create role manager;
Role created
SQL> select * from dba_roles where role in('CLERK','SALES','MANAGER');
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CLERK NO NONE
SALES NO NONE
MANAGER NO NONE
2、修改
SQL> ALTER role manager identified by manager;
Role altered
查看
SQL> select * from dba_roles where role in('CLERK','SALES','MANAGER');
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CLERK NO NONE
SALES NO NONE
MANAGER YES PASSWORD
3、授权
SQL> grant create session, create table, create view to clerk;
Grant succeeded
SQL> grant select any table,clerk to manager;
Grant succeeded
查看
SQL> select * from role_sys_privs where role in('CLERK','SALES','MANAGER');
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ -------------------- ------------
CLERK CREATE TABLE NO
CLERK CREATE VIEW NO
CLERK CREATE SESSION NO
MANAGER SELECT ANY TABLE NO
查看授权来源
SQL> select * from dba_role_privs where granted_role like 'CL%';
GRANTEE GRANTED_RO ADMIN_OPTION DEFAULT_ROLE
---------- ---------- ------------ ------------
MANAGER CLERK NO YES
SYS CLERK YES YES
4、默认角色
SQL> alter user cat default role all;
User altered
5、角色回收
revoke manager from pig;
SQL> create role clerk;
Role created
SQL> create role sales;
Role created
SQL> create role manager;
Role created
SQL> select * from dba_roles where role in('CLERK','SALES','MANAGER');
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CLERK NO NONE
SALES NO NONE
MANAGER NO NONE
2、修改
SQL> ALTER role manager identified by manager;
Role altered
查看
SQL> select * from dba_roles where role in('CLERK','SALES','MANAGER');
ROLE PASSWORD_REQUIRED AUTHENTICATION_TYPE
------------------------------ ----------------- -------------------
CLERK NO NONE
SALES NO NONE
MANAGER YES PASSWORD
3、授权
SQL> grant create session, create table, create view to clerk;
Grant succeeded
SQL> grant select any table,clerk to manager;
Grant succeeded
查看
SQL> select * from role_sys_privs where role in('CLERK','SALES','MANAGER');
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ -------------------- ------------
CLERK CREATE TABLE NO
CLERK CREATE VIEW NO
CLERK CREATE SESSION NO
MANAGER SELECT ANY TABLE NO
查看授权来源
SQL> select * from dba_role_privs where granted_role like 'CL%';
GRANTEE GRANTED_RO ADMIN_OPTION DEFAULT_ROLE
---------- ---------- ------------ ------------
MANAGER CLERK NO YES
SYS CLERK YES YES
4、默认角色
SQL> alter user cat default role all;
User altered
5、角色回收
revoke manager from pig;