Database Security Products (MOSC)

MOSC Banner

listing users with access to code in the database (procedures triggers packages functions....)

edited Feb 6, 2013 6:23PM in Database Security Products (MOSC) 1 commentAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center