Oracle查询用户拥有所有系统权限

复制代码
SQL> desc dba_sys_privs;   查询对象拥有的系统权限
 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';对象拥有的角色


GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
HR                             RESOURCE                       NO  YES

SQL> select * from role_sys_privs where ROLE='RESOURCE';  通过角色查找权限
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
--看起来很美好,但是这是等值才返回结果,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'; --一样无法达到效果; --a 通过用户查找拥有的角色 select * from dba_role_privs grantee granted_role -b 通过角色查找拥有的权限 select * from role_sys_privs 1-2 role granted_role -c 通过用户查找拥有的系统权限 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条件执行速度更快 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'; 意义:有些权限不是通过角色单独授予、或者单独授予权限:通过集合更好查找用户有啥权限 ****补充: --什么是对象权限,设计到具体的针对某一个对象的权限; grant select on scott.dept to hr; 查询会有具体对象的权限; 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;
复制代码

 

posted on   数据派  阅读(30)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

统计

点击右上角即可分享
微信分享提示