A 12c multitenant database introduces the new concept of local users and common users. This article shows simple use cases why DBAs may want to create common users – in contrast to the common users that are created automatically, like SYS, SYSTEM, MDSYS etc.
A typical requirement is to have a superuser other than SYS, but with the same power. Like the common user C##_SYS in the picture below.
Or suppose we have many pluggable databases (PDBs) and different superusers responsible for different PDBs like C##_ADMIN1 and C##_ADMIN2:
Let’s implement it as above. Initially, my demo environment looks like this:
SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID ------------------------------ ---------- ---------- PDB$SEED READ ONLY 2 PDB1 READ WRITE 3 PDB2 READ WRITE 4 PDB3 READ WRITE 5 SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1 SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1 SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
At first, I create C##_SYS and grant SYSDBA as a common privilege to that new user:
SQL> create user c##_sys identified by oracle container=all;
User created.
SQL> grant sysdba to c##_sys container=all;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0
C##_SYS can now do anything to any PDB:
SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba Connected. SQL> shutdown immediate Pluggable Database closed. SQL> connect / as sysdba Connected. SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID ------------------------------ ---------- ---------- PDB$SEED READ ONLY 2 PDB1 MOUNTED 3 PDB2 READ WRITE 4 PDB3 READ WRITE 5 SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba Connected. SQL> startup Pluggable Database opened. SQL> connect / as sysdba Connected. SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID ------------------------------ ---------- ---------- PDB$SEED READ ONLY 2 PDB1 READ WRITE 3 PDB2 READ WRITE 4 PDB3 READ WRITE 5
Notice that there is a subtle difference in granting SYSDBA of the container database (CDB) as local or common privilege:
SQL> revoke sysdba from c##_sys container=all; Revoke succeeded. SQL> grant sysdba to c##_sys; Grant succeeded. SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1 SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1 SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1 C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 1 SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba ERROR: ORA-01031: insufficient privileges Warning: You are no longer connected to ORACLE.
C##_SYS has SYSDBA of the CDB „only“, therefore the error. Although:
SQL> connect c##_sys/oracle as sysdba Connected. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> alter session set container=pdb1; Session altered. SQL> shutdown immediate Pluggable Database closed. SQL> show con_name CON_NAME ------------------------------ PDB1 SQL> startup Pluggable Database opened. SQL> connect c##_sys/oracle as sysdba Connected. SQL> select name,open_mode,con_id from v$pdbs; NAME OPEN_MODE CON_ID ------------------------------ ---------- ---------- PDB$SEED READ ONLY 2 PDB1 READ WRITE 3 PDB2 READ WRITE 4 PDB3 READ WRITE 5
However, the proper way is probably granting it as a common privilege:
SQL> revoke sysdba from c##_sys;
Revoke succeeded.
SQL> grant sysdba to c##_sys container=all;
Grant succeeded.
Now to the implementation of C##_ADMIN1 and C##_ADMIN2. The point is here, that SYSDBA can be granted as a local privilege for certain PDBs only, to the effect that different superusers can be responsible for different groups of PDBs:
SQL> create user c##_admin1 identified by oracle container=all; User created. SQL> alter session set container=pdb1; Session altered. SQL> grant sysdba to c##_admin1 container=current; Grant succeeded. SQL> connect / as sysdba Connected. SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1 SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1 SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1 C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0 C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 3 6 rows selected.
For now, C##_ADMIN1 can only connect to PDB1:
SQL> connect c##_admin1/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> select count(*) from session_privs;
COUNT(*)
----------
233
SQL> connect c##_admin1/oracle@edd2r6p0/pdb2 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
The completed implementation of the picture above:
SQL> connect / as sysdba Connected. SQL> alter session set container=pdb2; Session altered. SQL> grant sysdba to c##_admin1 container=current; Grant succeeded. SQL> connect / as sysdba Connected. SQL> create user c##_admin2 identified by oracle; User created. SQL> alter session set container=pdb3; Session altered. SQL> grant sysdba to c##_admin2 container=current; Grant succeeded. SQL> connect / as sysdba Connected. SQL> select * from v$pwfile_users; USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID ------------------------------ ----- ----- ----- ----- ----- ----- ---------- SYS TRUE TRUE FALSE FALSE FALSE FALSE 0 SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1 SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1 SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1 C##_SYS TRUE FALSE FALSE FALSE FALSE FALSE 0 C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 3 C##_ADMIN1 TRUE FALSE FALSE FALSE FALSE FALSE 4 C##_ADMIN2 TRUE FALSE FALSE FALSE FALSE FALSE 5 8 rows selected.
The whole example is from my present 12c New Features in Düsseldorf, by the way. Hope you find it useful 🙂
As always: Don’t believe it, test it!
Pickleball spielen 
#1 von collierjw am November 10, 2014 - 21:10
however, this does not stop the different sysdba from administering the root container?
#2 von collierjw am November 11, 2014 - 00:10
ORA-65175
#3 von vaurob am August 12, 2015 - 16:10
Hi!
How do I know if a privilege is Local or Common?
–A simple testcase throws an error
PDB2@CDB1> connect system/oracle@pdb2
PDB2@CDB1> grant set container to hr container=all;
grant set container to hr container=all
*
ERROR at line 1:
ORA-65030: one may not grant a Common Privilege to a Local User or Role
–The cause and action
SQL> !oerr ora 65030
65030, 00000, „one may not grant a Common Privilege to a Local User or Role“
// *Cause: A Common User issued a GRANT statement specifying
// CONTAINER=ALL and naming a Local User or Role as a grantee,
// which is illegal.
// *Action: If trying to grant a Local Privilege, remove CONTAINER=ALL from
// the statement. If trying to grant a Common Privilege, remove
// Local Users and Roles from the list of grantees.
//
However I was wondering about the definition of Local Privilege and Common Privilege
system_privilege_map does not give a clue..
SQL> desc system_privilege_map
Name Null? Type
———————————————————– ——– —————————————–
PRIVILEGE NOT NULL NUMBER
NAME NOT NULL VARCHAR2(40)
PROPERTY NOT NULL NUMBER
Any ideas? Link to the documentation?
Or is the wording totally confusing? Is this what they mean?
Local Privilege = „Locally granted Privilege“
Common Privilege = „Commonly granted Privilege“
Initially I thought ‚SET CONTAINER‘ privilege is a Common Privilege as it is a global CDB-wide privilege.
Furthermore a related question: What is the use of ‚grant set container‘ to a local user? It is not going to be able to set any other container if I’m not mistaken.
— Giving the grand in a pdb to pdbadmin
SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb4_copy;
Session altered.
SQL> grant set container to pdbadmin;
Grant succeeded.
— Using the grant
SQL> conn pdbadmin/xxx@pdb4_copy
Connected.
SQL> alter session set container=infraplu1;
ERROR:
ORA-01031: insufficient privileges
#4 von remya am Mai 22, 2018 - 16:08
Hi Hesse,
I am facing below error on EMexpress console under secuirty role->users
I have granted all privileges to PDB.
ORA-01435: user does not exist
ORA-06512: at „SYS.PRVTEMX_ADMIN“, line 7862
ORA-06512: at „SYS.PRVTEMX_ADMIN“, line 9237
ORA-06512: at „SYS.WRI$_REPT_SECURITY“, line 62
ORA-06512: at „SYS.DBMS_REPORT“, line 1028
ORA-06512: at line 1
#5 von Uwe Hesse am Mai 23, 2018 - 08:53
Hi, I suggest you take that kind of question to a public forum. You can find some here https://community.oracle.com/welcome