Secrets for auditing Oracle user activity
 

Changes in U.S. federal laws have mandated increased security for auditing Oracle user activity. HIPAA, the Sarbanes-Oxley Act, and the Gramm-Leach-Bliley Act have all produced serious constraints on Oracle professionals who are now required to produce detailed audit information for Oracle system users.

Starting with Oracle8i, Oracle introduced special triggers that are not associated with specific DML events (e.g., INSERT, UPDATE, and DELETE). These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers.

While Oracle provided the functionality for these new triggers, it was not clear how they could be used in order to track system-wide usage. This article describes my work in creating end-user login/logoff procedures to facilitate tracking end-user activity. Please be advised that the initial implementation of system-level triggers for end-user tracking is quite new and, as such, is still a bit lacking in robust functionality.

While the user logon/logoff triggers will accurately tell you the time of the user logon and logoff, the code does not capture any information regarding the specific tasks that were performed during the user's session.

Also note that these user logon/logoff triggers are best used for applications that utilize time-stamped users, which means those users who are given a unique Oracle user ID when they access the application. Applications that do not utilize time-stamped Oracle user IDs (SAP, PeopleSoft) may not benefit greatly by using these logon/logoff triggers.

Now that we understand the basics, let's take a look at how we can design the user audit table to track user activity.

To find out the secrets for auditing Oracle user activity, read the full article here:

http://www.dba-oracle.com/art_builder_sec_audit.htm

*****************************************

Need a Health Check?

Oracle is the worlds most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf

*****************************************

How do I do that in Oracle?

Explore this series for a collection of tips that explain in detail how to perform specific tasks in Oracle related to database administration. Consisting of expert advice from top Oracle gurus, these how-tos provide step-by-step instructions to guide you through database administration processes related to installation and upgrades, backup and recovery and other Oracle basics.

http://searchoracle.techtarget.com/tip/0,289483,sid41_gci1264060,00.html?Offer=ORburl0206