Common User vs. Local User – 12c Edition

Within Oracle Database 12c, there is new terminology being used for user accounts.  Oracle has come up with a concept called COMMON USERS and LOCAL USERS.  Ok, I know you are wondering what is the difference.  A COMMON USER is a database user whose identity and password are known in the root (CDB) and in every (existing/future) pluggable database (PDB).  

1. A COMMON USER can perform administrative tasks that are specific to the CDB or PDB, such as plugging and unplugging a PDB.  COMMON USERs are the only ones that can navigate between containers that belong to a CDB.  

2. When creating a COMMON USER account there are specific rules that must be followed:

3. Must be connected to the root and have the commonly granted CREATE USER system privilege

4. The session’s current container must be CDB$ROOT

5. The user name must start with C## or c##

6. Explicitly designate a user account as a common user, specify the CONTAINER=ALL as part of the CREATE USER statement

7. Do not create objects in the schemas of common users.

8. If specifying the DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA… ON, and PROFILE clauses as part of CREATE USER; then you must ensure that those objects existing in all containers (PDB) below the CDB

9. User-created schema objects owned by COMMON USERS cannot be shared across PDB boundaries

Now that we have outlined what a COMMON USER is, lets take a look at creating one with the CREATE USER sql syntax.  We need to create a COMMON USER for the CDB:

CREATE USER c##admin
IDENTIFIED BY welcome1
DEFAULT TABLESPACE USERS
QUOTA unlimited on USERS
TEMPORARY TABLESPACE temp
CONTAINER = ALL;

Error starting at line 3 in command:
Error at Command Line:17 Column:1
Error report:
SQL Error: ORA-65048: error encountered when processing the current DDL statement in pluggable database BEERS
ORA-00959: tablespace 'USERS' does not exist

Oh, we got an error.  What is it?  The ORA-65048 error is thrown when the CREATE USER statement looks in the PDB (BEERS)  for the USERS table.  The next error ORA-00959, tells us that the USERS tablespace does not exist.  These errors are thrown because the CREATE USER statement didn’t meet requirement 8 listed above.  How do we correct this issue then?  I can tell you, that MOS does not have any (at time of writing) support docs for ORA-65048/ORA-00959 yes, I looked.  Keeping in mind, requirement 8 is being violated; when I look at the PDB (BEERS), I see that I don’t have a USERS tablespace.  What would happen if I remove the tablespace and quota options? We end up with a very simple CREATE USER statement which creates successfully.

CREATE USER c##admin
IDENTIFIED BY welcome1
CONTAINER = ALL;

user C##ADMIN created.

Now that we understand what a COMMON USER is, what about a LOCAL USER?  

LOCAL USERS are a database user that exists only in a single PDB.  LOCAL USERS can have administrative privileges, but these privileges are localized to the PDB what the account is created in.  LOCAL USERS have the following characteristics:

1. LOCAL USER accounts cannot create user accounts or commonly grant them privileges.

2. Can grant local user accounts common roles. However, the privileges associated with the common role only apply to the local user’s PDB

3. LOCAL USER account must be unique only within its PDB

4. With the appropriate privileges, a local user can access object in a common user’s schema

5. Can be editions-enable a local user account but not a common user account

Now that the LOCAL USER has been outlined, lets look at creating a LOCAL USER in the PDB (BEERs) with the CREATE USER syntax.  We need to create a LOCAL USER for a PDB. Below is the SQL I used to create user BARMAID.  

ALTER SESSION SET CONTAINER = BEERS;

CREATE USER barmaid
IDENTIFIED BY beers
DEFAULT TABLESPACE beers
QUOTA unlimited on beers_part1
QUOTA unlimited on beers_part2
QUOTA unlimited on beers_part3
TEMPORARY TABLESPACE temp
CONTAINER = BEERS;

Error at Command Line:8 Column:13
Error report:
SQL Error: ORA-65013: invalid CONTAINER clause

You will notice that we first have to alter our session and move from the CDB into the PDB (BEERS) container.  This is actually placing us into the PDB.  Next we try to create the user BARMAID.   Everything is set correctly, tablespaces are there, quotas are assigned, what caused the error?  Error was caused from the ORA-65013.  The ORA-65013 is complaining about the value of the CONTAINER class.  Remember, we are already in the PDB and we need to create the user.  This means we need to set CONTAINER equal to CURRENT.  Lets see what happens:

CREATE USER barmaid
IDENTIFIED BY beers
DEFAULT TABLESPACE beers
QUOTA unlimited on beers_part1
QUOTA unlimited on beers_part2
QUOTA unlimited on beers_part3
TEMPORARY TABLESPACE temp
CONTAINER = CURRENT;

user BARMAID created.

Wala, the user is created.

In the end, when creating users we have to understand that there are some new rules for creating a use in multi-tenet databases.  The basic rules that should be taken away are:

1. COMMON USERS must always be created at the CDB level and start with a C## or c## prefixes.

2. LOCAL USERS can only be created at the PDB layer; also must use the CONTAINER clause set to CURRENT for the user to be created.

Well I hope everyone got something out of this post. 

Enjoy!

twitter: @curtisbl294

blog: http://dbasolved.com

Please follow and like: