Cheat Sheet For Common And Local Users, Roles, Profiles

When you transition from a single instance, non-CDB database to a multitenant database architecture, working with users, roles and profiles might get confusing.
Some users, roles and profiles can be created only at the root container level, some can be created at the pluggable database level.
All the mystery and confusion around common users will disappear in today’s blog post!

1. Types of Users, Roles, and Profiles In A Multitenant Database
2. Where And How Can I Create Common Users, Roles, Profiles?
3. Where And How Can I Create Local Users, Roles, Profiles?
4. How Are Permissions Granted In A Multitenant Database?
5. Bonus: Common vs Local Cheat Sheet

1. Types of Users, Roles, and Profiles In A Multitenant Database

Starting with the multitenant architecture, two types of users were introduced: the common user and the local user.

Common user names have to start with the characters C## or c##, or any character that is specified by the parameter COMMON_USER_PREFIX.
The default value of this parameter is C##, it can be changed, but it is not recommended to change it.

Local users are regular users in the database. Their name doesn’t start with C##, as a mater of fact, their name cannot start with those characters.

Same rules apply for common roles or profiles, the name must start with the prefix set by the COMMON_USER_PREFIX parameter, which by default is C##.

2. Where Can I Create Common Users, Roles, Profiles?

Common Users can be created only when you are connected to the ROOT container. When you create the common user in the root container, Oracle creates the user in all the pluggable databases, even if a pluggable database is not open.

You cannot create a common user explicitly in a pluggable database (PDB). The create user command for a common user, must be run in the root container.

Same rule applies for common roles and common profiles.

The syntax to create a common user is similar with the syntax to create a local user.

--common user:
SQL> create user C##TESTER1 identified by Oracl3#123 
        default tablespace users temporary tablespace temp CONTAINER=ALL;

--common role:
SQL> create role C##TESTER1_ROLE CONTAINER=ALL;

--common profile
SQL> create profile C##TESTER1_PROFILE;

Considerations:
– CONTAINER=ALL is the default behavior, no need to specify it.
– CONTAINER=ALL is the only allowed value you can use. CONTAINER=CURRENT is not allowed.
– the default tablespace and temporary tablespace mentioned for the user has to exist in the root container and all the PDBs.

3. Where Can I Create Local Users, Roles, Profiles?

Local users can be created only in pluggable databases. Local Users cannot be created in the root container.
The purpose of the root container is not to store local users and their objects. That is what the pluggable databases are for. Thus local users can be created only in pluggable databases.

Same rules apply for the local roles and profiles, these can only be created in the pluggable databases.

The syntax to create a local user is same as in previous pre 12c releases:

--local user:
SQL> create user TESTER1 identified by Oracle3#123 
     default tablespace users temporary tablespace temp CONTAINER=CURRENT;

--local role:
SQL> create role TESTER1_ROLE CONTAINER=CURRENT;

--local profile:

SQL> create profile TESTER1_PROFILE;

Consideration:
–CONTAINER=CURRENT is the default behavior, no need to specify during the user/role/profile creation
–CONTAINER=CURRENT is the only allowed value you can pass. CONTAINER=ALL is not allowed.

4. How Are Permissions Granted In A Multitenant Database?

When granting permissions in a multitenant database, the permissions are always granted by default locally. This means, when you are connected to the root container, and if you do not specify the CONTAINER clause, CONTAINER=CURRENT is the default behavior.

In the root container the CONTAINER clause can be CURRENT or ALL, with default being CURRENT.

--root container:
SQL> grant create table to C##TESTER1_ROLE CONTAINER=CURRENT; --> default
SQL> grant create table to C##TESTER1_ROLE CONTAINER=ALL;

5. Bonus: Common vs Local Cheat Sheet

To better remember these rules about common and local users, I created a mind map for you! Feel free to print it and use it!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana