Oracle table PRIVILEGE info

tab_priv.sql


TABLE PRIVILEGE
TABLE PRIVILEGES
tab priv

==================================

-- TABLE_PRIVILEGES

-- Grants on objects for which the user is the grantor, grantee, or owner, or PUBLIC is the grantee:





Accept owner prompt 'Enter owner Name or all..'
col grantee format a15
col owner format a15
col table_name format a25
col grantor format a15
col select_priv format a1 Heading 's|e|l'
col insert_priv format a1 Heading 'i|n|s'
col delete_priv format a1 Heading 'D|E|L'
col update_priv format a1 Heading 'U|P|D'
col references_priv format a1 Heading 'R|E|F'
col alter_priv format a1 Heading 'A|l|t'
col index_priv format a1 heading 'I|N|D'
col created format a11 heading 'Grnted on:'
break on owner skip 4 on table_name skip 1 on report
set linesize 130
btitle skip 1 center-
'Y=granted,N=not Granted,G=granted with grant option,'-
's=Granted on specific column,A=granted on A11 olumns'-

select owner,TABLE_NAME,GRANTEE,GRANTOR,CREATED,SELECT_PRIV,INSERT_PRIV,DELETE_PRIV,UPDATE_PRIV,REFERENCES_PRIV,ALTER_PRIV,INDEX_PRIV
from TABLE_PRIVILEGES
where owner not in ('SYS')
and owner=upper('&owner')
order by owner,table_name,grantor,grantee
/



SQL> @tab_priv
Enter owner Name or all..SYSTEM
old 4: and owner=upper('&owner')
new 4: and owner=upper('SYSTEM')

s i D U R A I
e n E P E l N
OWNER TABLE_NAME GRANTEE GRANTOR Grnted on: l s L D F t D
--------------- ------------------------- --------------- --------------- ----------- - - - - - - -
SYSTEM DEF$_AQCALL SYS SYSTEM G N N N N N N

DEF$_CALLDEST SYS SYSTEM G N N N N N N

DEF$_DESTINATION SYS SYSTEM G N N N N N N

DEF$_ERROR SYS SYSTEM G N N N N N N

HELP PUBLIC SYSTEM Y N N N N N N

MVIEW$_ADV_INDEX PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_OWB PUBLIC SYSTEM Y A Y A N N N

MVIEW$_ADV_PARTITION PUBLIC SYSTEM Y A Y A N N N

OL$ PUBLIC SYSTEM Y A Y A N N N

OL$HINTS PUBLIC SYSTEM Y A Y A N N N

OL$NODES PUBLIC SYSTEM Y A Y A N N N

PRODUCT_PRIVS PUBLIC SYSTEM Y N N N N N N

REPCAT$_REPPROP SYS SYSTEM G N N N N N N

REPCAT$_REPSCHEMA SYS SYSTEM G N N N N N N



Y=granted,N=not Granted,G=granted with grant option,s=Granted on specific column,A=granted on A11 olumns

14 rows selected.



SELECT grantor, table_name, privilege FROM user_tab_privs_recd;
/

set linesize 121
col select_priv format a10
col insert_priv format a10
col update_priv format a10
col delete_priv format a10

SELECT table_name, grantee,
MAX(DECODE(privilege, 'SELECT', 'SELECT')) AS select_priv,
MAX(DECODE(privilege, 'DELETE', 'DELETE')) AS delete_priv,
MAX(DECODE(privilege, 'UPDATE', 'UPDATE')) AS update_priv,
MAX(DECODE(privilege, 'INSERT', 'INSERT')) AS insert_priv
FROM dba_tab_privs
WHERE grantee IN ( SELECT role FROM dba_roles)
GROUP BY table_name, grantee;