oracle query to find priveleges on a stored procedure

Question

What query can I run to simply see whether a user has privelege to execute a stored procedure.

lets say user is UserA and stored procedure name is my_stored_proc

I want to know whether UserA has execute permission on my_stored_proc

UserA is not the owner of the storedproc. Some other owner grants him the permission.

Answer 1

You could try

select ap.*
from All_Procedures ap
where ap.owner = 'UserA'

This only tells you if UserA is the owner. I suppose UserA could still have permission even if not the owner. Not sure how to check for that.

EDIT: Other tables to check are

USER_SYS_PRIVS
USER_TAB_PRIVS
USER_ROLE_PRIVS
ROLE_SYS_PRIVS
ROLE_TAB_PRIVS

I've rarely queried these so I'm not exactly sure how to find what you're looking for, but I would start with these.

Answer 2

Got it...

SELECT * FROM DBA_TAB_PRIVS A WHERE GRANTEE = 'UserA' AND GRANTOR = 'someoneelse' and privilege = 'EXECUTE'

Answer 3

To account for grants through a role:

  select grantee, table_name, privilege
     from dba_tab_privs
     where
       table_name = 'my_stored_proc'
        and 
       owner = 'ownerOfObject'
       and
       (grantee = 'userA'
         or
         grantee in
          (select granted_role
        from dba_role_privs
        where grantee = 'userA'
           )
        )