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

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.

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.

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

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:

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)

ALTER SYSTEM SET PDB_OS_CREDENTIAL=generic_pdb_os_user SCOPE=SPFILE;

Error by the ALTER SYSTEM command.

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.

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.

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

CONNECT tvd_hr/tvd_hr@ol7db18.trivadislabs.com:1521/PDB1
CREATE OR REPLACE DIRECTORY exec_dir AS '/u01/eng';

Create the external table:

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:

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.

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.