oracle角色是否激活,安全性-如何在Oracle中查找授予用户的特权和角色？

安全性-如何在Oracle中查找授予用户的特权和角色？

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

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

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

select privilege

from dba_sys_privs

where grantee='SAMPLE'

order by 1;

请帮助解决问题。

谢谢

8个解决方案

60 votes

除了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';

user2668478 answered 2019-09-29T10:19:32Z

45 votes

看看[http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665]

检查USER_SYS_PRIVS，USER_TAB_PRIVS，USER_ROLE_PRIVS表。

VAV answered 2019-09-29T10:19:09Z

44 votes

没有其他答案对我有用，因此我编写了自己的解决方案：

从Oracle 11g开始。

用所需的用户名替换USER

授予的角色：

SELECT *

FROM DBA_ROLE_PRIVS

WHERE GRANTEE = 'USER';

直接授予用户的特权：

SELECT *

FROM DBA_TAB_PRIVS

WHERE GRANTEE = 'USER';

授予用户角色的特权：

SELECT *

FROM DBA_TAB_PRIVS

WHERE GRANTEE IN (SELECT granted_role

FROM DBA_ROLE_PRIVS

WHERE GRANTEE = 'USER');

授予的系统特权：

SELECT *

FROM DBA_SYS_PRIVS

WHERE GRANTEE = 'USER';

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

Mocking answered 2019-09-29T10:20:42Z

9 votes

如果通过某些角色将特权授予用户，则可以在下面的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';

upog answered 2019-09-29T10:21:05Z

7 votes

结合先前的建议来确定您的个人权限(即“ USER”权限)，然后使用以下方法：

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

ShamrockCS answered 2019-09-29T10:21:29Z

1 votes

SELECT *

FROM DBA_ROLE_PRIVS

WHERE UPPER(GRANTEE) LIKE '%XYZ%';

user2615480 answered 2019-09-29T10:21:46Z

1 votes

select *

from ROLE_TAB_PRIVS

where role in (

select granted_role

from dba_role_privs

where granted_role in ('ROLE1','ROLE2')

)

shans answered 2019-09-29T10:22:02Z

0 votes

总是使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

;

dave answered 2019-09-29T10:22:26Z