oracle 如何显示对特定表具有“SELECT”权限的列表用户？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24358444/
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 to show list users which have "SELECT" permission on a specific table?
提问by Hyman Vo
I need to grant a user to some tables in a schema on Oracle database, I already created this user before but the database has a lot of tables (over 1000 tables) so that I need a SQL query to show all tables that user can connect. This user only can use SELECT.
我需要授予用户访问 Oracle 数据库架构中的某些表的权限，我之前已经创建了该用户，但是该数据库有很多表（超过 1000 个表），因此我需要一个 SQL 查询来显示用户可以连接的所有表. 该用户只能使用 SELECT。
Please help me to solve this issue !
请帮我解决这个问题！
回答by Nishanthi Grashia
You can use the below query. It will show all the tables that the current session user has access to,
您可以使用以下查询。它将显示当前会话用户有权访问的所有表，
select * from user_tables;
To view all the tables,
要查看所有表，
select * from all_tables;
List all users who have been assigned a particular role
列出已分配特定角色的所有用户
-- Change 'DBA' to the required role
select * from dba_role_privs where granted_role = 'DBA'
List all roles given to a user
列出赋予用户的所有角色
-- Change 'PHIL@ to the required user
select * from dba_role_privs where grantee = 'PHIL';
List all privileges given to a user
列出授予用户的所有权限
select
lpad(' ', 2*level) || granted_role "User, his roles and privileges"
from
(
/* THE USERS */
select
null grantee,
username granted_role
from
dba_users
where
username like upper('%&enter_username%')
/* THE ROLES TO ROLES RELATIONS */
union
select
grantee,
granted_role
from
dba_role_privs
/* THE ROLES TO PRIVILEGE RELATIONS */
union
select
grantee,
privilege
from
dba_sys_privs
)
start with grantee is null
connect by grantee = prior granted_role;
Note: Taken from http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
注意：摘自http://www.adp-gmbh.ch/ora/misc/recursively_list_privilege.html
List which tables a certain role gives SELECT access to?
列出某个角色授予 SELECT 访问权限的哪些表？
-- Change 'DBA' to the required role.
select * from role_tab_privs where role='DBA' and privilege = 'SELECT';
List all tables a user can SELECT from?
列出用户可以从中选择的所有表？
--Change 'PHIL' to the required user
select * from dba_tab_privs where GRANTEE ='PHIL' and privilege = 'SELECT';
List all users who can SELECT on a particular table (either through being given a relevant role or through a direct grant (ie grant select on atable to joe))? The result of this query should also show through which role the user has this access or whether it was a direct grant.
列出可以对特定表进行 SELECT 的所有用户（通过获得相关角色或通过直接授权（即，将选择授权给 joe））？此查询的结果还应显示用户通过哪个角色具有此访问权限或是否为直接授予。
-- Change 'TABLENAME' below
select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;
回答by damien hawks
Look at http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665
看http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665
Check USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS tables.
检查 USER_SYS_PRIVS、USER_TAB_PRIVS、USER_ROLE_PRIVS 表。
Also using this is helpful:-
使用它也很有帮助：-
select * from USER_ROLE_PRIVS where USERNAME='SAMPLE';
select * from USER_TAB_PRIVS where Grantee = 'SAMPLE';
select * from USER_SYS_PRIVS where USERNAME = 'SAMPLE';
Put "user" instead of sample to get info on current logged user. Hope this helps!
将“用户”而不是样本以获取有关当前登录用户的信息。希望这可以帮助！
