Pete Finnigan's Oracle Security Weblog

who_has_role is the second script in this series of useful Oracle security scripts. This script can be used as part of a security audit of an Oracle database. Quite often you will want to find out which users have been granted a particular role. Good examples are the roles DBA or CONNECT and RESOURCE as all of these have powerful privileges that should not just be given out to just any users. If a role is also granted to a role ( and to a role...) then it can become quite difficult to see all of the real users that have the role. I wrote this script to aid in this task as like its counterpart find_all_privs.sql available on my tools page it also prints out the grants hierarchically. Here is a simple example where i check to see who has been granted the role SELECT_CATALOG_ROLE:

Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK                          [DBA]: SELECT_CATALOG_ROLE
OUTPUT METHOD Screen/File                [S]: S
FILE NAME FOR OUTPUT              [priv.lst]: 
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: 

Investigating Role => SELECT_CATALOG_ROLE (PWD = NO) which is granted to =>
====================================================================
        User => SH (ADM = NO)
        Role => DBA (ADM = YES|PWD = NO) which is granted to =>
                User => SYS (ADM = YES)
                User => WKSYS (ADM = NO)
                User => CTXSYS (ADM = NO)
                User => HACKER (ADM = NO)
                User => SYSTEM (ADM = YES)
        User => ODM (ADM = NO)
        User => SYS (ADM = YES)
        User => ODM_MTR (ADM = NO)
        Role => OLAP_DBA (ADM = NO|PWD = NO) which is granted to =>
                Role => DBA (ADM = NO|PWD = NO) which is granted to =>
                        User => SYS (ADM = YES)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                User => SYS (ADM = YES)
                User => OLAPSYS (ADM = NO)
        Role => EXP_FULL_DATABASE (ADM = NO|PWD = NO) which is granted to =>
                Role => DBA (ADM = NO|PWD = NO) which is granted to =>
                        User => SYS (ADM = YES)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                User => SYS (ADM = YES)
        Role => IMP_FULL_DATABASE (ADM = NO|PWD = NO) which is granted to =>
                Role => DBA (ADM = NO|PWD = NO) which is granted to =>
                        User => SYS (ADM = YES)
                        User => WKSYS (ADM = NO)
                        User => CTXSYS (ADM = NO)
                        User => HACKER (ADM = NO)
                        User => SYSTEM (ADM = YES)
                User => SYS (ADM = YES)

PL/SQL procedure successfully completed.

For updates please visit /tools.htm

SQL> 

As you can see a complete list of users is returned even if the SELECT_CATALOG_ROLE has been grant via another role such as EXP_FULL_DATABASE. The script can be set up to print the results to the screen by passing in 'S' as the second parameter or to a file by using 'F' and then supplying a directory and file name. The directory needs to be in a location pointed at by the parameter utl_file_dir or a valid DIRECTORY object that can be used by the user running the script.