Trigger if updating then
Now that we understand the basics, let's take a look at how we can design the user audit table to track user activity.
The first step is to create an Oracle table that can store the information gathered by the end-user logon/logoff triggers.
In order to properly design these triggers, let's begin by looking at the information that's available inside the system-level triggers.
First, we'll gather the information provided at login: Since the user logon/logoff triggers are separate entities, we have several choices in the design of a table to support this information.
We could design two separate tables: a user logon table and a user logoff table. In order to get around this table issue, we can create a database design whereby a single table is used to record both logon and logoff events (Listing A).
If we did, however, we'd have the difficulty of joining these two tables together and correlating which logon corresponds to which logoff and so on. This eliminates the need for table joins and data correlation.
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.
This article describes my work in creating end-user login/logoff procedures to facilitate tracking end-user activity.We update the last module that was accessed by the user session.This is accomplished by selecting the module column from the v$session table and then placing it into our user audit table.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.Next, we update our audit table to show the last program that was accessed during the session.Again, we invoke the SYS context function to select the program column from the v$session table.We begin by updating the user log table to include the last action performed by the user.As you'll note in Listing C, updating the last action is accomplished by using the SYS context function to grab the action column from the v$session table.To make a single table function for both logon and logoff events, it's first necessary to locate the logon row that is associated with the individual user session.As you might imagine, this is tricky because you may have many users who are signed on with identical user names.