blocks|key|152884|text|看看http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|152885|使用以下select语句检查USER_SYS_PRIVS、USER_TAB_PRIVS、USER_ROLE_PRIVS表|152886|SELECT+*+FROM+USER_SYS_PRIVS;+
SELECT+*+FROM+USER_TAB_PRIVS;+
SELECT+*+FROM+USER_ROLE_PRIVS;|code-block|syntax|javascript|152887|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665^0|2|1X|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

Look at <a href="http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665" rel="noreferrer">http://docs.oracle.com/cd/B10501_01/server.920/a96521/privs.htm#15665</a>

Check USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS tables with these select statements

<pre><code>SELECT * FROM USER_SYS_PRIVS; 
SELECT * FROM USER_TAB_PRIVS; 
SELECT * FROM USER_ROLE_PRIVS;
</code></pre>

blocks|key|371074|text|SELECT+*+
FROM+DBA_ROLE_PRIVS+
WHERE+UPPER(GRANTEE)+LIKE+'%25XYZ%25';|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|371075|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT * 
FROM DBA_ROLE_PRIVS 
WHERE UPPER(GRANTEE) LIKE '%XYZ%';
</code></pre>

blocks|key|152905|text|select+*+
from+ROLE_TAB_PRIVS+
where+role+in+(
++++select+granted_role
++++from+dba_role_privs+
++++where+granted_role+in+('ROLE1','ROLE2')
)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|152906|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select * 
from ROLE_TAB_PRIVS 
where role in (
 select granted_role
 from dba_role_privs 
 where granted_role in ('ROLE1','ROLE2')
)
</code></pre>

blocks|key|152931|text|除了VAV的答案之外，第一个答案在我的环境中最有用|type|unstyled|depth|inlineStyleRanges|entityRanges|data|152932|select+*+from+USER_ROLE_PRIVS+where+USERNAME='SAMPLE';
select+*+from+USER_TAB_PRIVS+where+Grantee+=+'SAMPLE';
select+*+from+USER_SYS_PRIVS+where+USERNAME+=+'SAMPLE';|code-block|syntax|javascript|152933|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>select * from USER_ROLE_PRIVS where USERNAME='SAMPLE';
select * from USER_TAB_PRIVS where Grantee = 'SAMPLE';
select * from USER_SYS_PRIVS where USERNAME = 'SAMPLE';
</code></pre>

blocks|key|371136|text|如果通过某些角色将权限授予用户，则可以使用以下SQL|type|unstyled|depth|inlineStyleRanges|entityRanges|data|371137|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';|code-block|syntax|javascript|371138|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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';
</code></pre>

blocks|key|371171|text|结合前面的建议来确定您的个人权限(即“用户”权限)，然后使用以下内容：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|371172|--+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);|code-block|syntax|javascript|371173|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>-- 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);
</code></pre>

blocks|key|153006|text|始终使SQL可重用：-:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|153007|--+===================================================
--+&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+'%25&&role_name%25'
--+===================================================


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
;|code-block|syntax|javascript|153008|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

always make SQL re-usuable: -:)

<pre><code>-- ===================================================
-- &amp;role_name will be "enter value for 'role_name'".
-- Date: 2015 NOV 11.

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


define role_name=&amp;role_name

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


select role, privilege,count(*)
 from ROLE_TAB_PRIVS
where ROLE = '&amp;&amp;role_name'
group by role, privilege
order by role, privilege asc
;
</code></pre>

blocks|key|371223|text|我所能理解的唯一可见结果是首先连接到我想要获得权限的用户，然后使用以下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|371224|SELECT+GRANTEE,+PRIVILEGE,+TABLE_NAME+FROM+USER_TAB_PRIVS;|code-block|syntax|javascript|371225|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:
<pre class="lang-sql prettyprint-override"><code>SELECT GRANTEE, PRIVILEGE, TABLE_NAME FROM USER_TAB_PRIVS;
</code></pre>

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.

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

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.

<pre><code>select privilege 
from dba_sys_privs 
where grantee='SAMPLE' 
order by 1;
</code></pre>

please help to resolve the issue.

Thanks

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

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我用的是Linux，Oracle10g。我已经创建了一个名为test的用户。并向同一用户授予create session和select any dictionary权限。我还将sysdba和sysoper角色授予相同的用户。现在，我想要显示授予用户的所有权限和角色。我找到以下查询，但它只显示create session...

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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