SQL> desc dba_sys_privs;   查詢(xún)對(duì)象擁有的系統(tǒng)權(quán)限
 Name                                     Null?    Type
 ---------------------------------------- -------- ---------------------------
 GRANTEE                                  NOT NULL VARCHAR2(30)
 PRIVILEGE                                NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                      VARCHAR2(3)
SQL> select * from dba_sys_privs where grantee='HR';
GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
HR                             UNLIMITED TABLESPACE                     NO
HR                             CREATE SESSION                           NO
HR                             CREATE TABLE                             NO
SQL> desc dba_role_privs;
 Name                                     Null?    Type
 ---------------------------------------- -------- ---------------------------
 USERNAME                                          VARCHAR2(30)
 GRANTED_ROLE                                      VARCHAR2(30)
 ADMIN_OPTION                                      VARCHAR2(3)
 DEFAULT_ROLE                                      VARCHAR2(3)
 OS_GRANTED                                        VARCHAR2(3)
SQL> select * from dba_role_privs where grantee='HR';對(duì)象擁有的角色
GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
HR                             RESOURCE                       NO  YES
SQL> select * from role_sys_privs where ROLE='RESOURCE';  通過(guò)角色查找權(quán)限
ROLE                           PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE PROCEDURE                         NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE TABLE                             NO
SQL>  select a.granted_role,b.privilege,c.privilege
     From dba_role_privs a,role_sys_privs b ,dba_sys_privs c
   Where a.granted_role=b.role and a.grantee=c.grantee and a.grantee='HR
RESOURCE        CREATE SEQUENCE      UNLIMITED TABLESPACE
RESOURCE        CREATE INDEXTYPE     CREATE SESSION
--看起來(lái)很美好，但是這是等值才返回結(jié)果，1=1=1   3*8=24t條記錄
select a.granted_role,b.privilege,c.privilege from dba_role_privs
a join role_sys_privs b on a.granted_role=b.role join dba_sys_privs
c on a.grantee=c.grantee and a.grantee='HR';  --一樣無(wú)法達(dá)到效果；
--a  通過(guò)用戶(hù)查找擁有的角色
select * from dba_role_privs  grantee granted_role
-b  通過(guò)角色查找擁有的權(quán)限
select * from role_sys_privs    1-2 role  granted_role
-c 通過(guò)用戶(hù)查找擁有的系統(tǒng)權(quán)限
select * from dba_sys_privs  1-3 grantee 
--思路1+2=2 => 2+3=總
select * from (select a.grantee,b.privilege from
 dba_role_privs a join role_sys_privs b on a.granted_role=b.role
  union
   select c.grantee,c.privilege from dba_sys_privs c)
    where grantee='HR';
--于上一樣--where條件執(zhí)行速度更快
select a.grantee,b.privilege from
 dba_role_privs a join role_sys_privs b 
    on a.granted_role=b.role where grantee='HR'
  union 
   select c.grantee,c.privilege from dba_sys_privs c where grantee='HR';
意義：有些權(quán)限不是通過(guò)角色單獨(dú)授予、或者單獨(dú)授予權(quán)限：通過(guò)集合更好查找用戶(hù)有啥權(quán)限
 ****補(bǔ)充：
--什么是對(duì)象權(quán)限，設(shè)計(jì)到具體的針對(duì)某一個(gè)對(duì)象的權(quán)限；
grant select on scott.dept to hr;
查詢(xún)會(huì)有具體對(duì)象的權(quán)限；
select * from dba_tab_privs where grantee='HR';
HR         SYS        YANG       SYS        WRITE      NO  NO
HR         SYS        YANG       SYS        READ       NO  NO
HR         SYS        DBMS_STATS SYS        EXECUTE    NO  NO
HR         SCOTT      DEPT       SCOTT      SELECT     NO  NO
回收： revoke execute on sys.DBMS_STATS from hr;