Common User Vs Local User In 12c Multitenant

Introduction: –Database users are the one who really uses and takes the benefits of the database. There will be different types of users depending on their need and way of accessing the database.

1. Application Programmers – They are the developers who concur with the database by means of DML queries. These DML queries are written in the application programs like C, C++, JAVA, Pascal etc. These queries are transfigured into object code to communicate with the database. For example, writing a C program to generate the report of employees who are working in particular department will involve a query to fetch the data from the database. It will include an embedded SQL query in the C Program.
2. Sophisticated Users – They are database developers, who write SQL queries to select/insert/delete/update data. They do not use any application or programs to request the database. They precisely interact with the database by means of the query language like SQL. These users will be scientists, engineers, analysts who thoroughly study SQL and DBMS to apply the concepts in their requirement. In short, we can say this category includes designers and developers of DBMS and SQL.
3. Specialized Users – These are also sophisticated users, but they write special database application programs. They are the developers who develop the complex programs to the requirement.
4. Stand-alone Users – These users will have stand –alone database for their personal use. These kinds of the database will have ready-made database packages which will have menus and graphical interfaces.
5. Native Users – these are the users who use the existing application to interact with the database. For example, online library system, ticket booking systems, ATMs etc which has the existing application and users use them to interact with the database to fulfill their requests.

In the world of 12c  database, we can find some new terminology about the user. With Oracle 12c Multitenant architecture, New terminologies for users has been introduced. LOCAL USER and COMMON USER.

COMMON USER:

1. A common user is a DB user, which work perform an activity in all the containers including root container of the CDB.
2. A common user can only be created in root container.
3. Common username must start with C##.
4. While creating a common user, we can’t mention container=CURRENT. Either mention container=ALL or don’t use container keyword.
5. It is not recommended to create objects under common user

Create a common user:

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create user C##TESTINGUSER identified by C##TESTINGUSER;

User created.

If you are creating the user, by explicitly mentioning the default tablespace, then that tablespace should be present in all the containers i.e root container and PDBS.
If the tablespace is not present in any of the PDBs, then it will throw an error as below.

SQL> create user C##TESTINGUSER2 identified by test default tablespacE test;
create user C##TESTINGUSER2 identified by test default tablespacE test
*
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable database PDB1
ORA-00959: tablespace 'TEST' does not exist

GRANTING PRIVILEGE TO COMMON USER:

grant create session to C##TESTINGUSER.

Now let’s try to connect to a PDB:

sqlplus C##TESTINGUSER/C##TESTINGUSER@pdb1
SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 26 11:41:59 2017

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

ERROR:
ORA-01045: user C##TESTINGUSER lacks CREATE SESSION privilege; logon denied

Let’s check the privileges for the user in each PDB.

set lines 299
col granted_role for a14
col grantee FOR a15
select grantee,granted_role,con_id from CDB_role_privs where grantee='C##TESTINGUSER';
GRANTEE GRANTED_ROLE CON_ID
--------------- -------------- ----------
C##TESTINGUSER CREATE SESSION 1

We can see only root container has this privilege, despite it is a common user.

Now I tried to grant privilege with the container=ALL option.

SQL> grant dba to C##TESTINGUSER container=ALL;

Grant succeeded.

set lines 299
col granted_role for a14
col grantee FOR a15
select grantee,granted_role,con_id from CDB_role_privs where grantee='C##TESTINGUSER';
GRANTEE GRANTED_ROLE CON_ID
--------------- -------------- ----------
C##TESTINGUSER CREATE SESSION 1
C##TESTINGUSER CREATE SESSION 3

— TRY TO CONNECT TO THE PDB:

sqlplus C##TESTINGUSER/C##TESTINGUSER@pdb1

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 26 11:46:16 2017

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

Last Successful login time: Wed Apr 26 2017 11:30:37 +03:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options

So, for a common user, we need to grant privilege with current=ALL, else PDBS will not inherit that privilege.

LOCAL USER:

1.Local users are the normal database user, which can be created only in PDBs. it is dedicated for that PDB. ( Means this user can’t be created in other PDBS).
2.With the appropriate privileges, a local user can access object in a common user’s schema

SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> CREATE USER testing identified by oracle;

User created.

Oracle-help is always available for help to solve the issue. Thank you, readers, for the kind attention on the article.