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. check_tablespace_info_CDB
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 container=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.
Common user privilege was granted with the clause container=ALL, but the last paragraph of this article mentioned that the privilege should be granted with the clause current=ALL. I guess that’s a mistake
Thanks Dear,
I have corrected it.
Regards
Admin