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