\r\nexit\r\n<\/pre>\nI 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.<\/p>\n
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.<\/p>\n
PDB were created to be as much as possible similar in use to standard database (without enabled multitenant option).<\/p>\n
Privileges granted commonly and locally<\/h2>\n
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.<\/p>\n
\r\nsql\r\n<\/pre>\n\r\ndrop user c##arma;\r\n<\/pre>\n\r\nORA-65048: error encountered when processing the current DDL statement in pluggable\r\ndatabase SPECIAL\r\nORA-01922: CASCADE must be specified to drop 'C##ARMA'\r\n<\/pre>\nError is related with existing table owned by c##arma common user in special PDB. Cascade option is required to successfully execute drop command.<\/p>\n
\r\ndrop user c##arma cascade;\r\ndrop user "c##ArthurMaxson" cascade;\r\n<\/pre>\nMy 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.<\/p>\n
\r\ncreate user c##jadm identified by secret123;\r\ngrant create session, create table, create view,\r\n create sequence, create trigger, create procedure,\r\n create synonym\r\n to c##jadm\r\n with admin option container = all;\r\ngrant create user, alter user\r\n to c##jadm container = all;\r\n<\/pre>\nNext 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.<\/p>\n
\r\ngrant set containter to c##jadm;\r\n<\/pre>\n\r\nORA-00990: missing or invalid privilege\r\n<\/pre>\nAdd container clause and watch the magic.<\/p>\n
\r\ngrant set container to c##jadm container = all;\r\n<\/pre>\n\r\nGrant succeeded.\r\n<\/pre>\nEven though the error message is right it could be a bit more precise, especially when dealing with new functionality like multitenant option.<\/p>\n
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:<\/p>\n
\r\nstart mt\/cprivilege121\r\n<\/pre>\n\r\nKey for column names\r\nC - common privilege?\r\nAO - admin option granted?\r\n\r\nAdd %% if needed for grantee name. Search is case insensitive.\r\n\r\nInput grantee name: %jadm%\r\nPrivileges and roles granted to LIKE %jadm%\r\n\r\nContainer name Grantee Privilege AO C\r\n------------------ ---------------------- ---------------------------------------- -- -\r\nCDB$ROOT C##JADM ALTER USER N Y\r\nCDB$ROOT C##JADM CREATE PROCEDURE Y Y\r\nCDB$ROOT C##JADM CREATE SEQUENCE Y Y\r\nCDB$ROOT C##JADM CREATE SESSION Y Y\r\nCDB$ROOT C##JADM CREATE SYNONYM Y Y\r\nCDB$ROOT C##JADM CREATE TABLE Y Y\r\nCDB$ROOT C##JADM CREATE TRIGGER Y Y\r\nCDB$ROOT C##JADM CREATE USER N Y\r\nCDB$ROOT C##JADM CREATE VIEW Y Y\r\nCDB$ROOT C##JADM SET CONTAINER N Y\r\n(...)\r\nPIPBOY C##JADM ALTER USER N Y\r\nPIPBOY C##JADM CREATE PROCEDURE Y Y\r\nPIPBOY C##JADM CREATE SEQUENCE Y Y\r\nPIPBOY C##JADM CREATE SESSION Y Y\r\nPIPBOY C##JADM CREATE SYNONYM Y Y\r\nPIPBOY C##JADM CREATE TABLE Y Y\r\nPIPBOY C##JADM CREATE TRIGGER Y Y\r\nPIPBOY C##JADM CREATE USER N Y\r\nPIPBOY C##JADM CREATE VIEW Y Y\r\nPIPBOY C##JADM SET CONTAINER N Y\r\nSPECIAL C##JADM ALTER USER N Y\r\nSPECIAL C##JADM CREATE PROCEDURE Y Y\r\nSPECIAL C##JADM CREATE SEQUENCE Y Y\r\nSPECIAL C##JADM CREATE SESSION Y Y\r\nSPECIAL C##JADM CREATE SYNONYM Y Y\r\nSPECIAL C##JADM CREATE TABLE Y Y\r\nSPECIAL C##JADM CREATE TRIGGER Y Y\r\nSPECIAL C##JADM CREATE USER N Y\r\nSPECIAL C##JADM CREATE VIEW Y Y\r\nSPECIAL C##JADM SET CONTAINER N Y\r\n<\/pre>\nNow, 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.<\/p>\n
\r\nalter session set container = pipboy;\r\ngrant create type, create materialized view\r\n to c##jadm container = current;\r\nstart mt\/cprivilege121\r\n<\/pre>\n\r\nInput grantee name: %jadm%\r\nPrivileges and roles granted to LIKE %jadm%\r\n\r\nContainer name Grantee Privilege AO C\r\n------------------ ---------------------- ---------------------------------------- -- -\r\nPIPBOY C##JADM ALTER USER N Y\r\nPIPBOY C##JADM CREATE MATERIALIZED VIEW N N\r\nPIPBOY C##JADM CREATE PROCEDURE Y Y\r\nPIPBOY C##JADM CREATE SEQUENCE Y Y\r\nPIPBOY C##JADM CREATE SESSION Y Y\r\nPIPBOY C##JADM CREATE SYNONYM Y Y\r\nPIPBOY C##JADM CREATE TABLE Y Y\r\nPIPBOY C##JADM CREATE TRIGGER Y Y\r\nPIPBOY C##JADM CREATE TYPE N N\r\nPIPBOY C##JADM CREATE USER N Y\r\nPIPBOY C##JADM CREATE VIEW Y Y\r\nPIPBOY C##JADM SET CONTAINER N Y\r\n<\/pre>\nFrom 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.<\/p>\n
In the listing we see create type and create materialized view privileges granted locally (common column with N value).<\/p>\n
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.<\/p>\n
\r\nconnect c##jadm\/secret123@pipboy\r\ncreate user drlesko identified by drlesko;\r\ngrant create table, create view, create session\r\n to drlesko;\r\nalter user drlesko quota 25m on vault13;\r\nconn \/ as sysdba\r\nstart mt\/cprivilege121\r\n<\/pre>\n\r\nInput grantee name: %lesko%\r\nPrivileges and roles granted to LIKE %lesko%\r\n\r\nContainer name Grantee Privilege AO C\r\n------------------ ---------------------- ---------------------------------------- -- -\r\nPIPBOY DRLESKO CREATE SESSION N N\r\nPIPBOY DRLESKO CREATE TABLE N N\r\nPIPBOY DRLESKO CREATE VIEW N N\r\n<\/pre>\nI’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).<\/p>\n
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:<\/p>\n
\r\nrevoke create table from c##jadm;\r\n<\/pre>\n\r\nORA-65092: system privilege granted with a different scope to 'C##JADM'\r\n<\/pre>\nScope 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:<\/p>\n
\r\nrevoke create table from c##jadm container = all;\r\n<\/pre>\n\r\nRevoke succeeded.\r\n<\/pre>\nTo 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.<\/p>\n
\r\nrevoke create type from c##jadm;\r\nrevoke create type from c##jadm container = all;\r\n<\/pre>\n\r\nORA-01952: system privileges not granted to 'C##JADM'\r\n<\/pre>\nContainer clause is not necessary as the default value solves our situation here – I’m adding it to highlight value of this parameter.<\/p>\n
\r\nalter session set container = pipboy;\r\nrevoke create type from c##jadm container = current;\r\n<\/pre>\n\r\nSession altered.\r\nRevoke succeeded.\r\n<\/pre>\nJust 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).<\/p>\n
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:<\/p>\n
\r\nalter session set container = pipboy;\r\ngrant create table to c##jadm;\r\nstart mt\/cprivilege121\r\n<\/pre>\n\r\nContainer name Grantee Privilege AO C\r\n------------------ ------------------------ ---------------------------------------- -- -\r\n(...)\r\nPIPBOY C##JADM CREATE TABLE Y Y\r\nPIPBOY C##JADM CREATE TABLE N N\r\n<\/pre>\nWhich privilege takes precedence?<\/p>\n
\r\nconnect c##jadm\/secret123@pipboy\r\ncreate user arthurmaxson identified by secret123;\r\ngrant create table to arthurmaxson;\r\n<\/pre>\n\r\nUser created.\r\nGrant succeeded.\r\n<\/pre>\nI’ll revoke common grant with admin option and create one without this clause.<\/p>\n
\r\nconnect \/ as sysdba\r\nrevoke create table from c##jadm container = all;\r\ngrant create table to c##jadm container = all;\r\nstart mt\/cprivilege121\r\n<\/pre>\n\r\nContainer name Grantee Privilege AO C\r\n------------------ ------------------------ ---------------------------------------- -- -\r\n(...)\r\nPIPBOY C##JADM CREATE TABLE N N\r\nPIPBOY C##JADM CREATE TABLE N Y\r\n<\/pre>\nI’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.<\/p>\n
\r\nconnect system\/manager@pipboy\r\nrevoke create table from arthurmaxson;\r\ngrant create table to c##jadm with admin option;\r\nconnect c##jadm\/secret123@pipboy\r\ngrant create table to arthurmaxson;\r\nstart mt\/cprivilege121\r\n<\/pre>\n\r\n(...)\r\nGrant succeeded.\r\n<\/pre>\nLooks 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.<\/p>\n
Roles granted commonly and locally<\/h2>\n
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).<\/p>\n
\r\nconnect \/ as sysdba\r\ncreate role c##reader container = all;\r\n<\/pre>\nWe 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.<\/p>\n
\r\nstart mt\/crole121\r\n<\/pre>\n\r\nKey for column names\r\nC - common role?\r\nAO - admin option granted?\r\nD - delegate option\r\nDR - default role\r\nOM - Oracle maintained\r\n\r\nAdd %% if needed for owner name. Search is case insensitive.\r\n\r\nInput role name: %reader%\r\nRoles with name LIKE %reader%\r\n\r\nContainer name Role name Pass. req. Authent. type C OM\r\n---------------- -------------------- ---------- ------------- - --\r\nCDB$ROOT C##READER NO NONE Y N\r\nMOLERAT_CLONE C##READER NO NONE Y N\r\nMOLERAT_CLONE37 C##READER NO NONE Y N\r\nPIPBOY C##READER NO NONE Y N\r\nSPECIAL C##READER NO NONE Y N\r\n\r\nRoles with name LIKE %reader% granted to other users and roles\r\n\r\nContainer name Grantee Granted role AO D DR C\r\n---------------- ------------------------ ------------------------ -- - -- -\r\nCDB$ROOT SYS C##READER Y N Y Y\r\nMOLERAT_CLONE SYS C##READER Y N Y Y\r\nMOLERAT_CLONE37 SYS C##READER Y N Y Y\r\nPIPBOY SYS C##READER Y N Y Y\r\nSPECIAL SYS C##READER Y N Y Y\r\n\r\nPrivileges granted to role LIKE %reader%\r\n\r\nno rows selected\r\n<\/pre>\nRole exists in each container. Role is granted to sys user in each container.<\/p>\n
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).<\/p>\n
\r\ngrant c##reader to c##jadm with admin option container = all;\r\nconnect c##jadm\/secret123@pipboy\r\ngrant c##reader to machete with admin option;\r\nconnect machete\/dominic@pipboy\r\ngrant c##reader to drlesko;\r\nconnect \/ as sysdba\r\nstart mt\/crole121\r\n<\/pre>\n\r\nKey for column names\r\nC - common role?\r\nAO - admin option granted?\r\nD - delegate option\r\nDR - default role\r\nOM - Oracle maintained\r\n\r\nAdd %% if needed for owner name. Search is case insensitive.\r\n\r\nInput role name: %reader%\r\nRoles with name LIKE %reader%\r\n\r\nContainer name Role name Pass. req. Authent. type C OM\r\n---------------- -------------------- ---------- ------------- - --\r\nCDB$ROOT C##READER NO NONE Y N\r\nMOLERAT_CLONE C##READER NO NONE Y N\r\nMOLERAT_CLONE37 C##READER NO NONE Y N\r\nPIPBOY C##READER NO NONE Y N\r\nSPECIAL C##READER NO NONE Y N\r\n\r\nRoles with name LIKE %reader% granted to other users and roles\r\n\r\nContainer name Grantee Granted role AO D DR C\r\n---------------- ------------------------ ------------------------ -- - -- -\r\nCDB$ROOT C##JADM C##READER Y N Y Y\r\nCDB$ROOT SYS C##READER Y N Y Y\r\nMOLERAT_CLONE C##JADM C##READER Y N Y Y\r\nMOLERAT_CLONE SYS C##READER Y N Y Y\r\nMOLERAT_CLONE37 C##JADM C##READER Y N Y Y\r\nMOLERAT_CLONE37 SYS C##READER Y N Y Y\r\nPIPBOY C##JADM C##READER Y N Y Y\r\nPIPBOY DRLESKO C##READER N N Y N\r\nPIPBOY MACHETE C##READER Y N Y N\r\nPIPBOY SYS C##READER Y N Y Y\r\nSPECIAL C##JADM C##READER Y N Y Y\r\nSPECIAL SYS C##READER Y N Y Y\r\n\r\n12 rows selected.\r\n\r\nPrivileges granted to role LIKE %reader%\r\n\r\nno rows selected\r\n\r\n<\/pre>\nEffect 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.<\/p>\n
\r\ncreate user c##vikia identified by secret123;\r\ngrant create session to c##vikia container = all;\r\nconnect machete\/dominic@pipboy\r\ngrant c##reader to c##vikia;\r\nconnect \/ as sysdba\r\nstart mt\/crole121\r\n<\/pre>\n\r\nRoles with name LIKE %reader% granted to other users and roles\r\n\r\nContainer name Grantee Granted role AO D DR C\r\n---------------- ------------------------ ------------------------ -- - -- -\r\n[... removed part of listing]\r\nPIPBOY C##VIKIA C##READER N N Y N\r\n[... removed part of listing]\r\n<\/pre>\nCommon 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.<\/p>\n
\r\ngrant select any table to c##reader container = current;\r\nstart mt\/crole121\r\n<\/pre>\n\r\n[... removed part of listing]\r\nPrivileges granted to role LIKE %read%\r\n\r\nContainer name Grantee Privilege AO C\r\n---------------- ------------------------ ---------------------------------------- -- -\r\nCDB$ROOT C##READER SELECT ANY TABLE N N\r\n<\/pre>\nI’ll add one more local grant from pipboy container.<\/p>\n
\r\nalter session set container = pipboy;\r\ngrant select any table to c##reader container = current;\r\nalter session set container = cdb$root;\r\nstart mt\/crole121\r\n<\/pre>\n\r\n[... removed part of listing]\r\nPrivileges granted to role LIKE %read%\r\n\r\nContainer name Grantee Privilege AO C\r\n---------------- ------------------------ ---------------------------------------- -- -\r\nCDB$ROOT C##READER SELECT ANY TABLE N N\r\nPIPBOY C##READER SELECT ANY TABLE N N\r\n<\/pre>\nNext test – I’ll try to add common privilege to current local privileges.<\/p>\n
\r\ngrant select any table to c##reader with admin option container = all;\r\nstart mt\/crole121\r\n<\/pre>\n\r\nPrivileges granted to role LIKE %read%\r\n\r\nContainer name Grantee Privilege AO C\r\n---------------- ------------------------ ---------------------------------------- -- -\r\nCDB$ROOT C##READER SELECT ANY TABLE Y Y\r\nCDB$ROOT C##READER SELECT ANY TABLE N N\r\nMOLERAT_CLONE C##READER SELECT ANY TABLE Y Y\r\nMOLERAT_CLONE37 C##READER SELECT ANY TABLE Y Y\r\nPIPBOY C##READER SELECT ANY TABLE Y Y\r\nPIPBOY C##READER SELECT ANY TABLE N N\r\nSPECIAL C##READER SELECT ANY TABLE Y Y\r\n<\/pre>\nWe’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.<\/p>\n
To revoke granted privilege I need to use the same containers which I’ve used previously.<\/p>\n
\r\nalter session set container = pipboy;\r\nrevoke select any table from c##reader;\r\nalter session set container = cdb$root;\r\nstart mt\/crole121\r\n<\/pre>\n\r\nPrivileges granted to role LIKE %reader%\r\n\r\nContainer name Grantee Privilege AO C\r\n---------------- ------------------------ ---------------------------------------- -- -\r\nCDB$ROOT C##READER SELECT ANY TABLE N N\r\nCDB$ROOT C##READER SELECT ANY TABLE Y Y\r\nMOLERAT_CLONE C##READER SELECT ANY TABLE Y Y\r\nMOLERAT_CLONE37 C##READER SELECT ANY TABLE Y Y\r\nPIPBOY C##READER SELECT ANY TABLE Y Y\r\nSPECIAL C##READER SELECT ANY TABLE Y Y\r\n<\/pre>\nPrivilege from pipboy PDB have been removed.<\/p>\n
\r\nrevoke select any table from c##reader container = all;\r\nstart mt\/crole121\r\n<\/pre>\n\r\nPrivileges granted to role LIKE %read%\r\n\r\nContainer name Grantee Privilege AO C\r\n---------------- ------------------------ ---------------------------------------- -- -\r\nCDB$ROOT C##READER SELECT ANY TABLE N N\r\n<\/pre>\nCommon role has lost common possibility to share select any table privilege with other users and roles. One more revoke to be done:<\/p>\n
\r\nrevoke select any table from c##reader container = current;\r\nstart mt\/crole121\r\n<\/pre>\n\r\nPrivileges granted to role LIKE %read%\r\n\r\nno rows selected\r\n<\/pre>\nThe last thing we can do now is to drop this common role.<\/p>\n
\r\ndrop role c##reader;\r\n<\/pre>\n\r\nRole dropped.\r\n<\/pre>\nNote, 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:<\/p>\n
\r\ncreate role reader;\r\n<\/pre>\n\r\nORA-65096: invalid common user or role name\r\n<\/pre>\n…and another test…<\/p>\n
\r\ncreate role reader container = current;\r\n<\/pre>\n\r\nORA-65049: creation of local user or role is not allowed in CDB$ROOT\r\n<\/pre>\nThis only confirms what we’ve seen earlier when I’ve tried to create local user in root container.<\/p>\n
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:<\/p>\n
\r\nconn \/ as sysdba\r\ncreate role c##reader;\r\nalter session set container=pipboy;\r\ncreate role newreader;\r\ngrant c##reader to newreader;\r\nrevoke c##reader from newreader;\r\ngrant newreader to c##reader;\r\nrevoke newreader from c##reader;\r\ndrop role newreader;\r\n<\/pre>\nThis concludes chapter related with create, grant, revoke and drop common role.<\/p>\n
Accessing container data<\/h2>\n
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.<\/p>\n
\r\nselect container_data, con_id, count(0)\r\n from cdb_views\r\n group by container_data, con_id\r\n order by container_data, con_id;\r\n<\/pre>\n\r\nC CON_ID COUNT(0)\r\n- ---------- ----------\r\nN 1 3833\r\nN 3 3843\r\nN 4 3843\r\nN 5 3844\r\nN 6 3844\r\nY 1 2665\r\nY 3 2665\r\nY 4 2665\r\nY 5 2665\r\nY 6 2665\r\n<\/pre>\n\r\ncol owner for a30 hea 'Owner'\r\ncol view_name for a30 hea 'View name'\r\ncol con_id for 999999 hea 'Con ID'\r\nselect owner, view_name, con_id\r\n from cdb_views\r\n where container_data = 'Y'\r\n and view_name in ('V_$SESSION','V_$PROCESS')\r\n order by view_name, con_id;\r\n<\/pre>\n\r\nOwner View name Con ID\r\n------------------------------ ------------------------------ -------\r\nSYS V_$PROCESS 1\r\nSYS V_$PROCESS 3\r\nSYS V_$PROCESS 4\r\nSYS V_$PROCESS 5\r\nSYS V_$PROCESS 6\r\nSYS V_$SESSION 1\r\nSYS V_$SESSION 3\r\nSYS V_$SESSION 4\r\nSYS V_$SESSION 5\r\nSYS V_$SESSION 6\r\n<\/pre>\nNotice, 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).<\/p>\n
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.<\/p>\n
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.<\/p>\n
I still have my c##jadm junior administrator. I’ll give him grant to select on v_$session (I’m using sys connection).<\/p>\n
\r\ngrant select on v_$session to c##jadm container = all;\r\nstart mt\/cobjectpriv121\r\n<\/pre>\n\r\nKey for column names\r\nC - common privilege?\r\nG - grant option added?\r\nH - hierarchy?\r\n\r\nAdd %% if needed for grantee name. Search is case insensitive.\r\n\r\nInput grantee name: %jadm%\r\nPrivileges granted on objects to LIKE %jadm%\r\n\r\nContainer name Grantee Owner Object name Privilege G H C Ob.type\r\n--------------- -------------- -------------- ------------------ ------------ - - - -------\r\nCDB$ROOT C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nMOLERAT_CLONE C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nMOLERAT_CLONE37 C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nPIPBOY C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nSPECIAL C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\n<\/pre>\nRight now I can select contents of v$session view from within<\/b> each container. I’ll check number of sessions from root container.<\/p>\n\r\nconnect c##jadm\/secret123@tcdb\r\ncol username for a30 hea 'Username'\r\ncol program for a35 hea 'Program'\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nThe 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.<\/p>\n
\r\nconnect c##jadm\/secret123@tcdb\r\ncol username for a30 hea 'Username'\r\ncol program for a35 hea 'Program'\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nResult 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.<\/p>\n
\r\nconnect \/ as sysdba\r\nalter user c##jadm\r\n set container_data = all container = current;\r\n<\/pre>\n\r\nUser altered.\r\n<\/pre>\nDon’t forget about container = current clause – without it you’ll receive an error presented below.<\/p>\n
\r\nalter user c##jadm\r\n set container_data = all;\r\n<\/pre>\n\r\nORA-65048: error encountered when processing the current DDL statement in pluggable\r\ndatabase PIPBOY\r\nORA-65056: CONTAINER_DATA attribute is not used in a pluggable database.\r\n<\/pre>\nThere 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.<\/p>\n
Ok, we’ve got all PDBs accessible as c##jadm – I’ll check again sessions.<\/p>\n
\r\nconnect c##jadm\/secret123@tcdb\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n 3 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nNow we can see system session. I’ll change this connection to special PDB which has different container id and execute the query again:<\/p>\n
\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n 4 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nNotice, 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.<\/p>\n
Now, I’ll cut available containers list only to pipboy (or I would like to…):<\/p>\n
\r\nconnect \/ as sysdba\r\nalter user c##jadm\r\n set container_data = (pipboy) container = current;\r\n<\/pre>\n\r\nORA-65057: CONTAINER_DATA attribute must always include the current container\r\n<\/pre>\nThis means that you always have to add root container to the list when specifying a list of containers.<\/p>\n
\r\nalter user c##jadm\r\n set container_data = (cdb$root, pipboy) container = current;\r\n<\/pre>\n\r\nUser altered.\r\n<\/pre>\nTo summarize so far: always use container = current and don’t forget about cdb$root in the list when altering user container_data options.<\/p>\n
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).<\/p>\n
\r\nconnect c##jadm\/secret123@tcdb\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nOnly one session – just like configured. I’m changing system session to pipboy and executing query again.<\/p>\n
\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n 3 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nWe 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:<\/p>\n
\r\nalter session set container = special;\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID Username Program COUNT(0)\r\n---------- ------------------------------ ----------------------------------- ----------\r\n 4 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n 4 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nFrom special PBD all is visible locally.<\/p>\n
I’ll add another privilege for c##jadm user and try to restrict access to only one of those views.<\/p>\n
\r\nconnect \/ as sysdba\r\ngrant select on v_$process\r\n to c##jadm container = all;\r\nstart mt\/cobjectpriv121\r\n<\/pre>\n\r\nInput grantee name: %jadm%\r\nPrivileges granted on objects to LIKE %jadm%\r\n\r\nContainer name Grantee Owner Object name Privilege G H C Ob.type\r\n--------------- -------------- -------------- ------------------ ------------ - - - -------\r\nCDB$ROOT C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nCDB$ROOT C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nMOLERAT_CLONE C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nMOLERAT_CLONE C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nMOLERAT_CLONE37 C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nMOLERAT_CLONE37 C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nPIPBOY C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nPIPBOY C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nSPECIAL C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nSPECIAL C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\n<\/pre>\nAnd adjust container data privileges using alter user command.<\/p>\n
\r\nalter user c##jadm\r\n set container_data = all for v_$session container = current;\r\nstart mt\/cdata121\r\n<\/pre>\n\r\nKey for column names\r\nD - default attribute\r\nAC - all containers\r\n\r\nAdd %% if needed for user name. Search is case insensitive.\r\n\r\nInput user name: %jadm%\r\nContainer data granted to user LIKE %jadm%\r\n\r\nContainer name User name D Object owner Object name AC\r\n---------------- -------------------- - -------------------- ---------------------------- --\r\nCDB$ROOT C##JADM Y N\r\nPIPBOY C##JADM Y N\r\n C##JADM N SYS V_$SESSION Y\r\n<\/pre>\nWith clause for<\/b> 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):<\/p>\n\r\nconnect c##jadm\/secret123@tcdb\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID User name Program COUNT(0)\r\n---------- -------------------- ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n(...)\r\n 4 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nBut we’re going to see v$process view contents from root container only for root and pipboy PDB (con_id = 3):<\/p>\n
\r\nselect con_id, username, program, count(0)\r\n from v$process\r\n where background is null\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID User name Program COUNT(0)\r\n---------- -------------------- ----------------------------------- ----------\r\n(...)\r\n 1 grid oracle@odone 1\r\n 3 grid oracle@odone 1\r\n<\/pre>\nWhen 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.<\/p>\n
\r\nselect con_id, username, program, count(0)\r\n from v$process\r\n where background is null\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n CON_ID User name Program COUNT(0)\r\n---------- -------------------- ----------------------------------- ----------\r\n(...)\r\n 1 grid oracle@odone 1\r\n<\/pre>\nNow 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:<\/p>\n
\r\nconnect \/ as sysdba\r\nstart mt\/cdata121\r\n<\/pre>\n\r\nKey for column names\r\nD - default attribute\r\nAC - all containers\r\n\r\nAdd %% if needed for user name. Search is case insensitive.\r\n\r\nInput user name: %jadm%\r\nContainer data granted to user LIKE %jadm%\r\n\r\nContainer name User name D Object owner Object name AC\r\n---------------- -------------------- - -------------------- ---------------------------- --\r\nCDB$ROOT C##JADM Y N\r\nPIPBOY C##JADM Y N\r\n C##JADM N SYS V_$SESSION Y\r\n<\/pre>\n\r\nstart mt\/cobjectpriv121\r\n<\/pre>\n\r\nKey for column names\r\nC - common privilege?\r\nG - grant option added?\r\nH - hierarchy?\r\n\r\nAdd %% if needed for grantee name. Search is case insensitive.\r\n\r\nInput grantee name: %jadm%\r\nPrivileges granted on objects to LIKE %jadm%\r\n\r\nContainer name Grantee Owner Object name Privilege G H C Ob.type\r\n--------------- -------------- -------------- ------------------ ------------ - - - -------\r\nCDB$ROOT C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nCDB$ROOT C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nMOLERAT_CLONE C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nMOLERAT_CLONE C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nMOLERAT_CLONE37 C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nMOLERAT_CLONE37 C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nPIPBOY C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nPIPBOY C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\nSPECIAL C##JADM SYS V_$PROCESS SELECT N N Y VIEW\r\nSPECIAL C##JADM SYS V_$SESSION SELECT N N Y VIEW\r\n<\/pre>\nI’ll try to remove access to v$session from c##jadm. First, I’ll try to remove only one grant from special PDB.<\/p>\n
\r\n alter user c##jadm\r\nremove container_data = (special) for v_$session container = current;\r\n<\/pre>\n\r\nORA-65062: CONTAINER_DATA attribute is set to ALL\r\n<\/pre>\nOk, I’ll try to remove with all value for container_data clause.<\/p>\n
\r\n alter user c##jadm\r\nremove container_data = all for v_$session container = current;\r\n<\/pre>\n\r\nORA-00922: missing or invalid option\r\n<\/pre>\nAh, yes, there is no syntax available to remove with all value for container_data (see alter user command in SQL Language Reference).<\/p>\n
Maybe I’ll be able to reset this user access and remove this grant overwriting it with default.<\/p>\n
\r\n alter user c##jadm\r\n set container_data = default for v_$session container = current;\r\nstart mt\/cdata121\r\n<\/pre>\n\r\nInput user name: %jadm%\r\nContainer data granted to user LIKE %jadm%\r\n\r\nContainer name User name D Object owner Object name AC\r\n---------------- -------------------- - -------------------- ---------------------------- --\r\nCDB$ROOT C##JADM Y N\r\nPIPBOY C##JADM Y N\r\n<\/pre>\nOk, I don’t know, why we have remove<\/b> in this command available – maybe we can remove single container from scope of this user.<\/p>\n\r\n alter user c##jadm\r\nremove container_data = (pipboy) container = current;\r\n<\/pre>\n\r\nInput user name: %jadm%\r\nContainer data granted to user LIKE %jadm%\r\n\r\nContainer name User name D Object owner Object name AC\r\n---------------- -------------------- - -------------------- ---------------------------- --\r\nCDB$ROOT C##JADM Y N\r\n<\/pre>\nProbably this is it. Now, how to remove this one entry too?<\/p>\n
\r\n alter user c##jadm\r\nremove container_data = (cdb$root) container = current;\r\n<\/pre>\n\r\nORA-65057: CONTAINER_DATA attribute must always include the current container\r\n<\/pre>\nMaybe reset to default would help just like with object privilege.<\/p>\n
\r\nalter user c##jadm\r\n set container_data = default container = current;\r\nstart mt\/cdata121\r\n<\/pre>\n\r\nInput user name: %jadm%\r\nContainer data granted to user LIKE %jadm%\r\n\r\nno rows selected\r\n<\/pre>\nAnd cleaning up the c##jadm user from grants:<\/p>\n
\r\nrevoke select on v_$process from c##jadm container = all;\r\nrevoke select on v_$session from c##jadm container = all;\r\n<\/pre>\nEarlier 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).<\/p>\n
\r\nconnect \/ as sysdba\r\ngrant select on v_$session to c##jadm container = current;\r\nalter user c##jadm\r\n set container_data = all for v_$session container = current;\r\nstart mt\/cdata121\r\n<\/pre>\n\r\nInput user name: %jadm%\r\nContainer data granted to user LIKE %jadm%\r\n\r\nContainer name User name D Object owner Object name AC\r\n---------------- -------------------- - -------------------- ---------------------------- --\r\n C##JADM N SYS V_$SESSION Y\r\n<\/pre>\n\r\nstart mt\/cobjectpriv121\r\n<\/pre>\n\r\nInput grantee name: %jadm%\r\nPrivileges granted on objects to LIKE %jadm%\r\n\r\nContainer name Grantee Owner Object name Privilege G H C Ob.type\r\n--------------- -------------- -------------- ------------------ ------------ - - - -------\r\nCDB$ROOT C##JADM SYS V_$SESSION SELECT N N N VIEW\r\n<\/pre>\n\r\nconnect c##jadm\/secret123@tcdb\r\ncol username for a30 hea 'Username'\r\ncol program for a35 hea 'Program'\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n Con ID Username Program COUNT(0)\r\n------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n 4 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nSwitching system session from special to pipboy in additional window and querying again about the session list from main window.<\/p>\n
\r\ncol username for a30 hea 'Username'\r\ncol program for a35 hea 'Program'\r\nselect con_id, username, program, count(0)\r\n from v$session\r\n where type != 'BACKGROUND'\r\n group by con_id, username, program\r\n order by con_id, username, program;\r\n<\/pre>\n\r\n Con ID Username Program COUNT(0)\r\n------- ------------------------------ ----------------------------------- ----------\r\n 1 C##JADM sqlplus@odone (TNS V1-V3) 1\r\n(...)\r\n 3 SYSTEM sqlplus@odone (TNS V1-V3) 1\r\n<\/pre>\nCleaning up again.<\/p>\n
\r\nalter user c##jadm\r\n set container_data = default for v_$session container = current;\r\nrevoke select on v_$session from c##jadm;\r\n<\/pre>\nFinally, 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.<\/p>\n
Conclusion<\/h2>\n
Container database is a great way to improve hardware<\/b> 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.<\/p>\n
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).<\/p>\n
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).<\/p>\n
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.<\/p>\n
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.<\/p>\n
Thanks for reading this topic up to the last word. This was quite a trip.<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts\/125"}],"collection":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=125"}],"version-history":[{"count":1,"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions"}],"predecessor-version":[{"id":126,"href":"https:\/\/oradistrict.com\/index.php?rest_route=\/wp\/v2\/posts\/125\/revisions\/126"}],"wp:attachment":[{"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=125"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=125"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oradistrict.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=125"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}