Sunday, January 10, 2016

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.

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.

Friday, January 1, 2016

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
-------------------