我该如何列出用户获得的所有授权？

Question

我需要查看Oracle数据库中的所有授权。

我使用了TOAD功能来比较模式，但它没有显示临时表授权等内容。因此，我的问题是：

如何列出Oracle数据库中的所有授权？

- guerda

Answer 1

如果你想获取更多的权限而不仅仅是直接表格授权（例如通过角色授权、系统特权如select any table等），以下是一些额外的查询：

用户的系统特权：

SELECT PRIVILEGE
  FROM sys.dba_sys_privs
 WHERE grantee = <theUser>
UNION
SELECT PRIVILEGE 
  FROM dba_role_privs rp JOIN role_sys_privs rsp ON (rp.granted_role = rsp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY 1;

直接授予表/视图的权限：

SELECT owner, table_name, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv 
  FROM table_privileges
 WHERE grantee = <theUser>
 ORDER BY owner, table_name;

对表/视图的间接授权：

SELECT DISTINCT owner, table_name, PRIVILEGE 
  FROM dba_role_privs rp JOIN role_tab_privs rtp ON (rp.granted_role = rtp.role)
 WHERE rp.grantee = <theUser>
 ORDER BY owner, table_name;

- DCookie

---

您可能没有查看sys.dba_sys_privs表的权限。 - Hannes

绝对正确。请咨询您的数据库管理员。如果他们拒绝，可能是因为存在合法的安全顾虑。查看这些视图的内容会向用户提供他们无法获得的信息。 - DCookie

将role_role_privs表左连接，然后使用CONNECT BY PRIOR granted_role = role递归到传递角色权限会很有趣... - Lukas Eder

Answer 2

假设您想列出特定用户已收到的所有对象上的授权：

select * from all_tab_privs_recd where grantee = 'your user'

这不会返回用户拥有的对象。如果需要这些对象，请使用all_tab_privs视图。

- Juris

Answer 3

抱歉大家，但是如果你在不同的用户下运行选择语句（例如SYS用户），从all_tab_privs_recd中选择授权人为“your user”的内容将不会得到除公共授权和当前用户授权之外的任何输出。正如文档所说的那样，ALL_TAB_PRIVS_RECD描述了以下类型的授权：

Object grants for which the current user is the grantee
Object grants for which an enabled role or PUBLIC is the grantee

所以，如果你是一位DBA并想列出一个特定用户（非SYS本身）的所有对象授权，你不能使用那个系统视图。

在这种情况下，你必须执行一个更复杂的查询。这里是从TOAD中提取（跟踪）的一个查询，用于选择一个特定用户的所有对象授权：

select tpm.name privilege,
       decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
       ue.name grantee,
       ur.name grantor,
       u.name owner,
       decode(o.TYPE#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
                       4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
                       7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
                       11, 'PACKAGE BODY', 12, 'TRIGGER',
                       13, 'TYPE', 14, 'TYPE BODY',
                       19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
                       22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
                       28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
                       32, 'INDEXTYPE', 33, 'OPERATOR',
                       34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
                       40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
                       42, 'MATERIALIZED VIEW',
                       43, 'DIMENSION',
                       44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
                       66, 'JOB', 67, 'PROGRAM', 74, 'SCHEDULE',
                       48, 'CONSUMER GROUP',
                       51, 'SUBSCRIPTION', 52, 'LOCATION',
                       55, 'XML SCHEMA', 56, 'JAVA DATA',
                       57, 'EDITION', 59, 'RULE',
                       62, 'EVALUATION CONTEXT',
                       'UNDEFINED') object_type,
       o.name object_name,
       '' column_name
        from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
             table_privilege_map tpm
        where oa.obj# = o.obj#
          and oa.grantor# = ur.user#
          and oa.grantee# = ue.user#
          and oa.col# is null
          and oa.privilege# = tpm.privilege
          and u.user# = o.owner#
          and o.TYPE# in (2, 4, 6, 9, 7, 8, 42, 23, 22, 13, 33, 32, 66, 67, 74, 57)
  and ue.name = 'your user'
  and bitand (o.flags, 128) = 0
union all -- column level grants
select tpm.name privilege,
       decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
       ue.name grantee,
       ur.name grantor,
       u.name owner,
       decode(o.TYPE#, 2, 'TABLE', 4, 'VIEW', 42, 'MATERIALIZED VIEW') object_type,
       o.name object_name,
       c.name column_name
from sys.objauth$ oa, sys.obj$ o, sys.user$ u, sys.user$ ur, sys.user$ ue,
     sys.col$ c, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and oa.obj# = c.obj#
  and oa.col# = c.col#
  and bitand(c.property, 32) = 0 /* not hidden column */
  and oa.col# is not null
  and oa.privilege# = tpm.privilege
  and u.user# = o.owner#
  and o.TYPE# in (2, 4, 42)
  and ue.name = 'your user'
  and bitand (o.flags, 128) = 0;

这将列出您指定的用户的所有对象授权（包括列授权）。如果您不想要列级别的授权，则删除选择语句中以“union”子句开头的所有部分。

更新：研究文档后，我找到了另一个以更简单方式列出所有授权的视图：

select * from DBA_TAB_PRIVS where grantee = 'your user';

请记住，在 Oracle 中没有名为 DBA_TAB_PRIVS_RECD 的视图。

没有

- Oleksii Cherkas

Answer 4

我知道的最全面和可靠的方法仍然是使用DBMS_METADATA：

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', :username ) from dual;
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', :username ) from dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', :username ) from dual;

（用户名必须匹配大小写，因此通常应该是大写）

虽然很有趣的回答。

- Matteo Steccolini

---

用户名必须与数据库中存储的大小写匹配。对于未加引号的标识符，Oracle会隐式地将标识符转换为大写，因此通常用户名应该是大写的；但是，如果用户名使用了带引号的标识符（这被认为是不良实践，但是可能存在），那么您必须匹配引号标识符中使用的确切大小写（可以是大写、小写或混合大小写）。 - MT0

我在OBJECT_GRANT上遇到了ORA-31608错误。 - Simon Logic

@SimonLogic 这意味着该用户没有此类授权（或该用户不存在，或者-很可能是-您当前拥有的特权无法查看现有的授权）。 - Matteo Steccolini

@MatteoSteccolini 是的，你是对的。每个用户都没有对象授权。顺便说一下，你的方法需要在读写模式下打开数据库（我们正在使用可插拔式数据库）。 - Simon Logic

Answer 5

select distinct 'GRANT '||privilege||' ON '||OWNER||'.'||TABLE_NAME||' TO '||RP.GRANTEE
from DBA_ROLE_PRIVS RP join ROLE_TAB_PRIVS RTP 
on (RP.GRANTED_ROLE = RTP.role)  
where (OWNER in ('YOUR USER') --Change User Name
   OR RP.GRANTEE in ('YOUR USER')) --Change User Name
and RP.GRANTEE not in ('SYS', 'SYSTEM')
;

- Sujit

---

一些解释会有助于这个答案，因为其他人来查找它时会更好理解。 - Andrew Barber

Answer 6

要列出当前用户（连接并拥有会话的用户）被授予的所有系统特权，可以使用以下查询：

select * from USER_SYS_PRIVS where USERNAME = <CURRENT_USER>;

该查询应在当前用户会话中执行，并且用户名必须包含引号。例如：

select * from USER_SYS_PRIVS where USERNAME = 'arash';

- Arash moradabadi

Answer 7

以下查询可以用来获取一个用户的所有权限...只需在第一个查询中提供用户名，即可获得该用户的所有权限

WITH users AS
 (SELECT 'SCHEMA_USER' usr FROM dual),
Roles AS
 (SELECT granted_role
    FROM dba_role_privs rp
    JOIN users
      ON rp.GRANTEE = users.usr
  UNION
  SELECT granted_role
    FROM role_role_privs
   WHERE role IN (SELECT granted_role
                    FROM dba_role_privs rp
                    JOIN users
                      ON rp.GRANTEE = users.usr)),
tab_privilage AS
 (SELECT OWNER, TABLE_NAME, PRIVILEGE
    FROM role_tab_privs rtp
    JOIN roles r
      ON rtp.role = r.granted_role
  UNION
  SELECT OWNER, TABLE_NAME, PRIVILEGE
    FROM Dba_Tab_Privs dtp
    JOIN Users
      ON dtp.grantee = users.usr),
sys_privileges AS
 (SELECT privilege
    FROM dba_sys_privs dsp
    JOIN users
      ON dsp.grantee = users.usr)
SELECT * FROM tab_privilage ORDER BY owner, table_name
--SELECT * FROM sys_privileges

- Pradeep Dewani