Multitenant – Commonality

Oracle Database 12c – Multitenant – Common users and roles

It’s time to use common users and roles.

Introduction

Starting point for this article is presented in a table below.

Attribute Value
Software Oracle Linux 6.5 (64 bit for x86)


Oracle Database 12.1.0.2.0 (64 bit for Linux 6.5)


Installation and configuration of this software is described in this article: http://oradistrict.com/?p=11
Additional tests on this installation and CDB/PDB – more details can be found here: http://oradistrict.com/?p=108

Databases One container database:
tcdb
glogin.sql file Add couple new lines inside your login script for sql*plus. Most of the time you really don’t care about time zone in your test database.
set termout off
alter session set nls_timestamp_tz_format = ‘yyyy-mm-dd hh24:mi:ss’;
alter session set nls_timestamp_format = ‘yyyy-mm-dd hh24:mi:ss’;
alter session set nls_date_format = ‘yyyy-mm-dd hh24:mi:ss’;
set linesize 92
set pagesize 250
set termout on
Scripts used in this guide ./mt/container121.sql – list containers (CDB + seed + PDBs) in your CDB
./sap/mysession121.sql – list details about your session by SID
./mt/cusers121.sql – list all users in CDB
./mt/cusersonm121.sql – list only non-Oracle managed users with IDs
./mt/cusersonml121 – list only local non-Oracle managed users with IDs
./iad/databaseproperties112.sql – list all database properties on container level only (dba view)
./mt/cdatabaseproperties121.sql – show all PDBs properties (or current PDB)
./mt/cobject121.sql – list of all objects owned by pointed user across all PDBs
./mt/cobjectpriv121.sql – privileges granted on objects to users
./mt/ctable121.sql – list tables owned by pointed user across all PDBs
./mt/cprivilege121.sql – list of privileges granted to user or role
./mt/crole121.sql – list all roles and privileges granted to this role
./mt/cdata121 – container data attributes across containers granted to common users
All scripts can be downloaded from my github project: Oradistrict Toolbox – download, unzip and use trunk directory to start menu via odv.sql. Most of those scripts reside under position 9 in the menu – enjoy!
oracle user shell It’s good to use aliases in shell – for example:
alias sql=”sqlplus / as sysdba”
provide easy way to login internally as sysdba. You can put it in your .bash_profile or .profile file.
references Security Guide
SQL Language Reference
Administrator’s Guide
Reference
Concepts
Error Messages – ORA-65056 (container data usage)

Common user

To create a new common user start with logging to cdb$root container as sys and list available containers. Remember to setup your environment before doing so.

. oraenv
sql
alter pluggable database all open read write;
start mt/container121
Key for column names
CID - Container ID

 CID Container name               DbID Size in MB Open mode  Open time              Res
---- -------------------- ------------ ---------- ---------- ---------------------- ---
   1 CDB$ROOT                918792381          0 READ WRITE 2015-08-23 09:14:50    NO
   2 PDB$SEED               4034246255        845 READ ONLY  2015-08-23 09:14:50    NO
   3 PIPBOY                 3788905833        927 READ WRITE 2015-08-23 09:21:44    NO
   4 SPECIAL                 510174438        911 READ WRITE 2015-08-23 09:21:44    NO
   5 MOLERAT_CLONE          2993991422       1315 READ WRITE 2015-08-23 09:21:44    NO
   6 MOLERAT_CLONE37        2904160425       1315 READ WRITE 2015-08-23 09:21:44    NO

Make sure that you’re connected with cdb$root container:

start sap/mysession121
User Name    OS User        Program                    Service Name Machine
------------ -------------- -------------------------- ------------ ----------------
SYS          oracle         sqlplus@odone (TNS V1-V3)  SYS$USERS    odone


Container Name
------------------------------------------------
CDB$ROOT

As sys common user we’ve got required privileges: create user and set container (with admin option to grant it to the new user). Issue the command to create new common user.

create user "c##ArthurMaxson"
  identified by secret123
  container = all;

Container parameter can be set only to value all which is default and cannot be changed. The same is true for container = current during creation of local user – the only valid and default value for local user when using connection with PDB.

Check database users using ./mt/cusers121.sql script. This script lists all users in the database (if your PDB is closed users from this PDB wont be enlisted as this output is based on CDB_* view). Focus on last part of this report with a query which lists only non-Oracle managed users.

start mt/cusers121
Key for column names
C - Common user
OM - Oracle Maintained

~~ only non-system users (non-oracle maintained) ~~

Container Name  User name                Account status     Created date        C OM
--------------- ------------------------ ------------------ ------------------- - --
CDB$ROOT        c##ArthurMaxson          OPEN               2015-08-23 10:47:10 Y N
PIPBOY          MAXSTONE                 OPEN               2015-04-13 11:30:50 N N
PIPBOY          c##ArthurMaxson          OPEN               2015-08-23 10:47:11 Y N
SPECIAL         MAXSTONE                 OPEN               2015-04-13 11:30:50 N N
SPECIAL         c##ArthurMaxson          OPEN               2015-08-23 10:47:11 Y N
MOLERAT_CLONE   AGNES                    OPEN               2015-04-19 10:54:57 N N
MOLERAT_CLONE   LEWIS                    OPEN               2015-04-19 10:54:57 N N
MOLERAT_CLONE   c##ArthurMaxson          OPEN               2015-08-23 10:47:11 Y N
MOLERAT_CLONE37 AGNES                    OPEN               2015-04-19 10:54:57 N N
MOLERAT_CLONE37 LEWIS                    OPEN               2015-04-19 10:54:57 N N
MOLERAT_CLONE37 NEWDBA                   OPEN               2015-06-10 00:03:59 N N
MOLERAT_CLONE37 c##ArthurMaxson          OPEN               2015-08-23 10:47:11 Y N

Common user “c##ArthurMaxson” is present separately with individual user id in each container according to PDB user id numeration. Interesting thing is that I’ve managed to create this user in two different times for root and PDBs – this suggests that common user is created as a normal user in each container and only managed differently according to container clause of alter/create user commands. Off course, you would not find such suggestion in Oracle documentation. Consider result of this script:

start mt/cusersonm121
Key for column names
C - Common user

~~ all users ~~
~~ only non-system users (non oracle maintained) ~~

 CnID Container Name      User Id User name                Account status     C
----- --------------- ----------- ------------------------ ------------------ -
    1 CDB$ROOT                112 c##ArthurMaxson          OPEN               Y
    3 PIPBOY                  102 MAXSTONE                 OPEN               N
    3 PIPBOY                  118 c##ArthurMaxson          OPEN               Y
    4 SPECIAL                 102 MAXSTONE                 OPEN               N
    4 SPECIAL                 115 c##ArthurMaxson          OPEN               Y
    5 MOLERAT_CLONE            70 AGNES                    OPEN               N
    5 MOLERAT_CLONE            71 LEWIS                    OPEN               N
    5 MOLERAT_CLONE            84 c##ArthurMaxson          OPEN               Y
    6 MOLERAT_CLONE37          70 AGNES                    OPEN               N
    6 MOLERAT_CLONE37          71 LEWIS                    OPEN               N
    6 MOLERAT_CLONE37          74 NEWDBA                   OPEN               N
    6 MOLERAT_CLONE37          84 c##ArthurMaxson          OPEN               Y

User id is related with PDB – commonality assure us that separation between PDBs is maintained. For end users this common user have the same id in PDB’s 5 and 6. The same could happen for two non-CDB’s. Local users agnes and lewis has the same ids in different PDBs but they are still two separate users without any common database objects. They can have different passwords and schema objects – totally separated.

As we are using a common user and have necessary privileges to switch between session PDB context – we will do so. Switch to molerat_clone PDB and check the result of the same script.

alter session set container = molerat_clone;
start mt/cusersonm121
 CnID Container Name      User Id User name                Account status     C
----- --------------- ----------- ------------------------ ------------------ -
    5 MOLERAT_CLONE            70 AGNES                    OPEN               N
    5 MOLERAT_CLONE            71 LEWIS                    OPEN               N
    5 MOLERAT_CLONE            73 c##ArthurMaxson          OPEN               Y

Right now we can see something that would be normal for non-CDB database. Only one set of database users. To see this result I’ve used cdb_users and v$containers views from which in PDB I can only query information that is related with this one PDB (*this is not a rule for all dictionary objects, check more about object links and metadata links in Oracle Database Concepts for details – link added to reference part above). To confirm that statement I’ve checked how many containers are visible in presented views:

select count(0) "Number of users", con_id
  from cdb_users
 group by con_id;
Number of users  CnID
--------------- -----
             22     5
select count(0) "Number of containers", con_id
  from v$containers
 group by con_id;
Number of containers  CnID
-------------------- -----
                   1     5

To summarize, I could create a common user because:

  • I was using cdb$root session during execution of create user command
  • I had required privileges (create user)
  • I’ve used container=all (with or without this clause it has to be used)
  • I’ve named my user with prefix c## (this could be C## too)
  • Oracle does not recommend creating objects in common user schema – this is a kind of odd recommendation (check in Database Security Guide, chapter 2 about creating common and local users)

There are two more rules related with common users which I’m going to check with next commands. Before going back to root container check if similar command (to the first one in this tutorial) can be executed as common users sys from molerat_clone PDB:

show con_name
CON_NAME
------------------------------
MOLERAT_CLONE
create user "c##RogerMaxson"
  identified by secret123
  container = all;
ERROR at line 2:
ORA-65050: Common DDLs only allowed in CDB$ROOT

This error confirms that we can’t create common user from PDB session and, moreover, we cannot issue ANY common DDL from anywhere else than cdb$root container. Just to check another rule try this command:

create user "c##RogerMaxson"
  identified by secret123
  container = current;
ERROR at line 1:
ORA-65094: invalid local user or role name

Prefix c## is reserved only for common users and roles – this is why we cannot use them on PDB level. Container parameter value current is valid only for local users, check this command to be sure, that creating new user is available from common user sys:

create user "RogerMaxson"
  identified by secret123
  container = current;
User created.
start mt/cusersonm121
Key for column names
C - Common user

~~ all users ~~
~~ only non-system users (non oracle maintained) ~~

 CnID Container Name      User Id User name                Account status     C
----- --------------- ----------- ------------------------ ------------------ -
    5 MOLERAT_CLONE            70 AGNES                    OPEN               N
    5 MOLERAT_CLONE            71 LEWIS                    OPEN               N
    5 MOLERAT_CLONE            84 c##ArthurMaxson          OPEN               Y
    5 MOLERAT_CLONE            85 RogerMaxson              OPEN               N

Now, we can go back to root container and check if we are able to skip naming requirements.

alter session set container = cdb$root;
create user "JohnMaxson"
  identified by secret123
  container = all;
ERROR at line 1:
ORA-65096: invalid common user or role name

Last combination of attempt to create local user in root container:

create user "JohnMaxson"
  identified by secret123
  container = current;
ERROR at line 2:
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

This concludes topic of user name requirements and container clause issued from active session in root and PDB container.

Now I’ll try to set default tablespace for a common user.

create user c##vree
  identified by secret123
  default tablespace users;
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable
database PIPBOY
ORA-00959: tablespace 'USERS' does not exist

And this is where I’ve got users tablespace:

select ts#, name, con_id
  from v$tablespace
 where name in ('USERS');
       TS# Container name        CnID
---------- -------------------- -----
         4 USERS                    1
         4 USERS                    5
         4 USERS                    6

This is how my containers looks like (con_id and cid are the same identifier of PDB in CDB):

start mt/container121
Key for column names
CID - Container ID

 CID Container name               DbID Size in MB Open mode  Open time              Res
---- -------------------- ------------ ---------- ---------- ---------------------- ---
   1 CDB$ROOT                918792381          0 READ WRITE 2015-08-23 09:14:50    NO
   2 PDB$SEED               4034246255        845 READ ONLY  2015-08-23 09:14:50    NO
   3 PIPBOY                 3788905833        927 READ WRITE 2015-08-23 09:21:44    NO
   4 SPECIAL                 510174438        911 READ WRITE 2015-08-23 09:21:44    NO
   5 MOLERAT_CLONE          2993991422       1315 READ WRITE 2015-08-23 09:21:44    NO
   6 MOLERAT_CLONE37        2904160425       1315 READ WRITE 2015-08-23 09:21:44    NO

I’ve created pipboy PDB with default tablespace vault13. Tablespace users is not part of neither pipboy or special PDBs. This is why Oracle throws an error and stopped on first problem with pipboy PDB. When we omit default tablespace clause common user receives default tablespace along to the one identified in PDB properties (value for DEFAULT_PERMANENT_TABLESPACE). We can check it using another script. First for the root and then for pipboy PDB.

start iad/databaseproperties112
Property name            Property value           Property description
------------------------ ------------------------ ------------------------------------------
DBTIMEZONE               +02:00                   DB time zone
DEFAULT_EDITION          ORA$BASE                 Name of the database default edition
DEFAULT_PERMANENT_TABLES USERS                    Name of default permanent tablespace
PACE
(...)
alter session set container = pipboy;
start iad/databaseproperties112
Property name            Property value           Property description
------------------------ ------------------------ ------------------------------------------
DBTIMEZONE               +02:00                   DB time zone
DEFAULT_EDITION          ORA$BASE                 Name of the database default edition
DEFAULT_PERMANENT_TABLES VAULT13                  Name of default permanent tablespace
PACE
(...)

The same is true for temporary tablespace name. If PDB uses different one and we would like to declare which one common user should use then, in case if this temporary tablespace does not exists in PDB, we’ll receive similar error.

Common user objects

Using the same session I’ll try to create a new table in root container.

alter session set container = cdb$root;
grant create session, create table to "c##ArthurMaxson";

Both privileges are granted locally – I’ll discuss this topic a bit later. More important is that… here comes the classic:

alter user "c##ArthurMaxson" quota unlimited on users;
ERROR at line 1:
ORA-65048: error encountered when processing the current DDL statement in pluggable
database PIPBOY
ORA-00959: tablespace 'USERS' does not exist

This is the consequence of previous tests when I wanted to create a new user with a default tablespace which is not present in all PDBs. We need to remember about common scope of this command (in some commands container clause default value vary so this is some kind of inconsequence from Oracle). Anyway, I can solve this problem globally (which isn’t pretty but “it works”) or locally.

I can switch between PDBs and assign correct quota in local scope using container = current clause, in this case, for root container:

alter user "c##ArthurMaxson" quota unlimited on users container = current;

What is interesting about user quotas that to stop showing it in cdb_ts_quotas (or dba_ts_quotas if you prefer non-CDB nomenclature) we need to set quota to 0 (zero).

alter user "c##ArthurMaxson" quota 0 on users container = current;

It is somewhat interesting in context of container_data clause which I’m going to describe later. You can always use burnmaster to get all required quota in just one shot:

grant unlimited tablespace to "c##ArthurMaxson" container = all;
connect "c##ArthurMaxson"/secret123@tcdb
create table am_table
(
  id number(12),
  blast_radius number(30)
);
insert into am_table values (1, 200000000);
commit;
select * from am_table;
        ID BLAST_RADIUS
---------- ------------
         1    200000000

Now, I’ll try to check the same query on PDB level, for example, in special PDB:

connect "c##ArthurMaxson"/secret123@special
ERROR:
ORA-01045: user c##ArthurMaxson lacks CREATE SESSION privilege; logon denied

Earlier privilege has been granted only on cdb$root level. To allow login on special PDB we need to either grant locally the same privilege or commonly from root level. I’m going for a second option.

connect / as sysdba
grant create session, create table to "c##ArthurMaxson" container = all;

And now back to special PDB:

connect "c##ArthurMaxson"/secret123@special
select * from am_table;
ERROR at line 1:
ORA-00942: table or view does not exist

Right now am_table exists only in root container. Against the documentation we can create objects in root container but we won’t have much use of it. Oracle managed schemas like apex, spatial or text schemas are shared between all PDBs from root container. One cannot do the same with his own objects.

I’m going to create this table in special PDB too.

create table am_table
(
  id number(12),
  blast_radius number(30)
);
insert into am_table values (1, 200000000);
commit;
select * from am_table;
        ID BLAST_RADIUS
---------- ------------
         1    200000000

Now we can go back to root container where we check, where Arthur Maxson tables are.

conn / as sysdba
start mt/cobject121
Add %% if needed for owner name. Search is case insensitive.

Input objects owner: %arthur%
Objects with owner LIKE %arthur%

Container name   Object type          Object name                    Status
---------------- -------------------- ------------------------------ -------
CDB$ROOT         TABLE                AM_TABLE                       VALID
SPECIAL          TABLE                AM_TABLE                       VALID

And one more check for tables owned by our user:

start mt/ctable121
Add %% if needed for owner name. Search is case insensitive.

Input objects owner: %arthur%
Tables with owner LIKE %arthur%

Container name   Table name                     Tablespace name
---------------- ------------------------------ ------------------------------
CDB$ROOT         AM_TABLE                       USERS
SPECIAL          AM_TABLE                       VAULT13

As a bonus I’ll try to export this user from this database and import it to another PDB. To be more precise – I’ll try to export common user from special and import to pipboy (there are the same default tablespaces vault13)

Exit from sql*plus, create a new directory on a file system, create directory object in PDB and make a simple export using Data Pump.

exit
mkdir -p /u02/expdp/tcdb
sqlplus system/manager@special
create directory amexpdir as '/u02/expdp/tcdb';
exit

Create export parameter file as we need it to use case sensitive name.

vi /u02/expdp/tcdb/amexp.par
userid=system/manager@special
directory=amexpdir
schemas='"c##ArthurMaxson"'
dumpfile=amexp.dmp
logfile=amexp.log
logtime=all

And export the c##ArthurMaxson user.

expdp parfile=/u02/expdp/tcdb/amexp.par

In parameter file I’ve used new parameter logtime – it provides timestamp for each operation logged by expdp. This way we can find out how much time Oracle spent on export of each object.

As you can see in log only PDB definition have been taken under consideration during the export. What about the import?

First of all I would like to see what DDLs are inside the dump file. Create impdp parfile for tests.

vi /u02/expdp/tcdb/amimpt.par
userid=system/manager@special
directory=amexpdir
schemas='"c##ArthurMaxson"'
dumpfile=amexp.dmp
logfile=amexpimpt.log
logtime=all
sqlfile=ameximpt.sql

And “import” the c##ArthurMaxson user.

impdp parfile=/u02/expdp/tcdb/amimpt.par

Check generated ameximpt.sql file. You can see that only PDB related commands are available. So, commonality is not important here – impdp ignore PDB level, it is related with a database, just like in previous versions of the database (non-CDBs). Even when you skip schemas parameter and let it go as full import it doesn’t matter – in sql file you still won’t find common user definition and it is aligned with the rules – on PDB level you are not able to issue common command (container = all) – this is why it’s useless to try to import common user to PDB – it is possible to create common user only in root container but from root container you won’t see PDBs data. This way common users in PDBs are exported similarly to the way old exp tool handled things in the past.

First, I’ll try to import the data to the same user but in different PDB. New parameter file need to be prepared. Remember to create the same directory in pipboy PDB.

vi /u02/expdp/tcdb/amimpc.par
userid=system/manager@pipboy
directory=amexpdir
schemas='"c##ArthurMaxson"'
dumpfile=amexp.dmp
logfile=amexpimpc.log
logtime=all
sqlplus system/manager@pipboy
create directory amexpdir as '/u02/expdp/tcdb';
exit
impdp parfile=/u02/expdp/tcdb/amimpc.par
sql
start mt/ctable121
Input objects owner: %arthur%
Tables with owner LIKE %arthur%

Container name   Table name                     Tablespace name
---------------- ------------------------------ ------------------------------
CDB$ROOT         AM_TABLE                       USERS
PIPBOY           AM_TABLE                       VAULT13
SPECIAL          AM_TABLE                       VAULT13

Import have been possible as target user alredy exists in pipboy PDB (as he is a common user). And another attempt with conversion to local user. Usually expdp export metadata too. I should be able to automatically create this user. But let’s check if it is true in this case too.

exit
vi /u02/expdp/tcdb/amimpcl.par
userid=system/manager@pipboy
directory=amexpdir
schemas='"c##ArthurMaxson"'
remap_schema='"c##ArthurMaxson"':arthurmaxson
dumpfile=amexp.dmp
logfile=amexpimpcl.log
logtime=all
impdp parfile=/u02/expdp/tcdb/amimpcl.par

And here, during the import I’ve received an error related with use of remap_schema – right now I’ll leave it as it is. I even tried to import as sysdba in pipboy – without any luck.

UDI-00014: invalid value for parameter, 'remap_schema'

From time to time I encounter some problems with case sensitivity – maybe it’s on of those nasty problems.

Again, to be sure, I’ve created another user called c##arma with the same privileges and one table, created export and now I’m trying to reimport it with remap schema (this time name is without “”).

sql
create user c##arma
  identified by secret123
  container = all;
grant create session, create table to c##arma container = all;
grant unlimited tablespace to c##arma container = all;
connect c##arma/secret123@special
create table am_table
(
  id number(12),
  blast_radius number(30)
);
insert into am_table values (1, 200000000);
commit;
select * from am_table;
        ID BLAST_RADIUS
---------- ------------
         1    200000000

My armaexp.par export parameter file looks like this:

userid=system/manager@special
directory=amexpdir
schemas='c##arma'
dumpfile=armaexp.dmp
logfile=armaexp.log
logtime=all

Exporting schema:

expdp parfile=/u02/expdp/tcdb/armaexp.par

You can see that I had too add ” for schema name. Without single quote I’ve received an error:

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

Ok, now it’s time for import with remapping of c##arma user. Here is my armaimpcl.par parameter file (similar to the one used with c##ArthurMaxstone user) and the result.

userid="sys/manager@pipboy as sysdba"
directory=amexpdir
schemas='c##arma'
remap_schema='c##arma':otheruser
dumpfile=armaexp.dmp
logfile=armaexpimpcl.log
logtime=all
impdp parfile=/u02/expdp/tcdb/armaimpcl.par
UDI-00014: invalid value for parameter, 'remap_schema'

So… looks like Oracle don’t manage in any way to remap exported common user. Interesting. Now we know, why it is not recommended to use common users schema to hold any data in it – we can have serious troubles with moving those schemas elsewhere. Maybe database link could be a solution here – but we should remember about restrictions related with moving data via database link. Anyway, I hope that we won’t find any surprises like this one inside database engine.

I’ve even tried to create destination user for remap clause – without any success.

sqlplus system/manager@pipboy
create user otheruser identified by secret123;
grant create table, create session to otheruser;
grant read, write on directory amexpdir to otheruser;
grant unlimited tablespace to otheruser;

Last attempt was to use newly created user to remap table, armaimptr.par parameter file:

userid=otheruser/secret123@pipboy
directory=amexpdir
tables='c##arma.am_table'
remap_table='c##arma.am_table':otheruser.am_table
dumpfile=armaexp.dmp
logfile=armaexpimptr.log
logtime=all
impdp parfile=/u02/expdp/tcdb/armaimptr.par
UDI-00014: invalid value for parameter, 'remap_table'

Toying with remap_table parameter leads me even to a state, where Oracle finished the import but probably sees table name as ‘c’ with remap defined as:

remap_table=\'"C##ARMA"."AM_TABLE"\':otheruser.am_table

I assume that it is not possible to import common user objects to other schemas using remap clause. I’ve tried other escape options too – no luck in all attempts.

Local user

If you think about users from 11g version and earlier you should see a user related with only one database (but many instances in RAC). Oracle now calls that kind of database as a non-CDB database. In PDBs local users means: available only for this PDB. PDBs are separated entities and should be treated by end users as such. When we create a user in special PDB then there is no way to use this username and password in another PDB, like pipboy. So, common users can use their privileges and login across all PDB (if create session is granted commonly or locally in each PDB) and local users stays the same like the old kind of users from earlier releases of Oracle Database.

I’ll create four users. Two in special PDB: machete and dominic. And another two in pipboy PDB: machete and mechanist. Machete has no problem with existence in two PDBs at once.

sqlplus system/manager@special
create user machete identified by dominic;
grant create table, create session to machete;
alter user machete quota 32m on vault13;
create user dominic identified by machete;
grant create table, create session to dominic;
alter user dominic quota 32m on vault13;
connect machete/dominic@special
create table canterbury
(
  id number(10),
  newname varchar2(40)
);
insert into canterbury values (123, '123');
commit;
connect dominic/machete@special
create table canterbury_dom
(
  id number(10),
  newname varchar2(40)
);
insert into canterbury_dom values (123, '123');
commit;

And another two local users on pipboy PDB.

connect system/manager@pipboy
create user machete identified by dominic;
grant create table, create session to machete;
alter user machete quota 32m on vault13;
create user mechanist identified by mechanist;
grant create table, create session to mechanist;
alter user mechanist quota 32m on vault13;
connect machete/dominic@pipboy
create table canterbury_pb
(
  id number(10),
  newname varchar2(40)
);
insert into canterbury_pb values (123, '123');
commit;
connect mechanist/mechanist@pipboy
create table canterbury_mech
(
  id number(10),
  newname varchar2(40)
);
insert into canterbury_mech values (123, '123');
commit;

Now we can go back to root container and see summary about users and objects that we’ve just created.

connect / as sysdba
start mt/cusersonml121
Key for column names
C - Common user

~~ all users ~~
~~ only non-system users (non oracle maintained and local) ~~

 CnID Container Name      User Id User name                Account status     C
----- --------------- ----------- ------------------------ ------------------ -
    3 PIPBOY                  102 MAXSTONE                 OPEN               N
    3 PIPBOY                  121 OTHERUSER                OPEN               N
    3 PIPBOY                  122 MACHETE                  OPEN               N
    3 PIPBOY                  123 MECHANIST                OPEN               N
    4 SPECIAL                 102 MAXSTONE                 OPEN               N
    4 SPECIAL                 118 MACHETE                  OPEN               N
    4 SPECIAL                 119 DOMINIC                  OPEN               N
    5 MOLERAT_CLONE            70 AGNES                    OPEN               N
    5 MOLERAT_CLONE            71 LEWIS                    OPEN               N
    5 MOLERAT_CLONE            85 RogerMaxson              OPEN               N
    6 MOLERAT_CLONE37          70 AGNES                    OPEN               N
    6 MOLERAT_CLONE37          71 LEWIS                    OPEN               N
    6 MOLERAT_CLONE37          74 NEWDBA                   OPEN               N

New users are present in different PDBs. Just to highlight the basics:

  • I was using container = current during creation all of the users – it’s automatically implied when connected to PDB and no container clause is given
  • I had required privileges (create user)
  • I’ve named my user without prefix c## – local user cannot have his name started with c##
  • I cannot use the same name for role and user – and I cannot use the same name for local and common user (because of prefix rule with common users and roles)

One more overview about database users objects, for example, tables that belongs to machete:

start mt/ctable121
Tables with owner LIKE machete

Container name   Table name                     Tablespace name
---------------- ------------------------------ ------------------------------
PIPBOY           CANTERBURY_PB                  VAULT13
SPECIAL          CANTERBURY                     VAULT13

We can connect directly to pipboy PDB and check contents of canterbury_pb table. To do so we need to use service name that is registered with a listener from our CDB. No entries in tnsnames.ora is created automatically when PDB is created from command line – it’s good to remember to add appropriate lines with configuration right away after creation.

connect machete/dominic@pipboy
select *
  from canterbury_pb;
        ID NEWNAME
---------- ----------------------------------------
       123 123

I’ll add two more rows and commit my changes. Than I’m going to switch to another PDB.

insert into canterbury_pb values (234, 'invisible');
insert into canterbury_pb values (345, 'rows');
commit;
select *
  from canterbury_pb;
        ID NEWNAME
---------- ----------------------------------------
       123 123
       234 invisible
       345 rows
connect machete/dominic@special
create table canterbury_pb
(
  id number(10),
  newname varchar2(40)
);
insert into canterbury_pb values (123, 'new table is right here');
commit;
select *
  from canterbury;
        ID NEWNAME
---------- ----------------------------------------
       123 123
select *
  from canterbury_pb;
        ID NEWNAME
---------- ----------------------------------------
       123 new table is right here
exit

I could create new table canterbury_pb in special PDB as namespaces are not shared between PDBs. Table in one PDB can have the same name like any other table in different PDB. CDB_* views shows information across PDBs but cannot access data inside those tables. To emphasize it: root container have only access to the metadata but the data and real definition of user table is inside PDB. Only common objects that are shared as linked objects shows the same contents in all PDB. User is not able to create a table (even in root container) and use it in all PDBs to make DMLs on it. It’s not possible with common user, it’s not possible with local user.

For local users PDB means the same like standalone or non-CDB database – difference for administrator is that internally we can login only to root container and then switch session container. To connect directly to PDB we need a service registered in a listener.

PDB were created to be as much as possible similar in use to standard database (without enabled multitenant option).

Privileges granted commonly and locally

To grant a privilege commonly means to give someone permission to do some activity in all PDBs, including root container. First of all I’ll drop users which are not needed any more.

sql
drop user c##arma;
ORA-65048: error encountered when processing the current DDL statement in pluggable
database SPECIAL
ORA-01922: CASCADE must be specified to drop 'C##ARMA'

Error is related with existing table owned by c##arma common user in special PDB. Cascade option is required to successfully execute drop command.

drop user c##arma cascade;
drop user "c##ArthurMaxson" cascade;

My scenario is to create a new common user who could be a global junior administrator. His responsibility will be creating local users and giving them some minimum privileges.

create user c##jadm identified by secret123;
grant create session, create table, create view,
      create sequence, create trigger, create procedure,
      create synonym
   to c##jadm
 with admin option container = all;
grant create user, alter user
   to c##jadm container = all;

Next command will not complete with success because this is an attempt of giving a common privilege (related only with PDBs) using container = current. This is the default value for grant command and even when in root container one have to remember, that this is the default for privileges.

grant set containter to c##jadm;
ORA-00990: missing or invalid privilege

Add container clause and watch the magic.

grant set container to c##jadm container = all;
Grant succeeded.

Even though the error message is right it could be a bit more precise, especially when dealing with new functionality like multitenant option.

Ok, it’s time to check how this user operate and what are his privileges in each PDB. Start with script that lists c##jadm common user privileges:

start mt/cprivilege121
Key for column names
C - common privilege?
AO - admin option granted?

Add %% if needed for grantee name. Search is case insensitive.

Input grantee name: %jadm%
Privileges and roles granted to LIKE %jadm%

Container name     Grantee                Privilege                                AO C
------------------ ---------------------- ---------------------------------------- -- -
CDB$ROOT           C##JADM                ALTER USER                               N  Y
CDB$ROOT           C##JADM                CREATE PROCEDURE                         Y  Y
CDB$ROOT           C##JADM                CREATE SEQUENCE                          Y  Y
CDB$ROOT           C##JADM                CREATE SESSION                           Y  Y
CDB$ROOT           C##JADM                CREATE SYNONYM                           Y  Y
CDB$ROOT           C##JADM                CREATE TABLE                             Y  Y
CDB$ROOT           C##JADM                CREATE TRIGGER                           Y  Y
CDB$ROOT           C##JADM                CREATE USER                              N  Y
CDB$ROOT           C##JADM                CREATE VIEW                              Y  Y
CDB$ROOT           C##JADM                SET CONTAINER                            N  Y
(...)
PIPBOY             C##JADM                ALTER USER                               N  Y
PIPBOY             C##JADM                CREATE PROCEDURE                         Y  Y
PIPBOY             C##JADM                CREATE SEQUENCE                          Y  Y
PIPBOY             C##JADM                CREATE SESSION                           Y  Y
PIPBOY             C##JADM                CREATE SYNONYM                           Y  Y
PIPBOY             C##JADM                CREATE TABLE                             Y  Y
PIPBOY             C##JADM                CREATE TRIGGER                           Y  Y
PIPBOY             C##JADM                CREATE USER                              N  Y
PIPBOY             C##JADM                CREATE VIEW                              Y  Y
PIPBOY             C##JADM                SET CONTAINER                            N  Y
SPECIAL            C##JADM                ALTER USER                               N  Y
SPECIAL            C##JADM                CREATE PROCEDURE                         Y  Y
SPECIAL            C##JADM                CREATE SEQUENCE                          Y  Y
SPECIAL            C##JADM                CREATE SESSION                           Y  Y
SPECIAL            C##JADM                CREATE SYNONYM                           Y  Y
SPECIAL            C##JADM                CREATE TABLE                             Y  Y
SPECIAL            C##JADM                CREATE TRIGGER                           Y  Y
SPECIAL            C##JADM                CREATE USER                              N  Y
SPECIAL            C##JADM                CREATE VIEW                              Y  Y
SPECIAL            C##JADM                SET CONTAINER                            N  Y

Now, still using common user sys, I’m going to switch to pipboy and grant two more privileges but only locally. It’s not possible to use other values than all or current in container clause.

alter session set container = pipboy;
grant create type, create materialized view
   to c##jadm container = current;
start mt/cprivilege121
Input grantee name: %jadm%
Privileges and roles granted to LIKE %jadm%

Container name     Grantee                Privilege                                AO C
------------------ ---------------------- ---------------------------------------- -- -
PIPBOY             C##JADM                ALTER USER                               N  Y
PIPBOY             C##JADM                CREATE MATERIALIZED VIEW                 N  N
PIPBOY             C##JADM                CREATE PROCEDURE                         Y  Y
PIPBOY             C##JADM                CREATE SEQUENCE                          Y  Y
PIPBOY             C##JADM                CREATE SESSION                           Y  Y
PIPBOY             C##JADM                CREATE SYNONYM                           Y  Y
PIPBOY             C##JADM                CREATE TABLE                             Y  Y
PIPBOY             C##JADM                CREATE TRIGGER                           Y  Y
PIPBOY             C##JADM                CREATE TYPE                              N  N
PIPBOY             C##JADM                CREATE USER                              N  Y
PIPBOY             C##JADM                CREATE VIEW                              Y  Y
PIPBOY             C##JADM                SET CONTAINER                            N  Y

From PDB session cdb_sys_privs behave just like dba_sys_privs but it is restricted to local informations only. v$container view exists on PDB level too thus we can use joins between those views and still get proper result.

In the listing we see create type and create materialized view privileges granted locally (common column with N value).

It’s time to connect as c##jadm and use his privileges to create another local user. Grant him basic privileges and allow to start his work.

connect c##jadm/secret123@pipboy
create user drlesko identified by drlesko;
grant create table, create view, create session
   to drlesko;
alter user drlesko quota 25m on vault13;
conn / as sysdba
start mt/cprivilege121
Input grantee name: %lesko%
Privileges and roles granted to LIKE %lesko%

Container name     Grantee                Privilege                                AO C
------------------ ---------------------- ---------------------------------------- -- -
PIPBOY             DRLESKO                CREATE SESSION                           N  N
PIPBOY             DRLESKO                CREATE TABLE                             N  N
PIPBOY             DRLESKO                CREATE VIEW                              N  N

I’ve used granted commonly privilege create user from c##jadm user to create drlesko. drlesko has been granted ability to create tables and views. In addition create session allows him to connect to the database (if he finally find out how to use sql*plus).

To revoke granted privileges we need to use the same container clauses like we did when granting the privileges. First attempt to revoke create table commonly granted privilege from c##jadm:

revoke create table from c##jadm;
ORA-65092: system privilege granted with a different scope to 'C##JADM'

Scope is different – when you go back to the listing with privileges you can see column C (for common grant) in line with create table privilege. We need to change default container clause (which is set to current) and use all value:

revoke create table from c##jadm container = all;
Revoke succeeded.

To revoke locally granted privilege we need to be in the same container where the grant command has been executed. It’s not possible from root container. Even with container = all we won’t succeed – we need to be more precise. Both commands presented below end up with the same ora error.

revoke create type from c##jadm;
revoke create type from c##jadm container = all;
ORA-01952: system privileges not granted to 'C##JADM'

Container clause is not necessary as the default value solves our situation here – I’m adding it to highlight value of this parameter.

alter session set container = pipboy;
revoke create type from c##jadm container = current;
Session altered.
Revoke succeeded.

Just the same way like I’ve granted locally create table, session and view to drlesko you could do the same from any other local user, who has such privileges. PDBs inside behave just like any other standalone (or according to latest naming convention: non-CDB).

It’s worth to mention about one situation. You grant commonly a privilege and then add the same one locally. On the list you’ll see locally and commonly granted privilege. Revoking common on local grant has no influence on the other one. For example:

alter session set container = pipboy;
grant create table to c##jadm;
start mt/cprivilege121
Container name     Grantee                  Privilege                                AO C
------------------ ------------------------ ---------------------------------------- -- -
(...)
PIPBOY             C##JADM                  CREATE TABLE                             Y  Y
PIPBOY             C##JADM                  CREATE TABLE                             N  N

Which privilege takes precedence?

connect c##jadm/secret123@pipboy
create user arthurmaxson identified by secret123;
grant create table to arthurmaxson;
User created.
Grant succeeded.

I’ll revoke common grant with admin option and create one without this clause.

connect / as sysdba
revoke create table from c##jadm container = all;
grant create table to c##jadm container = all;
start mt/cprivilege121
Container name     Grantee                  Privilege                                AO C
------------------ ------------------------ ---------------------------------------- -- -
(...)
PIPBOY             C##JADM                  CREATE TABLE                             N  N
PIPBOY             C##JADM                  CREATE TABLE                             N  Y

I’ll revoke previously granted privilege to arthurmaxson using system user and add admin option to local privilege for c##jadm user and try again grant this privilege.

connect system/manager@pipboy
revoke create table from arthurmaxson;
grant create table to c##jadm with admin option;
connect c##jadm/secret123@pipboy
grant create table to arthurmaxson;
start mt/cprivilege121
(...)
Grant succeeded.

Looks like common and local privileges works additive – only in one place admin option granted give the ability (on PDB level) to grant create table privilege.

Roles granted commonly and locally

To make our lives easier Oracle Database offers roles. With multitenant all gone wild and we’ve got new topic to redefine role usage. I’m going to start with new common role and one privilege inside – select any table (*which I’ll add later).

connect / as sysdba
create role c##reader container = all;

We don’t have to use container clause – as a default in root container it is automatically set to value all (which is different than for privilege command). Check status of this role with crole121.sql script.

start mt/crole121
Key for column names
C - common role?
AO - admin option granted?
D - delegate option
DR - default role
OM - Oracle maintained

Add %% if needed for owner name. Search is case insensitive.

Input role name: %reader%
Roles with name LIKE %reader%

Container name   Role name            Pass. req. Authent. type C OM
---------------- -------------------- ---------- ------------- - --
CDB$ROOT         C##READER            NO         NONE          Y N
MOLERAT_CLONE    C##READER            NO         NONE          Y N
MOLERAT_CLONE37  C##READER            NO         NONE          Y N
PIPBOY           C##READER            NO         NONE          Y N
SPECIAL          C##READER            NO         NONE          Y N

Roles with name LIKE %reader% granted to other users and roles

Container name   Grantee                  Granted role             AO D DR C
---------------- ------------------------ ------------------------ -- - -- -
CDB$ROOT         SYS                      C##READER                Y  N Y  Y
MOLERAT_CLONE    SYS                      C##READER                Y  N Y  Y
MOLERAT_CLONE37  SYS                      C##READER                Y  N Y  Y
PIPBOY           SYS                      C##READER                Y  N Y  Y
SPECIAL          SYS                      C##READER                Y  N Y  Y

Privileges granted to role LIKE %reader%

no rows selected

Role exists in each container. Role is granted to sys user in each container.

One of the rules that is mandatory is to use c## or C## prefix before role name to create a common one. Next restriction is related with use of role: you can grant common role to common or local user. I’ll grant c##reader to c##jadm user and give this user admin option (common to common grant). Then I’ll grant this role to machete local user in pipboy PDB with admin option (common to local user grant) and this user grant it to drlasko in pipboy PDB (local to local user grant).

grant c##reader to c##jadm with admin option container = all;
connect c##jadm/secret123@pipboy
grant c##reader to machete with admin option;
connect machete/dominic@pipboy
grant c##reader to drlesko;
connect / as sysdba
start mt/crole121
Key for column names
C - common role?
AO - admin option granted?
D - delegate option
DR - default role
OM - Oracle maintained

Add %% if needed for owner name. Search is case insensitive.

Input role name: %reader%
Roles with name LIKE %reader%

Container name   Role name            Pass. req. Authent. type C OM
---------------- -------------------- ---------- ------------- - --
CDB$ROOT         C##READER            NO         NONE          Y N
MOLERAT_CLONE    C##READER            NO         NONE          Y N
MOLERAT_CLONE37  C##READER            NO         NONE          Y N
PIPBOY           C##READER            NO         NONE          Y N
SPECIAL          C##READER            NO         NONE          Y N

Roles with name LIKE %reader% granted to other users and roles

Container name   Grantee                  Granted role             AO D DR C
---------------- ------------------------ ------------------------ -- - -- -
CDB$ROOT         C##JADM                  C##READER                Y  N Y  Y
CDB$ROOT         SYS                      C##READER                Y  N Y  Y
MOLERAT_CLONE    C##JADM                  C##READER                Y  N Y  Y
MOLERAT_CLONE    SYS                      C##READER                Y  N Y  Y
MOLERAT_CLONE37  C##JADM                  C##READER                Y  N Y  Y
MOLERAT_CLONE37  SYS                      C##READER                Y  N Y  Y
PIPBOY           C##JADM                  C##READER                Y  N Y  Y
PIPBOY           DRLESKO                  C##READER                N  N Y  N
PIPBOY           MACHETE                  C##READER                Y  N Y  N
PIPBOY           SYS                      C##READER                Y  N Y  Y
SPECIAL          C##JADM                  C##READER                Y  N Y  Y
SPECIAL          SYS                      C##READER                Y  N Y  Y

12 rows selected.

Privileges granted to role LIKE %reader%

no rows selected

Effect of those commands was aligned to the documentation – no surprises here. We’ve got local grants to machete with admin option and drlesko without admin option in pipboy PDB. Common grant to c##jadm with admin option. So far no privileges have been granted to our role. Last test require another common user to grant locally from local user to common one.

create user c##vikia identified by secret123;
grant create session to c##vikia container = all;
connect machete/dominic@pipboy
grant c##reader to c##vikia;
connect / as sysdba
start mt/crole121
Roles with name LIKE %reader% granted to other users and roles

Container name   Grantee                  Granted role             AO D DR C
---------------- ------------------------ ------------------------ -- - -- -
[... removed part of listing]
PIPBOY           C##VIKIA                 C##READER                N  N Y  N
[... removed part of listing]

Common role behave similar to what we know from earlier versions. One more layer have been added – common layer but this does not change anything for local users. Yet another role with c## prefix. I’ll add privilege to this common role.

grant select any table to c##reader container = current;
start mt/crole121
[... removed part of listing]
Privileges granted to role LIKE %read%

Container name   Grantee                  Privilege                                AO C
---------------- ------------------------ ---------------------------------------- -- -
CDB$ROOT         C##READER                SELECT ANY TABLE                         N  N

I’ll add one more local grant from pipboy container.

alter session set container = pipboy;
grant select any table to c##reader container = current;
alter session set container = cdb$root;
start mt/crole121
[... removed part of listing]
Privileges granted to role LIKE %read%

Container name   Grantee                  Privilege                                AO C
---------------- ------------------------ ---------------------------------------- -- -
CDB$ROOT         C##READER                SELECT ANY TABLE                         N  N
PIPBOY           C##READER                SELECT ANY TABLE                         N  N

Next test – I’ll try to add common privilege to current local privileges.

grant select any table to c##reader with admin option container = all;
start mt/crole121
Privileges granted to role LIKE %read%

Container name   Grantee                  Privilege                                AO C
---------------- ------------------------ ---------------------------------------- -- -
CDB$ROOT         C##READER                SELECT ANY TABLE                         Y  Y
CDB$ROOT         C##READER                SELECT ANY TABLE                         N  N
MOLERAT_CLONE    C##READER                SELECT ANY TABLE                         Y  Y
MOLERAT_CLONE37  C##READER                SELECT ANY TABLE                         Y  Y
PIPBOY           C##READER                SELECT ANY TABLE                         Y  Y
PIPBOY           C##READER                SELECT ANY TABLE                         N  N
SPECIAL          C##READER                SELECT ANY TABLE                         Y  Y

We’ve got doubled entries for containers where commonly and locally select any table privilege has been granted. I’ve added admin option just to improve visibility of output.

To revoke granted privilege I need to use the same containers which I’ve used previously.

alter session set container = pipboy;
revoke select any table from c##reader;
alter session set container = cdb$root;
start mt/crole121
Privileges granted to role LIKE %reader%

Container name   Grantee                  Privilege                                AO C
---------------- ------------------------ ---------------------------------------- -- -
CDB$ROOT         C##READER                SELECT ANY TABLE                         N  N
CDB$ROOT         C##READER                SELECT ANY TABLE                         Y  Y
MOLERAT_CLONE    C##READER                SELECT ANY TABLE                         Y  Y
MOLERAT_CLONE37  C##READER                SELECT ANY TABLE                         Y  Y
PIPBOY           C##READER                SELECT ANY TABLE                         Y  Y
SPECIAL          C##READER                SELECT ANY TABLE                         Y  Y

Privilege from pipboy PDB have been removed.

revoke select any table from c##reader container = all;
start mt/crole121
Privileges granted to role LIKE %read%

Container name   Grantee                  Privilege                                AO C
---------------- ------------------------ ---------------------------------------- -- -
CDB$ROOT         C##READER                SELECT ANY TABLE                         N  N

Common role has lost common possibility to share select any table privilege with other users and roles. One more revoke to be done:

revoke select any table from c##reader container = current;
start mt/crole121
Privileges granted to role LIKE %read%

no rows selected

The last thing we can do now is to drop this common role.

drop role c##reader;
Role dropped.

Note, that when I was creating this role I used container = all clause (for roles in root container using common user it is the default, a bit different than with users or privileges). There is no way to use this clause with drop role command – it’s not within the syntax. You just have to remember that common DDLs can be executed only from root container. Otherwise, you’ll receive an error reminding you about this rule. One more test with creating a local role from root container:

create role reader;
ORA-65096: invalid common user or role name

…and another test…

create role reader container = current;
ORA-65049: creation of local user or role is not allowed in CDB$ROOT

This only confirms what we’ve seen earlier when I’ve tried to create local user in root container.

Local roles are separated between PDBs and can be granted to common role and common roles can be granted to local roles. All commands below execute without an error:

conn / as sysdba
create role c##reader;
alter session set container=pipboy;
create role newreader;
grant c##reader to newreader;
revoke c##reader from newreader;
grant newreader to c##reader;
revoke newreader from c##reader;
drop role newreader;

This concludes chapter related with create, grant, revoke and drop common role.

Accessing container data

Oracle added new clause in alter user related with container data – only to alter user, it’s not possible to create user and give him those privileges in one command. This feature provide user ability to access CDB related data. You can check all_tables and all_views views to see if particular information is within container data scope.

select container_data, con_id, count(0)
  from cdb_views
 group by container_data, con_id
 order by container_data, con_id;
C     CON_ID   COUNT(0)
- ---------- ----------
N          1       3833
N          3       3843
N          4       3843
N          5       3844
N          6       3844
Y          1       2665
Y          3       2665
Y          4       2665
Y          5       2665
Y          6       2665
col owner for a30 hea 'Owner'
col view_name for a30 hea 'View name'
col con_id for 999999 hea 'Con ID'
select owner, view_name, con_id
  from cdb_views
 where container_data = 'Y'
   and view_name in ('V_$SESSION','V_$PROCESS')
 order by view_name, con_id;
Owner                          View name                       Con ID
------------------------------ ------------------------------ -------
SYS                            V_$PROCESS                           1
SYS                            V_$PROCESS                           3
SYS                            V_$PROCESS                           4
SYS                            V_$PROCESS                           5
SYS                            V_$PROCESS                           6
SYS                            V_$SESSION                           1
SYS                            V_$SESSION                           3
SYS                            V_$SESSION                           4
SYS                            V_$SESSION                           5
SYS                            V_$SESSION                           6

Notice, that in cdb_views we’ve got container_data column as varchar2(1) with values Y or N. In cdb_tables you can find the same column name but with other length: varchar2(3) and with values NO and probably YES – didn’t see that one coming. When you take a look on cdb_container_data view you can find default_attr and all_containers columns – first is char(1) and the another one varchar2(1) – both with values Y or N. I found even more inconsistency like those two in 12c release (for example, username column in v$pwfile_users or v$session_longops is varchar2(30) and in cdb_objects owner is varchar2(128) – just query cdb_tab_columns by username or owner and you’ll see what I’ve got on mind with columns inconsistency problem).

Ok, back to container data – as a default common user has access to default set of container data objects. This means, root container and CDB structures but without PDBs information. Off course, you can’t select anything until at least local grant in root container.

Altering user container data privileges on v$session in all containers gives access to all v$session data from containers 0 (CDB level), 1 (root level) and PDBs but it will require connection with root container. In the opposite, we can commonly grant select on v$session but this would require switching between PDBs to see local data – for example, connected sessions to pipboy PDB force c##jadm to switch to PDB pipboy to see them in v$session.

I still have my c##jadm junior administrator. I’ll give him grant to select on v_$session (I’m using sys connection).

grant select on v_$session to c##jadm container = all;
start mt/cobjectpriv121
Key for column names
C - common privilege?
G - grant option added?
H - hierarchy?

Add %% if needed for grantee name. Search is case insensitive.

Input grantee name: %jadm%
Privileges granted on objects to LIKE %jadm%

Container name  Grantee        Owner          Object name        Privilege    G H C Ob.type
--------------- -------------- -------------- ------------------ ------------ - - - -------
CDB$ROOT        C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
MOLERAT_CLONE   C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
MOLERAT_CLONE37 C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
PIPBOY          C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
SPECIAL         C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW

Right now I can select contents of v$session view from within each container. I’ll check number of sessions from root container.

connect c##jadm/secret123@tcdb
col username for a30 hea 'Username'
col program for a35 hea 'Program'
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1

The same result of this query is returned from sys user in root container. I’ll create new putty session with connection to pipboy PDB as system user (if you want to know more about containers and connecting to them review articles about CDB and PDBs available on my site or check in Administrator’s Guide) and connect as c##jadm user again to check on v$session with current session.

connect c##jadm/secret123@tcdb
col username for a30 hea 'Username'
col program for a35 hea 'Program'
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1

Result is just the same like earlier. We’ve got only local (root) access to v$session (just like described in docs as a default) because we didn’t alter container data yet. I’ll change it to see all containers and check again. Session as system user to pipboy still remains active.

connect / as sysdba
alter user c##jadm
  set container_data = all container = current;
User altered.

Don’t forget about container = current clause – without it you’ll receive an error presented below.

alter user c##jadm
  set container_data = all;
ORA-65048: error encountered when processing the current DDL statement in pluggable
database PIPBOY
ORA-65056: CONTAINER_DATA attribute is not used in a pluggable database.

There is a really nice description of this error and container_data column and clause usage (see cause and action section of error ORA-65056) – I think this description is better than alter user command and reference for dba_container_data view altogether from other docs.

Ok, we’ve got all PDBs accessible as c##jadm – I’ll check again sessions.

connect c##jadm/secret123@tcdb
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1
         3 SYSTEM                         sqlplus@odone (TNS V1-V3)                    1

Now we can see system session. I’ll change this connection to special PDB which has different container id and execute the query again:

    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1
         4 SYSTEM                         sqlplus@odone (TNS V1-V3)                    1

Notice, that we see this data in root container because we’ve altered container data privileges not because we’ve got commonly granted select on v$session.

Now, I’ll cut available containers list only to pipboy (or I would like to…):

connect / as sysdba
alter user c##jadm
  set container_data = (pipboy) container = current;
ORA-65057: CONTAINER_DATA attribute must always include the current container

This means that you always have to add root container to the list when specifying a list of containers.

alter user c##jadm
  set container_data = (cdb$root, pipboy) container = current;
User altered.

To summarize so far: always use container = current and don’t forget about cdb$root in the list when altering user container_data options.

I’ll check again session list from c##jadm user and then change system session and connect to pipboy again (I’m connected to special PDB right now).

connect c##jadm/secret123@tcdb
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1

Only one session – just like configured. I’m changing system session to pipboy and executing query again.

    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1
         3 SYSTEM                         sqlplus@odone (TNS V1-V3)                    1

We can see new session on the list. All is working properly. As we’ve got select on v$session in each PDB I’ll try to change container and see from within if I can see session in special PDB. I’m changing system connection from pipboy to special PDB and adjusting my session container for c##jadm:

alter session set container = special;
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID Username                       Program                               COUNT(0)
---------- ------------------------------ ----------------------------------- ----------
         4 C##JADM                        sqlplus@odone (TNS V1-V3)                    1
         4 SYSTEM                         sqlplus@odone (TNS V1-V3)                    1

From special PBD all is visible locally.

I’ll add another privilege for c##jadm user and try to restrict access to only one of those views.

connect / as sysdba
grant select on v_$process
   to c##jadm container = all;
start mt/cobjectpriv121
Input grantee name: %jadm%
Privileges granted on objects to LIKE %jadm%

Container name  Grantee        Owner          Object name        Privilege    G H C Ob.type
--------------- -------------- -------------- ------------------ ------------ - - - -------
CDB$ROOT        C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
CDB$ROOT        C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
MOLERAT_CLONE   C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
MOLERAT_CLONE   C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
MOLERAT_CLONE37 C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
MOLERAT_CLONE37 C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
PIPBOY          C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
PIPBOY          C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
SPECIAL         C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
SPECIAL         C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW

And adjust container data privileges using alter user command.

alter user c##jadm
  set container_data = all for v_$session container = current;
start mt/cdata121
Key for column names
D - default attribute
AC - all containers

Add %% if needed for user name. Search is case insensitive.

Input user name: %jadm%
Container data granted to user LIKE %jadm%

Container name   User name            D Object owner         Object name                  AC
---------------- -------------------- - -------------------- ---------------------------- --
CDB$ROOT         C##JADM              Y                                                   N
PIPBOY           C##JADM              Y                                                   N
                 C##JADM              N SYS                  V_$SESSION                   Y

With clause for we can chose specific object which contents user is going to see. We can see all sessions from root container according to granted access (all containers Y in AC column):

connect c##jadm/secret123@tcdb
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID User name            Program                               COUNT(0)
---------- -------------------- ----------------------------------- ----------
         1 C##JADM              sqlplus@odone (TNS V1-V3)                    1
(...)
         4 SYSTEM               sqlplus@odone (TNS V1-V3)                    1

But we’re going to see v$process view contents from root container only for root and pipboy PDB (con_id = 3):

select con_id, username, program, count(0)
  from v$process
 where background is null
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID User name            Program                               COUNT(0)
---------- -------------------- ----------------------------------- ----------
(...)
         1 grid                 oracle@odone                                 1
         3 grid                 oracle@odone                                 1

When I’ve terminated system connection to pipboy in second session and switched to special (con_id = 4) only from sys I could see this process. It isn’t visible for c##jadm.

select con_id, username, program, count(0)
  from v$process
 where background is null
 group by con_id, username, program
 order by con_id, username, program;
    CON_ID User name            Program                               COUNT(0)
---------- -------------------- ----------------------------------- ----------
(...)
         1 grid                 oracle@odone                                 1

Now it’s time to take back what we’ve given to c##jadm common user. Just to remind about current state of granted privileges on objects:

connect / as sysdba
start mt/cdata121
Key for column names
D - default attribute
AC - all containers

Add %% if needed for user name. Search is case insensitive.

Input user name: %jadm%
Container data granted to user LIKE %jadm%

Container name   User name            D Object owner         Object name                  AC
---------------- -------------------- - -------------------- ---------------------------- --
CDB$ROOT         C##JADM              Y                                                   N
PIPBOY           C##JADM              Y                                                   N
                 C##JADM              N SYS                  V_$SESSION                   Y
start mt/cobjectpriv121
Key for column names
C - common privilege?
G - grant option added?
H - hierarchy?

Add %% if needed for grantee name. Search is case insensitive.

Input grantee name: %jadm%
Privileges granted on objects to LIKE %jadm%

Container name  Grantee        Owner          Object name        Privilege    G H C Ob.type
--------------- -------------- -------------- ------------------ ------------ - - - -------
CDB$ROOT        C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
CDB$ROOT        C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
MOLERAT_CLONE   C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
MOLERAT_CLONE   C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
MOLERAT_CLONE37 C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
MOLERAT_CLONE37 C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
PIPBOY          C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
PIPBOY          C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW
SPECIAL         C##JADM        SYS            V_$PROCESS         SELECT       N N Y VIEW
SPECIAL         C##JADM        SYS            V_$SESSION         SELECT       N N Y VIEW

I’ll try to remove access to v$session from c##jadm. First, I’ll try to remove only one grant from special PDB.

 alter user c##jadm
remove container_data = (special) for v_$session container = current;
ORA-65062: CONTAINER_DATA attribute is set to ALL

Ok, I’ll try to remove with all value for container_data clause.

 alter user c##jadm
remove container_data = all for v_$session container = current;
ORA-00922: missing or invalid option

Ah, yes, there is no syntax available to remove with all value for container_data (see alter user command in SQL Language Reference).

Maybe I’ll be able to reset this user access and remove this grant overwriting it with default.

 alter user c##jadm
   set container_data = default for v_$session container = current;
start mt/cdata121
Input user name: %jadm%
Container data granted to user LIKE %jadm%

Container name   User name            D Object owner         Object name                  AC
---------------- -------------------- - -------------------- ---------------------------- --
CDB$ROOT         C##JADM              Y                                                   N
PIPBOY           C##JADM              Y                                                   N

Ok, I don’t know, why we have remove in this command available – maybe we can remove single container from scope of this user.

 alter user c##jadm
remove container_data = (pipboy) container = current;
Input user name: %jadm%
Container data granted to user LIKE %jadm%

Container name   User name            D Object owner         Object name                  AC
---------------- -------------------- - -------------------- ---------------------------- --
CDB$ROOT         C##JADM              Y                                                   N

Probably this is it. Now, how to remove this one entry too?

 alter user c##jadm
remove container_data = (cdb$root) container = current;
ORA-65057: CONTAINER_DATA attribute must always include the current container

Maybe reset to default would help just like with object privilege.

alter user c##jadm
  set container_data = default container = current;
start mt/cdata121
Input user name: %jadm%
Container data granted to user LIKE %jadm%

no rows selected

And cleaning up the c##jadm user from grants:

revoke select on v_$process from c##jadm container = all;
revoke select on v_$session from c##jadm container = all;

Earlier I’ve used common grants with select on both views – just to underline once more that common grants (or local) on objects are separated from container data available via alter user command. I’ll add only local grant to select v$session view and all container data to c##jadm privileges list. A session as system user to pipboy PDB have been established in a separate window (container id for pipboy PDB is 3 and for special it is 4).

connect / as sysdba
grant select on v_$session to c##jadm container = current;
alter user c##jadm
  set container_data = all for v_$session container = current;
start mt/cdata121
Input user name: %jadm%
Container data granted to user LIKE %jadm%

Container name   User name            D Object owner         Object name                  AC
---------------- -------------------- - -------------------- ---------------------------- --
                 C##JADM              N SYS                  V_$SESSION                   Y
start mt/cobjectpriv121
Input grantee name: %jadm%
Privileges granted on objects to LIKE %jadm%

Container name  Grantee        Owner          Object name        Privilege    G H C Ob.type
--------------- -------------- -------------- ------------------ ------------ - - - -------
CDB$ROOT        C##JADM        SYS            V_$SESSION         SELECT       N N N VIEW
connect c##jadm/secret123@tcdb
col username for a30 hea 'Username'
col program for a35 hea 'Program'
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
 Con ID Username                       Program                               COUNT(0)
------- ------------------------------ ----------------------------------- ----------
      1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1
      4 SYSTEM                         sqlplus@odone (TNS V1-V3)                    1

Switching system session from special to pipboy in additional window and querying again about the session list from main window.

col username for a30 hea 'Username'
col program for a35 hea 'Program'
select con_id, username, program, count(0)
  from v$session
 where type != 'BACKGROUND'
 group by con_id, username, program
 order by con_id, username, program;
 Con ID Username                       Program                               COUNT(0)
------- ------------------------------ ----------------------------------- ----------
      1 C##JADM                        sqlplus@odone (TNS V1-V3)                    1
(...)
      3 SYSTEM                         sqlplus@odone (TNS V1-V3)                    1

Cleaning up again.

alter user c##jadm
  set container_data = default for v_$session container = current;
revoke select on v_$session from c##jadm;

Finally, all privileges have been set to defaults. This means that c##jadm has standard access to container data: all root container views/tables which are granted for him to see their contents.

Conclusion

Container database is a great way to improve hardware resource usage – there is no doubt about it. One instance versus many is always better when thinking about of memory or space for system metadata.

Problems comes up when PDBs are far from being similar – different role names, different tablespaces, different security approach for applications that use those PDBs. You can see many simple commands that have failed (all according to implemented and documented features, off course, but not exactly convenient in use). Managing security is a heavy bargain when you need to manage common users across all PDBs and, what is terrifying, with one command drop user you can lose days of configuration adjusted for each PDB. Only restore of the root container will bring up this user (or SQL scripts which you prepare somewhere in a flat file away from the database – after all – expdp/impdp does not cooperate with root container too well and we cannot extract common users scripts from PDB too, like presented above).

Common users are impractical as schema owners – they should not have any objects at all – remap schema is not possible (or I need more suggestive hint from uncle google). Moreover, name usage in different parts of the database question sense of using them at all (# is just another special character and require special treatment).

You need to “use a force” to find a common sense within this new way of managing users across containers. It’s very cumbersome in use, many commands differs in container clause default value (privileges vs roles and users with theirs common friends) – the default value is strongly related with your current session and context of use. After spending a week with intensive testing I’m still far from getting used to it or be proficient with this feature. I strongly disagree that one CDB administrator replace couple administrators from many databases and leverage costs related with the number of people – I feel just the opposite – you need one new specialist who will explain everyone the new idea, tutor current administrators, create common users (probably administrators) and maintain order within container data privileges.

There is a hope that with second release all container clauses will be equalized, one default for all commands on root level and one default on PDB level. Improve container data and maybe extend to give access not only from root container but from PDB site too (to separate visibility of root sessions from many PDBs sessions). There are those columns length problem – this should be equalized too. A bit work and improve in documentation and this could be a great feature – unfortunately – before those tests and relying only on documentation I couldn’t get the idea behind the feature.

Thanks for reading this topic up to the last word. This was quite a trip.

Patrycjusz Konkol has written 5 articles

Oracle Database Administrator with around 7+ years of experience in Oracle Databases. From beginning of professional career worked as SQL and PL/SQL developer with use of Oracle Forms&Reports 6i, C# programmer and finally Oracle DBA with wide area of responsibilities in IBM Power environment with AIX 6 and 7. Right now works for Atos - international information technology services company as an Oracle Database Administrator.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>