Oracle 12c introduced a new, very useful security feature to store and display the last login information for non-sys users, logging in via SQL*PLUS. The last login time is displayed in the banner by default when we log into the database using SQL*PLUS in 12c.
Related Articles
• Oracle 12c: Data Redaction Unified Auditing Extended SHA-2
• Overview of Unified Auditing in Oracle Database 12c
Last Login Time in DBA_USERS
Let’s try to open a terminal and login with a test user.
$sqlplus
SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 14:42:22 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: test/[email protected]//localhost:1521/pdb1.erp.com
Last Successful login time: Fri May 09 2014 14:42:01 +05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
By default the last login information will be displayed, if you do not want it, just disable it with “nologintime”.
$sqlplus -nologintime test/[email protected]//localhost:1521/pdb1.erp.com
SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 14:46:15 2014
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 and Real Application Testing options
Irrespective of whether the -nologintime option is used or not, the last_login information is still collected. This information can also be queried from last_login column in dba_user table.
SQL> select username, last_login FROM DBA_USERS where username='TEST';
USERNAME LAST_LOGIN
-------------------- -------------------------------
TEST 09-MAY-14 02.46.16.000000000 PM +05:00
External Authentication
When a user is not authenticated by a password but by OS authentication using an OS group, the logon time is not recorded.
SQL> sqlplus TEST/TEST AS sysbackup
SQL*Plus: Release 12.1.0.1.0 Production on Fri May 9 14:49:14 2014
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> SELECT username,last_login FROM DBA_USERS WHERE username='TEST';
USERNAME LAST_LOGIN
--------------- -------------------------
TEST
Conclusion
From a DBA perspective it is very easy to check when the user last logged in. Although the information can be very handy, it has a couple of restrictions.
- Login time is only displayed when we logged in with sql plus.
- -nologintime only switches off display of time. The login time will still get recorded in the data dictionary.
External References
• New Features Guide 12c Release 1 (12.1) Last Login Time Information
• SYSDBA & SYSOPER Privileges in Oracle [50507.1]
• Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues [233223.1]
Leave a Reply