📋Steps in this guide1/4

Common User vs Local User in 12c Multitenant - DBACLASS DBACLASS

With oracle 12c Multitenant architecture, New terminologies for users has been introduced. LOCAL USER and COMMON USER. SEE ALSO: Oracle 12c Multitenant Architecture 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 […]

oracle clusteringintermediate

by OracleDba

13 views

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:

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. check_tablespace_info_CDB

Code/Command (click line numbers to comment):

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

User created.

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:

Now let’s try to connect to a PDB: Let’s check the privileges for the user in each PDB. 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. So, for a common user, we need to grant privilege with container=ALL, else PDBS will not inherit that privilege.

Code/Command (click line numbers to comment):

grant create session to C##TESTINGUSER.

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

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

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

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

Code/Command (click line numbers to comment):

SQL> show con_name

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

User created.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!