First, you should check to see that no general users are granted powerful roles, such as DBA.  A query like the dbagranted.sql  script can determine if such is the case.

A complete listing of the dbagranted.sql script can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. The query looks at granted_role in the sys.dba_role_privs view.

Likewise, you check to see if any user accounts have been granted sensitive privileges, or roles that provide them with the potential to cause serious damage to the database.  For example, a user with the unlimited tablespace  privilege can place data in the SYSTEM tablespace, which should only be reserved for data dictionary objects.  The sensprivs.sql  script can help you quickly find such accounts.

A complete listing of the sensprivs.sql script can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. This script also queries the sys.dba_role_privs view.

If any users do indeed have sensitive privileges or roles, output from the query above might look like this:

GRANTEE           PRIVILEGE         ADMIN_OPTION

------------------------------------------------

BAD_GUY           ALTER ANY PROCEDURE         NO

BAD_GUY           ALTER ANY TRIGGER           NO

BAD_GUY           CREATE ANY INDEX            NO

BAD_GUY           CREATE ANY PROCEDURE        NO

BAD_GUY           CREATE ANY TABLE            NO

BAD_GUY           CREATE ANY TRIGGER          NO

BAD_GUY           DROP ANY PROCEDURE          NO

BAD_GUY           DROP ANY TRIGGER            NO

BAD_GUY           EXECUTE ANY PROCEDURE       NO

BAD_GUY           UNLIMITED TABLESPACE        NO

Users found with these two queries should be examined to see if they really need the special privileges that they have been granted.  In addition to users having sensitive privileges or roles, you should check to see if any user accounts have undesired abilities with respect to storage. 

User accounts with unchecked storage powers can cause major headaches for you as a DBA.  The badstorage.sql  script can be used to find such accounts.

A complete listing of the badstorage.sql script can be obtained from the online Code Depot at http://www.dba-oracle.com/bp/bp_book5_perf.htm. This script queries the sys.user$ and sys.ts$ views.

If you have user accounts with unchecked storage capabilities, the output form the query above might look like this:

USERNAME                    PRIVILEGE

--------------------------------------------------

AQ_ADMINISTRATOR_ROLE       SYSTEM AS DEFAULT

BAD_GUY                     UNLIMITED TABLESPACE

BAD_ROLE                    SYSTEM AS DEFAULT

BILLY                       UNLIMITED TABLESPACE

BRKADMIN                    UNLIMITED TABLESPACE

CODER                       UNLIMITED TABLESPACE

Any user account that’s identified in the badstorage  query should be altered, so their storage settings don’t have the potential to impact the database in a negative manner.

Finally, although it’s surprising that DBAs in today’s very security-conscious environment would allow this to occur, you ought to make sure that no critical database exists with default passwords for the SYS and SYSTEM accounts.

Once you've plugged any database security holes, you should move on to examining each session's storage capabilities. 

If you're a DBA who's looking for real world Oracle tuning techniques, Oracle scripts, and advice on how to get to the heart of critical Oracle performance problems, then you've come to the right place. Oracle Performance Troubleshooting: With Dictionary Internals SQL & Tuning Scripts was written by one the world's most widely-read DBAs and Oracle internals experts. Robin Schumacher focuses his incredible knowledge of the Oracle data dictionary into a superb book that shows how to quickly troubleshoot and correct Oracle performance problems. 

Plus! The online code depot is available immediately!

http://www.dba-oracle.com/bp/bp_book5_perf.htm