CONTAINER_DATA for Common Users

Hello Everyone (if anyone reading this 🙂 ),

it has been a long year and I am glad it is over. As always, we hope the for “a time of period” like new year, birthday etc etc but hope is the only thing that we can hold on. I hope whole world get rid of this illness called corona, racism, wars (and again etc etc, list goes on)… Wish everyone a healthy, peaceful year.

In my personal blog, I mentioned vary of things and I realized that I didn’t mention about container database structure at all. Main reason for this is I was lazy on multi-tenant architecture and didn’t work on it, yeah, pretty much this was the reason 🙂

I wanted to start with common user’s container_data attribute (or property I am not sure). As you know, in multi-tenant architecture there two types of users COMMON and LOCAL. Common users are defined in the CDB (not in a pluggable database) and they are known by every current and future pdb. Local users are defined in a pluggable database and can access to only pluggable database they are defined. By saying “defined”, I mean “created”. So, local users don’t aware of other pluggable databases or container database (cdb) but common users are aware that they are in a CDB and can connect to PDBs on that CDB if they have privileges.

By default, newly created common users doesn’t see whole data in container data views or tables. what is “container data views”? As you noticed (even if you use non-cdb database 12c and above) there are some new columns on data dictionary and v$ views like CON_ID. in a multi-tenant database when a privileged user like SYS queried V$SESSION, user can see whole sessions in all containers (cdb$root and pdbs). Let’s see:

I will be running those queries on a 21.3 version. I have 3 PDBs on my CDB:

these are just dummy PDBs that I use for testing purpose on VM. Now Lets connect as SYS and query some views:

I connected as SYS and I am in the cdb$root container. I queried V$SESSION and V$DATAFILE views by grouping con_id column. I am able to see sessions on different PDBs, in my example, con_id 3 and 5 has 1 session each. V$DATAFILE query shows count of all data files per PDBs.

Now, let’s create a common user and give necessary privileges and query with that user:

I created C##MY_ADMIN user and grant DBA for all containers. So, this user can connect to any pdb and query any data dictionary view. for example, c##my_admin can connect to pdb1, pdb2 etc and query v$session in all of them and is able to see whole session for current pdb that user in it but problem is when c##my_admin connected to root (cdb$root), he/she won’t be able to see whole sessions in CDB (including PDB sessions). just the ones in cdb$root:

as you see, only sessions in con_id 1 which is cdb$root are listed. By the way, con_id 0 is CDB itself and only background processes are in there and they are listed in v$session all the time.

again, v$datafile lists only files in current container which is cdb$root. Please consider that c##my_admin is a DBA in CDB so it can connect to any PDB and query their data. it has necessary privileges for that. It has just don’t have “privilege” to query PDBs’ data in the CDB$ROOT container.

That is what CONTAINER_DATA does. it allows a common user to query some or all pdbs data in cdb$root. you must add necessary pdbs to common user’s container_data property:

this alter must be done by a privileged user in cdb$root container. Here, I set container_data of c##my_admin user as PDB1, PDB2 (con_ids are 3 and 4) and CDB$ROOT.

there are few important keys here:

1. this command must be run in cdb$root.
2. CDB$ROOT must be in container_data list because it is the root and common user will query data in it so, it must be able to see it
3. CONTAINER=CURRENT is mandatory because by default it is ALL and this command can be run only in cdb$root which is “current”

so, c##my_admin will be able to see whole sessions in v$session for cdb$root, pdb1 and pdb2 (not PROXY_PDB which its con_id is 5). Let’s see:

so, c##my_admin is able to query other pdbs data in cdb$root. There is no session in pdb2 by the way, that is why no session for con_id 4 in v$session.

instead of giving all container data view access you can specified necessary view as this:

by setting container_data to default it will reset everything. now, I just add container_data info just for V_$session view (which is based view for v$session, you can not grant on v$session) and query v$session and datafile again with c##my_admin:

here it is. we can see session rows for other pdbs in v$session but not for v$datafile.

if you want to set all pdbs at once:

you can use ALL clause for pdb list. remember when you use SET as “ALTER USER … SET CONTAINER_DATA” it will overwrite previous container_data property. if you want to add a new PDB into container_data list then you can use ADD instead of SET:

You can query who has access to what using CDB_CONTAINER_DATA data dictionary view.

I hope this will helpful, any comments is appreciated.

Thanks for reading, wish you healthy days.