Thursday, August 22, 2013

Multitenant Architecture Common Users and Roles

In this demo I will show the new common users and common roles that exist in a Multitenant environment. I will show how to create common users and roles and how they can be used.

Non-CDB - Pre 12c database or a non container 12c database
CDB - Container database
PDB - Pluggable database

Local users are what we traditional use in non-CDB databases. Common users are users that exist in the CDB root and all PDBs.

Local users can only be created in a PDB and can only interact with that specific PDB. Note a local user can interact with other PDBs but it would use traditional methods like using a DBlink.

Common users exist in the root CDB as well as the PDBs plugged into the CDB. Common user privileges can be granted differently in each PDB plugged into the CDB. The same is true that common users can be granted a privilege commonly across the CDB and all PDBs plugged into the CDB. Common roles can be created to commonly grant privileges to common users.

You maybe asking why create a common user. You could have a situation where you want a DBA to perform activities across all pluggable databases but with out giving the SYSDBA privilege. You could have another situation where you want a DBA to only support one or two pluggable databases in the CDB.

When new pluggable databases are created in the container database the common users and roles will automatically be created in the new pluggable databases. The common user will have the same privileges as in the existing pluggable databases with the common roles.

Environment

CDB- TESTDBS
PDB- DEV1DB, DEV2DB and TSTDB

Create Common User

I will create a common user called C##ORADBADMIN which will have access to the CDB as well as all PDBs plugged into the CDB. I will also create a common user called C##ORADBDEV2 that will have access only to one of the PDBs.

Note:
Common users names need to start with C## or c##.

Below you will see CONTAINER=ALL meaning that the statement executes in the root CDB and all PDBs plugged into the CDB.

Create User

Connected to the container root I create both common users.
$sqlplus / as sysdba
SQL>show con_name con_id
SQL>CREATE USER C##ORADBADMIN PROFILE DEFAULT IDENTIFIED BY temp1234 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK CONTAINER=ALL;

SQL>CREATE USER C##ORADBDEV2 PROFILE DEFAULT IDENTIFIED BY temp1234 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK CONTAINER=ALL;














Grant Connect Privilege

Connected to the container root I create both common users.
$sqlplus / as sysdba
SQL>show con_name con_id
SQL>GRANT CONNECT TO C##ORADBADMIN CONTAINER=ALL;










Switch containers
SQL>alter session set container=dev2db;
SQL>show con_name con_id
SQL>GRANT CONNECT TO C##ORADBDEV2;












Review Users and Privileges

Connected to the container root I use the new dictionary view CDB_USERS. Note you can still use the DBA_USERS or ALL_USERS but there is no con-id column.
$sqlplus / as sysdba
SQL>show con_name con_id
SQL>select con_id,username,common from cdb_users
where username like'C##%'
order by con_id asc;

Below you see the common users exist in the root container and all pluggable databases.


Switch containers
SQL>alter session set container=dev2db;
SQL>show con_name con_id
SQL>select con_id,username,common from cdb_users
where username like'C##%'
order by con_id asc;

Below you see connected to one of the pluggable databases we also see the common users.











Connected to the container root I use the new dictionary view CDB_ROLE_PRIVS. Note you can still use the DBA_ROLE_PRIVS but there is no con-id column.
$sqlplus / as sysdba
SQL>show con_name con_id
SQL>select con_id,grantee,granted_role,common from cdb_role_privs
where grantee like'C##%'
order by con_id asc;

Below you can see that C##ORADBADMIN has the connect role granted across all pluggable databases but C##ORADBDEV2 only has the connect role granted to pluggable database 5. Since the role is not commonly granted across all pluggable databases for C##ORADBDEV2 is labeled as not common.














SQL>alter session set container=dev2db;
SQL>show con_name con_id
SQL>select con_id,grantee,granted_role,common from cdb_role_privs
where grantee like'C##%'
order by con_id asc;

Below you see connected to the pluggable database DEV2DB we can also see the common and not common privileges granted to the common users.











SQL>alter session set container=dev1db;
SQL>show con_name con_id
SQL>select con_id,grantee,granted_role,common from cdb_role_privs
where grantee like'C##%'
order by con_id asc;

Below you can see when connected to pluggable database 4 you can see the common role granted to the common user C##ORADBADMIN but you do not see the common user C##ORADBDEV2 as we only granted the connect role to the common user C##ORADBDEV2 for pluggable database 5.

Test Connection

$sqlplus /nolog
SQL>connect C##ORADBDEV2/temp1234@dev2db
SQL>show con_name con_id
SQL>connect C##ORADBDEV2/temp1234@dev1db

Below you can see we can connect successfully to pluggable database dev2db but the connect command fails to connect to dev1db due to lack of create session privilege.

Create Common Role

I will create a common role with privileges to create, drop and alert users for all pluggable databases. Then I will grant the new role to the common users we created.

Note:
Common role names need to start with C## or c##.

Create Role

Connected to the root container I create the role
$sqlplus
SQL>show con_name con_id
SQL>CREATE ROLE C##MIDLEVELDBA NOT IDENTIFIED CONTAINER=ALL;
SQL>grant drop user to C##MIDLEVELDBA container=all;
SQL>grant create user to C##MIDLEVELDBA container=all;
SQL>grant alter user to C##MIDLEVELDBA container=all;



















Grant Role

Connected to the root container I grant the roles
$sqlplus
SQL>show con_name con_id
SQL>grant C##MIDLEVELDBA to C##ORADBADMIN container=all;











SQL>alter session set container=dev2db;
SQL>show con_name con_id
SQL>grant C##MIDLEVELDBA to C##ORADBDEV2;












Review Users and Privileges

SQL>alter session set container=cdb$root;
SQL>select con_id,username,common from cdb_users
where username like'C##%'
order by con_id asc;
SQL>select con_id,grantee,granted_role,common from cdb_role_privs
where grantee like'C##%'
order by con_id asc;

Below you can see that the C##MIDLEVELDBA role is granted commonly across all pluggable databases for common user C##ORADBADMIN. The role C##MIDLEVELDBA granted to common C##MIDLEVELDBA is not common as it was not commonly granted across all pluggable database only granted for pluggable database 5.
















With the privileges granted common user C##ORADBADMIN can administor user accounts on all pluggable databases. The common user C##ORADBDEV2 can only administor user account on pluggable database 5 DEV2DB.


New Pluggable Databases

If new pluggable database are created the common users and roles will automatically exist in the new pluggable databases.

Below you can see that I create 4 new pluggable databases and the common users and roles exist in the new pluggable databases.



20 comments:

  1. Hi Javier,
    What about export backup in 12c, container database.
    am not able to export the container db user ( expdp / schemas=username)

    ORA-39001: invalid argument value
    ORA-39170: Schema expression 'username' does not correspond to any schemas.

    ReplyDelete
    Replies
    1. For schemas you need to provide the name of the schemas. For example below for the schemas I exported my common user C##ORADBADMIN see below

      alpddbs002:{testdbs}:/media/sf_rdbms_grid_software_64bit/12c_scripts >expdp schemas=C##ORADBADMIN

      Export: Release 12.1.0.1.0 - Production on Tue Aug 27 11:34:20 2013

      Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.

      Username: system
      Password:

      Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
      and Real Application Testing options

      WARNING: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database.

      Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** schemas=C##ORADBADMIN
      Estimate in progress using BLOCKS method...
      Total estimation using BLOCKS method: 0 KB
      Processing object type SCHEMA_EXPORT/USER
      Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
      Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
      Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
      Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
      ******************************************************************************
      Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
      /orabase/admin/testdbs/dpdump/expdat.dmp
      Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Tue Aug 27 11:35:12 2013 elapsed 0 00:00:33

      Delete
    2. getting into the same suitation as above.
      not able to export the container db user ( expdp / schemas=username). No objects of the specified schema is exported. can you guide me with anyother way it could be done?

      Delete
  2. this is my case, try to help

    SQL> alter session set container=CDB1;

    Session altered.

    SQL> show con_name

    CON_NAME
    ------------------------------
    CDB1

    SQL> select username from dba_users where username like '%MY%';

    USERNAME
    --------------------------------------------------------------------------------
    MYUSER


    expdp schemas=MYUSER

    ORA-39001: invalid argument value
    ORA-39170: Schema expression 'MYUSER' does not correspond to any schemas.


    ReplyDelete
  3. Shanmugam,
    Run the following select to see what container the user MYUSER is a part of.
    select con_id,username from dba_users where username like '%MY%';

    The MYUSER is not a common user so in the expdp you would need to be connected directly to the PDB database.

    ReplyDelete
  4. Hi Javier,

    Is it possible to access multiple PDBs with multiple users?

    Consider the scenario:

    I am having two common user :- c##a and c##b
    and pdb :- PDBTEST

    I am connect with user c##a and created table + inserted records in table for PDBTEST pluggable database.

    Now , I am connecting with user c##b and access table which was created in PDBTEST.

    Is that case possible?

    How I can achieve above scenario?


    p.s. : Assume that user c##a and c##b both are granted with all privileges to access PDBTEST.

    Let me know if you need any further detail.

    ReplyDelete
  5. Please let me know, if any workaround for same case.

    ReplyDelete
  6. For common user this would be possible if the common user has the privilege to the table. This would require to connect to the PDB using the common user.

    ReplyDelete
  7. Thanks Javier for .

    I am facing problem :

    When I connect with common user - c##b, i am not able to see table in PDBTEST created by logging with c##a.

    Though, I have assigned rights in PDBTEST for both users c##a and c##b.

    For common user c##b, is there any other rights i need to assign to access PDBTEST.

    Steps i followed :

    Connect as sysdba

    1> created two user c##a and c##b (container = ALL)

    2> common role granted with all privileges : c##role (container = ALL)

    3> Assigned c##role to both user : c##a and c##b (container = ALL)

    4> Created PDB : PDBTEST

    Altered session and set container = PDBTEST

    5> Granted c##role to both user : c##a and c##b inside PDBTEST

    6> connect with c##a (not as sysdba)

    Altered session and set container = PDBTEST

    7> Created Table and inserted record : TestTable

    8> connecting with c##b (not as sysdba)

    Altered session and set container = PDBTEST

    I am not able to access table 'TestTable' after login with c##b..

    More, If i login with c##a , i can access inserted records.

    ReplyDelete
  8. What is the output of the below$sqlplus / as sysdba
    SQL>show con_name con_id
    SQL>select con_id,username,common from cdb_users
    where username like'C##%'
    order by con_id asc;

    ReplyDelete
  9. SQL> show con_name con_id

    CON_NAME
    ------------------------------
    CDB$ROOT

    CON_ID
    ------------------------------
    1

    SQL> select con_id,username,common from cdb_users
    where username like 'C##%' order by con_id asc;

    CON_ID USERNAME
    COM
    -------- -----------------------------------------------------------------
    --------------------------------------------------------- ---
    1 C##A
    YES
    1 C##B
    YES
    3 C##A
    YES
    3 C##B
    YES

    ReplyDelete
  10. What is granted to these accounts
    select con_id,grantee,granted_role,common from cdb_role_privs
    where grantee like'C##%'
    order by con_id asc;

    ReplyDelete
    Replies
    1. Have a look on script that i execute:
      ---

      ALTER SESSION SET Container = CDB$ROOT;

      CREATE USER c##a IDENTIFIED BY tempuser#a
      DEFAULT TABLESPACE users QUOTA UNLIMITED ON users
      account unlock CONTAINER = ALL;

      CREATE USER c##b IDENTIFIED BY tempuser#b
      DEFAULT TABLESPACE users QUOTA UNLIMITED ON users
      account unlock CONTAINER = ALL;

      CREATE ROLE c##role CONTAINER=ALL;

      GRANT ALL privileges TO c##role CONTAINER=ALL;

      GRANT c##role TO c##a WITH ADMIN OPTION CONTAINER=ALL;
      GRANT c##role TO c##b WITH ADMIN OPTION CONTAINER=ALL;

      COMMIT;

      conn c##a/tempuser#a@orcl as sysdba;

      ALTER session SET container=cdb$root;

      CREATE PLUGGABLE DATABASE pdbtest ADMIN USER pdbroot IDENTIFIED BY pdbtest#root
      roles = (DBA)
      DEFAULT TABLESPACE "USERS" DATAFILE '/app/root/oradata/orcl/PDB_pdbtest/USERS.dbf' SIZE 5M REUSE AUTOEXTEND ON
      FILE_NAME_CONVERT=(
      'SYSTEM', 'pdbtest',
      'SYSAUX','pdbtestAUX',
      'PDBSEED','PDB_pdbtest'
      )
      STORAGE UNLIMITED;

      ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
      COMMIT;

      ALTER session SET container = pdbtest;

      ALTER DATABASE DEFAULT TABLESPACE "USERS";
      GRANT UNLIMITED TABLESPACE TO c##a;
      GRANT UNLIMITED TABLESPACE TO c##b;
      GRANT c##role to c##a;
      GRANT c##role to c##b;


      conn c##a/tempuser#a@orcl;
      alter session set container=pdbtest;
      create table testTable (
      id number(5),
      name varchar2(50)
      );

      insert into testTable values (1,'test1');
      insert into testTable values (2,'test2');

      commit;
      select * from testTable;
      ID NAME
      ---------- --------------------------------------------------
      1 test1
      2 test2

      -----------------------------------

      Now I am connecting with c##b;
      ------------------------------
      conn c##b/tempuser#b@orcl;

      alter session set container=pdbtest;

      show con_id con_name user;

      CON_ID
      ------------------------------
      3

      CON_NAME
      ------------------------------
      PDBTEST
      USER is "C##B"

      SQL> select * from testTable;
      select * from testTable
      *
      ERROR at line 1:
      ORA-00942: table or view does not exist

      Delete
  11. here , User c##b can't access testTable..

    and according to oracle docs, common users have rights to access data from PDB.

    i am finding workaround for this scenario.

    ReplyDelete
  12. As a test try to explosively granting the rights on the table to the common user to see if that helps.

    ReplyDelete
  13. Developer did you get this resolved? What about doing the following select * from c##b.testTable;

    ReplyDelete
  14. Hi Javier,

    Sorry for late reply, i was busy with some other stuff.

    Yes, using select * from c##b.testTable; it works.
    Or i have option to create public synonyms too.

    but this can not help me where i have 350+ tables among 5 pdbs.
    and application have two different users (say c##a and c##b) with their different roles and privileges.

    Thanks for help.

    ReplyDelete
  15. Thanks for sharing, nice post! Post really provice useful information!

    An Thái Sơn chia sẻ trẻ sơ sinh nằm nôi điện có tốt không hay võng điện có tốt không và giải đáp cục điện đưa võng giá bao nhiêu cũng như mua máy đưa võng ở tphcm địa chỉ ở đâu uy tín.

    ReplyDelete

  16. Iam so thrilled because of finding your alluring website here.Actually i was searching for Oracle DBA.Your blog is so astounding and informative too..Iam very happy to find such a creative blog. Iam also find another one by mistake while am searching the same topicOracle APEX.Thank you soo much..

    ReplyDelete