role_sys_privs与dba_sys_privs区别

Question

为什么CONNECT角色查出来不一样？而查找DBA角色的结果却一样的。
SQL> select * from role_sys_privs where role='RESOURCE';SQL> select * from role_sys_privs where role='RESOURCE';
 
ROLE                           PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
SQL> select * from dba_sys_privs where grantee='RESOURCE';GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
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

Answer 1

select * from role_sys_privs where role='RESOURCE'查询的是当前用户下角色RESOURCE拥有的系统权限；
select * from dba_sys_privs where grantee='RESOURCE';查询的是数据库下角色RESOURCE拥有的系统权限；
你出现这种情况，确认一下你connect 上的用户是否已经赋予了RESOURCE的角色？

Answer 2

CONNECT角色，就只有CREATE SESSION一个权限。
你的当前用户没有赋于RESOURCE角色。

Answer 3

SQL> show user;
User is "SYS"
SQL> select * from dba_sys_privs where grantee='RESOURCE';
 
GRANTEE                        PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO
 
8 rows selected
 
SQL> select * from role_sys_privs where role='RESOURCE';
 
ROLE                           PRIVILEGE                                ADMIN_OPTION
------------------------------ ---------------------------------------- ------------
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO
 
8 rows selected