SQL 如何列出用户收到的所有赠款？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1298473/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I list ALL grants a user received?
提问by guerda
I need to see all grants on an Oracle DB.
我需要查看 Oracle DB 上的所有授权。
I used the TOAD feature to compare schemas but it does not shows temptable grants etc. so there's my question:
我使用 TOAD 功能来比较模式，但它没有显示临时授权等，所以我的问题是：
How can I list all grants on a Oracle DB?
如何列出 Oracle DB 上的所有授权？
回答by DCookie
If you want more than just direct table grants (e.g., grants via roles, system privileges such as select any table, etc.), here are some additional queries:
如果您想要的不仅仅是直接表授权（例如，通过角色授权、系统权限（如选择任意表等）），这里有一些额外的查询：
System privileges for a user:
用户的系统权限：
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;
Direct grants to tables/views:
直接授予表/视图：
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;
Indirect grants to tables/views:
对表/视图的间接授权：
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;
回答by Juris
Assuming you want to list grants on all objectsa particular user has received:
假设您要列出特定用户收到的所有对象的授权：
select * from all_tab_privs_recd where grantee = 'your user'
This will not return objects owned by the user. If you need those, use all_tab_privsview instead.
这不会返回用户拥有的对象。如果您需要这些，请改用all_tab_privs视图。
回答by Alex Cherkas
Sorry guys, but selecting from all_tab_privs_recd where grantee = 'your user' will not give any output except public grants and current user grants if you run the select from a different (let us say, SYS) user. As documentation says,
抱歉，如果您从不同的（让我们说，SYS）用户运行选择，那么从 all_tab_privs_recd 中选择，其中 grantee = 'your user' 将不会给出任何输出，除了公共授权和当前用户授权。正如文档所说，
ALL_TAB_PRIVS_RECD describes the following types of grants:
Object grants for which the current user is the grantee Object grants for which an enabled role or PUBLIC is the grantee
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
So, if you're a DBA and want to list all objectgrants for a particular (not SYS itself) user, you can't use that system view.
因此，如果您是 DBA 并且想要列出特定（不是 SYS 本身）用户的所有对象授权，则不能使用该系统视图。
In this case, you must perform a more complex query. Here is one taken (traced) from TOAD to select all object grants for a particular user:
在这种情况下，您必须执行更复杂的查询。这是从 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.
这将列出您（指定的）用户的所有对象授权（包括列授权）。如果您不想获得列级授权，则删除以“union”子句开头的所有选择部分。
UPD: Studying the documentation I found another view that lists all grants in much simpler way:
UPD：研究文档我发现另一个视图以更简单的方式列出所有赠款：
select * from DBA_TAB_PRIVS where grantee = 'your user';
Bear in mind that there's noDBA_TAB_PRIVS_RECD view in Oracle.
请记住，Oracle 中没有DBA_TAB_PRIVS_RECD 视图。
回答by Matteo Steccolini
The most comprehensive and reliable method I know is still by using DBMS_METADATA:
我所知道的最全面、最可靠的方法仍然是使用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;
Interesting answers though.
有趣的答案虽然。
回答by Sujit
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')
;
回答by Pradeep Dewani
Following query can be used to get all privileges of one user .. Just provide user name in first query and you will get all privileges to that
以下查询可用于获取一个用户的所有权限.. 只需在第一个查询中提供用户名，您将获得该用户的所有权限
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
WITH users AS (SELECT 'SCHEMA_USER' usr FROM dual), Roles AS (SELECT grant_role FROM dba_role_privs rp JOIN users ON rp.GRANTEE = users.usr UNION SELECT grant_role FROM role_role_privs WHERE role IN (SELECT grant_role FROM dba_role_privs ONrp.JOIN users 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.ONdptgt JOIN users.ON rtp.role 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
