DBSecWorx: DBMS

Oracle DBMS_SQLQ Built-In Package Version 19.3

Security Advisory

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.

If a user granted EXECUTE privileges on this package sets the threshold too low the result will be a self-inflicted Denial of Service attack.

Recommended Security Rules

NEVER

Grant EXECUTE on this package to any user unless they are assigned to perform DBA work

WITH GREAT CARE

Revoke EXECUTE from PUB and essentially every other grant created by Oracle replacing them, where necessary with an explicit grant to the specific schema by name

CAUTIONS

If anyone requests EXECUTE on this package consider the implications and determine whether the privilege might more properly be assigned to a designated operations DBA

How Oracle Works

This is, unfortunately, the most recent example of Oracle's development teams not fully appreciating security risk. Here are the details. If you look at the package's security model, below, you will see two things that interconnect. The first is that EXECUTE on this package is granted to PUBLIC. Something we almost always advise be immediately revoked upon install and this package is a good example why we recommend it. SQL> SELECT grantee 2 FROM dba_tab_privs 3 WHERE table_name = 'DBMS_SQLQ'; GRANTEE ------------------------------ PUBLIC 1 row selected. So the grant to PUBLIC is confirmed. Oracle appears to think this is not a big deal because they also require that the ADMINISTER SQL MANAGEMENT OBJECT privilege be granted. But that is a false sense of security. Let's look at why that is the case. SQL> SELECT grantee 2 FROM dba_sys_privs 3 WHERE privilege = 'ADMINISTER SQL MANAGEMENT OBJECT' 4 ORDER BY 1; GRANTEE ------------------------------ DBA EM_EXPRESS_ALL EXP_FULL_DATABASE IMP_FULL_DATABASE SYS SYSUMF_ROLE And let's lok at what this creates. SQL> SELECT DISTINCT grantee 2 FROM dba_role_privs 3 WHERE granted_role IN ( 4 SELECT grantee FROM dba_sys_privs 5 WHERE privilege = 'ADMINISTER SQL MANAGEMENT OBJECT') 6* ORDER BY 1; GRANTEE ------------------------------ DATAPUMP_EXP_FULL_DATABASE DATAPUMP_IMP_FULL_DATABASE DBA ORDSYS SYS SYS$UMF SYSTEM When we add these together we get everyone granted the DBA role, everyone with EM_EXPRESS_ALL, everyone with DATAPUMP FULL roles, ORDSYS, SYS, SYSTEM, SYS$UMF, and everyone granted the SYSUMF role. And every one of them can use DBMS_SQLQ to quarantine SQL and create a loss of service. In a secure environment the only users that has any business having EXECUTE granted to them on this package should be the one or two DBAs that manage the operations production environment. If someone else running DataPump has an issue with a SQL statement that are not happy with they need to take the issue to the DBA manager, get a ticket approved, and have that work performed by the appropriate DBAs. And ORDSYS needs SQL Quarantine ability? Take a look at the following ORDSYS owned objects and explain to us, if you can, why ORDSYS needs to quarantine any SQL statement it wishes. SQL> SELECT object_name 2 FROM dba_objects 3 WHERE owner = 'ORDSYS' 4 AND object_type = 'PACKAGE' 5* ORDER BY 1; OBJECT_NAME ------------------------------ CARTRIDGE IM ORDAUDIOEXCEPTIONS ORDAUDIO_PKG ORDDOCEXCEPTIONS ORDDOC_PKG ORDERROR ORDERRORINT ORDIMAGECONSTANTS ORDIMAGEEXCEPTIONS ORDIMAGESIEXCEPTIONS ORDIMERRORCODES ORDIMGEXTCODEC_PKG ORDIMGSI_PKG ORDIMG_PKG ORDPLSGWYUTIL ORDSOURCEEXCEPTIONS ORDUTIL ORDUTIL_PRV ORDVIDEOEXCEPTIONS ORDVIDEO_PKG ORD_AUDIO ORD_DATASOURCE_PKG ORD_DICOM ORD_DICOM_ADMIN ORD_DICOM_ADMIN_PRV ORD_DICOM_CT ORD_DICOM_PKG ORD_DOC ORD_IMAGE ORD_VIDEO

Please Oracle, try harder.

ACCESSIBLE BY Information

AUTHID CURRENT_USER

Constants

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

Dependencies

DBMS_SMB	DBMS_SQLQ_INTERNAL	DBMS_SYS_ERROR
DBMS_SMB_INTERNAL	DBMS_STANDARD

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 19c

Security Model Owned by SYS with EXECUTE granted to PUBLIC.

The ADMINISTER SQL MANAGEMENT OBJECT privilege is required.

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 (new 19c)

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 (new 19c)

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;

exec dbms_sqlq.create_quarantine_by_sql_id('94qn6y14kw01g', NULL);

      

      exec dbms_sqlq.create_quarantine_by_sql_id('d1071nwwx40fg', 970064335)

CREATE_QUARANTINE_BY_SQL_TEXT (new 19c)

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 (new 19c)

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 (new 19c)

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

exec dbms_sqlq.drop_quarantine('SQL_QUARANTINE_3z0mwuq3aqsm8cfe7a0e4');

GET_PARAM_VALUE_QUARANTINE (new 19c)

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 (new 19c)

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 (new 19c)

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;