Default administrative users when creating a PDB with DBCA
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:
But when you tell DBCA to create one ore more Pluggable Databases, the user PDBADMIN will be created, too:
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:
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:
- oracle-base.com: Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1)
- Oracle 12c Administrators Guide: Administering PDBs with SQL*Plus
- Oracle 12c Administrators Guide: Overview of Managing a Multitenant Environment
Ad (Amazon Link):