Auditing an Oracle database for security issues is very important. PeteFinnigan.com provides all of the information and tools that you will need Click here for details of PeteFinnigan.com Limited's detailed Oracle database security audit service Click here for details of PeteFinnigan.com Limited's Oracle Security Training Courses
There are 66 visitors online    
Cookie Policy:We only use essential cookies on small sections of this website. For details see here.

Pete Finnigan's Oracle security weblog


Home » Archives » October 2004 » who_has_role.sql : A script to find which users and roles have been granted a role

[Previous entry: "PeteFinnigan.com Tools page updated"] [Next entry: "Howard Rogers writes about Virtual Private databases"]

who_has_role.sql : A script to find which users and roles have been granted a role

October 5th, 2004 by Pete


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 http://www.petefinnigan.com/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.

October 2004
SMTWTFS
     12
3456789
10111213141516
17181920212223
24252627282930
31      

This is the weblog for Pete Finnigan. Pete works in the area of Oracle security and he specialises in auditing Oracle databases for security issues. This weblog is aimed squarely at those interested in the security of their Oracle databases.

Weblog Home
Weblog Archives


Home
Oracle Security Tools page
Oracle security papers
Oracle Security alerts

Web Development
SQL Server Security

RSS 1.0 FEED
RSS 2.0 FEED
Atom 0.3 FEED
Powered by gm-rss 2.0.0


Valid XHTML 1.0!