如何在 Oracle 中查找授予用户的权限和角色？

How to find the privileges and roles granted to a user in Oracle?

提问by Abhimanyu garg

I am using Linux, Oracle10g. I have created one user called test. and granted create session and select any dictionary permission to the same user.

我使用的是 Linux，Oracle10g。我创建了一个名为 test 的用户。并授予同一用户创建会话和选择任何字典的权限。

i also granted sysdba and sysoper roles to the same users.

我还向相同的用户授予了 sysdba 和 sysoper 角色。

Now i want to display all the privileges and roles granted to the user. I found following query but it shows only create session and select dictionary privileges.

现在我想显示授予用户的所有权限和角色。我发现以下查询，但它只显示创建会话和选择字典权限。

select privilege 
from dba_sys_privs 
where grantee='SAMPLE' 
order by 1;

please help to resolve the issue.

请帮助解决问题。

Thanks

谢谢

采纳答案by VAV

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 with these select statements

使用这些选择语句检查 USER_SYS_PRIVS、USER_TAB_PRIVS、USER_ROLE_PRIVS 表

SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS; 
SELECT * FROM USER_ROLE_PRIVS;

回答by user2668478

In addition to VAV's answer, The first one was most useful in my environment

除了 VAV 的回答，第一个在我的环境中最有用

select * from USER_ROLE_PRIVS where USERNAME='SAMPLE';
select * from USER_TAB_PRIVS where Grantee = 'SAMPLE';
select * from USER_SYS_PRIVS where USERNAME = 'SAMPLE';

回答by Mocking

None of the other answers worked for me so I wrote my own solution:

其他答案都不适合我，所以我写了自己的解决方案：

As of Oracle 11g.

从 Oracle 11g 开始。

Replace USER with the desired username

将 USER 替换为所需的用户名

Granted Roles:

授予的角色：

SELECT * 
  FROM DBA_ROLE_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges Granted Directly To User:

直接授予用户的特权：

SELECT * 
  FROM DBA_TAB_PRIVS 
 WHERE GRANTEE = 'USER';

Privileges Granted to Role Granted to User:

授予给用户的角色的权限：

SELECT * 
  FROM DBA_TAB_PRIVS  
 WHERE GRANTEE IN (SELECT granted_role 
                     FROM DBA_ROLE_PRIVS 
                    WHERE GRANTEE = 'USER');

Granted System Privileges:

授予的系统权限：

SELECT * 
  FROM DBA_SYS_PRIVS 
 WHERE GRANTEE = 'USER';

If you want to lookup for the user you are currently connected as, you can replace DBA in the table name with USER and remove the WHERE clause.

如果要查找当前连接的用户，可以将表名中的 DBA 替换为 USER 并删除 WHERE 子句。

回答by upog

IF privileges are given to a user through some roles, then below SQL can be used

如果通过某些角色赋予用户权限，则可以使用下面的SQL

select * from ROLE_ROLE_PRIVS where ROLE = 'ROLE_NAME';
select * from ROLE_TAB_PRIVS  where ROLE = 'ROLE_NAME';
select * from ROLE_SYS_PRIVS  where ROLE = 'ROLE_NAME';

回答by ShamrockCS

Combining the earlier suggestions to determine your personal permissions (ie 'USER' permissions), then use this:

结合前面的建议来确定您的个人权限（即“用户”权限），然后使用：

-- your permissions
select * from USER_ROLE_PRIVS where USERNAME= USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;

-- granted role permissions
select * from ROLE_ROLE_PRIVS where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME= USER);
select * from ROLE_TAB_PRIVS  where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME= USER);
select * from ROLE_SYS_PRIVS  where ROLE IN (select granted_role from USER_ROLE_PRIVS where USERNAME= USER);

回答by user2615480

SELECT * 
FROM DBA_ROLE_PRIVS 
WHERE UPPER(GRANTEE) LIKE '%XYZ%';

回答by shans

select * 
from ROLE_TAB_PRIVS 
where role in (
    select granted_role
    from dba_role_privs 
    where granted_role in ('ROLE1','ROLE2')
)

回答by dave

always make SQL re-usuable: -:)

始终使 SQL 可重用：-:)

-- ===================================================
-- &role_name will be "enter value for 'role_name'".
-- Date:  2015 NOV 11.

-- sample code:   define role_name=&role_name
-- sample code:   where role like '%&&role_name%'
-- ===================================================


define role_name=&role_name

select * from ROLE_ROLE_PRIVS where ROLE = '&&role_name';
select * from ROLE_SYS_PRIVS  where ROLE = '&&role_name';


select role, privilege,count(*)
 from ROLE_TAB_PRIVS
where ROLE = '&&role_name'
group by role, privilege
order by role, privilege asc
;