Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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