LATEST TOPICS

Oracle 12c: OS authentication and the multitenant architecture

Are you upgrading/migrating your databases to Oracle 12c? Are you using OS (external) authentication for schemas running batch jobs? If yes, then this post covers the areas that you need to look into, to be able to run those batch jobs without any interruptions.

It is a common practice to use OS authenticated Oracle database accounts (users) for running application batch jobs. However, with the introduction of Oracle 12c multitenant architecture, we have a challenge to continue that practice. As we know, in multitenant architecture, the pluggable databases are intended to be used by the application for storing application data, whereas the root container (CDB$ROOT) is the master container having all the core database components and the ability to manage all the pluggable databases.

Considering the multitenant architecture, if we want our batch scripts with OS authenticated database users to work with pluggable databases, there must be some way where we can create the external user within a pluggable database and get it authenticated by the operating system.

Let’s check, if the legacy method of defining an external user for OS authentication works with Oracle 12c pluggable database.

In the following example, I am creating an external user named ops$myapp in the pluggable database CDB1_PDB_1 which I want to be authenticated by the operating system (OS).

---//
---// create external user ops$myapp within PDB //---
---//
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1_PDB_1                     READ WRITE NO


SQL> alter session set container=CDB1_PDB_1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

SQL> create user ops$myapp identified externally;

User created.

SQL> grant connect,resource to ops$myapp;

Grant succeeded.

Let’s check if the OS authentication works for this user account. Hold on.. but how? We can have multiple pluggable databases within a CDB. We do not have a SID for each pluggable database. How would Oracle know, against which pluggable database it must validate the OS authentication? May be we can try using service name (TNS alias) for directly connecting to the pluggable database.

Lets give it a try.

---//
---// try to perform OS authentication against PDB using TNS alias //---
---//
[myapp@labserver1 ~]$ id
uid=504(myapp) gid=504(myapp) groups=504(myapp)

[myapp@labserver1 ~]$ tnsping cdb1_pdb_1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 15-FEB-2016 15:04:22

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = labserver1.oraclebuffer.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cdb1_pdb_1)))
OK (0 msec)

[myapp@labserver1 ~]$ sqlplus /@cdb1_pdb_1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 14 23:35:56 2016

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

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

No, we are not allowed to access the external account. Since we are using service name to connect as OS authenticated user, we need to set the REMOTE_OS_AUTHENT parameter to TRUE. Let’s set the parameter and check if OS authentication works for pluggable database.

---//
---// set remote_os_authent to TRUE //---
---//
SQL> show parameter remote_os_authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     FALSE

SQL> alter system set remote_os_authent=TRUE scope=spfile;

System altered.

---//
---// restart database for change to take effect //---
---//
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             281018472 bytes
Database Buffers          784334848 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

SQL> show parameter remote_os_authent

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_os_authent                    boolean     TRUE


Let’s check, if the OS authentication now works for pluggable database.

---//
---// validate OS authentication works for PDB //---
---//
[myapp@labserver1 ~]$ id
uid=504(myapp) gid=504(myapp) groups=504(myapp)
[myapp@labserver1 ~]$ sqlplus /@cdb1_pdb_1

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 15 15:11:05 2016

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

Last Successful login time: Mon Feb 15 2016 15:02:24 +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 "OPS$MYAPP"
SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

---//
---// validate authentication was performed by OS //---
---//
SQL> select sys_context('userenv','authentication_method') as Auth_mode
  2  from dual;

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

Yes, we are now able to connect to pluggable database as OS authenticated external user. However, the point to be mentioned here is that, we have enabled a deprecated parameter REMOTE_OS_AUTHENT to make the authentication work for pluggable database. REMOTE_OS_AUTHENT is a deprecated feature and should not be used for authentication (due to security concerns).

Although, we have made the OS authentication work by setting REMOTE_OS_AUTHENT to TRUE; It would be a better approach, if we can make the OS authentication work without changing the deprecated parameter. Since OS authentication should not be performed remotely, the other option would be to create a common external user within the root container (CDB$ROOT) and then switch to the desired pluggable database.

Let’s check if we can create an external account under the root container (CDB$ROOT) for OS authentication. If that is possible, at least we can continue to use our batch scripts with little modification (probably we can add an addition “alter session set container” statement within our script to switch to the desired pluggable database).

---//
---// create external user in CDB$ROOT for OS authentication //---
---//
SQL> show con_name

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

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 CDB1_PDB_1                     READ WRITE NO

SQL> create user ops$myapp identified externally;
create user ops$myapp identified externally
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

As the error (ORA-65096) suggests, we can only create a common user within the root container. Well, there is a way around. We know that Oracle uses the OS_AUTHENT_PREFIX parameter to map Oracle database accounts with that of Operating System accounts for OS authentication. In a 12c multitenant architecture, Oracle lets us leverage that parameter to create an external user within the root container (CDB$ROOT). To be able to create an external user for OS authentication in the root container (CDB$ROOT), we must set OS_AUTHENT_PREFIX parameter (by default ops$) to match the prefix of common users (by default C##) which is dictated by the COMMON_USER_PREFIX parameter (staring from 12.1.0.2) or by the hidden _COMMON_USER_PREFIX parameter (in 12.1.0.1)

---//
---// change OS_AUTHENT_PREFIX to match COMMON_USER_PREFIX //---
---//
SQL> show parameter os_authent_prefix

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
os_authent_prefix                    string      ops$


SQL> show parameter common

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix                   string      C##

SQL> alter system set os_authent_prefix='C##' scope=spfile;

System altered.

---//
---// reset the deprecated parameter REMOTE_OS_AUTHENT which was set earlier //---
---//
SQL> alter system reset remote_os_authent scope=spfile;

System altered.

---//
---// restart database for change to take effect //---
---//

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  2927528 bytes
Variable Size             184550488 bytes
Database Buffers          436207616 bytes
Redo Buffers                5459968 bytes
Database mounted.
Database opened.

SQL> select name,value from v$parameter where name in ('common_user_prefix','os_authent_prefix');

NAME                      VALUE
------------------------- ----------
os_authent_prefix         C##
common_user_prefix        C##

Now, we have the OS_AUTHENT_PREFIX and COMMON_USER_PREFIX set to the same value (C##). Let’s check if we are now allowed to create an external user in the root container (CDB$ROOT) for OS authentication.

---//
---// create external user in CDB$ROOT for OS authentication //---
---//
SQL> show con_name

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

SQL> create user C##MYAPP identified externally;

User created.

SQL> grant create session, resource, select_catalog_role to C##MYAPP;

Grant succeeded.

Yes, we are now able to create an external user in the root container (CDB$ROOT) for OS authentication. Let’s check if OS authentication is working for this account.

---//
---// validate OS authentication for the external database account //---
---//
[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 Sun Feb 14 18:33:12 2016

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

Last Successful login time: Sun Feb 14 2016 18:32:55 +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
------------------------------
CDB$ROOT

---//
---// validate authentication was performed by OS //---
---//
SQL> select sys_context('userenv','authentication_method') as Auth_mode
  2  from dual;

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

As we can see, OS authentication is working as expected for the database account C##MYAPP. The point to be noted here is that, this external account is nothing but a common account which will be accessible to all the associated pluggable databases in the container. We can confirm that by querying the pluggable database as shown below

---//
---// external (common) user propagated to the PDB //---
---//
SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

SQL> select username,external_name,authentication_type,common
  2  from dba_users where username='C##MYAPP';

USERNAME   EXTERNAL_N AUTHENTICATION_TYPE  COM
---------- ---------- -------------------- ---
C##MYAPP              EXTERNAL             YES

Having setup the OS authentication for the database account in the root container (CDB$ROOT), our next goal is to be able to switch to the desired pluggable database in order to run our batch scripts. We can do that while logged in the root container (CDB$ROOT) as the external database user, provided the external user has necessary privileges to switch to other containers. Let’s check if the external user C##MYAPP can switch to other pluggable database.

---//
---// switch to pluggable database as external user //---
---//
SQL> show user
USER is "C##MYAPP"

SQL> show con_name

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

SQL> alter session set container=CDB1_PDB_1;
ERROR:
ORA-01031: insufficient privileges

Looks like the external user (C##MYAPP) doesn’t have privileges to switch to pluggable databases. Let’s grant privilege to the external user.

---//
---// grant privilege to external user to be able to switch container //---
---//
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.

---//
---// validate, external user can switch to containers //---
---//
SQL> !id
uid=504(myapp) gid=504(myapp) groups=504(myapp)

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

SQL> show con_name

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

SQL> alter session set container=CDB1_PDB_1;

Session altered.

SQL> show con_name

CON_NAME
------------------------------
CDB1_PDB_1

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

We are able to switch to the pluggable database (CDB1_PDB_1) as the external user (OS authenticated user). Now, we should be good in terms of running our batch scripts which uses external authentication for database account. We would need to accommodate minimal changes (alter session set container=pdb_name) in our existing scripts to continue running the scripts without any issues.

We still have a concern with this common external user (OS authenticated) implementation. We may don’t want the external user to access other pluggable databases and restrict it to just a specific pluggable database. I shall cover that aspect in an upcoming post.

Stay tuned…

6 Comments
  1. Foued
  2. DanielleITCS
  3. Bertrand N'cho
    • Abu Fazal Abbas
  4. Bertrand N'cho
    • Abu Fazal Abbas
%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed