Recording Last Successful Login Time for Database Users is a nice little security feature introduced in Oracle 12c. In a four part series, I am going to describe this feature along with caveats and pitfalls with its usage/adoption.
Background : In production environment, it is always a headache to identify "inactive" user accounts. These user accounts could be belonging to person(s), no longer associated with the organization, user accounts no longer relevant (Either the Application underneath got upgraded or got migrated to a new architecture rendering these accounts redundant). The Secure-By-Default policy recommends having all such user accounts removed or at least have them as locked/unusable state, with all their system/object level privileges revoked.
Prior to 12c, however, there was no "easy" way to derive this information. The DBAs had to create a logon trigger to store the logon time in a separate table or set up auditing to audit connects in order to record successful logon time. However, in addition to the burden of maintaining audit records and/or audit policies, it also entail certain overhead to logon process, as we may need to perform DMLs underneath the logon trigger. Besides, one has to write additional code to review audit-logs/logon-tables against active accounts and check for no logins and then locking inactive accounts.
Oracle 12c introduces a new feature, by which, Oracle takes this pain of identifying inactive account onto itself and provides an easy way to determine the last time, a user may have logged in.
Implementation : DBA_USERS dictionary view has a new column LAST_LOGIN defined on top of SPARE6 column of USER$ table to record the Last Successful Login Time of a user. USER_USERS and ALL_USERS does not host this information, though. Suppose, you want to list out all users, who have not logged in during last 3 months. Following SQL query will list out all such users
How to enable : This security feature is enabled by default for all database users and does not require any additional configuration/setup !! It is available in Oracle Multi tenant as well as non-Multi tenant installations.
Prior to 12c, however, there was no "easy" way to derive this information. The DBAs had to create a logon trigger to store the logon time in a separate table or set up auditing to audit connects in order to record successful logon time. However, in addition to the burden of maintaining audit records and/or audit policies, it also entail certain overhead to logon process, as we may need to perform DMLs underneath the logon trigger. Besides, one has to write additional code to review audit-logs/logon-tables against active accounts and check for no logins and then locking inactive accounts.
Oracle 12c introduces a new feature, by which, Oracle takes this pain of identifying inactive account onto itself and provides an easy way to determine the last time, a user may have logged in.
Implementation : DBA_USERS dictionary view has a new column LAST_LOGIN defined on top of SPARE6 column of USER$ table to record the Last Successful Login Time of a user. USER_USERS and ALL_USERS does not host this information, though. Suppose, you want to list out all users, who have not logged in during last 3 months. Following SQL query will list out all such users
select
username, last_login
from
dba_users
where
months_between (sysdate, last_login) >= 3
order by
last_login;
How to enable : This security feature is enabled by default for all database users and does not require any additional configuration/setup !! It is available in Oracle Multi tenant as well as non-Multi tenant installations.
%% sqlplus system/manager
SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 10 20:20:25 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat Oct 10 2015 20:20:22 +05:30
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL>
-nologintime : You can use the "-nologintime" option in SQL*Plus to disable displaying Last Successful Login Time on screen. However, this only disables the _display_ and Oracle continues to record the logon time in USER$. In fact, there is no way to disable recording of LSLT for any users (Sorry, no underscore parameter hack either ;-))
connect /as sysdbacreate user testUser identified by testPass;
grant create session to testUser;
conn testUser/testPass
%% sqlplus -nologintime testUser/testPass
SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 10 20:28:02 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL>
REM
REM LAST_LOGIN for testUser gets rightly updated, even when -nologintime option
REM was used at the time of SQL*Plus command line invocation.
REM
connect system/manager
SQL> select last_login from dba_users where username='TESTUSER';
LAST_LOGIN
-----------------------------------------------
10-OCT-15 08.28.06.000000000 PM +05:30
Additional Notes :
1) The last login time is displayed in local time format. Here is a query against USER$.SPARE6 to confirm the same (This is exactly how DBA_USERS.LAST_LOGIN field is populated out of USER$.SPARE6 column field inside cdenv.sql located underneath %ORACLE_HOME%/rdbms/admin)
SQL> select from_tz(to_timestamp(to_char(spare6, 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS'), '0:00') at time zone sessiontimezone as LAST_LOGIN from sys.user$ where name='TESTUSER';
LAST_LOGIN
-----------------------------------------------
10-OCT-15 08.28.06.000000000 PM +05:30
2) The Last Successful Login Time is displayed only at the time of SQL*Plus invocation. So if you invoke SQL*Plus and then issue a CONNECT command like the followings, it does not display the LSLT on successful login.
%% sqlplus /nolog
SQL*Plus: Release 12.1.0.2.0 Production on Sat Oct 10 20:41:25 201
Copyright (c) 1982, 2014, Oracle. All rights reserved.
SQL> connect testUser/testPass
Connected.
3) Recording LSLT is not something specific to SQL*Plus clients. Connections to Oracle DB established via other clients like OCI, JDBC etc. also populate the LSLT in USER$. However, they may not see the "Last Successful Login Time" message which gets displayed alongside the SQL*Plus banner.
Further details on Last Successful Login Time
Further details on Last Successful Login Time
No comments:
Post a Comment