Oracle | POD_CDB_ADMIN

Oracle POD_CDB_ADMIN_UTIL Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Implements helper APIs that must to be installed as common IN an Autonomous Database.

The source for the PL/SQL package CLOUD_LOGGER, owned by C##CLOUD$SERVICE in the Autonomous Database, could not be found so a valid deployment of this package was not possible and the demos below untested.

AUTHID DEFINER

Data Types -- record and collection representing PDB drift TYPE drift_rec IS RECORD ( name VARCHAR(80), value VARCHAR2(4000), default_value VARCHAR2(4000), con_id NUMBER); TYPE drift_rec_tab IS TABLE OF drift_rec;

Dependencies

DATABASE_PROPERTIES	DBMS_PDB_LIB	GV$PDBS
DBA_LOCKDOWN_PROFILES	DBMS_SQL	GV$SYSTEM_PARAMETER
DBMS_ASSERT	DBMS_STANDARD	PLITBLM
DBMS_OUTPUT	DBMS_UTILITY	V$PDBS

Documented No

First Available Not known

Security Model Owned by SYS with no privileges granted

Source {ORACLE_HOME}/rdbms/admin/pod_cdb_admin_util_spec.sql
{ORACLE_HOME}/rdbms/admin/pod_cdb_admin_util.plb

SQL> @?/rdbms/admin/pod_cdb_admin_util_spec.sql

      

      SP2-0808: Package created with compilation warnings

      

      Errors for PACKAGE POD_CDB_ADMIN_UTIL:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      1/1 PLW-05018: unit POD_CDB_ADMIN_UTIL omitted optional AUTHID clause; default value DEFINER used

      5/5 PLW-06010: keyword "NAME" used as a defined name

      6/5 PLW-05004: identifier VALUE is also declared in STANDARD or is a SQL builtin

      6/5 PLW-06010: keyword "VALUE" used as a defined name

      43/6 PLW-07203: parameter 'USERNAME' may benefit from use of the NOCOPY compiler hint

      44/6 PLW-07203: parameter 'PASSWORD' may benefit from use of the NOCOPY compiler hint

      45/6 PLW-06010: keyword "KEY" used as a defined name

      45/6 PLW-07203: parameter 'KEY' may benefit from use of the NOCOPY compiler hint

      50/5 PLW-07203: parameter 'WALLET_LOC' may benefit from use of the NOCOPY compiler hint

      51/5 PLW-07203: parameter 'WALLET_PASSWD' may benefit from use of the NOCOPY compiler hint

      

      SQL> @?/rdbms/admin/pod_cdb_admin_util.plb

      

      Warning: Package Body created with compilation errors.

      

      Errors for PACKAGE BODY POD_CDB_ADMIN_UTIL:

      

      LINE/COL ERROR

      -------- -----------------------------------------------------------------

      55/6 PLW-07203: parameter 'USERNAME' may benefit from use of the NOCOPY compiler hint

      56/6 PLW-07203: parameter 'PASSWORD' may benefit from use of the NOCOPY compiler hint

      57/6 PLW-07203: parameter 'KEY' may benefit from use of the NOCOPY compiler hint

      71/5 PLW-07203: parameter 'WALLET_LOC' may benefit from use of the NOCOPY compiler hint

      72/5 PLW-07203: parameter 'WALLET_PASSWD' may benefit from use of the NOCOPY compiler hint

      87/5 PL/SQL: Statement ignored

      87/5 PLS-00201: identifier 'CLOUD_LOGGER.DEBUG' must be declared

      89/5 PL/SQL: Statement ignored

      89/5 PLS-00201: identifier 'CLOUD_LOGGER.SET_MODULE_NAME' must be declared

      92/7 PL/SQL: Statement ignored

      92/7 PLS-00201: identifier 'CLOUD_LOGGER.ERROR' must be declared

      93/7 PL/SQL: Statement ignored

      93/7 PLS-00201: identifier 'CLOUD_LOGGER.SET_MODULE_NAME' must be declared

      159/7 PL/SQL: Statement ignored

      159/7 PLS-00201: identifier 'CLOUD_LOGGER.DEBUG' must be declared

      419/3 PL/SQL: Statement ignored

      419/3 PLS-00201: identifier 'CLOUD_LOGGER.SET_CLIENT' must be declared

Subprograms

CHECK_PDB_DRIFT	GET_DB_PROP	GET_SSL_WALLET
GET_CREDENTIAL_INFO	GET_QUALIFIED_NAME	MASK_PASSWD
GET_CURRENT_USER	GET_SESSION_USER

CHECK_PDB_DRIFT (new 21c)

This function is RAC sensitive, meaning it can be invoked from a node where the PDB is IN a mount state and is actually opened read-write elsewhere pod_cdb_admin_util.check_pdb_drift RETURN drift_rec_tab PIPELINED;

-- the Oracle doc's example SQL rewritten to be valid

      

      SELECT COUNT(DISTINCT(con_id))

      FROM TABLE(pod_cdb_admin_util.check_pdb_drift);

GET_CREDENTIAL_INFO (new 21c)

Return information for a credential object

This procedures "screams" security risk. Further research will be required. pod_cdb_admin_util.get_credential_info( credential_name IN VARCHAR2, username OUT VARCHAR2, -- username obtained from credential object password OUT VARCHAR2, -- password obtained from credential object key OUT VARCHAR2); -- key attributes obtained from credential object

TBD

GET_CURRENT_USER (new 21c)

Returns the current effective user name pod_cdb_admin_util.get_current_user RETURN VARCHAR2;

SELECT pod_cdb_admin_util.get_current_user

      FROM dual;

GET_DB_PROP (new 21c)

Get the value corresponding to a property name pod_cdb_admin_util.get_db_prop( prop_name IN VARCHAR2, container_name IN VARCHAR2 DEFAULT NULL, container_closed IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;

TBD

GET_QUALIFIED_NAME (new 21c)

Returns the qualified name for an object name with schema name concatenated pod_cdb_admin_util.get_qualified_name( object_name IN VARCHAR2, schema_name IN VARCHAR2, upcase IN BOOLEAN DEFAULT FALSE) RETURN VARCHAR2;

SELECT pod_cdb_admin_util.get_qualified_name('servers','c##uwclass',TRUE)

      FROM dual;

GET_SESSION_USER (new 21c)

Returns the current session's user name pod_cdb_admin_util.get_session_user RETURN VARCHAR2;

SELECT pod_cdb_admin_util.get_session_user

      FROM dual;

GET_SSL_WALLET (new 21c)

Returns SSL Wallet information

This procedures "screams" security risk. Further research will be required. pod_cdb_admin_util.get_ssl_wallet( wallet_loc OUT database_properties.property_value%TYPE, wallet_passwd OUT VARCHAR2);

DECLARE

       wloc database_properties.property_value%TYPE;

       wpwd dbms_id; 

      BEGIN

        pod_cdb_admin_util.get_ssl_wallet(wloc, wpwd);

        dbms_output.put_line(wloc);

        dbms_output.put_line(wpwd);

      END;

      /

MASK_PASSWD (new 21c)

Masks the OUT password field from a SQL statement

Overload 1 pod_cdb_admin_util.mask_passwd( sql_stmt IN CLOB, delimit1 IN CLOB, delimit2 IN CLOB) RETURN CLOB;

TBD

Overloaded version needed for cases where more than one argument may contain the password and requires masking

Overload 2 pod_cdb_admin_util.mask_passwd( ql_stmt IN CLOB, delimit1 IN dbms_sql.clob_table, delimit2 IN dbms_sql.clob_table) RETURN CLOB;

TBD