How to use the CREATE SESSION command to track Oracle database logins

Out-of-the-box, the Oracle database is not well configured to track the comings and goings of the database users. However, this does not mean that it is not well-suited for such tasks. In fact, the Oracle database auditing capabilities are incredibly configurable, robust and flexible. Properly and effectively using the integral database auditing capability is the cornerstone of an effective security policy. Since we can't prevent zero-day exploits, it is just as important to be able to perform forensics on the actions of someone who has gained unauthorized access to our data stores as it is to prevent the occurrence in the first place.

NOTE: I'm unaware of what version of Oracle RDBMS you are using, but the following solution should be effective for versions 9i-11g, unless otherwise noted.

For your specific need, determining which users have not logged in during the past 30 days, we'll start by enabling auditing in your database (Note – You must be logged in under a privileged account to perform these actions):

SQL> alter SYSTEM set audit_trail=DB scope=spfile;
SQL> shutdown immediate
SQL> startup
SQL> show parameter audit_trail;

This action will take effect after the next restart of the database, which is why I included the shutdown and startup. Once this parameter has been set and the database restarted, your database will NOT automatically start auditing user actions, but it will have the ABILITY to do so at any time, without another restart.

I have chosen to set the audit_trail parameter to 'DB' in this example, because this will record activity in the SYS.AUD$ table, inside the database. This allows us to use simple SQL queries, and Oracle-provided views to quickly and easily aggregate the information we are looking for. There are other options to extend the information collected or to store the audit information in files of various formats in the Operating System's file system. The Oracle documentation on audit trail provides a full description of all the options.

Because we have chosen to store the audited activity inside the database, now is a good time to consider an important best practice for the SYS.AUD$ table. The SYS.AUD$ table is going to grow as we collect and store audit information, and since it resides in the SYSTEM tablespace, it will cause the SYSTEM tablespace to grow. Unfortunately, there is really no graceful way to shrink the SYSTEM tablespace after it grows. Luckily, the SYS.AUD$ table is one of the few SYS tables that we are able to move without creating any issues inside the database!

The best practice is to simply create a tablespace specifically for the SYS.AUD$ table. This allows it to grow without impacting the size of the SYSTEM tablespace and associated datafile. If the auditing table and a Non-SYSTEM tablespace grows too large, we have a lot of options to archive the table contents and resize the tablespace, or move it to a new one. In addition, this tablespace can be sequestered to a different folder with very specific permissions to enhance our security posture, and further prevent tampering with our audit information. (The audit information will be the very first target of a sophisticated attack.) However, this is a bit outside the scope of the original issue.

Here are some sample commands to accomplish this best practice. First we create a new tablespace for the audit table:

SQL> CREATE BIGFILE TABLESPACE "AUDITING"   
  DATAFILE '[Insert your path here without square brackets].AUDIT01.DBF' 
  SIZE 10M 
  AUTOEXTEND ON NEXT 1024K 
  MAXSIZE UNLIMITED 
  LOGGING 
  EXTENT MANAGEMENT LOCAL 
  SEGMENT SPACE MANAGEMENT AUTO;

-- NOTE – 'BIGFILE' is only applicable to versions 10g & 11g, omit for 9i use

Then we will move the SYS.AUD$ audit table to the new tablespace:

SQL> alter TABLE SYS.AUD$ move tablespace AUDITING; 

This will allow you to verify the successful move:

SQL> select TABLE_NAME, TABLESPACE_NAME, STATUS
  from DBA_TABLES
  where TABLE_NAME like 'AUD$';

The next step is to start auditing the actions that will produce the data we need to determine which users have logged in during the past 30 days, namely CREATE SESSION. This will record who logged in, when they logged in, and if it was successful.

SQL> audit CREATE SESSION;

That's it! Now we just use SQL to filter out the users that have SUCCESSFULLY logged in during the past 30 days. For ease-of-use and performance, I highly suggest creating a view that selects all the users that have successfully logged in the past 30 days and then remove those users from the selection via the query. The query will also let you add Oracle default users and users that are necessary, but cannot login so they will not be displayed in the results set.

SQL> create view LOGIN_LAST_THIRTY_DAYS_VIEW
  as
  select DISTINCT u.USERNAME
    from DBA_USERS u join DBA_AUDIT_SESSION a
    on u.USERNAME = a.USERNAME
    where a.RETURNCODE = 0
      and SYSDATE - a.TIMESTAMP between 0 and 30;

SQL> select DISTINCT USERNAME
  from DBA_USERS
  where 
    USERNAME not in
    (select * from LOGIN_LAST_THIRTY_DAYS_VIEW)
    and USERNAME not in
    ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP', 'Add other oracle default schemas here as needed')
  order by USERNAME;

This activity seem formidable, but in reality, it is just a couple SQL commands and a reboot – don't be intimidated by all the background information! Now that you have done all the legwork, the information you are collecting in the audit table can also be used to detect brute force attacks, perform behavior analysis to highlight suspicious activity, generate usage stats for profiling and much more.