角色，用户，权限

已SYSTEM用户为例，查询所属的角色及拥有的权限

 

所属角色：

SQL> select grantee,granted_role from dba_role_privs where grantee='SYSTEM';

GRANTEE                        GRANTED_ROLE
----------------------------   ------------------------------
SYSTEM                         AQ_ADMINISTRATOR_ROLE
SYSTEM                         DBA

角色权限：

SQL> select * from role_sys_privs  where role='DBA';

ROLE                          PRIVILEGE                        ADM
-----------------             ------------------------         ----------------
DBA                           CREATE SESSION                   YES
DBA                           ALTER SESSION                    YES
......

用户权限：

SQL> select * from dba_sys_privs where grantee='SYSTEM';

GRANTEE                        PRIVILEGE                          ADM
--------------------------     ------------------------           -----------------
SYSTEM                         GLOBAL QUERYREWRITE                NO
SYSTEM                         CREATE MATERIALIZEDVIEW            NO
SYSTEM                         CREATE TABLE                       NO
SYSTEM                         UNLIMITEDTABLESPACE                YES
SYSTEM                         SELECT ANY TABLE                   NO

查看用户的所有权限：

select privilege from dba_sys_privs where grantee='SYSTEM' 

union 

select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SYSTEM' );