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

我正在使用 Linux，Oracle10g。 我已经创建了一个名为 test 的用户，并向同一用户授予了 create session 和选择任何字典权限。

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

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

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

请帮忙解决这个问题。

谢谢

877791 次浏览
小开
最佳答案

Look at 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

SELECT * FROM USER_SYS_PRIVS;
SELECT * FROM USER_TAB_PRIVS;
SELECT * FROM USER_ROLE_PRIVS;
小开 
SELECT *
FROM DBA_ROLE_PRIVS
WHERE UPPER(GRANTEE) LIKE '%XYZ%';
小开 
select *
from ROLE_TAB_PRIVS
where role in (
select granted_role
from dba_role_privs
where granted_role in ('ROLE1','ROLE2')
)
小开

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

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

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

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

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

always make SQL re-usuable: -:)

-- ===================================================
-- &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
;
小开

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

As of Oracle 11g.

Replace USER with the desired username

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.

小开

The only visible result I was able to understand was first to connect with the user I wanted to get the rights, then with the following query:

SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM USER_TAB_PRIVS;

提交答案