Tag Archives: SQL*Plus

Oracle Database 12c New Feature: Last Login Time

As Markus Flechtner has already mentioned in his blog, 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 scott.

oracle@urania:~/ [TDB12] sqlplus scott/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='SCOTT';

USERNAME        LAST_LOGIN
--------------- -------------------------
SCOTT           12.08.2013 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='SCOTT';

NAME            SPARE6
--------------- -------------------
SCOTT           12.08.2013 19:59:19

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

oracle@urania:~/ [TDB12] sqlplus -nologintime scott/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 Mon Aug 12 22:24:04 2013 above and below

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='SCOTT';

USERNAME        LAST_LOGIN
--------------- -------------------------
SCOTT           12.08.2013 22:24:04

Ok, whats about SYSDBA and other password file users?

A little less 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@urania:~/ [TDB12] sqlplus king/kong 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='KING';

USERNAME        LAST_LOGIN
--------------- -------------------------
KING

As seen above, no logon time is recorded. This is because the user KING 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='KING';

USERNAME        SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
--------------- ----- ----- ----- ----- ----- ----- ----------
KING            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 KING is logging into the database.

oracle@urania:~/ [TDB12] sqlplus king/kong

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
--------------- -------------------------
KING            12.08.2013 22:46:28

SQL> SHOW USER
USER IS "KING"

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.08.2013 09:04:06
SYSTEM          05.08.2013 14:52:12
TEST            12.08.2013 21:14:45
SCOTT           12.08.2013 22:24:04
KING            12.08.2013 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.

References

Links related to this post, password file users, security and more

  • Oracle® Database New Features Guide 12c Release 1 (12.1) Last Login Time Information
  • SQL*Plus® User’s Guide and Reference Release 12.1 No Login Time Option
  • SYSDBA and SYSOPER Privileges in Oracle [50507.1]
  • Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues [233223.1]
  • Why Can I Login AS SYSDBA With any Username and Password? [242258.1]
  • All About Security: User, Privilege, Role, SYSDBA, O/S Authentication, Audit, Encryption, OLS, Database Vault, Audit Vault [207959.1]