角色相关

hxl

角色相关视图:

1.role_sys_privs

查看角色用户的系统权限

1.  Select *
   
2.   From Role_Sys_Privs a
   
3.  Where a.ROLE In('CONNECT','RESOURCE');
   
4. 
   
5. ROLE     PRIVILEGE         ADM
   
6. ------------------------------ ---------------------------------------- ---
   
7. 
   
8. CONNECT  CREATE SESSION    NO
   
9. RESOURCE CREATE SEQUENCE   NO
   
10. RESOURCE CREATE TRIGGER    NO
    
11. RESOURCE CREATE CLUSTER    NO
    
12. RESOURCE CREATE PROCEDURE  NO
    
13. RESOURCE CREATE TYPE       NO
    
14. RESOURCE CREATE OPERATOR   NO
    
15. RESOURCE CREATE TABLE      NO
    
16. RESOURCE CREATE INDEXTYPE  NO

2.role_role_privs

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

1. SQL> Select * From role_role_privs a
   
2.   2 Where a.role = 'DBA';
   
3. 
   
4. ROLE   GRANTED_ROLE                ADM
   
5. ------------------------------ ------------------------------ ---
   
6. 
   
7. DBA OLAP_DBA                       NO
   
8. DBA SCHEDULER_ADMIN                YES
   
9. DBA DATAPUMP_IMP_FULL_DATABASE     NO
   
10. DBA OLAP_XS_ADMIN                  NO
    
11. DBA DELETE_CATALOG_ROLE            YES
    
12. DBA EXECUTE_CATALOG_ROLE           YES
    
13. DBA PLUSTRACE                      YES
    
14. DBA WM_ADMIN_ROLE                  NO
    
15. DBA EXP_FULL_DATABASE              NO
    
16. DBA SELECT_CATALOG_ROLE            YES
    
17. DBA JAVA_DEPLOY                    NO
    
18. DBA GATHER_SYSTEM_STATISTICS       NO
    
19. DBA XDB_SET_INVOKER                NO
    
20. DBA JAVA_ADMIN                     NO
    
21. DBA DATAPUMP_EXP_FULL_DATABASE     NO
    
22. DBA XDBADMIN                       NO
    
23. DBA IMP_FULL_DATABASE              NO

3.role_tab_privs

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

1. SQL> Select Role,owner,table_name,privilege
   
2.   2 From role_tab_privs a
   
3.   3 Where a.role = 'GATHER_SYSTEM_STATISTICS';
   
4. 
   
5. ROLE OWNER TABLE_NAME PRIVILEGE
   
6. ------------------------- ----- ---------- ---------
   
7. 
   
8. GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ UPDATE
   
9. GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ SELECT
   
10. GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ DELETE
    
11. GATHER_SYSTEM_STATISTICS SYS AUX_STATS$ INSERT

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

1. create user app_dev identified by app_dev
   
2. default tablespace tps_test;
   
3. 
   
4. alter user app_dev
   
5.   quota unlimited on tps_test;
   
6.   
   
7. -- 相应开发角色
   
8. 
   
9. grant create sequence to app_dev;
   
10. grant create trigger to app_dev;
    
11. grant create cluster to app_dev;
    
12. grant create procedure to app_dev;
    
13. grant create type to app_dev;
    
14. grant create session to app_dev;
    
15. grant create operator to app_dev;
    
16. grant create table to app_dev;
    
17. grant create indextype to app_dev;