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 |
| 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.
