Default administrative users when creating a PDB with DBCA

2016-07-04 Off By Markus Flechtner

When creating a CDB and at least 1 PDB, DBCA creates the well known users SYS and SYSTEM and the user PDBADMIN. Where is this user created and which privileges are granted to PDBADMIN?

First of all, when you create an empty CDB (i.e. without PDBs) only SYS and SYSTEM are created:

001-dbca-01

002-dbca-002

But when you tell DBCA to create one ore more Pluggable Databases, the user PDBADMIN will be created, too:

003-dbca-1pdb-001

004-dbca-1pdb-002

Is it a global (“common”) user or a local user? And what are the privileges of PDBADMIN?

SQL> show pdbs

 CON_ID     CON_NAME           OPEN MODE RESTRICTED
---------- ------------------ ---------- ----------
 2         PDB$SEED           READ ONLY   NO
 3         TESTPDB1           READ WRITE  NO

SQL> select con_id,username,common from cdb_users 
   2 where username like 'PDB%';

CON_ID      USERNAME                    COM
---------- ---------------------------- ---
 3          PDBADMIN                    NO

OK, it’s a local user.

Attention: if you create more than one PDB when creating the database with DBCA, the users PDBADMIN in all PDBS get the same initial password. You should change them after database creation.

What are the privileges?

SQL> select con_id,grantee,granted_role from cdb_role_privs where grantee='PDBADMIN';

CON_ID      GRANTEE            GRANTED_ROLE
---------- ------------------ ------------------
 3          PDBADMIN           PDB_DBA

SQL> select con_id,grantee,privilege from cdb_sys_privs where grantee='PDBADMIN';
no rows selected

SQL> select con_id,grantee,table_name,privilege from cdb_tab_privs where grantee='PDBADMIN';
no rows selected

Which privileges are contained in the role PDB_DBA?

SQL> select con_id,grantee,table_name,privilege from cdb_tab_privs where grantee='PDB_DBA';

CON_ID GRANTEE TABLE_NAME                PRIVILEGE
------ -------- ------------------------ -------------------------
 1     PDB_DBA PDB_PLUG_IN_VIOLATIONS     SELECT
 1     PDB_DBA PDB_ALERTS                 SELECT
 3     PDB_DBA PDB_ALERTS                 SELECT
 3     PDB_DBA PDB_PLUG_IN_VIOLATIONS     SELECT
 
SQL> select con_id,grantee,privilege from cdb_sys_privs where grantee='PDB_DBA';

CON_ID     GRANTEE     PRIVILEGE
---------- ---------- ------------------------------
 3         PDB_DBA     SET CONTAINER
 3         PDB_DBA     CREATE SESSION
 3         PDB_DBA     CREATE PLUGGABLE DATABASE


SQL> select con_id,grantee,granted_role from cdb_role_privs where grantee='PDB_DBA';

CON_ID      GRANTEE     GRANTED_ROLE
---------- ---------- ------------------------------
 3          PDB_DBA     CONNECT

For “daily DBA work”, the role DBA (on PDB-level) is missing.

If you use DBCA to create a new PDB in an existing CDB, you can choose the name otf the admin user:

005-dbca-add-pdb

And if you create a PDB using SQL*Plus and the command “CREATE PLUGGABLE DATABASE”, you can add additional roles to the role “PDB_DBA”:

SQL> CREATE PLUGGABLE DATABASE crm05 ADMIN USER crm05_adm IDENTIFIED BY manager ROLES=(DBA)
 2 PATH_PREFIX = '/u01/oradata/'
 3 STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 256M)
 4 FILE_NAME_CONVERT = ('/u01/oradata/TVD12CDB/pdbseed/','/u01/oradata/TVD12CDB/crm05/')
 5 DEFAULT TABLESPACE data 
 6 DATAFILE '/u01/oradata/TVD12CDB/crm05/data_01.dbf' SIZE 25M AUTOEXTEND ON MAXSIZE 512M
 6 TEMPFILE REUSE;
Pluggable database created.
SQL> ALTER PLUGGABLE DATABASE crm05 OPEN;

Pluggable database altered.

SQL> CONNECT crm05_adm/manager@training:1521/crm05.markusdba.local
Connected.

SQL> select granted_role from dba_role_privs 
   2 where grantee='PDB_DBA';
GRANTED_ROLE
-----------------------------------
DBA

Update: Role CDB_DBA vs. PDB_DBA

There’s an additonal role CDB_DBA. Let’s compare CDB_DBA and PDB_DBA, both on root-level and on PDB-level:

CDB$ROOT:

SQL> show con_id con_name

CON_ID
------------------------------
1

SQL> select * from cdb_roles where role like '%DBA%' and con_id in (1,3);

ROLE PASSWORD AUTHENTICAT COM O CON_ID
-------------------- -------- ----------- --- - ----------
DBA NO NONE YES Y 1
CDB_DBA NO NONE YES Y 1
PDB_DBA NO NONE YES Y 1
XDBADMIN NO NONE YES Y 1
OLAP_DBA NO NONE YES Y 1
LBAC_DBA NO NONE YES Y 1
DBA NO NONE YES Y 3
CDB_DBA NO NONE YES Y 3
PDB_DBA NO NONE YES Y 3
XDBADMIN NO NONE YES Y 3
OLAP_DBA NO NONE YES Y 3
LBAC_DBA NO NONE YES Y 3

SQL> select * from dba_sys_privs where grantee = 'CDB_DBA';

GRANTEE PRIVILEGE ADM COM
------- ---------------------------------------- --- ---
CDB_DBA SET CONTAINER NO YES

SQL> select * from dba_role_privs where grantee = 'CDB_DBA';
no rows selected

SQL> select privilege,table_name from dba_tab_privs where grantee = 'CDB_DBA';

PRIVILEGE TABLE_NAME
--------- ----------------------------------------
SELECT    PDB_PLUG_IN_VIOLATIONS
SELECT    PDB_ALERTS
SELECT    CDB_LOCAL_ADMIN_PRIVS

SQL> select * from role_role_privs where role = 'CDB_DBA';
no rows selected

SQL> select * from dba_sys_privs where grantee = 'PDB_DBA';
no rows selected

SQL> select * from dba_role_privs where grantee = 'PDB_DBA';
no rows selected

SQL> select privilege,table_name from dba_tab_privs where grantee = 'PDB_DBA';

PRIVILEGE TABLE_NAME
--------- ----------------------------------------
SELECT    PDB_PLUG_IN_VIOLATIONS
SELECT    PDB_ALERTS

SQL> select * from role_role_privs where role = 'PDB_DBA';
no rows selected

On PDB level:

SQL> show con_id con_name

CON_ID
------------------------------
3

SQL> select * from dba_roles where role like '%DBA%';

ROLE PASSWORD AUTHENTICAT COM O
--------------- -------- ----------- --- -
DBA NO NONE YES Y
CDB_DBA NO NONE YES Y
PDB_DBA NO NONE YES Y
XDBADMIN NO NONE YES Y
OLAP_DBA NO NONE YES Y
LBAC_DBA NO NONE YES Y

SQL> select * from dba_sys_privs where grantee = 'PDB_DBA';

GRANTEE PRIVILEGE ADM COM
------- ------------------------------ --- ---
PDB_DBA SET CONTAINER NO NO
PDB_DBA CREATE PLUGGABLE DATABASE NO NO
PDB_DBA CREATE SESSION NO NO

SQL> select * from dba_role_privs where grantee = 'PDB_DBA';
no rows selected

SQL> select privilege,table_name from dba_tab_privs where grantee = 'PDB_DBA';

PRIVILEGE TABLE_NAME
--------------- ----------------------------------------
SELECT PDB_PLUG_IN_VIOLATIONS
SELECT PDB_ALERTS

SQL> select * from role_role_privs where role = 'PDB_DBA';
no rows selected

Additional links:


Ad (Amazon Link):