Saturday, January 2, 2016

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.

No comments:

Post a Comment