Oracle OWM_DYNSQL

Oracle OWM_DYNSQL_ACCESS Version 19c

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.

Purpose Note: This page has been posted prior to the GA release of 20c. It will be completed once we have access to the release version.

AUTHID DEFINER

Data Types CREATE OR REPLACE NONEDITIONABLE TYPE wmsys.wm$exp_map_type AUTHID DEFINER AS OBJECT( code INTEGER, nfield1 NUMBER, nfield2 NUMBER, nfield3 NUMBER, vfield1 VARCHAR2(128), vfield2 VARCHAR2(128), vfield3 CLOB); / CREATE OR REPLACE NONEDITIONABLE TYPE wmsys.wm$lock_info_type AUTHID DEFINER AS OBJECT( table_owner VARCHAR2(128), table_name VARCHAR2(128), info VARCHAR(100)); / CREATE OR REPLACE NONEDITIONABLE TYPE wmsys.wm$table_type AS TABLE OF wmsys.wm$lock_info_type; /

Dependencies

ALL_WM_CONSTRAINT_VIOLATIONS	NO_VM_DROP_A	WM$ALL_LOCKS_VIEW
ALL_WM_LOCKED_TABLES	ORA_DICT_OBJ_TYPE	WM$EXP_MAP_TAB
ALL_WM_VERSIONED_TABLES	OWM_ASSERT_PKG	WM$EXP_MAP_TYPE
DBA_WM_VERSIONED_TABLES	OWM_BULK_LOAD_PKG	WM$LOCK_INFO_TYPE
DBA_WORKSPACE_SESSIONS	OWM_CPKG_PKG	WM$LOCK_TABLE_TYPE
LT	OWM_DDL_PKG	WM$OPER_LOCKVALUES_ARRAY_TYPE
LTADM	OWM_IEXP_PKG	WM$OPER_LOCKVALUES_TYPE
LTDDL	OWM_MIG_PKG	WM$RIC_TABLE$
LTDTRG	OWM_MP_PKG	WM$VERSIONED_TABLES
LTRIC	PLITBLM	WM$WORKSPACE_SESSIONS_VIEW
LTUTIL	UD_TRIGS	WM_COMPRESS_BATCH_SIZES
LT_CTX_PKG	USER_WM_LOCKED_TABLES	WM_DDL_UTIL
NO_VM_DDL	USER_WM_VERSIONED_TABLES	WM_ERROR

Documented No

Exceptions

Error Code	Reason
ORA-20122	workspace <workspace_name> does not exist
ORA-20235	invalid system parameter name or value

First Available 19.1

Security Model Owned by WMSYS with no privileges granted.

It appears likely that some objects within this package are protected from direct execution by means of an ACCESSIBLE BY clause.

Source {ORACLE_HOME}/rdbms/admin/

Subprograms

ALLOWDDLOPERATION	CREATEUNIONBASEVIEW	NO_VM_CREATE_PROC
ARETHERECONFLICTS	CREATEVTTABLE	NO_VM_DROP_PROC
ARETHEREDIFFS	DROPRICLOCKINGTABLES	NULL_TABLE_FUNC
CHECKWHERECLAUSE	EXECSQLFROMCLOB	OPERCOUNT
CLEANUPMETADATABYUSER	EXISTCONFLICTS	PREFIXSTR
CLEANUPSTALEMETADATA 6	FIXOWMCOLSWHIST	RECREATEINSTOFTRIGS
COMPAREINDEXPROPERTIES	GETBATCHWHERECLAUSES	RECREATEPTAFTERTRIGS
COMPUTERICWEIGHT	GETDISPATCHERINFO	RECREATEVIEWS
CREATEBASEVIEW	GETNCA	REFRESHVERSINWSPCLIST
CREATECONFLICTVIEW	GETRICINSTEADOFTRIGSTRS	REGENUDTRIGDISPATCHER
CREATECONSVIEW	GETRICSESSLOCKSFORTABLE	REGENUDTRIGPROCS
CREATEDIFFVIEW	GETSPVERSION	RELEASELOCK
CREATEHISTORYVIEW	GETSTATELOCKID	RELRICSESSLOCKSFORTABLE
CREATEINLISTFROMQUERY	GETSYSTEMPARAMETER	REMOVEDOUBLEQUOTESINUSERNAME
CREATELCKTABLE	GETUCDETAILS	RETURNCONSTRAINTVIOLATIONS
CREATELOCKVIEW	GETUDHINT	RIC_ENABLE_VERSIONING_INTERNAL
CREATEMWVIEW	GETVARIABLE_B	RIC_TOPO_TABLE_REFS
CREATENESTEDTABLEBASEVIEW	GETVARIABLE_N	SETEVCHECKPOINT
CREATENESTEDTABLECONSVIEW	GET_LOCK_TABLE	SETIMPORTVARS
CREATEPACKAGE	HAVEIDENTICALCOLUMNS	SETLTLOCKINFO
CREATERICLOCKINGTABLES	IMPORT	TRF_UDTRGS_ON_VER_ENABLE
CREATERICVIEWS	ISMODIFIEDINSUBTREE	UNSETIMPORTVARS
CREATETOPVIEW	LOCKRICROWS	WMFLAGENCODE
CREATETRIGGERS	NO_VM_ALTER_PROC

ALLOW_DDL_OPERATION (new 19c)

Undocumented owm_dynsql_access.allowDDLOperation(status IN VARCHAR2);

exec wmsys.owm_dynsql_access.allowDDLOperation('STOP');

      

      PL/SQL procedure successfully completed.

CLEANUPMETADATABYUSER (new 19c)

Cleans up metadata by schema owm_dynsql_access.cleanupMetadataByUser(schema IN VARCHAR2);

exec wmsys.owm_dynsql_access.cleanupMetadataByUser('WMSYS');

      

      PL/SQL procedure successfully completed.

CLEANUPSTALEMETADATA (new 19c)

Cleans up all stale metadata owm_dynsql_access.cleanupStaleMetadata;

exec wmsys.owm_dynsql_access.cleanupStaleMetadata;

      

      PL/SQL procedure successfully completed.

CREATEPACKAGE (new 19c)

Undocumented

Likely protected by an ACCESSIBLE BY clause owm_dynsql_access.createPackage( OBJ_OWNER_V IN VARCHAR2, OBJ_NAME_V IN VARCHAR2, BUFFERCODE IN BOOLEAN);

conn sys@pdbdev as sysdba

      

      exec wmsys.owm_dynsql_access.createPackage('UWCLASS', 'ODACP');

      BEGIN wmsys.owm_dynsql_access.createPackage('UWCLASS', 'ODACP'); END;

      *

      ERROR at line 1:

      ORA-20289: insufficient privileges on WMSYS owned package

      ORA-06512: at "WMSYS.WM_ERROR", line 449

      ORA-06512: at "WMSYS.WM_ERROR", line 463

      ORA-06512: at line 2

      ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215

      ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 27

      ORA-06512: at "WMSYS.OWM_CPKG_PKG", line 11

      ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 399

      ORA-06512: at line 1

CREATERICVIEWS (new 19c)

Undocumented

Likely protected by an ACCESSIBLE BY clause owm_dynsql_access.createRICViews( TABLE_OWNER_V IN VARCHAR2, TABLE_NAME_V IN VARCHAR2);

conn sys@pdbdev as sysdba

      

      exec wmsys.owm_dynsql_access.createRICViews('UWCLASS', 'ODACP');

      BEGIN 
      wmsys.owm_dynsql_access.createRICViews('UWCLASS', 'ODACP'); END;

      *

      ERROR at line 1:

      ORA-20289: insufficient privileges on WMSYS owned package

      ORA-06512: at "WMSYS.WM_ERROR", line 449

      ORA-06512: at "WMSYS.WM_ERROR", line 463

      ORA-06512: at line 2

      ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215

      ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 27

      ORA-06512: at "WMSYS.OWM_DDL_PKG", line 2701

      ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 411

      ORA-06512: at line 1

EXECSQLFROMCLOB (new 19c)

Undocumented

Likely protected by an ACCESSIBLE BY clause owm_dynsql_access.execSQLfromCLOB( CSTR IN CLOB, COMPRESS_FLAG IN BOOLEAN);

DECLARE

       c CLOB := 'CREATE TABLE t (col DATE)';

      BEGIN

        wmsys.owm_dynsql_access.execSQLFromClob(c, TRUE);

      END;

      /

      DECLARE

      *

      ERROR at line 1:

      ORA-20289: insufficient privileges on WMSYS owned package

      ORA-06512: at "WMSYS.WM_ERROR", line 449

      ORA-06512: at "WMSYS.WM_ERROR", line 463

      ORA-06512: at line 2

      ORA-06512: at "WMSYS.OWM_ASSERT_PKG", line 215

      ORA-06512: at "WMSYS.LTADM", line 8242

      ORA-06512: at "WMSYS.OWM_DYNSQL_ACCESS", line 479

      ORA-06512: at line 5

GETSTATELOCKID (new 19c)

Undocumented

The "state" parameter appears to be the name of a workspace owm_dynsql_access.getStateLockID(state IN VARCHAR2) RETURN NUMBER;

SELECT workspace

      FROM dba_workspaces;

      

      WORKSPACE

      ---------

      LIVE

      

      SELECT wmsys.owm_dynsql_access.getStateLockID('LIVE')

      FROM dual;

      

      WMSYS.OWM_DYNSQL_ACCESS.GETSTATELOCKID('LIVE')

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

                                                   0

GETSYSTEMPARAMETER (new 19c)

Returns the value of a Workspace Manager parameter

Workspace Manager parameters are defined in the LT (dbms_wm) package owm_dynsql_access.getSystemParameter(p_name IN VARCHAR2) RETURN VARACHAR2;

SELECT wmsys.owm_dynsql_access.getSystemParameter('ALLOW_CAPTURE_EVENTS')

      FROM dual;

      

      WMSYS.OWM_DYNSQL_ACCESS.GETSYSTEMPARAMETER('ALLOW_CAPTURE_EVENTS')

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

      OFF

GETUCDETAILS (new 19c)

Undocumented owm_dynsql_access.getUCDetails( index_owner_var IN VARCHAR2, index_name_var IN VARCHAR2, table_owner_var IN VARCHAR2, table_name_var IN VARCHAR2) RETURN VARCHAR2;

TBD

GETVARIABLE_B (new 19c)

Undocumented owm_dynsql_access.getVariable_b(varname IN VARCHAR2) RETURN BOOLEAN;

BEGIN

        IF wmsys.owm_dynsql_access.getVariable_b('Morgan') THEN

          dbms_output.put_line('T');

        ELSE

          dbms_output.put_line('F');

        END IF;

      END;

      /

      F

      

      PL/SQL procedure successfully completed.

GETVARIABLE_N (new 19c)

Undocumented owm_dynsql_access.getVariable_n(varname IN VARCHAR2) RETURN NUMBER;

DECLARE

       inVal  VARCHAR2(20) := 'MORGAN';

       retVal NUMBER;

      BEGIN

        SELECT wmsys.owm_dynsql_access.getVariable_n(inVal)

        INTO retVal

        FROM dual;

      

        dbms_output.put_line(retVal);

      END;

      /

      

      PL/SQL procedure successfully completed.

NULL_TABLE_FUNC (new 19c)

Undocumented owm_dynsql_access.null_table_func RETURN wmsys.wm$exp.map_tab;

DECLARE

       emt wmsys.wm$exp_map_tab;

      BEGIN

        SELECT wmsys.owm_dynsql_access.null_table_func

        INTO emt

        FROM dual;

      END;

      /

      

      PL/SQL procedure successfully completed.

OPERCOUNT (new 19c)

Undocumented but suspected to be the count of operations owm_dynsql_access.oper_count(flag IN NUMBER) RETURN NUMBER;

SELECT wmsys.owm_dynsql_access.operCount(1)

      FROM dual;

      

      WMSYS.OWM_DYNSQL_ACCESS.OPERCOUNT(1)

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

                                        39

RELEASELOCK (new 19c)

Releases a lock but which specific lock on what is not documented owm_dynsql_access.releaseLock(lock_id IN NUMBER);

exec wmsys.owm_dynsql_access.releaseLock(1);

      

      PL/SQL procedure successfully completed.

RETURNCONSTRAINTVIOLATIONS (new 19c)

Completes successfully if no constraint violations exist owm_dynsql_access.returnConstraintViolations RETURN wmsys.wm$exp_map_tab;

DECLARE

       rcv wmsys.wm$exp_map_tab;

      BEGIN

        SELECT wmsys.owm_dynsql_access.returnConstraintViolations

        INTO rcv

        FROM dual;

      END;

      /

      

      PL/SQL procedure successfully completed.

SETLTLOCKINFO (new 19c)

Undocumented

Locking modes are defined in the LT (dbms_wm) package owm_dynsql_access.setLTLockInfo(wm_ltlock IN VARCHAR2) RETURN VARCHAR2;

SELECT wmsys.owm_dynsql_access.setLTLockInfo('E')

      FROM dual;

      

      WMSYS.OWM_DYNSQL_ACCESS.SETLTLOCKINFO('E')

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

      E*-1,-9*

UNSETIMPORTVARS (new 19c)

Unsets the value of import variables owm_dynsql_access.unsetImportVars;

exec wmsys.owm_dynsql_access.unsetImportVars;

      

      PL/SQL procedure successfully completed.