ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Show Table and System Privileges[akadia]

Show Table and System Privileges[akadia]

原创 Linux操作系统 作者:jlandzpa 时间:2019-04-28 21:03:05 0 删除 编辑

It is normally difficult to list all privileges and roles assigned to a specific user in one select, since a privilege can be assigned to a role, which can be assigned to another role, which in turn can be assigned to another role, which can be granted a user. The following view lists all of your roles, all of the roles granted to those roles, and so on:

CREATE VIEW user_role_hierarchy
AS
SELECT u2.name granted_role
FROM (SELECT * FROM sys.sysauth$
CONNECT BY PRIOR privilege# = grantee#
START WITH grantee# = uid OR grantee# = 1) sa, sys.user$ u2
WHERE u2.user#=sa.privilege#
UNION ALL SELECT user FROM DUAL
UNION ALL SELECT 'PUBLIC' FROM DUAL
/

GRANT SELECT ON user_role_hierarchy TO PUBLIC;

That view is based on the existing data dictionary view, and if you create the new view as SYS, it will show the currently logged-in users their roles (and all of the roles granted to those roles, and so on). You'll find this view to be very fast.

To get all of your table privileges, you should do this:

SELECT DISTINCT privilege, owner, table_name
FROM dba_tab_privs
WHERE grantee IN (SELECT * FROM user_role_hierarchy);

And to get all of your system privileges, you should do this:

select distinct privilege
from dba_sys_privs
where grantee in (select * from user_role_hierarchy);


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/936/viewspace-60575/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2001-10-12

  • 博文量
    110
  • 访问量
    82259