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;
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库