Oracle Database 12c New Feature: Last Login Time

Oracle has started to record the last login time. It is a small but very useful 12c security feature and operates independently of the database audit. Nevertheless, there are some restrictions. But let’s start at the beginning…

A simple example
Ok, lets try to connect as user afsar

oracle@afiya~/ [ABD] sqlplus afsar/tiger

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 21:59:19 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 06 2013 07:29:29 +02:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

The highlighted line above show the last login information. The information itself is stored in column SPARE6 of SYS.USER$ and can be queried in the column LAST_LOGIN of DBA_USERS.


SQL> col username FOR a15
SQL> col last_login FOR a25
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

SESSION altered.

SQL> SELECT username,last_login FROM dba_users WHERE username='AFSAR';

USERNAME        LAST_LOGIN
--------------- -------------------------
AFSAR           12.09.2017 21:59:19

col name FOR a15
ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

SQL> SELECT name,spare6 FROM USER$ WHERE name='AFSAR';

NAME            SPARE6
--------------- -------------------
AFSAR           12.08.2017 19:59:19

The display of the last login information can also be disabled with -nologintime.

oracle@afiya:~/ [ADB] sqlplus -nologintime afsar/tiger

SQL*Plus: Release 12.1.0.1.0 Production on Mon Aug 12 22:24:04 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options


Nevertheless the login time is still being recorded. Visible through the identical timestamp 

QL> col username FOR a15
SQL> col last_login FOR a25                                                                
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

SESSION altered.

SQL> SELECT username,last_login FROM dba_users WHERE username='AFSAR';

USERNAME        LAST_LOGIN
--------------- -------------------------
AFSAR           12.09.2017 22:24:04

Ok, whats about SYSDBA and other password file users?

simple example


Let’s create a test user with required privileges. 

That is CREATE SESSION, SYSBACKUP and SELECT on DBA_USERS

SQL> CREATE USER king IDENTIFIED BY kong;

USER created.

SQL> GRANT CREATE SESSION TO king;

GRANT succeeded.

SQL> GRANT sysbackup TO king;

GRANT succeeded.

SQL> GRANT SELECT ON dba_users TO king;

GRANT succeeded.

First we initiated an SQL*Plus session as SYSBACKUP

oracle@afiya:~/ [ABD] sqlplus akb/pass AS sysbackup

SQL*Plus: Release 12.1.0.1.0 Production ON Mon Aug 12 22:35:36 2013

Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.


Connected TO:
Oracle DATABASE 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, Advanced Analytics, REAL Application Testing
AND Unified Auditing options

SQL> col username FOR a15
SQL> col last_login FOR a25
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

SQL> SELECT username,last_login FROM dba_users WHERE username='AKB';

USERNAME        LAST_LOGIN
--------------- -------------------------
AKB

As seen above, no logon time is recorded. This is because the user AKB has not been authenticated by a password but rather by OS authentication and the corresponding OS group. The same applies if the password file is used for authentication.

SQL> SHOW USER
USER IS "SYSBACKUP"

SQL> SELECT * FROM v$pwfile_users WHERE username='AKB';

USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
--------------- ----- ----- ----- ----- ----- ----- ----------
AKB            FALSE FALSE FALSE TRUE  FALSE FALSE          0

But again if we connect as regular user with password authentication the logon time will be recorded. In this case nothing will be displayed because it’s the first time the user AKB is logging into the database.

oracle@afiya:~/ [ABD] sqlplus akb/pass

SQL*Plus: Release 12.1.0.1.0 Production ON Mon Aug 12 22:46:28 2013

Copyright (c) 1982, 2013, Oracle.  ALL rights reserved.


Connected TO:
Oracle DATABASE 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
WITH the Partitioning, OLAP, Advanced Analytics, REAL Application Testing
AND Unified Auditing options

SQL> col username FOR a15
SQL> col last_login FOR a25
SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';

SESSION altered.

SQL> SELECT username,last_login FROM dba_users WHERE username='KING';

USERNAME        LAST_LOGIN
--------------- -------------------------
AKB             12.09.2017 22:46:28

SQL> SHOW USER
USER IS "AKB"

Conclusion

As stated above, this is a small but nice security feature. It does record logon and connect from any regular user. For a DBA , it is easy to check whether a user has logged in and how long it’s been.

SQL> SELECT username,last_login FROM dba_users WHERE last_login IS NOT NULL ORDER BY last_login;

USERNAME        LAST_LOGIN
--------------- -------------------------
HR              05.09.2017 09:04:06
SYSTEM          05.09.2017 14:52:12
TEST            12.09.2017 21:14:45
AFSAR           12.09.2017 22:24:04
AKB             12.09.2017 22:46:28

On the other hand, there are some small drawbacks.

• No login time recording for administrative users respectively password file users
• Login time is only displayed when logged in by SQL*Plus
• -nologintime just switch of display not recording of login time

Since this feature is intended primarily for interactive use, the limitations are understandable to a certain degree. Especially for SYSDBA, SYSDG and SYSBACKUP the last login time would be flooded by a bunch of internal connections by RMAN or dataguard. To ensure the traceability, it is mandatory to use database audit including audit of SYS operation.