oracle query to find priveleges on a stored procedure

What query can I run to simply see whether a user has privelege t开发者_开发问答o 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.

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'
           )
        )

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.

Got it...

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

This is what worked for me, I wanted to find all SPs that I have access to:

select * from USER_TAB_PRIVS where GRANTEE='______' and PRIVILEGE='EXECUTE'

Columns in USER_TAB_PRIVS include GRANTEE, OWNER, GRANTOR, TABLE_NAME (in this case, the SP name) and PRIVILEGE, so in my opinion, this is perfect.

My understanding is that dpbradley and Omnipresent's answers won't work for a normal user because they can't access DBA_* tables.