Tracking user activities in Oracle using triggers (1)

Oracle introduced special triggers starting with Oracle8i. These triggers are not DML events associated with special DML events, such as INSERT, UPDATE, and DELETE ). These system-level triggers include database start triggers, DDL triggers, and end-user login/logout triggers.

When Oracle provides the functions of these new triggers, it is unclear how to use these triggers to track system usage. This article describes how to use the method of creating terminal user login/logout to track terminal user activities. The system-level trigger for tracking end users is very new at the beginning, because it is very new, so it is still not fully functional.

Although the user login/logout trigger will tell you the exact time for user login and user cancellation, the Code cannot obtain any information about the activities performed during the user session stage.

These user login/logout triggers are very useful for applications that use timestamp users. The so-called timestamp user is the person who is given a unique Oracle user ID when accessing the application. Applications that do not use the timestamp Oracle user ID may not use these login/logout triggers.

Now that we know the basic knowledge, let's see how to design a user audit table to track user activities.

Design a user audit table

The first step is to create an Oracle table to store terminal user login/logout trigger information. To properly design these triggers, Let's first look at the system-level triggers. First, we will obtain the following information during login:

User ID-User ID, used for login;

Session ID-Oracle indicates the user-controlled session ID;

Host-computer name;

Logon Time-an Oracle time data type with the same logon time as a user, accurate to 0.001 seconds.

Now, we will obtain information that takes precedence over user cancellation only. When a user logs out, the Oracle system-level trigger can provide the user's current session status and activity information:

The final program-name of the last Program executed by the user upon cancellation.

Last activity-the last activity performed by the user in the session type.

Last module-the name of the last module that the user accesses before logging out.

Cancellation time-an Oracle time data type that is the same as the actual cancellation time of the user, accurate to 0.001 seconds.

Now that we know the login and logout information, how can we collect and manage the information? Let's take a look at the available options.

User table Standardization

Because the user's login/logout trigger is two separate entities, we can have multiple options when designing a table that supports storing this information. We can design two isolated tables: one user login table and one user logout table. If we do this, it will be difficult to connect the two tables and determine which user login caused the cancellation of the user. This opportunistic approach may lead to incorrect manufacturing opportunities. For example, how do users log out before logon? And so on.

Now, let's consider a better choice. To better use the table information, we can create a database that contains a single table to record the login and logout time information. This method saves the trouble of connecting tables and associated data. In addition, we will create a region for calculating the time used by each user in a specific session. This computation is done by the trigger in advance, which saves time and allows you to create a wide range of reports, which will be mentioned later.

Design a login trigger

Once the table is designed, the next step is to create a system-level login trigger, which records as much information as possible when the login time occurs. The login audit trigger I created. As you can see, I have created some information values that can be used during login in this table:

User-Oracle user ID for creating an Oracle session

Session ID-use the Oracle SYS context Function to directly obtain the session ID of Oracle from the v $ session table. Host-use the Oracle SYS context Function to obtain the host name when an Oracle session is created. Note that it is important to obtain the host name when using the Oracle Parallel Server or the actual application group, because we may have Session connections with many different remote hosts at the same time.

Logon Time-obtain the actual logon time, accurate to 0.001 seconds. Note how we divide the logon time into two regions. Generating a logon date and a logon time can make the final report readable.

Since the login trigger has been created, we need to create a logout trigger to obtain all information about the user's session completion.

Design a logout trigger

To enable a single table to serve both logon and logout events, you must first locate the logon row of a single user session. As you think, this tip allows many users to log on at the same time using the same user name. To limit this situation, I use the Oracle session ID. As you know, when each user logs on to Oracle, Oracle assigns a unique session ID to each user and adds the ID to the v $ session table. We can use this session ID like a primary key. In this way, when a user logs out, his logout information can update our user audit table.

Now let's take a look at the information we can use after using the logout trigger. First, we update the user's log table so that it contains the last activity of the user before cancellation. As you can see, the last activity is updated by using the SYS context Function to obtain the activity attribute information from the table v $ session.

Next, we update our audit table to display the last program accessed during the session. Again, we use the SYS context Function to select the program attribute column from the v $ session table.

We update the last module accessed by the user session. This is done by selecting the module attribute column in the v $ session table and filling our user audit table with its content.

The last and most important step is to add the logout time and calculate the user's stay time in the session. As shown in the Code, the logout time data type is used to update the user login table and then calculate the time used by the session. As I mentioned earlier, calculating the time of each user session in advance will make every record in the audit table very useful because it shows the session duration.

Let's check several reports that can be generated by the system. To meet special requirements, you can enrich/modify these reports. Now, it is very important to calculate the time used by the session in advance: it can create a more useful report.