PDB_OS_CREDENTIAL with external table pre-processor

As part of a customer project I am currently enhancing PDB security and isolation. Since OS interaction is necessary, I can not just use lockdown profile to block OS access. The idea is to isolate the PDB with lockdown profiles and allow dedicated OS access. The idea is to isolate the PDB with lockdown profiles and allow dedicated OS access. However, the OS access should be performed by another user than Oracle. This is where PDB_OS_CREDENTIAL comes in. According to Oracle documentation PDB_OS_CREDENTIAL allows to specify the credentials for dedicated operating system user, which is used for OS interaction of the PDB. See PDB_OS_CREDENTIAL in Oracle® Database Reference 19c or Managing Security for a Multitenant Environment in Oracle® Multitenant Administrator’s Guide 19c.

The following OS Interaction are covered by PDB_OS_CREDENTIAL:

  • External table pre-processors
  • PL/SQL library executions
  • External jobs that do not already have an operating system credential specified

OS Configuration

First a dedicated operating system user is required. I use one OS user per PDB and one generic OS user for the CDB. Let’s create them using

useradd
useradd. Create a dedicated OS group, the OS user for the CDB and set its password to manager. Which in fact is not a secure password, but sufficient for this simple test. 🙂

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
groupadd restricted
useradd --create-home --gid restricted \
--shell /bin/bash oracdb
echo "manager" | passwd oracdb --stdin
groupadd restricted useradd --create-home --gid restricted \ --shell /bin/bash oracdb echo "manager" | passwd oracdb --stdin
groupadd restricted
useradd --create-home --gid restricted \
--shell /bin/bash oracdb

echo "manager" | passwd oracdb --stdin

Create OS users for PDB1 and PDB2 and set the passwords to manager.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
useradd --create-home --gid restricted \
--shell /bin/bash orapdb1
useradd --create-home --gid restricted \
--shell /bin/bash orapdb2
echo "manager" | passwd orapdb1 --stdin
echo "manager" | passwd orapdb2 --stdin
useradd --create-home --gid restricted \ --shell /bin/bash orapdb1 useradd --create-home --gid restricted \ --shell /bin/bash orapdb2 echo "manager" | passwd orapdb1 --stdin echo "manager" | passwd orapdb2 --stdin
useradd --create-home --gid restricted \
--shell /bin/bash orapdb1

useradd --create-home --gid restricted \
--shell /bin/bash orapdb2

echo "manager" | passwd orapdb1 --stdin
echo "manager" | passwd orapdb2 --stdin

In addition to the user, we need a test script, which we can be executed as an external table pre-processor. I’ll create a dummy script executing the OS command id. This will show the user as which the script is executed.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
echo "/bin/id" >/u01/eng/run_id.sh
chmod 755 /u01/eng/run_id.sh
echo "/bin/id" >/u01/eng/run_id.sh chmod 755 /u01/eng/run_id.sh
echo "/bin/id" >/u01/eng/run_id.sh
chmod 755 /u01/eng/run_id.sh

Create Credentials (DBMS_CREDENTIAL)

Create the credentials for the different OS users using DBMS_CREDENTIAL. The credentials will be created in the root container (cdb$root).

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
ALTER SESSION SET CONTAINER=cdb$root;
BEGIN
dbms_credential.create_credential(
credential_name =>'GENERIC_PDB_OS_USER',
username => 'oracdb',
password => 'manager');
END;
/
BEGIN
dbms_credential.create_credential(
credential_name => 'PDB1_OS_USER',
username => 'orapdb1',
password => 'manager');
END;
/
ALTER SESSION SET CONTAINER=cdb$root; BEGIN dbms_credential.create_credential( credential_name =>'GENERIC_PDB_OS_USER', username => 'oracdb', password => 'manager'); END; / BEGIN dbms_credential.create_credential( credential_name => 'PDB1_OS_USER', username => 'orapdb1', password => 'manager'); END; /
ALTER SESSION SET CONTAINER=cdb$root;

BEGIN
dbms_credential.create_credential(
credential_name =>'GENERIC_PDB_OS_USER',
username => 'oracdb',
password => 'manager');
END;
/

BEGIN
dbms_credential.create_credential(
credential_name => 'PDB1_OS_USER',
username => 'orapdb1',
password => 'manager');
END;
/

Show the credentials:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
COLUMN con_id FORMAT 999999
COLUMN owner FORMAT A10
COLUMN credential_name FORMAT A20
SELECT con_id, owner, credential_name
FROM cdb_credentials;
CON_ID OWNER CREDENTIAL_NAME
====== ===== ===============
1 SYS PDB1_OS_USER
1 SYS GENERIC_PDB_OS_USER
COLUMN con_id FORMAT 999999 COLUMN owner FORMAT A10 COLUMN credential_name FORMAT A20 SELECT con_id, owner, credential_name FROM cdb_credentials; CON_ID OWNER CREDENTIAL_NAME ====== ===== =============== 1 SYS PDB1_OS_USER 1 SYS GENERIC_PDB_OS_USER
COLUMN con_id FORMAT 999999
COLUMN owner FORMAT A10
COLUMN credential_name FORMAT A20

SELECT con_id, owner, credential_name
FROM cdb_credentials;

CON_ID OWNER CREDENTIAL_NAME
====== ===== ===============
     1 SYS   PDB1_OS_USER
     1 SYS   GENERIC_PDB_OS_USER

Assign Credentials (PDB_OS_CREDENTIAL)

The init.ora parameter PDB_OS_CREDENTIAL is now assigned to the credentials created above. By setting PDB_OS_CREDENTIAL in the CDB we define a default credential for all PDBs. Although the documentation shows, that the parameter can be set directly with an ALTER SYSTEM, this does not work. (See Multitenant : Pluggable Database (PDB) Operating System (OS) Credentials in Oracle Database 12c Release 2 (12.2) by Tim Hall)

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
ALTER SYSTEM SET PDB_OS_CREDENTIAL=generic_pdb_os_user SCOPE=SPFILE;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=generic_pdb_os_user SCOPE=SPFILE;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=generic_pdb_os_user SCOPE=SPFILE;

Error by the ALTER SYSTEM command.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65046: operation not allowed from outside a pluggable database
ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-65046: operation not allowed from outside a pluggable database
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-65046: operation not allowed from outside a pluggable database

Setting PDB_OS_CREDENTIAL via parameter file does work.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CONNECT / AS SYSDBA
SHOW PARAMETER PDB_OS_CREDENTIAL
SHUTDOWN IMMEDIATE;
CREATE PFILE='/tmp/pfile.txt' FROM SPFILE;
HOST echo "*.pdb_os_credential=GENERIC_PDB_OS_USER" >> /tmp/pfile.txt
CREATE SPFILE FROM PFILE='/tmp/pfile.txt';
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL
NAME TYPE VALUE
================= ====== ===================
pdb_os_credential string GENERIC_PDB_OS_USER
CONNECT / AS SYSDBA SHOW PARAMETER PDB_OS_CREDENTIAL SHUTDOWN IMMEDIATE; CREATE PFILE='/tmp/pfile.txt' FROM SPFILE; HOST echo "*.pdb_os_credential=GENERIC_PDB_OS_USER" >> /tmp/pfile.txt CREATE SPFILE FROM PFILE='/tmp/pfile.txt'; STARTUP; SHOW PARAMETER PDB_OS_CREDENTIAL NAME TYPE VALUE ================= ====== =================== pdb_os_credential string GENERIC_PDB_OS_USER
CONNECT / AS SYSDBA
SHOW PARAMETER PDB_OS_CREDENTIAL
SHUTDOWN IMMEDIATE;
CREATE PFILE='/tmp/pfile.txt' FROM SPFILE;
HOST echo "*.pdb_os_credential=GENERIC_PDB_OS_USER" >> /tmp/pfile.txt
CREATE SPFILE FROM PFILE='/tmp/pfile.txt';
STARTUP;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME              TYPE   VALUE
================= ====== ===================
pdb_os_credential string GENERIC_PDB_OS_USER

Setting a PDB specific credential for the PDB1.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- set the container to PDB1
ALTER SESSION SET CONTAINER=pdb1;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=PDB1_OS_USER SCOPE=SPFILE;
STARTUP FORCE;
SHOW PARAMETER PDB_OS_CREDENTIAL
NAME TYPE VALUE
================= ====== ===================
pdb_os_credential string PDB1_OS_USER
-- set the container to PDB1 ALTER SESSION SET CONTAINER=pdb1; ALTER SYSTEM SET PDB_OS_CREDENTIAL=PDB1_OS_USER SCOPE=SPFILE; STARTUP FORCE; SHOW PARAMETER PDB_OS_CREDENTIAL NAME TYPE VALUE ================= ====== =================== pdb_os_credential string PDB1_OS_USER
-- set the container to PDB1
ALTER SESSION SET CONTAINER=pdb1;
ALTER SYSTEM SET PDB_OS_CREDENTIAL=PDB1_OS_USER SCOPE=SPFILE;
STARTUP FORCE;
SHOW PARAMETER PDB_OS_CREDENTIAL

NAME              TYPE   VALUE
================= ====== ===================
pdb_os_credential string PDB1_OS_USER

Test the credential

To test the credential we create an external table using the simple script created above as table pre-processor.

Create the directory:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CONNECT tvd_hr/tvd_hr@ol7db18.trivadislabs.com:1521/PDB1
CREATE OR REPLACE DIRECTORY exec_dir AS '/u01/eng';
CONNECT tvd_hr/tvd_hr@ol7db18.trivadislabs.com:1521/PDB1 CREATE OR REPLACE DIRECTORY exec_dir AS '/u01/eng';
CONNECT tvd_hr/tvd_hr@ol7db18.trivadislabs.com:1521/PDB1
CREATE OR REPLACE DIRECTORY exec_dir AS '/u01/eng';

Create the external table:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
CREATE TABLE id (id VARCHAR2(2000))
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exec_dir
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir:'run_id.sh')
LOCATION(exec_dir:'run_id.sh')
);
CREATE TABLE id (id VARCHAR2(2000)) ORGANIZATION EXTERNAL( TYPE ORACLE_LOADER DEFAULT DIRECTORY exec_dir ACCESS PARAMETERS( RECORDS DELIMITED BY NEWLINE PREPROCESSOR exec_dir:'run_id.sh') LOCATION(exec_dir:'run_id.sh') );
CREATE TABLE id (id VARCHAR2(2000))
ORGANIZATION EXTERNAL(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY exec_dir
ACCESS PARAMETERS(
RECORDS DELIMITED BY NEWLINE
PREPROCESSOR exec_dir:'run_id.sh')
LOCATION(exec_dir:'run_id.sh')
);

Select from the external table:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
SELECT * FROM id;
ID
========================================================= =
uid=1001(oracle) gid=1010(oinstall) groups=1010(oinstall)
SELECT * FROM id; ID ========================================================= = uid=1001(oracle) gid=1010(oinstall) groups=1010(oinstall)
SELECT * FROM id;

ID
========================================================= =
uid=1001(oracle) gid=1010(oinstall) groups=1010(oinstall)

As you can see the user returned from the external table pre-processor is still oracle. Also the log file of the external table pre-processor is created as user oracle.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
host ls -al /u01/eng
total 8
drwxr-xr-x. 2 oracle oinstall 43 Jun 17 04:47 .
drwxrwxr-x. 5 oracle oinstall 47 Jun 14 12:20 ..
-rw-r--r--. 1 oracle oinstall 582 Jun 17 04:47 ID_30158.log
-rwxr-xr-x. 1 oracle oinstall 8 Jun 17 04:24 run_id.sh
host ls -al /u01/eng total 8 drwxr-xr-x. 2 oracle oinstall 43 Jun 17 04:47 . drwxrwxr-x. 5 oracle oinstall 47 Jun 14 12:20 .. -rw-r--r--. 1 oracle oinstall 582 Jun 17 04:47 ID_30158.log -rwxr-xr-x. 1 oracle oinstall 8 Jun 17 04:24 run_id.sh
host ls -al /u01/eng
total 8
drwxr-xr-x. 2 oracle oinstall 43 Jun 17 04:47 .
drwxrwxr-x. 5 oracle oinstall 47 Jun 14 12:20 ..
-rw-r--r--. 1 oracle oinstall 582 Jun 17 04:47 ID_30158.log
-rwxr-xr-x. 1 oracle oinstall 8 Jun 17 04:24 run_id.sh

Conclusion

The parameter PDB_OS_CREDENTIAL is a promising feature to enhance PDB Isolation and security. It seems to be broken for this particular use case. I have opened corresponding service requests with Oracle. But the issue is still investigated. So far there are several bugs assigned to this issue, but unfortunately none of them is public.

  • Bug 18814778 CDB: Tracking bug: PDB_OS_CREDENTIAL does not influence os environment
  • Bug 25820082 PDB_OS_CREDENTIAL parameter not working
  • Bug 29791472 12.2 Lockdown Profile not working as desired
  • Bug 29791380 12.2 Lockdown Profile not working as desired

I’ll provide an update as soon there is a workaround or solution to this issue.