How can I list ALL grants a user received?

I need to see all grants on an Oracle DB.

I used the TOAD feature to compare schemas but it does not shows temptable grants etc. so there's my question:

How can I list all grants on a Oracle DB?

606305 次浏览
小开

Assuming you want to list grants on all 物品 a particular user has 收到:

select * from all_tab_privs_recd where grantee = 'your user'

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

小开
最佳答案

如果您想要的不仅仅是直接的表授权(例如，通过角色授权、系统特权(如选择任何表等)) ，这里还有一些其他的查询:

用户的系统特权:

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;
小开 
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')
;
小开

对不起，伙计们，但是如果从另一个用户(比如 SYS)运行 select，那么从 all _ tab _ pris _ recd 中选择 grantee = ‘ 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;

This will list all object grants (including column grants) for your (specified) user. If you don't want column level grants then delete all part of the select beginning with 'union' clause.

UPD: 通过研究文档，我发现了另一种观点，它以更简单的方式列出了所有赠款:

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

请记住，在 Oracle 中有 no DBA _ TAB _ PRIVS _ RECD 视图。

小开

据我所知，最全面、最可靠的方法仍然是使用 数据库管理系统:

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;

(用户名必须匹配大小写，所以通常应该是大写)

不过答案很有趣。

小开

下面的查询可以用来获得一个用户的所有权限... 只要在第一个查询中提供用户名，您就可以获得所有的权限

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
小开

要列出授予当前用户(连接并拥有会话的用户)的所有系统权限，可以使用以下查询:

select * from USER_SYS_PRIVS where USERNAME = <CURRENT_USER>;

The query should execute in current user session and username 必须的 include quotations. For example:

select * from USER_SYS_PRIVS where USERNAME = 'arash';


提交答案