9

I'm looking for some query to list all users that have grants over a package.

For example, user Schema_A has been granted to execute package: B.MyPackage.

I'm querying views or tables like: role_tab_privs, role_sys_privs,sys.dba_sys_privs, dba_role_privs... but I can't find what I'm looking.

15

If the privilege has been granted directly or to a role, the grant will appear in DBA_TAB_PRIVS

SELECT grantee, privilege
  FROM dba_tab_privs
 WHERE owner = 'B'
   AND table_name = 'MYPACKAGE'
   AND privilege = 'EXECUTE'

If the grantee is a role, you would then need to look at dba_role_privs to see what users (or roles) have been granted that role and follow the chain if you have roles granted to other roles. If you need to account for users that have grants because of the (very dangerous) ANY grants (i.e. EXECUTE ANY PROCEDURE), that would require a separate query.

If you want to get more sophisticated than simply doing a straight query against dba_tab_privs, though, you are probably better off using on of Pete Finnigan's scripts like the who_has_priv.sql (or who_has_priv_procedure.sql). Pete is probably the leading expert on Oracle security so these are much more likely to account for every possible corner case than anything I would attempt to cobble together.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.