12c Database : Commons users & Local Users

Before to 12c, when using a single database the user management is pretty straight , create a user and assign the privileges or roles and manage it.

In view of multitenant architecture, where multiple databases exists managing multiple databases with a single users can be cumbersome, For example you a host that hosts 10 applications in a single container databases every application has their own dba, assuming this is a consolidated server in your environment.

So now one dba want to shut down their database in normal case, we have to inform all other 9 application dba’s and get their blessing and so on.

Now with multi tenant , your database is self contained (PDB) and can bring down or up without impacting the other databases.

To manage this way , Oracle introduced a concept called Common Users and Local Users, CDB_DBA or PDB_DBA

Lets take a look at Common Users

• A common user is a user exist in the root database and all the PDB databases
• Contains super privileges to manage whole database (CDB)
• A common user can perform administrative tasks specific to the root or PDBs, such as plugging and unplugging PDBs
• specifying the temporary tablespace for the multitenant container database (CDB).
• Common users can connect to the root and perform operationsC
• Common users can perform the following operations across multiple PDBs, Granting privs, Alter database command that effects pdb, Alter pluggable database etc.
• Example of Common Users: SYS, SYSTEM

Lets take a look at Local Users

• Local users exists only in one PDB and local to that database only
• Local user accounts cannot create common user accounts or commonly grant them privileges
• A local user can create and modify local user accounts or locally grant privileges to common or local users in a given PDB.
• The local user account must be unique only within its PDB. Means a user can exists with same name in other PDB, CDB_USERS view has to be filtered with con_id
• With the appropriate privileges, a local user can access objects in a common user's schema. For example, a local user can access a table within the schema of a common user if the common user has granted the local user privileges to access it.

A picture saves 1000 words

In the above Picture

I want to create

In CDB, a common user C##ADMIN and a local user CDB_DBA in root database who can perform CDB (root level) operations

In PDB (racpdb) a local user will need to create localuser,test,pdbadmin for different purposes

Let’s see examples

Example to create a common user

Rules:-

• To create common user you must be in CDB$root
• The user must have create user privilege
• Name of common user must start with C## or c## and contain only ASCII characters
• designate a user account as a common user, in the CREATE USER statement, specify CONTAINER=ALL.
• Do not create the tables or any stuff related to application in common users, can cause problems when plug and unplug
• CONTAINER= clause should be provided in the create user statement , by default container=all is set , means a user created inn CDB will be replicated to all PDB’s
• When you create common user with container=all and assigning default tablespace, that tablespace must be in all containers

Example: Create a common user and assign users tablespace and quota

CONNECT SYSTEM@root
Enter password: password
Connected.

CREATE USER c##admin
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
QUOTA 100M ON USERS
TEMPORARY TABLESPACE temp
CONTAINER = ALL;

CREATE USER CDB_DBA
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
QUOTA 100M ON USERS
TEMPORARY TABLESPACE temp
CONTAINER = CURRENT;

GRANT SET CONTAINER, CREATE SESSION, CREATE_USER TO c##_admin CONTAINER = ALL;

GRANT SET CONTAINER, CREATE SESSION, DBA TO CDB_DBA CONTAINER = CURRENT;

Example to create a local user

Rules:-

• Connect to the PDB you want to create the user
• Use Container=current clause in create user statement
• Your username cannot start with C##
• User name cannot be same as username in root database
• Both common and local users connected to a PDB can create local user accounts, as long as they have the appropriate privileges.

Example to create a Local user

CONNECT SYSTEM@racpdb
Enter password: password
Connected.

CREATE USER localuser
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
QUOTA 100M ON USERS
TEMPORARY TABLESPACE temp
CONTAINER = CURRENT;

CREATE USER test
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
QUOTA 100M ON USERS
TEMPORARY TABLESPACE temp
CONTAINER = CURRENT;

CREATE USER pdb_admin
IDENTIFIED BY password
DEFAULT TABLESPACE USERS
QUOTA 100M ON USERS
TEMPORARY TABLESPACE temp
CONTAINER = CURRENT;

GRANT SET CONTAINER, CREATE SESSION to localuser,test,pdbadmin CONTAINER = CURRENT;

GRANT DBA TO PDB_ADMIN CONTAINER=CURRENT;

Altering Users also requires container clause

For all containers in CDB

ALTER USER c##admin
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA 200M ON USERS
CONTAINER=ALL

For the current container in CDB

ALTER USER c##admin
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA 200M ON USERS
CONTAINER=CURRENT

Enabling Single view for data related to other containers from current container

ALTER USER c##admin
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE temp
QUOTA 200M ON USERS
CONTAINER=CURRENT SET CONTAINER_DATA=(EMP_DB, HR_DB) FOR V$SESSION CONTAINER = CURRENT;

The above enables the common user C##admin to view the emp_db, hr_db sessions details with in the current container only, need not to access from that container.

Important views

CDB_USERS, DBA_USERS,ALL_USERS, DBA_PDBS

Viewing the users for all PDB’s  (Note PDB_ID > 2 means I am excluding the root and PDB$seed which will always have 1,2 in container enabled databases)

COLUMN PDB_NAME FORMAT A15
COLUMN USERNAME FORMAT A30
SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
  FROM DBA_PDBS p, CDB_USERS u
  WHERE p.PDB_ID > 2 AND
        p.PDB_ID = u.CON_ID
  ORDER BY p.PDB_ID;

   PDB_ID PDB_NAME        USERNAME
---------- --------------- ------------------------------
         .
         .
         .
         3 RACPDB           LOCALUSER
         3 RACPDB           TEST
         3 RACPDB           PDB_ADMIN

Source: Online Documentation 12c

-Thanks

Geek DBA