X
  • February 18, 2015

Oracle Multitenant - Common Users

Guest Author
In a Multitenant database,you can create user accounts within the PDB's just like you normally would. For example this command:
SQL> show con_name
CON_NAME
------------------------------
TESTPDB
SQL> create user dbargf identified by robert;
Will create a user account called dbargf within the container named TESTPDB. The user will not be created in any other container. This is known as a local user account within the CDB architecture. It is local to a unique PDB.
This isolation is in alignment with the notion that each PDB is isolated from the parent CDB.
If you had a PDB called PDBTWO, then you could create a different dbargf account in that PDB. That account would be completely separate from the TESTPDB local user account created earlier. The upshot of all of this is that, in general, the namespace for a user account is at the level of the PDB. However, there is an exception.
In the root container of a CDB you cannot create normal user accounts, as seen in this example:
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> create user dbargf identified by robert;
create user dbargf identified by robert
*
ERROR at line 1:
ORA-65096: invalid common user or role name
This is a problem because we will probably need to create separate accounts to administer the CDB at some level (for backups, for overall management) or even across PDB's but with restricted privileges.
For example, let's say I wanted to have a DBA account called dbargf that would be able to create tablespaces in any PDB. I would create a new kind of user account called a common account.
The common account naming format is similar to a normal account name - except that it starts with a special set of characters, C## by default. Too create a common user account called dbargf we would log into the root container and use the create user command as seen here:
SQL>create user c##dbargf identified by robert;
Likewise you use the drop user command to remove a common user account.
When a common user account is created, the account is created in all of the open PDB's of the pluggable database. At the same time, the account is not granted any privileges.
If a PDB was not open when the common user account is created, it will be created when the PDB is opened. When a PDB is plugged in, the common user account will be added to that PDB.
As I mentioned before, in a non-CDB environment and in PDB's, when a user account is created it does not have any privileges and the same is true with a common user account.
For example, if we try to log into the new c##dbargf account we get a familiar error:
ERROR:
ORA-01045: user C##DBARGF lacks CREATE SESSION privilege; logon denied
The beauty of a common user account or role is that it's privileges can span across PDB's. For example, a common user account can have DBA privileges
in two PDB's in a CDB, but it might not have DBA privileges in the remaining PDB's.
You grant privileges to common users as you would any other user - through the grant command as seen here:
SQL> connect / as sysdba
Connected.
SQL> grant create session to c##dbargf;
Grant succeeded.
SQL> connect c##dbargf/robert
Connected.
When the grant is issued from the ROOT container, the default scope of that grant is just to the ROOT container. As a result of this grant then, we can connect to the root container.
C:\app\Robert\product\12.1.0.2\dbhome_2\NETWORK\ADMIN>sqlplus c##rgfdba/robert
SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 18 14:15:24 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Wed Feb 18 2015 14:15:20 -08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> exit
However if we try to connect to a PDB, we will get an error:
SQL> alter session set container=newpdb;
ERROR:
ORA-01031: insufficient privileges
It is in this way that the isolation of PDB’s is maintained (by default).
The default scope of all grants is limited to the container (or PDB) which they are granted. So, if you grant a privilege in the NEWPDB PDB, then that grant only has effect in the NEWPDB PDB.
As an example of this isolation, let's see what happens when we grant the create user privilege to the c##dbargf user and try to create a new common user with c##dbargf afterwards.
First, we grant the create user privilege - this is pretty much what we do today:
grant create user to c##dbargf;
However, when c##dbargf tries to create a user, we still get an error:
create user c##dbanew identified by dbanew
*
ERROR at line 1:
ORA-01031: insufficient privileges
This serves to underscore that by default, grants to a common user (any user for that matter) by default only apply to the container that the grant occurs in. So, the create privilege grant in this case only applies to the ROOT container.
The problem here is that when you create a common user, Oracle tries to create that user in all PDB's. Since c##dbargf does not have create user privileges in all of the PDB's, the command fails when Oracle recurses through the PDB's and tries to create the c##dbargf common user.
So, how do we deal with this? How do we grant the create user privilege to the c$$dbargf account so that it's able to create other common users.
What we do is use the new containers clause which is part of the grant command. In this example, we are using the container=all parameter to indicate that the grant should apply across all containers. Here is an example:
SQL>Connect / as sysdba
connected.
SQL> grant create session to c##dbargf container=all;
Grant succeeded.
Now, let’s try that create user command again:
SQL> create user c##dbanew identified by dbanew;
User created.
Note that we had to log in as SYSDBA to issue the grant. This is because, at this time, the SYSDBA privileged account was the only account that had the ability to grant the create session privilege across all PDB's.
We could give the c##dbargf account the ability to grant the create user privilege command to other accounts if we had included the "with admin option" option during the grant.
So, it's clear then that just because you create a common user, it's like any other user. It essentially has no rights to begin with anywhere. Next time I'll address common users in PDBs and I'll also talk a bit more about grants in the PDB world.

Join the discussion

Comments ( 1 )
  • guest Thursday, March 12, 2015

    hi Guys,

    Have you encountered this in your system? even as sysdba on cdb, it still doesnt work:

    SQL> sho parameter con_name

    SQL> sho con_name

    CON_NAME

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

    CDB$ROOT

    SQL> select cdb from v$database;

    CDB

    ---

    YES

    SQL> sho user

    USER is "SYS"

    SQL> alter session set container =orcl_p1;

    ERROR:

    ORA-01031: insufficient privileges

    SQL>


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.