General Information
Library Note
Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have?
Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx .
Be sure to view the full listing of monographs in Morgan's Library
Notes
This page is specific to the PDB$SEED container. For more general information about CDBs, PDBs, and CDB$ROOT follow the containers link at page bottom.
Data Dictionary Objects
CDB_CONTAINER_DATA
LOGMNRG_SEED$
V$CONTAINERS
CONTAINER$
LOGMNR_SEED$
V$PDBS
DBA_CONTAINER_DATA
Roles
System Privileges
CREATE PLUGGABLE DATABASE
SET CONTAINER
check out:
catcon.pl, catcon.pm
PDB$SEED from PDB$ROOT
Seed Pluggable Container Exploration from Root
conn / as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> desc container$
Name Null? Type
------------------ -------- ------
OBJ# NOT NULL NUMBER
CON_ID# NOT NULL NUMBER
DBID NOT NULL NUMBER
CON_UID NOT NULL NUMBER
STATUS NOT NULL NUMBER
CREATE_SCNWRP NOT NULL NUMBER
CREATE_SCNBAS NOT NULL NUMBER
CLNSCNWRP NUMBER
CLNSCNBAS NUMBER
RDBA NOT NULL NUMBER
FLAGS NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 VARCHAR2(128)
SPARE4 VARCHAR2(128)
SQL> SELECT obj#, con_id#, dbid, con_uid, status, create_scnbas, flags, rdba
2 FROM container$
3 ODER BY 2;
OBJ# CON_ID# DBID CON_UID STATUS CREATE_SCNBAS FLAGS RDBA
----- ------- ---------- ---------- ------ ------------- ----- -------
175 1 1646890724 1 2 0 0 4194824
91834 2 2025707704 2025707704 2 1689010 1 4194824
91992 3 3821213821 3821213821 2 1702206 0 4194824
SQL> desc v$pdbs
Name Null? Type
----------------- -------- ------------
CON_ID NUMBER
DBID NUMBER
CON_UID NUMBER
GUID RAW(16)
NAME VARCHAR2(30)
OPEN_MODE VARCHAR2(10)
RESTRICTED VARCHAR2(3)
OPEN_TIME TIMESTAMP(3)
CREATE_SCN NUMBER
TOTAL_SIZE NUMBER
col name format a11
col open_time format a26
SQL> SELECT con_id, dbid, name, open_mode, restricted, open_time, create_scn
2 FROM v$pdbs
3 WHERE con_id = 2;
CON_ID DBID NAME OPEN_MODE RES OPEN_TIME CREATE_SCN
---------- ---------- --------------- ---------- --- -------------------------- ----------
2 4043696482 PDB$SEED READ ONLY NO 14-FEB-13 08.15.16.456 AM 2389989
SQL> ALTER PLUGGABLE DATABASE pdb$seed CLOSE;
alter pluggable database pdb$seed close
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered
-- Note: From inside any other PDB you get
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
SQL> SELECT owner, object_type
2 FROM dba_objects
3 WHERE object_name = 'PDB$SEED';
OWNER OBJECT_TYPE
------------------------- --------------------
SYS UNDEFINED
SQL> SELECT COUNT(*)
2 FROM dba_objects
3 WHERE object_name LIKE 'CDB%';
COUNT(*)
----------
1874
SQL> SELECT COUNT(*)
2 FROM dba_objects
3 WHERE object_name = 'CDB_OBJECTS';
COUNT(*)
----------
2
PDB$SEED Internals
Seed Pluggable Container Exploration from inside the container
conn / as sysdba
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> ALTER SESSION SET CONTAINER = PDB$SEED;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
PDB$SEED
-- compare this to the result seen in pdb$root above
SQL> SELECT dbid, name, open_mode, restricted
2 FROM v$pdbs;
DBID NAME OPEN_MODE RES
---------- --------------- ---------- ---
4043696482 PDB$SEED READ ONLY NO
SQL> SELECT COUNT(*)
2 FROM dba_objects;
COUNT(*)
----------
90698
SQL> SELECT COUNT(*)
2 FROM all_objects;
COUNT(*)
----------
89185
col username format a25
col account_status format a17
col default_tablespace format a7
col profile format a10
col temporary_tablespace format a4
SQL> SELECT username, common, account_status, lock_date, expiry_date
2 FROM dba_users
3 ORDER BY 1;
USERNAME COM ACCOUNT_STATUS LOCK_DATE EXPIRY_DATE
---------------------- --- ----------------- -------------------- --------------------
ANONYMOUS YES EXPIRED 11-SEP-2016 11:15:19
APEX_040200 YES EXPIRED & LOCKED 11-SEP-2016 10:33:25 11-SEP-2016 10:33:25
APEX_PUBLIC_USER YES OPEN 05-OCT-2016 15:12:32
APPQOSSYS YES EXPIRED & LOCKED 11-SEP-2016 08:54:34 11-SEP-2016 08:54:34
AUDSYS YES EXPIRED & LOCKED 11-SEP-2016 08:40:57 11-SEP-2016 08:40:57
CTXSYS YES EXPIRED & LOCKED 11-SEP-2016 09:49:25 11-SEP-2016 09:49:25
DBSNMP YES EXPIRED & LOCKED 11-SEP-2016 08:54:32 11-SEP-2016 08:54:32
DIP YES EXPIRED & LOCKED 11-SEP-2016 08:44:33 11-SEP-2016 08:44:33
DVF YES EXPIRED & LOCKED 11-SEP-2016 11:15:19 11-SEP-2016 11:15:19
DVSYS YES EXPIRED & LOCKED 11-SEP-2016 11:13:42 11-SEP-2016 11:13:42
FLOWS_FILES YES EXPIRED & LOCKED 11-SEP-2016 10:33:25 11-SEP-2016 10:33:25
GSMADMIN_INTERNAL YES EXPIRED & LOCKED 11-SEP-2016 08:44:25 11-SEP-2016 08:44:25
GSMCATUSER YES EXPIRED & LOCKED 11-SEP-2016 08:58:54 11-SEP-2016 08:58:54
GSMUSER YES EXPIRED & LOCKED 11-SEP-2016 08:44:25 11-SEP-2016 08:44:25
LBACSYS YES EXPIRED & LOCKED 11-SEP-2016 11:15:19 11-SEP-2016 11:15:19
MDDATA YES EXPIRED & LOCKED 11-SEP-2016 11:15:19 11-SEP-2016 11:15:19
MDSYS YES EXPIRED & LOCKED 11-SEP-2016 09:49:50 11-SEP-2016 09:49:50
OJVMSYS YES EXPIRED & LOCKED 11-SEP-2016 09:22:55 11-SEP-2016 09:22:55
OLAPSYS YES EXPIRED & LOCKED 11-SEP-2016 10:05:58 11-SEP-2016 10:05:58
ORACLE_OCM YES EXPIRED & LOCKED 11-SEP-2016 08:46:09 11-SEP-2016 08:46:09
ORDDATA YES EXPIRED & LOCKED 11-SEP-2016 09:49:49 11-SEP-2016 09:49:49
ORDPLUGINS YES EXPIRED & LOCKED 11-SEP-2016 09:49:49 11-SEP-2016 09:49:49
ORDSYS YES EXPIRED & LOCKED 11-SEP-2016 09:49:49 11-SEP-2016 09:49:49
OUTLN YES EXPIRED & LOCKED 11-SEP-2016 08:41:00 11-SEP-2016 08:41:00
SI_INFORMTN_SCHEMA YES EXPIRED & LOCKED 11-SEP-2016 09:49:49 11-SEP-2016 09:49:49
SPATIAL_CSW_ADMIN_USR YES EXPIRED & LOCKED 11-SEP-2016 10:18:44 11-SEP-2016 10:18:44
SPATIAL_WFS_ADMIN_USR YES EXPIRED & LOCKED 11-SEP-2016 10:18:38 11-SEP-2016 10:18:38
SYS YES OPEN 02-OCT-2016 22:10:47
SYSBACKUP YES OPEN 23-NOV-2016 19:18:53
SYSDG YES OPEN 01-DEC-2016 10:03:46
SYSKM YES EXPIRED & LOCKED 11-SEP-2016 08:40:57 11-SEP-2016 08:40:57
SYSTEM YES OPEN 02-OCT-2016 22:10:47
WMSYS YES EXPIRED & LOCKED 11-SEP-2016 09:12:11 11-SEP-2016 09:12:11
XDB YES OPEN 05-OCT-2016 15:19:49
XS$NULL YES EXPIRED & LOCKED 11-SEP-2016 08:45:34 11-SEP-2016 08:45:34
SELECT username, default_tablespace, temporary_tablespace, created, profile, editions_enabled
FROM dba_users
ORDER BY 1;
SQL> SELECT username, default_tablespace, temporary_tablespace, created, profile, editions_enabled
2 FROM dba_users
3 ORDER BY 1;
USERNAME DEFAULT TEMP CREATED EDITIONS E
------------------------- ------- ---- -------------------- -------- -
ANONYMOUS SYSAUX TEMP 11-SEP-2016 08:54:43 DEFAULT N
APEX_040200 SYSAUX TEMP 11-SEP-2016 10:29:19 DEFAULT N
APEX_PUBLIC_USER USERS TEMP 11-SEP-2016 10:29:19 DEFAULT N
APPQOSSYS SYSAUX TEMP 11-SEP-2016 08:54:34 DEFAULT N
AUDSYS USERS TEMP 11-SEP-2016 08:40:57 DEFAULT N
CTXSYS SYSAUX TEMP 11-SEP-2016 09:47:42 DEFAULT N
DBSNMP SYSAUX TEMP 11-SEP-2016 08:54:32 DEFAULT N
DIP USERS TEMP 11-SEP-2016 08:44:33 DEFAULT N
DVF SYSAUX TEMP 11-SEP-2016 11:12:47 DEFAULT N
DVSYS SYSAUX TEMP 11-SEP-2016 11:12:47 DEFAULT N
FLOWS_FILES SYSAUX TEMP 11-SEP-2016 10:29:19 DEFAULT N
GSMADMIN_INTERNAL SYSAUX TEMP 11-SEP-2016 08:44:25 DEFAULT N
GSMCATUSER USERS TEMP 11-SEP-2016 08:58:54 DEFAULT N
GSMUSER USERS TEMP 11-SEP-2016 08:44:25 DEFAULT N
LBACSYS SYSTEM TEMP 11-SEP-2016 10:28:35 DEFAULT N
MDDATA USERS TEMP 11-SEP-2016 10:07:47 DEFAULT N
MDSYS SYSAUX TEMP 11-SEP-2016 09:49:50 DEFAULT N
OJVMSYS USERS TEMP 11-SEP-2016 09:22:54 DEFAULT N
OLAPSYS SYSAUX TEMP 11-SEP-2016 10:05:58 DEFAULT N
ORACLE_OCM USERS TEMP 11-SEP-2016 08:46:09 DEFAULT N
ORDDATA SYSAUX TEMP 11-SEP-2016 09:49:49 DEFAULT N
ORDPLUGINS SYSAUX TEMP 11-SEP-2016 09:49:49 DEFAULT N
ORDSYS SYSAUX TEMP 11-SEP-2016 09:49:49 DEFAULT N
OUTLN SYSTEM TEMP 11-SEP-2016 08:41:00 DEFAULT N
SI_INFORMTN_SCHEMA SYSAUX TEMP 11-SEP-2016 09:49:49 DEFAULT N
SPATIAL_CSW_ADMIN_USR USERS TEMP 11-SEP-2016 10:18:44 DEFAULT N
SPATIAL_WFS_ADMIN_USR USERS TEMP 11-SEP-2016 10:18:38 DEFAULT N
SYS SYSTEM TEMP 11-SEP-2016 08:40:57 DEFAULT N
SYSBACKUP USERS TEMP 11-SEP-2016 08:40:57 DEFAULT N
SYSDG USERS TEMP 11-SEP-2016 08:40:57 DEFAULT N
SYSKM USERS TEMP 11-SEP-2016 08:40:57 DEFAULT N
SYSTEM SYSTEM TEMP 11-SEP-2016 08:40:57 DEFAULT N
WMSYS SYSAUX TEMP 11-SEP-2016 09:12:11 DEFAULT N
XDB SYSAUX TEMP 11-SEP-2016 08:54:43 DEFAULT N
XS$NULL USERS TEMP 11-SEP-2016 08:45:34 DEFAULT N
SQL> SELECT COUNT(*)
2 FROM dba_objects
3 WHERE object_name LIKE 'CDB%';
COUNT(*)
----------
1963
Common Users and Roles
Create Common User
conn / as sysdba
CREATE USER c##abc IDENTIFIED BY abc;
ALTER SESSION SET CONTAINER = PDB$SEED;
sho con_name
SELECT username
FROM dba_users
WHERE username LIKE 'C##%';
ALTER SESSION SET CONTAINER = PDBDEV;
sho con_name
SELECT username
FROM dba_users
WHERE username LIKE 'C##%';
SQL> DROP USER c##abc;
DROP USER c##abc
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
Create Common Role
conn / as sysdba
CREATE ROLE c##mlib;
ALTER SESSION SET CONTAINER = PDB$SEED;
sho con_name
SELECT role
FROM dba_roles
WHERE role LIKE 'C##%';
SELECT *
FROM cdb_roles
WHERE role LIKE 'C##%';
ALTER SESSION SET CONTAINER = PDBDEV;
sho con_name
SELECT role
FROM dba_roles
WHERE role LIKE 'C##%';
SELECT *
FROM cdb_roles
WHERE role LIKE 'C##%';
sho user
SQL> DROP ROLE c##mlib;
DROP ROLEe c##mlib
*
ERROR at line 1:
ORA-65050: Common DDLs only allowed in CDB$ROOT
Queries
PDB Related Functions
conn / as sysdba
ALTER SESSION SET CONTAINER = PDB$SEED;
sho con_name
SQL> SELECT con_id, dbid, con_uid, guid
2 FROM v$pdbs;
CON_ID DBID CON_UID GUID
---------- ---------- ---------- --------------------------------
2 911226043 911226043 CB88F618DED84CEE885718FD49F56CC4
SQL> SELECT con_dbid_to_id (911226043)
2 FROM dual;
CON_DBID_TO_ID(911226043)
--------------------------
2
SQL> SELECT con_name_to_id ('PDB$SEED')
2 FROM dual;
CON_NAME_TO_ID('PDB$SEED')
--------------------------
2
SQL> SELECT con_uid_to_id (911226043)
2 FROM dual;
CON_UID_TO_ID(911226043)
-------------------------
2
-- the function con_guid_to_id appears to be non-functional as seen below
SQL> SELECT con_guid_to_id('CB88F618DED84CEE885718FD49F56CC4')
2 FROM dual;
CON_GUID_TO_ID('CB88F618DED84CEE885718FD49F56CC4')
--------------------------------------------------
SQL> SELECT con_guid_to_id(CB88F618DED84CEE885718FD49F56CC4)
2 FROM dual;
SELECT con_guid_to_id(CB88F618DED84CEE885718FD49F56CC4)
*
ERROR at line 1:
ORA-00972: identifier is too long