--创建角色
--Create Role role_name [not identified | identified by role_password];
SQL> create role role1 not identified ;
Role created
SQL> create role role2 identified by a123;
Role created
--修改用户默认角色
alter user user_name
default role role_name1,...,role_nameN | all [except role_name1,...,role_nameN] |None
全局有效
--禁用、启用角色
set role role_name1,...,role_nameN | all [except role_name1,...,role_nameN] |None
session_roles,在当前session有效
--修改角色
--Alter Role role_name [not identified | identified by role_password];
SQL> alter role role1 identified by role1;
Role altered
SQL> alter role role2 not identified;
Role altered
--删除角色
SQL> Drop Role role1;
Role dropped
--查用户权限
SQL> Create Role Role1;
Role created
SQL> Grant Create Session,Create Table,Create View To Role1;
Grant succeeded
SQL> Grant Update On scott.dept To Role1;
Grant succeeded
SQL> Create User u Identified By u ;
User created
SQL> Grant Unlimited Tablespace To u;
Grant succeeded
SQL> Grant Select On scott.emp To u;
Grant succeeded
SQL> Grant role1 To u;
Grant succeeded
SQL> Select * From dba_sys_privs Where grantee='U';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
U UNLIMITED TABLESPACE NO
SQL> Select * From dba_tab_privs Where grantee='U';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
U SCOTT EMP SCOTT SELECT NO NO
SQL> Select * From Dba_Role_Privs Where grantee='U';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
U ROLE1 NO YES
SQL> Select * From Role_Sys_Privs Where Role='ROLE1';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ROLE1 CREATE SESSION NO
ROLE1 CREATE TABLE NO
ROLE1 CREATE VIEW NO
SQL> Select * From Role_TAB_Privs Where Role='ROLE1';
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRANTABLE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---------
ROLE1 SCOTT DEPT UPDATE NO
--Create Role role_name [not identified | identified by role_password];
SQL> create role role1 not identified ;
Role created
SQL> create role role2 identified by a123;
Role created
--修改用户默认角色
alter user user_name
default role role_name1,...,role_nameN | all [except role_name1,...,role_nameN] |None
全局有效
--禁用、启用角色
set role role_name1,...,role_nameN | all [except role_name1,...,role_nameN] |None
session_roles,在当前session有效
--修改角色
--Alter Role role_name [not identified | identified by role_password];
SQL> alter role role1 identified by role1;
Role altered
SQL> alter role role2 not identified;
Role altered
--删除角色
SQL> Drop Role role1;
Role dropped
--查用户权限
SQL> Create Role Role1;
Role created
SQL> Grant Create Session,Create Table,Create View To Role1;
Grant succeeded
SQL> Grant Update On scott.dept To Role1;
Grant succeeded
SQL> Create User u Identified By u ;
User created
SQL> Grant Unlimited Tablespace To u;
Grant succeeded
SQL> Grant Select On scott.emp To u;
Grant succeeded
SQL> Grant role1 To u;
Grant succeeded
SQL> Select * From dba_sys_privs Where grantee='U';
GRANTEE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
U UNLIMITED TABLESPACE NO
SQL> Select * From dba_tab_privs Where grantee='U';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
U SCOTT EMP SCOTT SELECT NO NO
SQL> Select * From Dba_Role_Privs Where grantee='U';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------------------------ ------------------------------ ------------ ------------
U ROLE1 NO YES
SQL> Select * From Role_Sys_Privs Where Role='ROLE1';
ROLE PRIVILEGE ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
ROLE1 CREATE SESSION NO
ROLE1 CREATE TABLE NO
ROLE1 CREATE VIEW NO
SQL> Select * From Role_TAB_Privs Where Role='ROLE1';
ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRANTABLE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------- ---------
ROLE1 SCOTT DEPT UPDATE NO