Role的权限 role_sys_privs not dba_sys_privs

    select * from ( select r.privilege
    from user_role_privs u,role_sys_privs r
    where u.granted_role=r.role
    union all
    select privilege
    from user_sys_privs
    )
    where privilege = 'ANALYZE ANY'
      and rownum < 2;
    
    PRIVILEGE
    ----------------------------------------
    ANALYZE ANY
    


select *from dba_role_privs;
select *from role_sys_privs;
select *from role_tab_privs;


    
    SQL> select r.role, r.privilege
         from user_role_privs u,role_sys_privs r
         where u.granted_role=r.role
         and r.privilege='ANALYZE ANY'
         /
    
    no rows selected
    
    SQL> select privilege
         from user_sys_privs
         where privilege='ANALYZE ANY'
         /
    
    PRIVILEGE
    ----------------------------------------
    ANALYZE ANY
    
    1 row selected.
CHANGES
 

CAUSE
Several default roles were missing necessary privileges :


SQL> select * from USER_ROLE_PRIVS
     order by granted_role;
    
    USERNAME   GRANTED_ROLE   ADM   DEF   OS_
    SYS   OEM_ADVISOR   YES   YES   NO
    SYS   SCHEDULER_ADMIN   YES   YES   NO
    
    2 rows selected.
    
SQL> select * from ROLE_SYS_PRIVS
     order by role;
    
    ROLE              PRIVILEGE                  ADM
    ----------------  -------------------------  ---
    OEM_ADVISOR       ADMINISTER SQL TUNING SET  NO
    OEM_ADVISOR       ADVISOR                    NO
    OEM_ADVISOR       CREATE JOB                 NO
    SCHEDULER_ADMIN   CREATE ANY JOB             YES
    SCHEDULER_ADMIN   CREATE EXTERNAL JOB        YES
    SCHEDULER_ADMIN   CREATE JOB                 YES
    SCHEDULER_ADMIN   EXECUTE ANY CLASS          YES
    SCHEDULER_ADMIN   EXECUTE ANY PROGRAM        YES
    SCHEDULER_ADMIN   MANAGE SCHEDULER           YES
    
    9 rows selected.
    

Roles like DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE are not granted to SYS anymore or are lacking the necessary privileges.

SOLUTION
In a clean created 10G database there should be are 297 privileges granted to the SYS-roles :


SQL> select role, count(*)
from ROLE_SYS_PRIVS
group by role
order by role;

ROLE                           COUNT(*)
------------------------------ ----------
AQ_ADMINISTRATOR_ROLE             6
CONNECT                           1
DBA                             160
EXP_FULL_DATABASE                 8
IMP_FULL_DATABASE                68
JAVADEBUGPRIV                     2
MGMT_USER                         2
OEM_ADVISOR                       3
OEM_MONITOR                       7
OLAP_DBA                         10
OLAP_USER                         5
RECOVERY_CATALOG_OWNER           11
RESOURCE                          8
SCHEDULER_ADMIN                   6

14 rows selected.

  • 3
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值