By Franck Pachot

.
In 12.1.0.1 the common user prefix was set to C## by an underscore parameter. In 12.1.0.2 it’s a documented parameter and you can change it. But is there any reason for that?

I can think of a reason. Let’s say I want to use external authentication for my common users
SQL> show parameter prefix

 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix                   string      C##
os_authent_prefix                    string      ops$

I want to use OPS$ users which are identified externally. For example, the user OPS$ORACLE can connect without password from the linux user oracle. If I still want to do that in multitenant, I can define the common user prefix to OPS$:

SQL> alter system set common_user_prefix='ops$' scope=spfile;
System altered.
 
SQL> startup force
ORACLE instance started.
...
SQL> create user OPS$ORACLE identified externally;
User created.
&nbsp
SQL> grant dba to OPS$ORACLE container=all;
Grant succeeded.

Then from OS user oracle:


SQL> host id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),495(vboxsf),54322(dba),54323(oper) context=unconfined_u:unconfined_r:unconfined_t:s0-s0:c0.c1023
 
SQL> connect /
Connected.
SQL> show user
USER is "OPS$ORACLE"

This is exactly what I wanted. A common user identified externally.

Of course, it makes no sense to connect though service because external authentication is local only:

SQL> connect /@//localhost/PDB
ERROR:
ORA-01017: invalid username/password; logon denied
 
Warning: You are no longer connected to ORACLE.

So external authentication makes sense only for common users.

In that case, rather that changing the common_user_prefix, it should be better to keep C## and change the os_authent_prefix to C##. The reason is that even when you change the common_user_prefix, the C## is still forbidden, so you have two prefixes that can fail with ORA-65094:


SQL> show parameter common_user_prefix
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
common_user_prefix                   string      ops$
 
SQL> create user c##test identified by oracle;
create user c##test identified by oracle
            *
ERROR at line 1:
ORA-65094: invalid local user or role name

So rather let’s reset the common user prefix to it’s default C## because this is hardcoded to prevent local users starting with it, and change the external identification prefix.


SQL> alter system reset common_user_prefix;
System altered.
SQL> alter system set os_authent_prefix ='C##' scope=spfile;
System altered.
SQL> startup force
...

Of course, then you have to create a C##ORACLE instead of OPS$ORACLE:


create user C##ORACLE identified externally container=all;
User created.
grant DBA to C##ORACLE container=all;
Grant succeeded.

and you can connect without password


SQL> connect /
Connected.
SQL> show user
USER is "C##ORACLE"

Note: this is not SYSDBA connection here. I used oracle user for the example, but this has nothing to do with being in the dba group.

This is an easy way to have all administrators connect with their names. You create linux accounts for them and oracle accounts identified externally. Easy to connect to servers with ssh keys and then to instance. Passwordless and with named users. Remember, SYSDBA is only for things that you cannot do without SYSDBA.