Pete Finnigan's Oracle Security Weblog

I have just updated my tools page (again) and added a new script written by myself called who_has_priv.sql that can be used to find out which users and roles and then hierarchically via those roles who has been granted a specific system privilege.

This is the third in a series of useful scripts that can be used to help audit an Oracle database for security issues in the configuration or set-up of it.

The script is simple to use and like the others can send the output to the screen by specifying 'S' as the second parameter or to a file by specifying 'F' instead. If a file is used then the name and directory also need to be specified and the directory has to be named as a utl_file_dir parameter setting or a DIRECTORY object that the user who is running the script can access needs to be created.

Here is an example of running the script to test for users that have been granted the SELECT ANY TABLE privilege:

who_has_priv: Release 1.0.1.0.0 - Production on Sat Oct 09 21:10:24 20
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

PRIVILEGE TO CHECK        [SELECT ANY TABLE]: SELECT ANY TABLE
OUTPUT METHOD Screen/File                [S]: S
FILE NAME FOR OUTPUT              [priv.lst]: 
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]: 

Privilege => SELECT ANY TABLE has been granted to =>
====================================================================
        User => AZG (ADM = NO)
        Role => DBA (ADM = YES) 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 => XDB (ADM = NO)
        User => MDSYS (ADM = NO)
        User => OSCAN (ADM = NO)
        User => WKSYS (ADM = NO)
        User => ZULIA (ADM = NO)
        User => CTXSYS (ADM = NO)
        User => OLAPSYS (ADM = NO)
        Role => OLAP_DBA (ADM = NO) which is granted to =>
                Role => DBA (ADM = 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) which is granted to =>
                Role => DBA (ADM = 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) which is granted to =>
                Role => DBA (ADM = 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> 

The report shows the users who have been granted the privilege directly and also those that have had the privilege granted via a role or deeper.

Again the script can be found here.