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 67 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_can_access.sql : a script to find uses and roles that can access a particular object

[Previous entry: "SQL Injection papers"] [Next entry: "eweek article on alert #68 discusses public exploit availability"]

who_can_access.sql : a script to find uses and roles that can access a particular object

October 15th, 2004 by Pete


This is the fourth script in a series of five scripts that I am presenting here that are very useful when auditing the security of an Oracle database. This script is called who_can_access.sql and is also available from my tools page. The script is used to test who has been granted privileges on specific objects in the database. This includes most objects in the database for instance TABLES, VIEWS, PL/SQL, LIBRARIES etc.

This script also does the same as the others in the series and prints out hierarchical reports of privileges granted via roles, or roles granted to roles granted to users. This allows you to see a complete picture of which users can see or access an object and from where the privilege was granted.

The report can be specified to the screen or to a file. If the report is sent to a file then either the utl_file_dir parameter needs to be configured to point at the directory to be used or a DIRECTORY object needs to exist that the user of the report has access to.

There are a couple of limitations, well not limitations really but design decisions. The first is that the report does not include access granted via privileges with the key word ALL included. For instance if you were to check SCOTT.EMP to see who has privileges on it you will find a list of users and roles. This list does not include those users granted the SELECT ANY TABLE privilege. These users would also be able to SELECT from this table. The same would apply to dictionary tables and the SELECT ANY DICTIONARY privilege. This is done deliberately. I feel it is better to check key %ANY% privileges separately. The script who_has_priv.sql can be used for this.

The second limitation, again not really a limitation but a design decision is that base SYS owned objects are used to get the details of the objects. This means that a user who has access to the SYS owned objects used in the script is needed.

Here is a simple example of the use of this tool to check who has access to the built in package UTL_FILE:



who_can_access: Release 1.0.1.0.0 - Production on Fri Oct 15 13:25:29 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: UTL_FILE
OWNER OF THE OBJECT TO CHECK [USER]: SYS
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Checking object => SYS.UTL_FILE
====================================================================


Object type is => PACKAGE (TAB)
Privilege => EXECUTE 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 => DBSNMP (ADM = NO)
Role => PUBLIC (ADM = NO)
User => SCOTT (ADM = NO)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm


The report is pretty simple and self explanatory. A second example below for the table EMP owned by the user SCOTT is now given. This shows that the report includes every privilege that has been granted:



who_can_access: Release 1.0.1.0.0 - Production on Fri Oct 15 13:28:06 2004
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK [USER_OBJECTS]: EMP
OWNER OF THE OBJECT TO CHECK [USER]: SCOTT
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:

Checking object => SCOTT.EMP
====================================================================


Object type is => TABLE (TAB)
Privilege => DELETE is granted to =>
User => OUTLN (ADM = NO)
Privilege => FLASHBACK is granted to =>
User => SYSTEM (ADM = NO)
Privilege => INSERT is granted to =>
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)
Privilege => SELECT is granted to =>
Role => PLSQL_ROLE_PRIV (ADM = NO) which is granted to =>
User => SYS (ADM = YES)
User => PLSQL_ROLES (ADM = NO)
Role => PUBLIC (ADM = NO)
User => SYSTEM (ADM = NO)
Privilege => UPDATE 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)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>


As you can see we have users and roles that have been granted SELECT, INSERT, UPDATE, DELETE and FLASHBACK privileges on this table. You can also see the hierarchy of privileges for the INSERT privilege.

Again the script can be found here.
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!