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