Wednesday, 31 May 2017

Multitenant : Pluggable Database (PDB) Operating System (OS) Credentials in Oracle Database 12c Release 2

There are a number of database features that require access to the operating system, for example external jobs without explicit credentials, PL/SQL library executions and preprocessor executions for external tables. By default these run using the Oracle software owner on the operating system, which is a highly privileged user and represents a security risk if you are trying to consolidate multiple systems into a single container.
Oracle 12.2 allows you to assign a different default operating system (OS) credential to each pluggable database (PDB), giving a greater degree of separation between the pluggable databases and therefore better control over security.

  • Create Operating Systems (OS) Users


In this example we will define a separate group and user for the CDB and each PDB using the following commands, run as the "root" user.

# groupadd -g 2000 cdb1_user
# useradd -g cdb1_user -u 2000 cdb1_user
# id cdb1_user
uid=2000(cdb1_user) gid=2000(cdb1_user) groups=2000(cdb1_user)
# passwd cdb1_user


# groupadd -g 1001 pdb1_user
# useradd -g pdb1_user -u 1001 pdb1_user
# id pdb1_user
uid=1001(pdb1_user) gid=1001(pdb1_user) groups=1001(pdb1_user)
# passwd pdb1_user


# groupadd -g 1002 pdb2_user
# useradd -g pdb2_user -u 1002 pdb2_user
# id pdb2_user
uid=1002(pdb2_user) gid=1002(pdb2_user) groups=1002(pdb2_user)
# passwd pdb2_user


# groupadd -g 1003 pdb3_user
# useradd -g pdb3_user -u 1003 pdb3_user
# id pdb3_user
uid=1003(pdb3_user) gid=1003(pdb3_user) groups=1003(pdb3_user)
# passwd pdb3_user
The CDB credential is used as the default OS user for all PDBs if they don't have a PDB-specific credential set.

  • Create Credentials (DBMS_CREDENTIAL)


Create the relevant database credential for each container using the DBMS_CREDENTIAL package.

CONN / AS SYSDBA

-- CDB Credential (Default for all PDBs)
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'cdb1_user_cred',
    username        => 'cdb1_user',
    password        => 'cdb1_user');
END;
/

-- PDB1 Credential
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'pdb1_user_cred',
    username        => 'pdb1_user',
    password        => 'pdb1_user');
END;
/

-- PDB2 Credential
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'pdb2_user_cred',
    username        => 'pdb2_user',
    password        => 'pdb2_user');
END;
/

-- PDB1 Credential
BEGIN
  DBMS_CREDENTIAL.create_credential(
    credential_name => 'pdb3_user_cred',
    username        => 'pdb3_user',
    password        => 'pdb3_user');
END;
/

Check the credentials are all present and owned by the root container using the 

CDB_CREDENTIALS view.

COLUMN owner FORMAT A30
COLUMN credential_name FORMAT A30

SELECT con_id, owner, credential_name
FROM   cdb_credentials
ORDER BY 1, 2, 3;

CON_ID      OWNER     CREDENTIAL_NAME
----------  ------------        ------------------------------
         1      SYS                CDB1_USER_CRED
         1      SYS                PDB1_USER_CRED
         1      SYS                PDB2_USER_CRED
         1      SYS                PDB3_USER_CRED

SQL>

  • Assign Credentials (PDB_OS_CREDENTIAL)


The PDB_OS_CREDENTIAL initialization parameter is used to define the default OS credential for the container. When this is set in the root container, it defines the default OS credential for all PDBs. Setting it at the PDB level overrides the CDB default setting.

The documentation suggests you should be able set the parameter in the root container as follows.

CONN / AS SYSDBA
ALTER SYSTEM SET PDB_OS_CREDENTIAL=cdb1_user_cred SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

If you try that, you get the following error from the ALTER SYSTEM command.

ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65046: operation not allowed from outside a pluggable database

Instead, I had to do the following.

CONN / AS SYSDBA
SHUTDOWN IMMEDIATE;
CREATE PFILE='/tmp/pfile.txt' FROM SPFILE;
HOST echo "*.pdb_os_credential=cdb1_user_cred" >> /tmp/pfile.txt
CREATE SPFILE FROM PFILE='/tmp/pfile.txt';
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME                   TYPE        VALUE
-------------------    ----------- ---------------
pdb_os_credential   string      cdb1_user_cred

SQL>

The credential was then visible from all the PDBs when using the SHOW PARAMETER PDB_OS_CREDENTIAL command.

With the default in place we can set the PDB-specific credentials as follows.

CONN / AS SYSDBA

-- PDB1 Credential
ALTER SESSION SET CONTAINER=pdb1;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=pdb1_user_cred SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME                   TYPE        VALUE
-------------------    ----------- ---------------
pdb_os_credential     string      PDB2_USER_CRED

SQL>


-- PDB2 Credential
ALTER SESSION SET CONTAINER=pdb2;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=pdb2_user_cred SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME                   TYPE        VALUE
-------------------    ----------- ---------------
pdb_os_credential     string      PDB2_USER_CRED

SQL>


-- PDB3 Credential
ALTER SESSION SET CONTAINER=pdb3;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=pdb3_user_cred SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL


NAME                   TYPE        VALUE
-------------------    ----------- ---------------
pdb_os_credential     string      PDB3_USER_CRED

SQL>