今天在公司請同事查看自己在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
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/，如需转载，请注明出处，否则将追究法律责任。
上一篇：
Tips--常见的要求排序的操作
下一篇：
NFS設定全攻略（ZT）
请登录后发表评论
登录
全部评论
