角色相关

角色相关视图:

1.role_sys_privs

查看角色用户的系统权限

 Select *
  From Role_Sys_Privs a
 Where a.ROLE In('CONNECT','RESOURCE');

ROLE     PRIVILEGE         ADM
------------------------------ ---------------------------------------- ---

CONNECT  CREATE SESSION    NO
RESOURCE CREATE SEQUENCE   NO
RESOURCE CREATE TRIGGER    NO
RESOURCE CREATE CLUSTER    NO
RESOURCE CREATE PROCEDURE  NO
RESOURCE CREATE TYPE       NO
RESOURCE CREATE OPERATOR   NO
RESOURCE CREATE TABLE      NO
RESOURCE CREATE INDEXTYPE  NO

2.role_role_privs

查询某个角色所拥有的角色

SQL> Select * From role_role_privs a
  2 Where a.role = 'DBA';

ROLE   GRANTED_ROLE                ADM
------------------------------ ------------------------------ ---

DBA OLAP_DBA                       NO
DBA SCHEDULER_ADMIN                YES
DBA DATAPUMP_IMP_FULL_DATABASE     NO
DBA OLAP_XS_ADMIN                  NO
DBA DELETE_CATALOG_ROLE            YES
DBA EXECUTE_CATALOG_ROLE           YES
DBA PLUSTRACE                      YES
DBA WM_ADMIN_ROLE                  NO
DBA EXP_FULL_DATABASE              NO
DBA SELECT_CATALOG_ROLE            YES
DBA JAVA_DEPLOY                    NO
DBA GATHER_SYSTEM_STATISTICS       NO
DBA XDB_SET_INVOKER                NO
DBA JAVA_ADMIN                     NO
DBA DATAPUMP_EXP_FULL_DATABASE     NO
DBA XDBADMIN                       NO
DBA IMP_FULL_DATABASE              NO

3.role_tab_privs

查询某个角色所用户对表(select/insert/update/delete),过程(execute)的相应权限

SQL> Select Role,owner,table_name,privilege
  2 From role_tab_privs a
  3 Where a.role = 'GATHER_SYSTEM_STATISTICS';

ROLE OWNER TABLE_NAME PRIVILEGE
------------------------- ----- ---------- ---------

GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ UPDATE
GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ SELECT
GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ DELETE
GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ INSERT

4.查看某个用户拥有的角色

SQL> select grantee,granted_role from dba_role_privs where grantee='hxl';
GRANTEE                 GRANTED_ROLE
----------- ------------------------------
HXL                     CONNECT
HXL                     RESOURCE

5.创建用户开发的用户并赋予相应的角色

create user app_dev identified by app_dev
default tablespace tps_test;

alter user app_dev
  quota unlimited on tps_test;

-- 相应开发角色

grant create sequence to app_dev;
grant create trigger to app_dev;
grant create cluster to app_dev;
grant create procedure to app_dev;
grant create type to app_dev;
grant create session to app_dev;
grant create operator to app_dev;
grant create table to app_dev;
grant create indextype to app_dev;