It is not very often that a DBA creates externally authenticated users. Creating such users in a multitenant database, needs some extra steps.

 

When I talk about multitenant databases and users, the first thing that comes to my mind is common users and local users. If you need a refresher on these definitions, you can find them here.

 

How do you setup a user that is authenticated externally, when you are in a multitenant database environment? How about, setting up an externally authenticated user in a non-multitenant environment?

 

I’ll cover both scenarios in today’s post!

 

1. What Is An Externally Authenticated User?
2. Setup Externally Authenticated Users In Non-Multitenant Database.
3. Setup Externally Authenticated Users In Multitenant Database.

 

1. What Is An Externally Authenticated User?

When an Oracle user is authenticated externally, it simply means that the database will not authenticate the user with a password.

The authentication of the user is happening outside of the database by either the operating system, Kerberos or Radius. For the last two, the Advanced Security Option is required, and it is out of the scope of today’s post.

An externally authenticated user can connect to the database without specifying a username and a password. In this case, the database relies on the underlying operating system to authenticate the user.

 

2. Setup Externally Authenticated Users In Non-Multitenant Database.

Setting up an externally authenticated user in a non-multitenant database, is the same as in the pre 12c versions.

 

If you want the operating system to authenticate the user, you will need to set the following parameter:
OS_AUTHENT_PREFIX. This parameter defines a prefix, that will be used in naming externally authenticated users.

 

The parameter’s default value is OPS$, but you can set it to what value suits you.

The following rules also apply:

• • username must be enclosed in double quotes
  • username must be in UPPERCASE (Windows only)
  • username must be in the format of DOMAIN_NAME\USERNAME (Windows only)
  • username might be case sensitive on certain OS

Let’s look at an example in Unix, for creating an externally authenticated database user for the OS user drobete (lowercase):

SQL> show parameter OS_AUTHENT_PREFIX

NAME               TYPE        VALUE
------------------ ----------- ---------------
os_authent_prefix  string      OPS$

SQL> create user "OPS$drobete" identified externally;
User created.

SQL> grant dba to "OPS$drobete";
Grant succeeded.

-- drobete OS user connects to the server and then to the database.

SQL> connect /
Connected.

SQL> show user
USER is "OPS$drobete"


-- Windows example

SQL> show parameter os_authent_prefix

NAME               TYPE        VALUE
------------------ ----------- ---------------
os_authent_prefix  string      OPS$

SQL> create user "OPS$DBAPARADISE\DROBETE" identified externally;
User created.

SQL> grant dba to "OPS$DBAPARADISE\DROBETE";
Grant succeeded.

-- the user that is logged on to the server is DBAPARADISE\DROBETE, 
-- where DBAPARADISE is the domain name.

sqlplus /
Connected.
SQL> show user
USER is "OPS$DBAPARADISE\DROBETE"

3. Setup Externally Authenticated Users In Multitenant Database.

 

The process is similar in a multitenant environment, with only one difference.

The OS_AUTHENT_PREFIX must match COMMON_USER_PREFIX (default value C##). Depending on the version of Oracle, in 12.1.0.1 it was not possible to change the prefix for common users,
however starting with 12.1.0.2 you are able to set this prefix to any value. Source: How To Use OS External Authentication In A Container Database (Doc ID 2042219.1)

--Windows example:

SQL> show parameter os_authent_prefix
NAME               TYPE        VALUE
------------------ ----------- ---------------
os_authent_prefix  string      OPS$

SQL> show parameter common_user_prefix
NAME               TYPE        VALUE
------------------ ----------- ---------------
common_user_prefix string      C##

SQL> alter system set os_authent_prefix='C##' scope=spfile; 
     -- in the root container

SQL> shutdown immediate;
SQL> startup;

SQL> show parameter os_authent_prefix
NAME               TYPE        VALUE
------------------ ----------- ---------------
os_authent_prefix  string      C##

SQL> show parameter common_user_prefix
NAME               TYPE        VALUE
------------------ ----------- ---------------
common_user_prefix string      C##

SQL> create user "C##DBAPARADISE\DROBETE" identified externally;
User created.

SQL> grant dba , set container to "C##DBAPARADISE\DROBETE" container=all;
Grant succeeded.

-- the user that is logged on to the server is DBAPARADISE\DROBETE, 
-- where DBAPARADISE is the domain name.

sqlplus /
Connected.

SQL> show user
USER is "C##DBAPARADISE\DROBETE"

SQL> connect /@pdb1
Connected.
SQL> show user
USER is "C##DBAPARADISE\DROBETE"

SQL> select username,AUTHENTICATION_TYPE,COMMON 
     from dba_users where username like 'C##%';

USERNAME                AUTHENTICATION_ COMMON
----------------------- --------------- ----------
C##DABAPARADISE\DROBETE EXTERNAL        YES
...