如何列出用户收到的所有资助? [英] How can I list ALL grants a user received?

问题描述

我需要查看Oracle数据库上的所有拨款.

I need to see all grants on an Oracle DB.

我使用TOAD功能比较架构，但是它没有显示临时授权等.所以我有一个问题:

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

如何在Oracle数据库上列出所有赠款?

How can I list all grants on a Oracle DB?

推荐答案

如果您不仅要获得直接表授予(例如，通过角色进行的授予，诸如选择任何表之类的系统特权等)，还可以进行一些其他查询:

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:

用户的系统特权:

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;

对表/视图的间接授予:

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;

这篇关于如何列出用户收到的所有资助?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！