Oracle | DBMS

Oracle DBMS_SQLQ 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 This package provides the API for configuring quarantine thresholds for SQL statements and their execution plans. SQL statements that cross the quarantine thresholds are terminated and quarantined. The quarantined SQL statements are not allowed to run again in a database.

Oracle Licensing Note: Use of this PL/SQL package is only permitted on ExaCC and ExaCS.
As tempting as it may be ... do not deploy this capability in any other environment until Oracle makes doing so legal.

AUTHID CURRENT_USER

Constants

Name	Data Type	Value
General
ALWAYS_QUARANTINE	VARCHAR2(30)	'4294967295'
DROP_THRESHOLD	VARCHAR2(30)	'0'

Dependencies

DBMS_SMB	DBMS_SQLCONTROL_LIB	DBMS_SYS_ERROR
DBMS_SMB_INTERNAL	DBMS_SQLQ_INTERNAL

Documented Yes

Exceptions

Error Code	Reason
ORA-06564	Object does not exist
ORA-13825	missing SQL statement text
ORA-31684	Object type SQL Quarantine already exists
ORA-38133	Invalid parameter value specified
ORA-38134	Invalid parameter value specified

First Available 19.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC.

The ADMINISTER SQL MANAGEMENT OBJECT privilege is required.

The grant to PUBLIC is horrifying. Yes the Admin privilege is required but puts this power into the hands of many users that have no business having the ability to perform a DDOS attack.

Source {ORACLE_HOME}/rdbms/admin/dbmssqlq.sql

Subprograms

ALTER_QUARANTINE	CREATE_STGTAB_QUARANTINE	PACK_STGTAB_QUARANTINE
CREATE_QUARANTINE_BY_SQL_ID	DROP_QUARANTINE	UNPACK_STGTAB_QUARANTINE
CREATE_QUARANTINE_BY_SQL_TEXT	GET_PARAM_VALUE_QUARANTINE

ALTER_QUARANTINE

Alters the quarantine configuration

dbms_sqlq.alter_quarantine(

      quarantine_name IN VARCHAR2, 

      parameter_name  IN VARCHAR2,

      parameter_value IN VARCHAR2);

      

      
        
          Parameter Names
        
        
          'AUTOPURGE'
        
        
          'CPU_TIME'
        
        
          'ELAPSED_TIME'
        
        
          'ENABLED'
        
        
          'IO_LOGICAL'
        
        
          'IO_REQUESTS'
        
        
          'IO_MEGABYTES'

exec dbms_sqlq.alter_quarantine('TOO_MUCH_DATA', 'IO_MEGABYTES', '1000');

CREATE_QUARANTINE_BY_SQL_ID

Creates a SQL quarantine configuration for storing quarantine information using SQL id dbms_sqlq.create_quarantine_by_sql_id( sql_id IN VARCHAR2, plan_hash_value IN NUMBER DEFAULT NULL) RETURN VARCHAR2;

SELECT dbms_sqlq.create_quarantine_by_sql_id('94qn6y14kw01g', 
      NULL)

      FROM dual;

      

      

      SELECT dbms_sqlq.create_quarantine_by_sql_id('d1071nwwx40fg', 970064335)

      FROM dual;

CREATE_QUARANTINE_BY_SQL_TEXT

Creates a SQL quarantine configuration for storing quarantine information using SQL text dbms_sqlq.create_quarantine_by_sql_text( sql_text IN CLOB, plan_hash_value IN NUMBER DEFAULT NULL) RETURN VARCHAR2;

DECLARE

       sqltxt CLOB := 'TRUNCATE TABLE servers';

      BEGIN

        retVal := dbms_sqlq.create_quarantine_by_sql_text(sqltxt);

        dbms_output.put_line(retVal);

      END;

      /

CREATE_STGTAB_QUARANTINE

Creates a staging table to pack SQL quarantine configurations dbms_sqlq.create_stgtab_quarantine( staging_table_name IN VARCHAR2, staging_table_owner IN VARCHAR2 DEFAULT NULL, tablespace_name IN VARCHAR2 DEFAULT NULL);

exec dbms_sqlq.create_stgtab_quarantine('UWQUARANTINE', 'UWCLASS', 'UWDATA');

DROP_QUARANTINE

Drops a quarantine configuration dbms_sqlq.drop_quarantine(quarantine_name IN VARCHAR2);

exec dbms_sqlq.drop_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');

GET_PARAM_VALUE_QUARANTINE

Returns the value of the quarantine parameter for the given SQL quarantine configuration dbms_sqlq.get_param_value_quarantine( quarantine_name IN VARCHAR2, parameter_name IN VARCHAR2) RETURN VARCHAR2;

SELECT dbms_sqlq.get_param_value_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4', 'IO_MEGABYTES')

      FROM dual;

PACK_STGTAB_QUARANTINE

Packs SQL quarantine configurations into a staging table dbms_sqlq.pack_stgtab_quarantine( staging_table_name IN VARCHAR2, staging_table_owner IN VARCHAR2 DEFAULT NULL, name IN VARCHAR2 DEFAULT '%', sql_text IN VARCHAR2 DEFAULT '%', enabled IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;

SELECT dbms_sqlq.pack_stgtab_quarantine('UW_QSTAGING', 'UWCLASS')

      FROM dual;

UNPACK_STGTAB_QUARANTINE

Unpacks SQL quarantine configurations from a staging table dbms_sqlq.unpack_stgtab_quarantine( staging_table_name IN VARCHAR2, staging_table_owner IN VARCHAR2 DEFAULT NULL, name IN VARCHAR2 DEFAULT '%', sql_text IN VARCHAR2 DEFAULT '%', enabled IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;

SELECT dbms_sqlq.unpack_stgtab_quarantine('UW_QSTAGING', 'UWCLASS')

      FROM dual;