[Previous entry: "Tools page has been updated again"] [Next entry: "Oracle remids all customers to apply Patches for alert #68"]
who_has_priv.sql : script to find user who have been granted a system privilege
October 9th, 2004 by Pete
Post to del.icio.us
Post to Furl
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 http://www.petefinnigan.com/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.
