Pete Finnigan's Oracle Security Weblog

I have just made some updates to my scripts who_can_access.sql, who_has_role.sql and who_has_priv.sql that are available from my Oracle security tools page. The changes were to add two new parameters to the scripts. The first asks if you wish to exclude a user(s) from the report output, the second asks for the username to exclude. Here is an example for who_has_role.sql to check for users granted the DBA role and also to exclude the user SYSTEM.

SQL> @c:\petefinnigan.com\who_has_role.sql

who_has_priv: Release 1.0.3.0.0 - Production on Wed May 04 20:29:42 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK [DBA]: DBA
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]: Y
USER TO SKIP [TEST%]: SYSTEM

Investigating Role => DBA (PWD = NO) which is granted to =>
====================================================================
User => SYS (ADM = YES)
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
Role => HACKER (ADM = NO|PWD = ) which is granted to =>
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit /tools.htm

SQL>

The user SYSTEM has been removed from the output report. It is also possible to use % in the name specification so that I could for instance remove SYS and SYSTEM from the output as follows:

SQL> @c:\petefinnigan.com\who_has_role.sql

who_has_priv: Release 1.0.3.0.0 - Production on Wed May 04 20:31:57 2005
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

ROLE TO CHECK [DBA]: DBA
OUTPUT METHOD Screen/File [S]: S
FILE NAME FOR OUTPUT [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY or file (/tmp)]:
EXCLUDE CERTAIN USERS [N]: Y
USER TO SKIP [TEST%]: SYS%

Investigating Role => DBA (PWD = NO) which is granted to =>
====================================================================
User => HACK (ADM = NO)
User => TEMP (ADM = NO)
User => WKSYS (ADM = NO)
User => CTXSYS (ADM = NO)
Role => HACKER (ADM = NO|PWD = ) which is granted to =>
User => TEST01 (ADM = NO)
User => APP_EXAMPLE (ADM = NO)
User => TEST01_PRIV (ADM = NO)
User => SCHEMA_OWNER (ADM = NO)
User => REMOTE_OS_USER (ADM = NO)

PL/SQL procedure successfully completed.

For updates please visit /tools.htm

SQL>

This new feature was added for use in the SANS 6 day hands on securing Oracle track that I wrote in case you are wondering why I added these changes.

If you have downloaded these scripts before please feel free to get them again, they are now all at version 1.0.3.0.0