ITPub博客

首页 > Linux操作系统 > Linux操作系统 > SQL--查看當前user對表或視圖的相關權限查詢

SQL--查看當前user對表或視圖的相關權限查詢

原创 Linux操作系统 作者:vongates 时间:2019-07-01 20:03:06 0 删除 编辑
今天在公司請同事查看自己在ERP DB中對哪些表有什麽樣的權限。因爲沒有DBA_表的查詢權限。呵呵,只能請大家各自彙總自己的權限做整理。帖上一同事寫的改寫的SQL
select t.grantee,
t.table_schema,
t.table_name,
decode(sum(decode(t.privilege, 'SELECT', '1', '0')),'1','Y','0','') "SELECT",
decode(sum(decode(t.privilege, 'DELETE', '1', '0')),'1','Y','0','') "DELETE",
decode(sum(decode(t.privilege, 'UPDATE', '1', '0')),'1','Y','0','') "UPDATE",
decode(sum(decode(t.privilege, 'INSERT', '1', '0')),'1','Y','0','') "INSERT",
decode(sum(decode(t.privilege, 'INDEX', '1', '0')),'1','Y','0','') "INDEX",
decode(sum(decode(t.privilege, 'EXECUTE', '1', '0')),'1','Y','0','') "EXECUTE",
decode(sum(decode(t.privilege, 'ALTER', '1', '0')),'1','Y','0','') "ALTER",
decode(sum(decode(t.privilege, 'READ', '1', '0')),'1','Y','0','') "READ",
decode(sum(decode(t.privilege, 'REFERENCE', '1', '0')),'1','Y','0','') "REFERENCE"
from all_tab_privs t
where t.grantee <> 'PUBLIC'
group by t.grantee,
t.table_schema,
t.table_name

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29987/viewspace-51893/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-09-11

  • 博文量
    448
  • 访问量
    290111