LATEST TOPICS

Oracle 12c: Restrict OS authentication to a single pluggable database

In my last article, I have discussed about the process of implementing OS authentication in a multitenant Oracle 12c database without setting the deprecated parameter REMOTE_OS_AUTHENT. We have seen that we can create a common user for OS authentication by setting the OS_AUTHENT_PREFIX parameter to match the prefix of a common user (dictated by COMMON_USER_PREFIX/_COMMON_USER_PREFIX).

Now, there is a concern with this type of common user implementation for OS authentication. Since we have created a common user for OS authentication, it can switch to any available pluggable database container (provided it have privileges to switch containers). In general, we would need OS authentication for application specific database accounts and may not want that user to switch to other pluggable databases (which may have a different application context)

Let me elaborate the case with a quick example. Consider we have two applications MYAPP and MYAPP2 mapped to two distinct pluggable databases MYPDB_1 and MYPDB_2 within the same container database ORPCB1. We want to create a OS authenticated user for MYAPP application for running the application specific database batch jobs. Now, when we create a OS authenticated common user (example: C##MYAPP) we don’t want that user to be able to switch to the other pluggable database MYPDB_2 which is not in its context. We also don’t want that user to be able to switch to any pluggable databases which may get added/pluggable to this container in the future.

In this post, I will discuss how we can restrict a common external (OS authenticated) user from switching to any pluggable databases except the intended pluggable database. Let’s say I have a 12c container database ORPCDB1 with two pluggable databases MYPDB_1 and MYPDB_2. Let’s say I have created a OS authenticated common user C##MYAPP using the process outlined in the last post. So, I have the following setup at this point.

---//
---// 12c container database named ORPCB1 //---
---//
SQL> select name,cdb from v$database;

NAME      CDB
--------- ---
ORPCDB1   YES

---//
---// list of available pluggable databases //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYPDB_1            		  	  READ WRITE NO
         4 MYPDB_2                     	  READ WRITE NO


---//
---// external user (C##MYAPP) available in all containers //---
---//		 
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

		 
SQL> select c.name "Container", u.username, u.AUTHENTICATION_TYPE
  2  from v$containers c, cdb_users u
  3  where c.con_id=u.con_id
  4  and u.username='C##MYAPP'
  5  order by 1;

Container            USERNAME        AUTHENTICATION_TYPE
-------------------- --------------- -------------------------
CDB$ROOT             C##MYAPP        EXTERNAL
MYPDB_1              C##MYAPP        EXTERNAL
MYPDB_2              C##MYAPP        EXTERNAL

As we can observe from the above output, the external (OS authenticated) user is present in each of the containers (root and pluggable databases). This means that the external user would be able to switch to any pluggable database provided it has access to switch containers. Lets see if the external user is granted “SET CONTAINER” privilege and whether it can switch to all containers.

---//
---// checking external user's "set container" privilege //---
---//
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select c.name "Container", r.grantee, r.privilege, r.common
  2  from v$containers c, cdb_sys_privs r
  3  where c.con_id=r.con_id
  4  and r.grantee='C##MYAPP'
  5  and r.privilege like '%CONTAINER%'
  6  order by 1,3;

Container  GRANTEE         PRIVILEGE       COM
---------- --------------- --------------- ---
CDB$ROOT   C##MYAPP        SET CONTAINER   YES
MYPDB_1    C##MYAPP        SET CONTAINER   YES
MYPDB_2	   C##MYAPP        SET CONTAINER   YES

As we can see the external user C##MYAPP is granted the “SET CONTAINER” privilege in all of the containers (as common privilege), which would enable it to switch to any available container as shown below

---//
---// switching containers as external user //---
---//
SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select sys_context('userenv','authentication_method') as Auth_mode
  2  from dual;

AUTH_MODE
-------------------
OS

---//
---// switching to pluggable database MYPDB_1 from CDB$ROOT //---
---//
SQL> alter session set container=MYPDB_1;

Session altered.

SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
------------------------------
MYPDB_1

---//
---// switching to pluggable database MYPDB_2 from MYPDB_1 //---
---//

SQL> alter session set container=MYPDB_2;

Session altered.

SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
------------------------------
MYPDB_2

As we can see the external (OS authenticated) user is able to switch to any container. Now, this is a concern as we want the OS authenticated user to be able to switch to only a particular pluggable database (MYPDB_1 in this case).

One way to do that, is to revoke the external user’s privilege of switching any container (“SET CONTAINER” common privilege) and grant it the switching privilege (“SET CONTAINER”) on a specific container (MYPDB_1 in our case).

---//
---// revoke "SET CONTAINER" common privs from external user //---
---// need to log in to root (CDB$ROOT) container 			 //---
---//
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> revoke set container from C##MYAPP container=all;

Revoke succeeded.

---//
---// switch to the PDB to which we want the SET CONTAINER privilege //---
---//
SQL> alter session set container=MYPDB_1;

Session altered.

SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
MYPDB_1

---//
---// grant the "SET CONTAINER" privilege in the current PDB //---
---//
SQL> grant set container to C##MYAPP container=current;

Grant succeeded.

Now, the external (OS authenticated) user would not be able to switch to any container except the container MYPDB_1 where we have granted it the “SET CONTAINER” privilege as show below

---//
---// log in as the external (OS authenticated) user //---
---//
SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select sys_context('userenv','authentication_method') as Auth_mode
  2  from dual;

AUTH_MODE
-------------------
OS

---//
---// switch to pluggable database MYPDB_2 from CDB$ROOT //---
---//
SQL> alter session set container=MYPDB_2;
ERROR:
ORA-01031: insufficient privileges

---//
---// switch to pluggable database MYPDB_1 from CDB$ROOT //---
---//
SQL> alter session set container=MYPDB_1;

Session altered.

SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
------------------------------
MYPDB_1

---//
---// switch to pluggable database MYPDB_2 from MYPDB_1 //---
---//
SQL> alter session set container=MYPDB_2;
ERROR:
ORA-01031: insufficient privileges

As we can see, the OS authenticated common user is now restricted to a particular PDB (MYPDB_1 in our case). We can further create a logon trigger at the root (CDB$ROOT) container level to direct the common user to the specific PDB and prevent it from viewing other PDBs as shown below.

---//
---// log in to root (CDB$ROOT) container as SYS user //---
---//
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

---//
---// create logon trigger to switch to pdb //---
---//
SQL> create or replace trigger C##MYAPP.SET_PDB
  2  after logon
  3  on database
  4  begin
  5  --- check if logged in user is C##MYAPP
  6  if sys_context('userenv','session_user')='C##MYAPP'
  7  then
  8  --- switch to pluggable database MYPDB_1
  9  execute immediate 'alter session set container=MYPDB_1';
 10  end if;
 11  end;
 12  /

Trigger created.

---//
---// enable the logon trigger //---
---//
SQL> alter trigger C##MYAPP.SET_PDB enable;

Trigger altered.

Now, whenever we login to the container database as the the external (OS authenticated) user, the user will be switched to the specific pluggable database (MYPDB_1 in our case) as shown below

---//
---// validate logon trigger is working as expected //---
---//
[myapp@labserver1 ~]$ id
uid=504(myapp) gid=504(myapp) groups=504(myapp)
[myapp@labserver1 ~]$ sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 19 15:49:52 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Feb 19 2016 15:39:39 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
------------------------------
MYPDB_1

SQL> select sys_context('userenv','authentication_method') as Auth_mode
  2  from dual;

AUTH_MODE
-----------------
OS

As we can see, when we logged in as the external (OS authenticated) user, the external user was directly switched to pluggable database MYPDB_1. By this implementation, we can ensure that the external user is always logged in to a specific container/pluggable database (MYPDB_1 in our case) and we don’t even need to adjust our existing batch jobs to have an additional “ALTER SESSION SET CONTAINER” statement.

However, there is still a potential to switch to other containers if the common user is granted back the “SET CONTAINER” privilege on other PDBs. We may create another schema level trigger in each of the pluggable database, on the “SET CONTAINER” event to forcefully switch the common user to the desired PDB if it tries to switch to any container other than the one that we want it to switch to.

We might create a trigger similar to the following in each of the pluggable database

---//
---// schema level trigger on "SET CONTAINER" event //--
---//
SQL> create or replace trigger C##MYAPP.REVERT_PDB
  2  after set container
  3  on C##MYAPP.SCHEMA 
  4  begin
  5  --- check if logged in user is C##MYAPP
  6  --- check if container is not set to MYPDB_1   
  7  if (sys_context('userenv','session_user')='C##MYAPP'
  8  AND sys_context('userenv','con_name') != 'MYPDB_1')  
  9  then
 10  --- switch to pluggable database MYPDB_1
 11	 execute immediate 'alter session set container=MYPDB_1'; 
 12  end if;
 13  end;
 14  /

Trigger created.

SQL> alter trigger C##MYAPP.REVERT_PDB enable;

Trigger altered.

Although this trigger looks realist, it is not going to work as there is a restriction to execute “SET CONTAINER” command from a PL/SQL block within pluggable database.
Thanks to “Connor McDonald” for highlighting this restriction and providing the reference.

Let’s validate that by granting back “SET CONTAINER” common privilege to the common OS authenticated user.

---//
---// granting "SET CONTAINER" common privs to external user //---
---//
SQL> show user
USER is "SYS"
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> grant set container to C##MYAPP container=all;

Grant succeeded.

Let’s see what happens, when we try to switch to any container other than MYPDB_1.

[myapp@labserver1 ~]$ id
uid=504(myapp) gid=504(myapp) groups=504(myapp)
[myapp@labserver1 ~]$ sqlplus /

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 19 16:30:19 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Feb 19 2016 15:49:52 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user
USER is "C##MYAPP"
SQL> show con_name

CON_NAME
-------------
MYPDB_1   ---// switched to PDB (MYPDB_1) due to logon trigger C##MYAPP.SET_PDB //---
 
SQL> select sys_context('userenv','authentication_method') as Auth_mode
  2  from dual;

AUTH_MODE
-------------
OS

SQL> alter session set container=MYPDB_2;  ---// schema level trigger C##MYAPP.REVERT_PDB is fired //---
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at line 9

---// trigger couldn't prevent common user from switching container //---
SQL> show con_name

CON_NAME
------------------------------
MYPDB_2

As we can see from the above output, the common user C##MYAPP was switched to the PDB (MYPDB_1) due to the logon trigger (C##MYAPP.SET_PDB) defined at the root container. However, the SCHEMA level trigger defined on the “SET CONTAINER” event erred out with “ORA-01031: insufficient privileges” while executing “SET CONTAINER” from the trigger block and due to which the common user couldn’t be prevented from switching to the other pluggable database (MYPDB_2 in our case).

The trigger was erred out due to the restriction of using “SET CONTAINER” command in a PL/SQL block within pluggable databases. This restriction doesn’t apply to the root container and hence we were able to use the “SET CONTAINER” command in the “SET_PDB” logon trigger defined in the root container (CDB$ROOT).

Due to this restriction with “SET CONTAINER” command, currently it is not possible to completely restrict a OS authenticated common user from switching to other pluggable databases. The only way to restrict the OS authenticated common user is to ensure it doesn’t have “SET CONTAINER” privilege on other pluggable databases and probably create a logon trigger (like the ones showed in this post) to route the common user directly to the desired pluggable database.

I really wish Oracle lifts the restriction around using “SET CONTAINER” command from a PL/SQL block within pluggable databases. Till then the only way is to ensure the OS authenticated common user is granted “SET CONTAINER” privilege only in the specific pluggable database.

One Response
  1. Foued
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed