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.

Related Topics
Built-in Functions
Built-in Packages
Database Security
OWM_ASSERT_PKG
OWM_CPKG_PKG
OWM_IEXP_PKG
What's New In 19c
What's New In 20c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx