Wednesday, June 29, 2016

select grants on tables to users or roles


select grants on tables to users or roles



SELECT
  OWNER,
  GRANTEE,
  GRANTOR,
  TABLE_NAME,
  PRIVILEGE
FROM
  DBA_TAB_PRIVS
WHERE
  table_name   = 'TABLENAME'
AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE')
AND GRANTEE   IN ('ROLE1','ROLE2')
ORDER BY
  1,2;



select grants on table to a user thru role or direct priv



select Grantee,'Granted Through Role' as Grant_Type, role, table_name
from role_tab_privs rtp, dba_role_privs drp
where rtp.role = drp.granted_role
and table_name = 'TABLENAME'
union
select Grantee,'Direct Grant' as Grant_type, null as role, table_name
from dba_tab_privs
where table_name = 'TABLENAME' ;

No comments:

Post a Comment