• Database Blog
  • GoldenGate 12c
    • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Master Oracle 12c DBA
  • Oracle Tutorials
    • Oracle Tips & Tricks
    • Oracle VirtualBox
  • Learn Excel

VitalSoftTech

Database Administration | Oracle | Oracle Training | DBA Support | Oracle 12c DBA | GoldenGate | GoldenGate 12c | DBA Interview Questions | Goldengate Interview Questions | Natik Ameen

  • Digital Marketing
  • Blogging Tutorial and Tips
  • Cool Names
  •   Login
« Previous Post
Next Post »

Oracle 12c Last Login time for Non-Sys Users

July 3, 2014 by Natik Ameen   /  Oracle 12c, Oracle 12c Database

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.

  1. Login time is only displayed when we logged in with sql plus.
  2. -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]

Filed Under: Oracle 12c, Oracle 12c Database Tagged With: 12C Database Security

Leave a Reply Cancel reply

Your email address will not be published.

« Previous Post
Next Post »

Join over 3,000 others My posts. Your Inbox. Beautiful.

Database    GoldenGate

Natik Ameen

About Natik Ameen

Natik Ameen is an Oracle Production DBA, Oracle Certified RAC Expert and a DBA track Certification trainer for over 17 years. He is an Oracle Evangelist and has presented at IOUG & UTOUG conferences. He writes on topics such as Database Administration, RAC, GoldenGate and the Cloud.

Who is Natik Ameen?

Guides

  • My GoldenGate Tutorials
  • Oracle 12c Database Tutorials
  • GoldenGate GGSCI Command Series
  • My OraTips and Tricks

Popular Blogs

  • My GoldenGate Home Page
  • Top 50 Oracle Goldengate Interview Questions To Get the Job!
  • Top 30 RAC Interview Questions That Helped Me. Are You Prepared?
  • Oracle GoldenGate Concepts and Architecture Made Simple!
  • Virtualize Oracle RAC 12c on My Laptop
  • Step by Step Oracle 12c Database Install on Virtual Box

Popular Oracle Articles

Oracle Announces Beta Availability of Oracle Database 12c Release 2
Top 5 Announcements at Oracle Open World
News on Oracle Database STANDARD EDITION 12.1.0.2

Technology Centers

Oracle Business Intelligence and DW
Cloud Computing, SOA, Virtualization
Big data, Java, Linux, PLSQL, Security
Enterprise Architecture, Digital Experience

Oracle Documentation

10g R1, 10g R2, 11g R1, 11g R2, 12c R1
Oracle CPU's, EM 11g, EM 12c
GoldenGate 12c, Oracle VM, VirtualBox

Copyright ©2021

BLOGOracle Tutorials