Playing with Oracle 12c Multitenant Users and Roles

I’ve realized these days that the great list of articles by Oracle Alchemist does not contain any articles describing a little more in depth common roles and Users.

I’ve found these ones by Pete Finnigan and Bobby Curtis:

http://www.petefinnigan.com/weblog/archives/00001366.htm

http://dbasolved.com/2013/06/29/common-user-vs-local-user-12c-edition/

http://www.petefinnigan.com/weblog/archives/00001370.htm

But I would like to investigate a little more.

My test environment

Just to give you an idea, I have two PDBs (HR and HR2), each containing an HR schema.

Creating the common user

As already pointed by the existing articles, I can’t create a common user into the root container without the c## prefix, unless I’m altering the hidden parameter _common_user_prefix.

so I specify the correct prefix, and it works:

The user is common, so it appears in all the containers, I can check it by querying CDB_USERS from the root container.

Creating the local user

Then I create also a local user into the HR PDB.

 

CDB_USERS scope

From the PDB I see only the users in the PDB scope:

If I change to the root, I see the users valid into all the containers:

Creating a common role

Do the roles obey to the same rules valid for the users?

Yes, they do! So, let’s create a common role with the C## prefix:

It works, but if I try to create a common role into the root container only, I get an error:

And also if I try to create a local role into the root, I can’t:

Note that the error ORA-65049 is different from the ORA-65096 that I’ve got before.

My conclusion is that the clause container of the create role and create user statements doesn’t make sense as you can ONLY create common users and roles into the root container and only local ones into the PDBs.

 Creating a local role

Just as experiment, I’ve tried to see if I can create a local role with container=ALL. It doesn’t work:

So I create the local role with container=current:

Now, from the PDB I can see the two roles I can access, whereas from the root I can see all the roles I’ve defined so far: the common role is available from all the PDBs, the local role only from the db where it has been defined, just like the users.

 Granting object privileges to the roles

From the root I can’t give grants on objects that reside in a PDB since I cannot see them: I need to connect to the PDB and give the grants from there:

Now, if I query CDB_TAB_PRIVS from the root, I see that the grants are given at a local level (con_id=3 and common=N):

 Granting common and local roles to commond and local users

From a PDB, I can grant local roles to local users or common users:

But I can’t grant a common role to a common user with container=all if I’m in a PDB:

To grant the a common role to a common user I can decide either to:

  •  give the grant locally while connected to the PDB:

  •  give the grant commonly while connected to the root:

I can also grant locally both roles and system privileges to common users while connected to the root container: in this case the privileges are applied to the root container only. Finally having the clause container finally starts to make sense:

Verifying the grants

Ok, I’ve given the grants and I’ve never verified if they work, so far.

Let’s try with the select any table privilege I’ve given in the last snippet. I expect C##GOOFY to select any table from the root container and only HR.COUNTRIES and HR.REGIONS on the HR PDB bacause they have been granted through the two roles.

What’s the mess? When I’ve created the user c##goofy, I’ve granted create and alter session without the container=all:

According to the documentation, the grant command uses container=current by default (common=N):

So, I need to give the grants commonly to let c##goofy connect to all the PDBs:

Now I see that the grants give two distinct permissions : one local and the other common.

If I revoke the grants without container clause, actually only the local one is revoked and the user can continue to login. To revoke the grants I would need to check and revoke both local and common privileges.

After the first revoke statement, I can still connect to HR and verify that my select any table privilege doesn’t apply to the PDB as it’s local to the root container:

After that, I want to check the privileges given through the local and common roles.

I expect both users to select from hr.countries and hr.regions since they have been granted indirectly by the roles.

Let’s try the local user first:

Yeah, it works as expected.

Now let’s try the common user:

It also work, so everything is ok.

Common and local grants, why you must pay attention

During the example, I’ve granted the C##COUNTRY_ROLE many times: locally to PDB, locally to the ROOT, commonly. The result is that I’ve flooded the grant table with many entries:

Let’s try to clean things: for sure I don’t need the grant local to the root:

Then I can choose between revoking the common privilege or the local one. Let’s try to remove the local one:

I’ve removed the local one, but I have still the common one (I’m connected to the PDB so the entries from the other containers are not displayed):

I still have access to the tables as expected:

So, you must pay attention to a couple of things:

  • When granting privileges from the root container, keep in mind that container=current is the default even when the grantee or the role granted are common.
  • When revoking the grants with a Multitenant architecture, keep in mind that there is a scope and you may need more than one statement to actually remove the grant from all the scopes.

As always, I look for opinions and suggestions, feel free to comment!

🙂


Ludovico

Oracle Database 12c: Multitenant, Services and Standard Edition RAC

The installation process of a typical Standard Edition RAC does not differ from the Enterprise Edition. To achieve a successful installation refer to the nice quick guide made by Yury Velikanov and change accordingly the Edition when installing the DB software.

Standard Edition and Feature availability

The first thing that impressed me, is that you’re still able to choose to enable pluggable databases in DBCA even if Multitenant option is not available for the SE.

So I decided to create a container database CDB01 using template files, so all options of EE are normally cabled into the new DB. The Pluggable Database name is PDB01.

As you can see,  the initial banner contains “Real Application Clusters and Automatic Storage Management options“.

Multitenant option is not avilable. How SE reacts to its usage?

First, on the ROOT db, dba_feature_usage_statistics is empty.

This is interesting, because all features are in (remember it’s created from the generic template) , so the feature check is moved from the ROOT to the pluggable databases.

On the local PDB I have:

Having ONE PDB is not triggering the usage of Multitenant (as I was expecting).

How if I try to create a new pluggable database?

A-AH!! Correctly, I can have a maximum of ONE pluggable database in my container.

This allows however:

  • Smooth migration from SE to a Multitenant Architecture
  • Quick upgrade from one release to another

To be sure that I can plug/unplug, I’ve tried it:

Other features of Enterprise off course don’t work

Create a Service on the RAC Standard Edition (just to check if it works)

I’ve just followed the steps to do it on an EE. Keep in mind that I’m using admin managed DB (something will come about policy managed DBs, stay tuned).

As you can see it works pretty well. Comparing to 11g you have to specify the -pdb parameter:

Then I can access my DB (and preferred instance) using the service_name I specified.

 

Let me know what do you think about SE RAC on 12c. It is valuable for you?

I’m also on twitter: @ludovicocaldara

Cheers

Ludo