手机版
你好,游客 登录 注册 搜索
背景:
阅读新闻

Oracle 11g用户权限管理学习笔记

[日期:2015-10-02] 来源:Linux社区  作者:黄杉 [字体: ]

总结工作中经常用到一些Oracle 11g用户权限管理学习笔记:

1、查看数据库里所有用户各自拥有的角色:
select * from (select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee ) a
where exists (select 1 from dba_users b where b.username=a.username)
order by 1,2
;

2、查看数据库所有用户的系统权限:
select d.username,d.privilege from
(select a.username,b.privilege from
(select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee) a,
(select grantee,privilege from dba_sys_privs) b
where a.granted_role=b.grantee
union
select grantee,privilege from dba_sys_privs) d
where exists((select 1 from dba_users c where d.username=c.username))
order by 1,2;

3、查看数据库所有用户的表权限
select d.username,d.privilege,d.owner,d.table_name from
(select a.username,b.privilege,b.owner,b.table_name from
(select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee) a,
(select grantee,owner,table_name,privilege from dba_tab_privs) b
where a.granted_role=b.grantee
union
select grantee,privilege,owner,table_name from dba_tab_privs) d
where exists((select 1 from dba_users c where d.username=c.username))
order by 1,2;

4、如何查看拥有DBA角色的用户?
常规做法,一般是直接查询DBA_ROLE_PRIVS视图。查询语句如下:
select grantee,granted_role from dba_role_privs where granted_role=’DBA’;
这会漏掉用户,测试如下:

正确的查询应按如下语句查询:
select * from (select distinct connect_by_root grantee username,granted_role
from dba_role_privs
connect by prior granted_role =grantee ) a
where a.granted_role=’DBA’;

5、查看拥有SELECT ANY TABLE权限的用户
对于授予角色的,看看都谁有查询所有表的权限
select distinct rp.grantee  from dba_role_privs rp ,dba_sys_privs sp
where rp.granted_role = sp.grantee
and sp.privilege like 'SELECT ANY TABLE%'

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址http://www.linuxidc.com/Linux/2015-10/123735.htm

linux
相关资讯       Oracle用户权限  Oracle 11g用户权限 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款