Multitenancy provides the feature of common and local user but the thing that we need to understand is that do we actually have a requirement of common and local user, The answer is yes. One of the most use case requirement of common user is the golden gate replication and since the replication in golden gate depends upon the change vectors so yes, we do have a requirement of common user.
By default, keyword for a
common user is C##, it means that whenever
we’ve to create a common user we must have to start the username with C## otherwise the create user command will throw
an error.
SQL> show parameter common_user_prefix
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
common_user_prefix string C##
SQL> create user common_test identified by Common#123;
create user common_test
identified by Common#123
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL> create user
C##common_user identified by Common#123;
User created.
SQL> set lines 300
SQL> col username for
a20
SQL> col account_status
for a20
SQL> col con_id for
9999
SQL> col common for a15
SQL> select
con_id,username,account_status from
cdb_users where username='C##COMMON_USER';
CON_ID USERNAME ACCOUNT_STATUS COMMON
------ --------------------
-------------------- -------------
1 C##COMMON_USER OPEN YES
3 C##COMMON_USER OPEN YES
Remove
Prefix keyword while creating a common user
In a real time environment,
we don’t use the prefix and most of the cases we leave that parameter as blank
so that if there is a need to create a common user then we can create it as per
the requirement.
SQL> alter system set common_user_prefix='' scope=spfile sid='*';
System altered.
[oracle@oratest1 ~]$
srvctl stop database -d OCIPRIM
[oracle@oratest1 ~]$ srvctl start database -d OCIPRIM
SQL> show parameter
common_user_prefix
NAME TYPE
VALUE
------------------------------------
----------- ------------------------------
common_user_prefix
string
SQL> create user ggsadmin identified by Ggsadmin123;
User created.
SQL> set lines 300
SQL> col username for
a20
SQL> col account_status
for a20
SQL> col con_id for
9999
SQL> col common for
a15
SQL> select
con_id,username,account_status,common from cdb_users where username='GGSADMIN';
CON_ID USERNAME ACCOUNT_STATUS COMMON
------ --------------------
-------------------- -------------
1 GGSADMIN OPEN YES
3 GGSADMIN OPEN YES
NOTE: All the users created at root container will be a
common user irrespective of the container’s environment whether it’s a
dedicated container or a shared container.
