listing users with access to code in the database (procedures triggers packages functions....)
I have been asked to provide a list of individual that have access to run or change code in the database.
The two queries below look quite complex for answering such a simple question.
Am I heading in the wrong direction on the below queries?
#################################################################
## user with access to code through a role
#################################################################
SELECT DISTINCT NN.USERNAME, MM.GRANTED_ROLE, OO.OBJECT_TYPE, OO.OBJECT_NAME, OO.PATH
FROM DBA_ROLE_PRIVS MM
, DBA_USERS NN
, (
SELECT DISTINCT SS.GRANTEE FN_ROLE, SS.OBJECT_TYPE, SS.OWNER, SS.OBJECT_NAME
, RR.GRANTEE, RR. GRANTED_ROLE , RR.PATH
FROM
(SELECT grantee, granted_role, sys_connect_by_path(granted_role,'/') path