Oracle to list a user privilege

Question:  I want to display all of the privileges for an Oracle user, including direct grants and role grants.  How do I display everything that has been granted to  user?

Answer: For full scripts, download the Oracle script collection.  There are many views that contain the privileges for a user:

  • dba_sys_privs
  • dba_tab_privs
  • dba_role_privs
  • table_privileges

This query shows all table-level granted privileges for a user named 'MYUSER':

select
   owner,
   table_name,
   select_priv,
   insert_priv,
   delete_priv,
   update_priv,
   references_priv,
   alter_priv,
   index_priv
from
   table_privileges
where
   grantee = 'USER_A'
order by
   owner,
   table_name;

This query shows all role privileges for a user:

select distinct
   owner,
   table_name,
   privilege
from
   dba_role_privs rp,
   role_tab_privs rtp
where
   rp.granted_role = rtp.role
and
   rp.grantee = 'MYUSER'
order by
   owner,
   table_name;

The following example will display all system and role privileges for a user named MYUSER:

select
   privilege
from
   sys.dba_sys_privs
 where
   grantee = 'MYUSER'
union
select
   privilege
from
   dba_role_privs rp
join
   role_sys_privs rsp
on (rp.granted_role = rsp.role)
 where rp.grantee = 'MYUER'
 order by 1;

posted @   耀阳居士  阅读(77)  评论(0编辑  收藏  举报
编辑推荐:
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 凌晨三点救火实录:Java内存泄漏的七个神坑,你至少踩过三个!
历史上的今天:
2020-03-31 How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)
2020-03-31 11g新特性之IO校准(IO Calibration)
点击右上角即可分享
微信分享提示