PDB_OS_CREDENTIAL and PL/SQL external libraries

In the previous blog post I have described PDB_OS_CREDENTIAL initialization parameter and when it can help you in isolating PDBs from each other. Today I’ll show you an example of how it works with PL/SQL external libraries.

Of course we need to setup the whole environment first, but I provided the pointers already in the previous post. Let’s start with creating our simple code in C, which will return the current UID of the user running it.

$ vi run_id.c
int run_id() {
  return getuid();
}

We have to compile it and copy to our $ORACLE_HOME/lib directory (I want to keep this example as simple as possible, without playing with any additional parameters or environment variables).

-- Compile the program
$ gcc -fPIC -c run_id.c

-- Generate shared object
$ ld -shared -o run_id.so run_id.o

-- Set proper permissions and copy shared object to $ORACLE_HOME/lib
$ chmod 755 run_id.so
$ cp run_id.so $ORACLE_HOME/lib

This allows to create library object in our PDB and then simple function using it:

SQL> alter session set container = test001;

SQL> create library run_id_lib is 
     '/u01/app/oracle/product/12.2.0.1/dbhome_1/lib/run_id.so';
     /

SQL> create or replace function run_id_f return binary_integer as
     external name "run_id" library run_id_lib language c;
     /

That’t it, now we can test if PDB_OS_CREDENTIAL works correctly:

SQL> select run_id_f from dual;

  RUN_ID_F
----------
     54000

SQL> !id 54000
uid=54000(ora_test001) gid=53763(restricted) groups=53763(restricted)

Yes it is – ora_test001 user has been used, instead of oracle :).