select grantee,table_name,
NVL (MAX (CASE privilege when ‘INSERT’ then ‘I’ else null end ),’ ‘) as “Insert”,
NVL (MAX (CASE privilege when ‘UPDATE’ then ‘U’ else null end ),’ ‘) as “Update”,
NVL (MAX (CASE privilege when ‘DELETE’ then ‘D’ else null end ),’ ‘) as “Delete”,
NVL (MAX (CASE privilege when ‘SELECT’ then ‘S’ else null end ),’ ‘) as “Select”,
NVL (MAX (CASE privilege when ‘EXECUTE’ then ‘E’ else null end ),’ ‘) as “Execute”,
NVL (MAX (CASE privilege when ‘ALTER’ then ‘A’ else null end ),’ ‘) as “Alter”
from DBA_TAB_PRIVS
where grantee in (‘ALEYA’,'JAHANGIR’,'LUCKY’,'MAHAMUD’,'MOHI’,'MOJLISH’,'MOKTER’,'RAKIB’,'SAIFUL’,'SHARIFUL’,'TAPAN’,'JASIM’,'KAWSIK’,'ALEYA’,
‘ATMUTL’,'BACH’,'BACHINT’,'FIUINT’,'ISLBAS’,'ISLIMG’,'ISLITS’,'ISLSYS’,'MYBANK’,'ORBBBR’,'ORBITS’,'SMSGTWAY’,'STFOREX’,'STLBAS’,'STLIMG’,'STLSYS’,'WEBADMIN’,
‘BACHINT’,'ISLBAS’,'ISLIMG’,'ISLSYS’,'MYBANK’,'ORBBBR’,'SMSGTWAY’,'STLBAS’,'STLIMG’,'STLSYS’,'MAMUN_ERA’)
group by grantee,table_name
order by grantee
NVL (MAX (CASE privilege when ‘INSERT’ then ‘I’ else null end ),’ ‘) as “Insert”,
NVL (MAX (CASE privilege when ‘UPDATE’ then ‘U’ else null end ),’ ‘) as “Update”,
NVL (MAX (CASE privilege when ‘DELETE’ then ‘D’ else null end ),’ ‘) as “Delete”,
NVL (MAX (CASE privilege when ‘SELECT’ then ‘S’ else null end ),’ ‘) as “Select”,
NVL (MAX (CASE privilege when ‘EXECUTE’ then ‘E’ else null end ),’ ‘) as “Execute”,
NVL (MAX (CASE privilege when ‘ALTER’ then ‘A’ else null end ),’ ‘) as “Alter”
from DBA_TAB_PRIVS
where grantee in (‘ALEYA’,'JAHANGIR’,'LUCKY’,'MAHAMUD’,'MOHI’,'MOJLISH’,'MOKTER’,'RAKIB’,'SAIFUL’,'SHARIFUL’,'TAPAN’,'JASIM’,'KAWSIK’,'ALEYA’,
‘ATMUTL’,'BACH’,'BACHINT’,'FIUINT’,'ISLBAS’,'ISLIMG’,'ISLITS’,'ISLSYS’,'MYBANK’,'ORBBBR’,'ORBITS’,'SMSGTWAY’,'STFOREX’,'STLBAS’,'STLIMG’,'STLSYS’,'WEBADMIN’,
‘BACHINT’,'ISLBAS’,'ISLIMG’,'ISLSYS’,'MYBANK’,'ORBBBR’,'SMSGTWAY’,'STLBAS’,'STLIMG’,'STLSYS’,'MAMUN_ERA’)
group by grantee,table_name
order by grantee
Thnx for this awesome post...
ReplyDelete