Wednesday, August 23, 2017

How to check if I have write permissions to an Oracle table

SELECT CASE WHEN COUNT(*) > 0 THEN 'YES' ELSE 'NO' END AS PERMISSIONS FROM (
SELECT privilege FROM (
select * from dba_tab_privs  
where (grantee = 'MY_USER_NAME' or grantee in (select granted_role from dba_role_privs
connect by prior granted_role = grantee start with grantee = 'MY_USER_NAME') )
and owner = 'THE_OWNER'
and table_name = 'THE_TABLE' and privilege = 'UPDATE'
) 
UNION
SELECT PRIVILEGE FROM (
select * from dba_sys_privs  where grantee = 'MY_USER_NAME' or grantee in (select granted_role from dba_role_privs 
connect by prior granted_role = grantee start with grantee = 'MY_USER_NAME') 
) where privilege like '%UPDATE%'
);

Saving here in case ostensibly duplicate question at S.O. is deleted.

No comments:

Post a Comment