Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

image

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

2 comments to 12c Database : Commons users & Local Users