12c User and Privilege for Multi tenant database

12c User and Privilege for Multi tenant database

In 12c, Container CDB database divide into two types:
Common User/Role: User/Roll present in all containers include root and all pdbs.
Local User/Role: User/Roll present in specific PDB (also same name exists in other pdb but not related to each other)

Note: Common User/Role is prefixed with C##

Create User in 12 Environment

Create the common user for all container:

CREATE USER c##scott IDENTIFIED BY password123 CONTAINER=ALL;
GRANT CREATE SESSION TO c##scott CONTAINER=ALL;

 

Create the local user for specific pdb

— Connect with sysdba user or common user and switch the container for which you want to create user
ALTER SESSION SET CONTAINER = pdb1;

— Create the local user with the CONTAINER clause.
CREATE USER SCOTT IDENTIFIED BY password123 CONTAINER=CURRENT;

GRANT CREATE SESSION TO scott CONTAINER=CURRENT;
OR
— Connect to a privileged user on direct PDB database.
CONN system/password@pdb1

— Create the local user using the default CONTAINER setting.
CREATE USER scott IDENTIFIED BY password1;
GRANT CREATE SESSION TO scott;

Create Role in Container Database

Create common role in Multi tenant 12c environment

sqlplus / as sysdba
CREATE ROLE c##role1;
GRANT CREATE SESSION TO c##role1;

— Grant to a common user.
GRANT c##role1 TO c##scott CONTAINER=ALL;

— Grant to a local user.
ALTER SESSION SET CONTAINER = pdb1;
GRANT c##role1 TO scott;

 
Create local role in Multi tenant 12c environment

You can assign local role within PDB database only.
sqlplus / as sysdba

CREATE ROLE role1;
GRANT CREATE SESSION TO role1;

— Grant to a local user.
GRANT role1 TO scott.

— Grant to a common user.
GRANT role1 TO c##scott;
Note: privileges only valid with the current PDB which is assigned from role1 to common user c##scott.

 

Example of granting privileges:

–Commonly Granted System Privileges

GRANT CREATE ANY TABLE TO c##hr_admin CONTAINER=ALL;

–Commonly Granted Object Privileges Work

GRANT SELECT ON DBA_OBJECTS TO c##hr_admin CONTAINER=ALL;

Note: CONTAINER to ALL applies the privilege to all containers and to CURRENT applies the privilege to the local container.
 

Check the privileges on CDB Database present

COLUMN USERNAME FORMAT A15
COLUMN DEFAULT_ATTR FORMAT A7
COLUMN OWNER FORMAT A15
COLUMN OBJECT_NAME FORMAT A15
COLUMN ALL_CONTAINERS FORMAT A3
COLUMN CONTAINER_NAME FORMAT A10
COLUMN CON_ID FORMAT A6
SELECT USERNAME, DEFAULT_ATTR, OWNER, OBJECT_NAME, ALL_CONTAINERS, CONTAINER_NAME, CON_ID FROM CDB_CONTAINER_DATA ORDER BY OBJECT_NAME;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s