8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : Pluggable Database (PDB) Operating System (OS) Credentials in Oracle Database 12c Release 2 (12.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.
Credentials do not affect the ownership of files created by the UTL_FILE package.
- Create Operating Systems (OS) Users
- Create Credentials (DBMS_CREDENTIAL)
- Assign Credentials (PDB_OS_CREDENTIAL)
Related articles.
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 PDB1_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>
For more information see:
- Configuring an Operating System User for a PDB
- PDB_OS_CREDENTIAL
- DBMS_CREDENTIAL
- Multitenant : All Articles
Hope this helps. Regards Tim...