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.
Purpose
Provides an interface to examine and manipulate data about pluggable databases.
AUTHID
CURRENT_USER
Constants
Name
Data Type
Value
Rule ID
V_FEAT_INV
NUMBER
0
V_FEAT_ALL
NUMBER
1
V_FEAT_NETWORK_ACCESS
NUMBER
2
V_FEAT_COMMON_SCHEMA_ACCESS
NUMBER
3
V_FEAT_TCP
NUMBER
4
V_FEAT_HTTP
NUMBER
5
V_FEAT_SMTP
NUMBER
6
V_FEAT_INADDR
NUMBER
7
V_FEAT_JDWP
NUMBER
8
V_FEAT_XDB_PROTOCOLS
NUMBER
9
V_FEAT_JAVA
NUMBER
10
V_FEAT_CTX_PROTOCOLS
NUMBER
11
V_FEAT_OS_ACCESS
NUMBER
12
V_FEAT_FILE
NUMBER
13
V_FEAT_EXTPROC
NUMBER
14
V_FEAT_JAVA_OS_ACCESS
NUMBER
15
V_FEAT_JAVA_RUNTIME
NUMBER
16
V_FEAT_TRACE_VIEWS
NUMBER
17
V_FEAT_AQ_PROTOCOLS
NUMBER
18
V_FEAT_EXT_GLB_AUTH
NUMBER
19
V_FEAT_SECURITY_POLICIES
NUMBER
20
V_FEAT_CONNECTIONS
NUMBER
21
V_FEAT_LOCSYSOPERRESMOD
NUMBER
22
V_FEAT_COMUSERCONN
NUMBER
23
V_FEAT_AWR_ACCESS
NUMBER
24
V_FEAT_LUSER_CSCHEMA_ACCESS
NUMBER
25
V_FEAT_CUSER_LSCHEMA_ACCESS
NUMBER
26
V_FEAT_DROP_TS_KEEP_DATAFILES
NUMBER
27
V_FEAT_EXT_FILE_ACCESS
NUMBER
28
V_FEAT_CTX_LOGGING
NUMBER
29
V_FEAT_LOB_FILE
NUMBER
30
V_FEAT_ADR_ACCESS
NUMBER
31
V_FEAT_FILE_TRANSFER
NUMBER
32
V_FEAT_XDB_DEPRECATED
NUMBER
33
V_FEAT_SYSDATA
NUMBER
34
V_FEAT_COMUSERCONN_LOCALSVC
NUMBER
35
V_FEAT_USR_PASS_VERIFIER
NUMBER
36
V_FEAT_XDB_REPOSITORY
NUMBER
37
V_FEAT_PROTECTED_SCHEMA_ACCESS
NUMBER
38
V_FEAT_USER_PROFILE_SENSITIVE
NUMBER
39
V_FEAT_MAX
NUMBER
40
V_ALTER_SESSION
NUMBER
42
V_ALTER_SYSTEM
NUMBER
49
Rule Type
V_STATEMENT
NUMBER
1
V_OPTION
NUMBER
2
V_FEATURE
NUMBER
3
Dependencies
DBMS_PDB_ALTER_SHARING
DBMS_PDB_LIB
DBMS_SQLDIAG
DBMS_PDB_CHECK_LOCKDOWN
DBMS_PLUGTS
DBMS_STATS_INTERNAL
DBMS_PDB_EXEC_SQL
DBMS_PREPLUGIN_BACKUP
SDO_GEOR_DEF
DBMS_PDB_IS_VALID_PATH
Documented
Yes
Exceptions
Error Code
Reason
ORA-65026
XML metadata file error : LPX-00202: could
not open "<pdb_describe_file_location.xml" (error 0)
ORA-65046
Operation not allowed from outside a pluggable database
ORA-65160
Invalid cleanup task ID
First Available
12.1
Security Model
Owned by SYS with EXECUTE granted to MDSYS and EXECUTE_CATALOG_ROLE role. The script catqm_int.sql can grant EXECUTE to XDB if run.
BEGIN
IF dbms_pdb.check_plug_compatibility('/home/oracle/pdbdev.xml', 'PDBDEV') THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/
col time format a30
col name format a12
col cause format a10
col type format a8
col action format a50
col message format a95
col con_id format 999
SELECT time, name, type, message
FROM pdb_plug_in_violations;
TIME NAME TYPE MESSAGE
------------------------------ --------- -------- -----------------------------------------------------
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR Database option APS mismatch: PDB installed version
21.0.0.0.0. CDB installed version NULL.
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR Database option XOQ mismatch: PDB installed version
21.0.0.0.0. CDB installed version NULL.
17-JAN-21 02.22.09.504732 AM PDB$SEED WARNING Database option RAC mismatch: PDB installed version NULL.
CDB installed version 21.0.0.0.0.
17-JAN-21 02.22.13.427033 AM TEST21P1 WARNING Database option RAC mismatch: PDB installed version NULL.
CDB installed version 21.0.0.0.0.
17-JAN-21 02.22.13.724639 AM TEST21P1 WARNING Tablespace SYSTEM is not encrypted.
Oracle Cloud mandates all tablespaces should be encrypted.
17-JAN-21 02.22.13.727556 AM TEST21P1 WARNING Tablespace SYSAUX is not encrypted.
Oracle Cloud mandates all tablespaces should be encrypted.
SELECT time, name, type, status, action
FROM pdb_plug_in_violations;
TIME NAME TYPE STATUS ACTION
------------------------------ --------- -------- --------- --------------------------------------------
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR RESOLVED Fix the database option in the PDB or the CDB
11-DEC-20 06.16.54.684970 PM PDB$SEED ERROR RESOLVED Fix the database option in the PDB or the CDB
17-JAN-21 02.22.09.504732 AM PDB$SEED WARNING PENDING Fix the database option in the PDB or the CDB
17-JAN-21 02.22.13.427033 AM TEST21P1 WARNING PENDING Fix the database option in the PDB or the CDB
17-JAN-21 02.22.13.724639 AM TEST21P1 WARNING PENDING Encrypt the tablespace.
17-JAN-21 02.22.13.727556 AM TEST21P1 WARNING PENDING Encrypt the tablespace.
Performs cleanup task previously performed by SMON
-- returns 0 if the next scheduled time for the job does not need to be changed. N if the next schedule job should be N seconds from now (not my experience as shown below)
dbms_pdb.cleanup_task(task_id IN NUMBER) RETURN NUMBER;
SELECT dbms_pdb.cleanup_task(153)
FROM dual;
*
ERROR at line 1:
ORA-65160: invalid cleanup task ID
ORA-06512: at "SYS.DBMS_PDB", line 85
dbms_pdb.convert_to_local(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
object_subname IN VARCHAR2 DEFAULT NULL,
sig_mismatch IN BOOLEAN DEFAULT FALSE);
Use only in the ADW cloud where some users/roles, which should have been Oracle maintained, was not and needs to be converted in an existing PDB
Note: This could be extraordinarily dangerous with patching. Do not use this procedure.
dbms_pdb.convert_to_oracle_maintained(user_name IN VARCHAR2);
CREATE USER c##convert NO AUTHENTICATION;
User created.
SELECT oracle_maintained
FROM dba_users
WHERE username = 'C##CONVERT';
O
-
N
exec dbms_pdb.convert_to_oracle_maintained('C##CONVERT');
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_PDB", line 575
ORA-06512: at line 1
-- A decision has been made not to publish the
workaround
This procedure enables execution of certain restricted statements (most DDLs) on metadata-linked objects, from within a PDB
Note: This procedure is called by the package initialization section.
This is accomplished by running the statement as an oracle script (i.e. with the parameter _oracle_script set to TRUE). This is for use by Oracle internal packages only.
dbms_pdb.exec_as_oracle_script(sql_stmt IN VARCHAR2);
Exports RMAN backup information that belong to the pluggable database to its dictionary before unplug so that pre-plugin backups can be used. The pluggable database has to be opened in read write mode.
If the database is non-cdb, then pdb_name must be omitted. If the pdb_name is omitted, then the pluggable database to which the session is connected will be exported. If the pdb_name is omitted, and the session is connected to the Root, an error is returned.
dbms_pdb.exportRMANbackup(pdb_name IN VARCHAR2 DEFAULT NULL);
Generates Partial Archive Logs for all non-spooled online logs
dbms_pdb.generate_partial_logs(
partial_log_dest IN VARCHAR2,
remote_dblink IN VARCHAR2 DEFAULT NULL,
include_fralogs IN BOOLEAN DEFAULT FALSE,
copy_fralogs IN BOOLEAN DEFAULT TRUE,
copy_dop IN NUMBER DEFAULT 16,
force_copy_logs IN BOOLEAN DEFAULT FALSE);
Checks whether given path_name is corresponding to the path_prefix property
dbms_pdb.is_valid_path(path_name IN VARCHAR2) RETURN BOOLEAN;
BEGIN
IF dbms_pdb.is_valid_path('/u04/app/oracle/oradata/orabase/orabase/uwapp/') THEN
dbms_output.put_line('T');
ELSE
dbms_output.put_line('F');
END IF;
END;
/ F
Generates an XML file describing a pluggable database by using data files belonging to the database. The XML file can be used to plug the PDB into a CDB using CREATE PLUGGABLE DATABASE.
dbms_pdb.recover(
pdb_descr_file IN VARCHAR2,
pdb_name IN VARCHAR2,
filenames IN VARCHAR2);
SQL> exec dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf');
BEGIN dbms_pdb.recover('c:\tmp\orabase_desc.xml', 'ORABASE1', 'c\app\oracle\oradata\orabase\orabase1\orabase1_temp.dbf'); END;
*
ERROR at line 1:
ORA-65128: PDB recover data file name not specified
ORA-06512: at "SYS.DBMS_PDB", line 104
ORA-06512: at line 1
Used to mark an object as Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_data_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Use to mark an object as Extended Data linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, and where there was no support for application containers
dbms_pdb.set_ext_data_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Used to mark an object as Metadata linked in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_metadata_linked(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL)
Used to mark a profile as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the profiles would have been marked as implicit Application Common profile.
dbms_pdb.set_profile_explicit(profile_name IN VARCHAR2);
BEGIN
dbms_pdb.set_profile_explicit('MATRIX_PROFILE');
END;
/
Used to mark a role as an explicit Application Common role. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the roles would have been marked as implicit Application Common roles.
dbms_pdb.set_role_explicit(role_name IN VARCHAR2);
BEGIN
dbms_pdb.set_role_explicit('MATRIX_RO');
END;
/
Used to set SHARTING=NONE status on an object in an App Root. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
dbms_pdb.set_sharing_none(
schema_name IN VARCHAR2,
object_name IN VARCHAR2,
namespace IN NUMBER,
edition_name IN VARCHAR2 DEFAULT NULL);
Used to mark a user as an explicit Application Common user. It is intended to be used in migration cases where an application was already installed in a PDB or a non-CDB, where there was no support for application containers.
When such a PDB or non-CDB is converted into an Application Root via clone or plugin, the users would have been marked as implicit Application Common users.
dbms_pdb.set_user_explicit(user_name IN VARCHAR2);
BEGIN
dbms_pdb.set_user_explicit('MATRIX');
END;
/
Updates CDB View Stats and returns 0 if the next scheduled job change time does not need to be changed: N if the next scheduled time should be N seconds from SYSDATE
dbms_pdb.update_cdbvw_stats RETURN NUMBER;
set timing on
SELECT dbms_pdb.update_cdbvw_stats
FROM dual;
UPDATE_CDBVW_STATS
------------------
3600
Elapsed: 00:04:27.10
set timing off
-- will appear to hang the system for a very long time while stats are collected
