CONTAINER clause in Oracle Multi-Tenant Environment : Part I

Oracle 12c Multitenant added an optional CONTAINER clause to various DDL statements, by which one could specify the scope of execution of a DDL statement and whether it would effect one container or all containers across the CDB. With the advent of common users|roles|profiles in consolidated database, this clause assumes significance, as it allow a CDB level Database Administrator to manage these common entities without going into each individual PDBs.

Simple Use Case
--------------------
Suppose, the CDB administrator decides to lock out WMSYS user across the consolidated database. Since WMSYS is an Oracle supplied common user present in all the containers, one obvious way to accomplish this would be to log into each PDB and lock it. But it is somewhat cumbersome and does not cover any future PDBs, which might get plugged into the CDB. So the DBA could do something like the followings

  conn system/password@rootorcl
  alter session set container=MARKETING;
  alter user wmsys account lock;
  alter session set container=SALES;
  alter user wmsys account lock;
  alter session set container=HR;
  alter user wmsys acccount lock;

What if we would accomplish this in one single statement ?? Yes, that's what CONTAINER clause gives you. The ability to manage these common entities by sitting in CDB$ROOT and without iterating through each of the PDBs underneath.

  conn system/password@rootorcl
  alter user wmsys account lock CONTAINER=ALL

and the best part is, in future, if a new Pluggable database container get plugged in to the CDB, this statement will get executed inside the newly plugged PDB and WMSYS account will be locked in the new PDB as well (This gets accomplished via a small feature call "PDB Sync", which I hope to cover in my future blogs).

For entities local to a Pluggable database i.e, LOCAL users|roles|profiles, this clause even when specified will simply be ignored. However,  we shall see that CONTAINER=CURRENT also allows a PDB level administrator to set certain attributes for common users, like resource profile, proxy authorization etc.

Important Facts/Usage Notes
---------------------------------

• You can't specify this clause when connected to a non-Multitenant environment (Any attempt to do so will result into an ORA-65117 error)
• This clause can be specified when connected either to CDB$ROOT container or a Pluggable Database container.
• The only values allowed for container clause are CURRENT and ALL, raises ORA-65013 error otherwise.
• CONTAINER=ALL from within a Pluggable database container is not allowed (Any attempt to do so will result into an ORA-65050 error) and can be specified ONLY when connected to CDB$ROOT container
• CONTAINER=CURRENT can be specified both within a Pluggable database container as well as CDB$ROOT container.
• The clause is optional and when omitted, defaults to either CURRENT or ALL, depending on following two things
• A) Type of DDL statement being executed
• B) The container where the DDL statement is being executed
• The only DDL statements which accept CONTAINER clause are {CREATE|ALTER} {USER|ROLE|PROFILE} and ALTER SYSTEM.

In a subsequent post, I will go over each of these DDL statements along with usage examples.

Account Status Check During Administrative Logon In Oracle Multitenant

In an earlier post, I had talked about a behavior change in 12c by which administrative logons AS SYSDBA/SYSOPER etc are made to honour account status for authenticating user. In this post, I will cover the Oracle multi-tenant aspect of the same.

As is true for most (though, admittedly not all) features of Oracle RDBMS, the support for honouring account status during SYSDBA/SYSOPER logons is available in Oracle multitenant environment as well. In addition, there are few Multi-tenant specific behavior, which is worth mentioning.

• Account status check happens for both PDB local as well as CDB common users
• Any container level local lock on a common user account is also honoured. So if the common user, who possesses administrative privileges, granted either commonly across the CDB or locally inside the PDB, is locked locally inside that PDB, any attempt to connect AS SYSDBA to that PDB will fail with ORA-28000 error.
• If the PDB, where the common user account is locked, is closed for business, the SYSDBA logon would go through successfully.
• Here too, SYS user is exempted from any container level lock, either a PDB local lock or CDB wide common lock.

Setup For Common Administrative Privilege User With PDB Local Lock

----------------------------------------------------------------------------

conn sys/knl_test7@rootorcl as sysdba

create user c##a identified by a;

grant sysdba to c##a container=all;

alter session set container=pdborcl;

alter user c##a account lock;

alter session set container=cdb$root;

SQL> select account_status, decode(con_id, 1, 'CDB$ROOT', 2, 'PDB$SEED', 3, 'PDBORCL') as con_name from cdb_users where username = 'C##A' order by 2;

ACCOUNT_STATUS       CON_NAME

----------------------    ------------

OPEN                           CDB$ROOT

LOCKED                       PDBORCL

Logon Behavior
-----------------

REM

REM SYSDBA connection to CDB$ROOT and other pluggable databases

REM goes through successfully, but those to PDBORCL fail with ORA-28000

REM

SQL> conn c##a/a@pdborcl as sysdba

ERROR:

ORA-28000: the account is locked

SQL> conn c##a/a@rootorcl as sysdba

Connected.

REM

REM However, if the PDB is closed, SYSDBA connection to it goes through, as

REM account status can't be retrieved from PDB's USER$, when PDB is down

REM

SQL> alter pluggable database pdborcl close;

Pluggable database altered.

SQL> conn c##a/a@pdborcl as sysdba

Connected.

Behavior in 12.1.0.1

----------------------

• "SYS user exemption from account lock check" is NOT available in 12.1.0.1. This is true for PDB local lock as well.

Further Reading

-------------------

• Honouring Account Status During Administrative Logon
• 12c Security Guide Enterprise Security Based Authentication
• Automatic Locking of User Accounts After Failed Logins
• Pete Finnigan's answer on whether it is possible to lock out SYS

Honouring Account Status During Administrative Logon

In this post, I will talk about a behavior change in 12c to honour account status for authenticating user during an administrative logon specifying AS SYSDBA/SYSOPER etc. (I will stop sort of calling it a feature, as strictly speaking, this should have been _THE_ behavior since Day 1)

Till 12c, unlike regular logons, any administrative privileged logons, like "AS SYSDBA" or "AS SYSOPER", does not perform any check on account status of authenticating user. So if an administrative privileged user account is LOCKED, a regular connection i.e, logon attempt WITHOUT specifying AS SYSDBA will fail with ORA-28000 error, but an administrative connection (WITH AS SYSDBA) will go through successfully.

This behavior has been _sort of_ "accepted" by Oracle customers as expected behaviors (Why would you block Administrative Privileged users, who are highly privileged user accounts and in a production environment, only a handful of people would have it). But strictly speaking from security principles' perspective, it is clearly a violation. In 12c, Oracle has made an attempt to correct this anomaly. Now if the user account is locked, the logon would fail with ORA-28000 error.

Locked Administrative Privileged User Setup

------------------------------------------------

conn sys/knl_test7@pdborcl as sysdba

create user u1 identified by u1;

grant connect, sysdba to u1;

alter user u1 account lock;

Pre 12c Behavior

-------------------

REM

REM DIRECT logon without specifying AS SYSDBA fails with ORA-28000 error, 
REM but an ADMINISTRATIVE logon AS SYSDBA goes through successfully !!

REM

conn u1/u1@pdborcl

conn u1/u1@pdborcl as sysdba

12c Behavior
--------------

REM

REM DIRECT logon without specifying AS SYSDBA as well as

REM ADMINISTRATIVE logon AS SYSDBA fails with ORA-28000 error

REM

conn u1/u1@pdborcl

conn u1/u1@pdborcl as sysdba

Important Facts/Usage Notes
---------------------------------

• The check does NOT get enforced for local OS based Authentication.
• The check gets enforced for both password file based authentication and LDAP based Authentication making use of Oracle Internet Directory.
• Password file does not host information about user' account status and relies on the relevant dictionary (user$ table to be precise) queryable state. So the account lock check is enforced, only when instance is up and running. So if the DB is down, Oracle will not perform any check on locked account status and allows the SYSDBA connection to go through successfully, even when, the user account may be locked.
• SYS user is exempted from this check. So even if SYS user is explicitly locked via "ALTER USER ACCOUNT LOCK" DDL, SYSDBA connection as SYS user will go through successfully. From a security stand point, one may argue against this cotton-wool treatment for SYS user, but SYS is considered like an equivalent of ROOT in *nix enviornment and is expected to have unbridled access to the system. So it is _OK_ to have him logged in irrespective of whatever his account status may be.
• The check is limited to ACCOUNT STATUS and still does not take the PASSWORD STATUS into account. So if administrative user' password is EXPIRED, a DIRECT logon would raise ORA-28001 error and logon will NOT proceed without a password change. However, an ADMINISTRATIVE logon AS SYSDBA goes through successfully without ORA-28001 error being raised during logon and any prompt for password change prompt.

Behavior in 12.1.0.1

----------------------

• "SYS user exemption from account lock check" is NOT available in 12.1.0.1. This was rectified in 12.1.0.2 and was duly noted by Paul Wright in his blog post on July 2014 CPU "In other news I noticed that the stealth SYS locking feature is now reverted by Oracle in 12.1.0.2 – good move in my view. Maybe some more to come on this in the future."

Pre-12c Behavior

-------------------

• RDBMS 11.2.0.4.0 also supports locked account status check for administrative privileged logons
• "SYS user exemption from account lock check" is NOT available in 11.2.0.4.
• RDBMS 11.2.0.3 and prior versions does not perform any account status related check on administrative logons

Further Reading

-------------------

• 12c Security Guide Enterprise Security Based Authentication
• Automatic Locking of User Accounts After Failed Logins
• Pete Finnigan's answer on whether it is possible to lock out SYS

I have covered Oracle Multitenant Behavior in a follow up post
  http://ora1017.blogspot.in/2016/01/account-status-check-during.html

Last Successful Login Time Oracle 12c: Part IV

In this concluding post of 4-part series on LSLT(Last Successful Login Time), I will cover some of the details related to LSLT implementation for Real Application Users.

Oracle 12c introduced a new feature called "Real Application Security" to better manage Application users, when they interact with the backend. One could create Application users, who can create lightweight application sessions to the database through the middle tier or create heavyweight database sessions by connecting to the database directly [Refer to Database Real Application Security Administrator's and Developer's Guide for further information).

Simple Example : In the following example, I am creating two application users, one a direct login application user and other a regular non-direct login application user. Once created, try to establish lightweight application and heavyweight database sessions

conn /as sysdba
exec xs_principal.create_user('appUser1');
exec xs_principal.create_user('appUser2');
exec xs_principal.set_password('appUser2','appPass2');
declare
  sessionid raw(16);
begin
  sys.dbms_xs_sessions.create_session('appUser1', sessionid);
  sys.dbms_xs_sessions.attach_session(sessionid);
  sys.dbms_xs_sessions.detach_session;
end;
/
SQL> conn appUser2/appPass2
Connected.

Dictionary View to Query LSLT : Unlike DBA_USERS, the dictionary view corresponding to RAS users DBA_XS_USERS does not have any column to display Last Successful Login Time field. At present, XS$PRIN (The sort of USER$ for RAS users) does have a column LSLOGONTIME, but it does not look like it is being populated in case of RAS user logons, as is evident from the following query.

conn /as sysdba

SQL> select 1 from XS$PRIN where PRIN# in (select id from XS$OBJ where name in ('APPUSER1','APPUSER2')) and LSLOGONTIME is NULL;
         1
----------
         1
         1

RAS was enhanced in 12.1.0.2 to support user password policy management, such as account locking, password aging, and expiry, password complexity verification, and password history management for direct login Real Application users. However, LSLT support was not included. Perhaps, in future releases, Oracle may add this feature. 

After Thought on LSLT for RAS users : Now that it is confirmed that LSLT is not recorded as part of XS$PRIN, let's establish another connection for direct login RAS user using SQL*Plus

%% sqlplus appUser2/appPass2

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 12 06:27:30 2015

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

Last Successful login time: Mon Oct 12 2015 06:23:42 -07:00

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 options

SQL> show user
USER is "APPUSER2"

As you can see, we are seeing an LSLT printed on the screen, though not necessarily those of RAS user !! So the natural question is, if XS$PRIN is not recording the LSLT, from where we are populating it ?? The answer is, all RAS users' (Application users) sessions gets treated internally as that of XS$NULL (DB User) session. So when the login happens, we are pulling the SPARE6 from USER$ of XS$NULL user instead of LSLOGONTIME from XS$PRIN for APPUSER2, resulting into an incorrect display of LSLT on SQL*Plus invocation. This can be confirmed from a LSLT query against DBA_USERS for XS$NULL DB user.

conn /as sysdba
SQL> select last_login from dba_users where username='XS$NULL';

LAST_LOGIN
-----------------------------------------------
12-OCT-15 06.27.30.000000000 AM -07:00

What this also means is, if there are two direct login Application users, SALESAPPUSER and HRAPPUSER, both of them will incorrectly be updating LSLT for XS$NULL user in USER$, but nothing in XS$PRIN. So it would suffice to say that LSLT is not supported (yet) for Real Application Security Direct Login Users, as on 12.1.0.2.

Further details on Last Successful Login Time

• Part I (Introduction/Usecase in Production Environment)
• Part II (Known Limitations/Workaround)
• Part III (Oracle Multitenant/Common Users)

Last Successful Login Time Oracle 12c: Part III

In my earlier posts [Part I Part II], I had described LSLT(Last Successful Login Time) at length and explained how the newly introduced feature in 12c can be used to weed out inactive user accounts in a production environment. Today, I am going to explain how does it behaves for local and common users, when connected to a CDB environment aka Oracle Multitenant. If you are not already aware of(must be sleeping to do so), Oracle multi-tenant supports two types of users, depending on the container types 

1) Local User : These are same as the customer created users in pre-12c era. Since such users exists only within a PDB (Pluggable Database) and CDB$ROOT container can't have LOCAL users, whatever has been discussed in Part I and Part II continues to hold good for PDB local users.

2) Common User : Since the common user (Either an Oracle supplied one or a customer created one with "C##" prefix) exists as a valid user across all the containers, a common user can have as many as 1 (CDB$ROOT) + 252 (PDBs) = 253 LSLTs across the CDB. Here is how it behaves, depending on container, it connects to.

• A) Connection to ROOT : For common users' successful connection to CDB$ROOT, only ROOT's user$ has its LSLT updated. LSLT(s), if any, present inside PDBs' user$ are left untouched.
• B) Connection to PDB : For common users' successful connection to PDB, Last Successful Login Time is updated in two places i> CDB$Root as well as ii> The PDB to which connection is being attempted. The LSLT, if any, present in other PDBs is left untouched.

What this essentially means is the followings

• C) DBA_USERS at CDB$ROOT would show the LAST_LOGIN as the time when the common user last connected to any of the PDB's, including CDB$ROOT.
• D) DBA_USERS inside a PDB would show the LAST_LOGIN as the time when the common user last connected to that PDB. This time might be different from the LAST_LOGIN in the CDB$ROOT or in other PDBs, as that common user could have connected to any other PDB in between the last login to this PDB and now.

3) Which containers' LSLT gets displayed at the time of logon ?? For connections to ROOT or to the PDBs, the LSLT displayed during SQL*Plus invocation, is always the one fetched from CDB$ROOT, as LSLT is associated to a user account instead of containers, he or she should always see his LAST SUCCESSFUL LOGIN ATTEMPT, irrespective of which containers it happened against.

conn /as sysdba

create user c##a identified by a;

grant create session to c##a container=all;

conn c##a/a@rootorcl

conn /as sysdba

SQL> show con_name

CON_NAME

-------------

CDB$ROOT

SQL> select last_login from dba_users where username='C##A';

LAST_LOGIN
-----------------------------------------------

12-OCT-15 03.38.00.000000000 PM +05:30

alter session set container=pdborcl;

SQL> show con_name

CON_NAME

-------------

PDBORCL

SQL> select 1 from dba_users where username='C##A' and last_login is NULL;

         1

----------

         1

The LSLT inside PDB's USER$ is expectedly showing up as NULL, as the common user has not logged into the PDB at all. However, when he or she tries to login to the PDB, it does show the LSLT, as was recorded in CDB$ROOT's USER$.

%% sqlplus c##a/a@pdborcl

SQL*Plus: Release 12.1.0.2.0 Production on Mon Oct 12 15:39:12 2015

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

Last Successful login time: Mon Oct 12 2015 15:38:00 +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> show con_name

CON_NAME
-------------

PDBORCL

Now if we query the LSLT inside PDB and at CDB$ROOT, we see both of them have been updated.

conn /as sysdba

SQL> select last_login from dba_users where username='C##A';

LAST_LOGIN

-----------------------------------------------

12-OCT-15 03.39.17.000000000 PM +05:30

SQL> alter session set container=pdborcl;
Session altered.

SQL> select last_login from dba_users where username='C##A';

LAST_LOGIN

----------------------------------------------

12-OCT-15 03.39.17.000000000 PM +05:30

[PDBORCL routes the connection to PDBs while those to ROOTORCL points them to CDB$ROOT]

4) How to see LSLT across the containers : There is a new view CDB_USERS, which pulls user data across all the containers, including CDB$ROOT, and presents a consolidated view at CDB$ROOT [Not just DBA_USERS, there a bunch of Oracle supplied DBA_* views, which also have a corresponding CDB_* view, all aggregating data across the containers].

However, you can't query user data from other PDBs, while being connected to a PDB. You _MUST_ be connected to CDB$ROOT to be able to query data across containers. Inside a PDB, CDB_USERS view displays the same results as DBA_USERS view.

conn system/manager@pdborcl

exec dbms_lock.sleep(10);

conn system/manager@rootorcl

SQL> select last_login, decode (con_id, 1, 'CDB$ROOT', 3, 'PDBORCL') as CONTAINER_NAME from cdb_users where username = 'SYSTEM' order by 1;

LAST_LOGIN                                                         CONTAINER_NAME
----------------------------------------------- ----------------------
12-OCT-15 04.36.21.000000000 PM +05:30  PDBORCL
12-OCT-15 04.36.31.000000000 PM +05:30  CDB$ROOT

5) Read Only PDB : As explained in Part II, if the DB is open in READ ONLY mode, we don't record LSLT in in-memeory hash table V$RO_USER_ACCOUNT. However, in a multi-tenant environment, individual PDBs can be opened in READ ONLY mode, while CDB$ROOT may still be open in READ WRITE mode. In such cases, common user' logons to PDB do update LSLT at CDB$ROOT's USER$ table. However, such logons to PDB will not leave any footprint inside PDB's USER$.

connect /as sysdba
create user c##userA identified by passA container=all;
grant create session to c##userA container=all;

alter pluggable database pdborcl open read only force; <-- Open PDB in Read Only Mode

connect c##userA/passA@pdborcl
conn /as sysdba
SQL> select nvl(to_char(last_login), 'NULL') as last_login, decode (con_id, 1, 'CDB$ROOT', 3, 'PDBORCL') as CONTAINER_NAME from cdb_users where username = 'C##USERA' order by 1;

LAST_LOGIN                                                          CONTAINER_NAME

----------------------------------------------- --------------------

12-OCT-15 04.44.28.000000000 PM +05:30  CDB$ROOT

NULL                                                                         PDBORCL

6) Behavior in 12.1.0.1 : Some of the notable differences in behavior between 12.1.0.2 and 12.1.0.1 are as follows

• Common users' logon to PDB didn't cause any updates to LSLT at CDB$ROOT
• LSLT displayed on SQL*Plus invocation was the LSLT from the container, where the common user is attempting the connection against.
• Logon to a Read Only PDB would not update LSLT in ROOT's user$

Further details on Last Successful Login Time

• Part I (Introduction/Usecase in Production Environment)
• Part II (Known Limitations/Workaround)
• Part IV (Real Application Security/Direct Login Users)

Last Successful Login Time Oracle 12c : Part II

In my previous post, I had briefly talked about a feature newly added in Oracle 12c. Today, I am going to elaborate more on its usage, especially the caveats/considerations, before one should start adopting its usage in production environment.

1) Administrative Users : There is no support for recording last successful logon time for administrative logons i.e, logons as SYSOPER, SYSDBA or the newly added SoD (Separation Of Duty) administrative privileges, like SYSBACKUP, SYDG, SYSKM. So if a user DBAOPER has been granted administrative privileges, say SYSOPER and tries logging into to the system "AS SYSOPER" by using Password File/Operating System based authentication, his LSLT will not be updated in USER$ and hence not visible in DBA_USERS view. So while devising rules for locking inactive users, based on LAST_LOGIN field, you must consider excluding the administrative privileged users. Also note that V$PWFILE_USERS does not have any column for LSLT unlike DBA_USERS.

2) Externally Authenticated Users : They are supported. I tried Operating System based authentication by making use of OS_AUTHENT_PREFIX parameter and SSL based authentication making use of digital certificates and in both the instances, DBA_USER had their Last Successful Login Time recorded.

3) Proxy Authentication : When a proxy user logs in (connect proxy[client]/proxyPass), the connections gets established using credentials of proxy user, but the actual login happens as client user. In other words, once the login happens, the session behaves as if, it were established using client' credentials. So the natural question is whose LSLT gets updated post successful logon ?? Is it PROXY or CLIENT or BOTH ?? Ideally, for a successful proxy logon, LSLT for both proxy and client users should be updated, because both the users account status needs to be ACTIVE in order to establish a successful proxy connection.

However, As on 12.1.0.2, this holds true only for Double Session Proxy sessions (not supported in SQL*Plus, but supported in OCI and JDBC). For Single session proxy sessions (supported in SQL*Plus, OCI and JDBC), a successful proxy logon _does not_ update LSLT for the proxy user and only CLIENT user' LSLT gets updated (I plan to cover the various types of proxy authentications some day, hopefully soon enough for my readers)

So while sifting through DBA_USERS.LAST_LOGIN, you want to do some special handling for PROXY Only users, who may only connect to the system via PROXY authentication and may not connect directly leaving behind a stale LSLT in USER$.

4) Read Only Database : Starting 11gR2 (11.2.0.4 patchset release to be precise), Oracle does support account lockout by honouring failed login attempts, when DB is in Read Only mode. An in-memory hash table accessible via V$RO_USER_ACCOUNT view hosts relevant information, w,r,t failed login attempts and account lock status/time. However, there is no support of recording Last Successful Login Time.

Further details on Last Successful Login Time

• Part I (Introduction/Usecase in Production Environment)
• Part III (Oracle Multitenant/Common Users)
• Part IV (Real Application Security/Direct Login Users)

Last Successful Login Time Oracle 12c : Part 1

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

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.

Example : 

%% 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 sysdba
create 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)

conn /as sysdba
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

• Part II (Known Limitations/Workaround)
• Part III (Oracle Multitenant/Common Users)
• Part IV (Real Application Security/Direct Login Users)