如何在 Oracle 中显示用户的所有权限？

有人能告诉我如何在 sql 控制台中显示特定用户的所有特权/规则吗？

524572 次浏览
小开
最佳答案

You can try these below views.

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;

DBA 和其他高级用户可以找到授予具有相同视图的 DBA_版本的其他用户的特权。它们被 文件覆盖。

这些视图只显示授予用户的 直接权限。寻找 所有的特权，包括那些通过角色间接授予的特权，需要更复杂的递归 SQL 语句:

select * from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER' order by 1,2,3;
select * from dba_sys_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3;
select * from dba_tab_privs  where grantee = '&USER' or grantee in (select granted_role from dba_role_privs connect by prior granted_role = grantee start with grantee = '&USER') order by 1,2,3,4;
小开

There are various scripts floating around that will do that depending on how crazy you want to get. I would personally use Pete Finnigan's 查找所有的脚本.

如果您想自己编写它，那么查询将变得相当具有挑战性。用户可以被授予在 DBA_SYS_PRIVS中可见的系统特权。它们可以被授予在 DBA_TAB_PRIVS中可见的对象特权。它们可以被授予在 DBA_ROLE_PRIVS中可见的角色(角色可以是默认的或非默认的，也可以需要密码，所以仅仅因为用户被授予了一个角色并不意味着用户可以使用他通过默认角色获得的特权)。但是这些角色可以反过来被授予系统特权、对象特权和其他角色，这些角色可以通过查看 ROLE_SYS_PRIVS、 ROLE_TAB_PRIVS和 ROLE_ROLE_PRIVS来查看。Pete 的脚本遍历这些关系以显示最终流向用户的所有特权。

小开

Another useful resource:

Http://psoug.org/reference/roles.html

  • DBA _ SYS _ PRIVS
  • DBA_TAB_PRIVS
  • DBA _ ROLE _ PRIVS
小开

您可以使用下面的代码从所有用户获取所有权限列表。

select * from dba_sys_privs
小开

虽然 Raviteja Vutukuri 的回答可以工作，而且组装起来也很快，但是它对于改变过滤器并不特别灵活，如果您希望以编程方式进行某些操作，那么它也没有太大的帮助。因此，我提出了自己的疑问:

SELECT
PRIVILEGE,
OBJ_OWNER,
OBJ_NAME,
USERNAME,
LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lists the sources of the permission
MAX(ADMIN_OR_GRANT_OPT) AS ADMIN_OR_GRANT_OPT, -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
MAX(HIERARCHY_OPT) AS HIERARCHY_OPT -- MAX acts as a Boolean OR by picking 'YES' over 'NO'
FROM (
-- Gets all roles a user has, even inherited ones
WITH ALL_ROLES_FOR_USER AS (
SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
)
SELECT
PRIVILEGE,
OBJ_OWNER,
OBJ_NAME,
USERNAME,
REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
ADMIN_OR_GRANT_OPT,
HIERARCHY_OPT
FROM (
-- System privileges granted directly to users
SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
FROM DBA_SYS_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION ALL
-- System privileges granted users through roles
SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
FROM DBA_SYS_PRIVS
JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
UNION ALL
-- Object privileges granted directly to users
SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION ALL
-- Object privileges granted users through roles
SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, ALL_ROLES_FOR_USER.GRANTED_ROLE AS GRANT_TARGET, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS
JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
) ALL_USER_PRIVS
-- Adjust your filter here
WHERE USERNAME = 'USER_NAME'
) DISTINCT_USER_PRIVS
GROUP BY
PRIVILEGE,
OBJ_OWNER,
OBJ_NAME,
USERNAME
;

优点:

  • I easily can filter by a lot of different pieces of information, like the object, the privilege, whether it's through a particular role, etc. just by changing that one WHERE clause.
  • 这是一个简单的查询，意味着我不必在脑海中一起构建结果。
  • 它解决了是否可以授予特权的问题，以及它是否包含子对象的特权(“层次化”部分)跨特权的不同来源。
  • 很容易看到我需要做的一切来撤销特权，因为它列出了特权的所有来源。
  • 它将表和系统特权组合成一个一致的视图，允许我们一下子列出用户的 所有特权。
  • 它是一个 query，而不是一个将所有这些输出到 DBMS_OUTPUT或其他什么的函数(与 Pete Finnigan 的链接脚本相比)。这使得它对于编程使用和导出非常有用。
  • 该过滤器不会重复，它只出现一次。这使得更改变得更容易。
  • 如果您需要通过每个单独的 GRANT检查子查询，那么可以很容易地提取它。
小开

To show all privileges:

从 system _ rights _ map 中选择 name;

小开

更简单的单查询 Oracle 版本。

WITH data
AS (SELECT granted_role
FROM   dba_role_privs
CONNECT BY PRIOR granted_role = grantee
START WITH grantee = '&USER')
SELECT 'SYSTEM'     typ,
grantee      grantee,
privilege    priv,
admin_option ad,
'--'         tabnm,
'--'         colnm,
'--'         owner
FROM   dba_sys_privs
WHERE  grantee = '&USER'
OR grantee IN (SELECT granted_role
FROM   data)
UNION
SELECT 'TABLE'    typ,
grantee    grantee,
privilege  priv,
grantable  ad,
table_name tabnm,
'--'       colnm,
owner      owner
FROM   dba_tab_privs
WHERE  grantee = '&USER'
OR grantee IN (SELECT granted_role
FROM   data)
ORDER  BY 1;

提交答案