DBSecWorx: DBMS_SQL_TRANSLATOR

Oracle DBMS_SQL_TRANSLATOR_EXPORT Built-In Package Versions 12.1 - 19.3

Security Advisory

This undocumented and unsupported package contains internal utilities that are used to facilitate exporting SQL Translations.

SQL Translations are, by definition, dangerous. They allow a SQL statement to be rewritten inside Oracle memory where what is happening cannot be monitored or audited.

But the warning about this package comes just from the names of its internal objects. The minute we see something with the word GRANT in it we start paying close attention. When the word after GRANT is SYSPRIVS we are already building a box around it. But apparently this type of functionality doesn't alarm someone in Redwood Shores because EXECUTE is granted to PUBLIC.

Again, Oracle, why does anyone with no privilege other than CREATE SESSION need access to anything that grants, audits, or revokes anything?

Recommended Security Rules

NEVER

Put any database into any QA or Production environment with execute on this package granted to PUBLIC.

WITH GREAT CARE

Before revoking EXECUTE from PUBLIC explicitly grant EXECUTE to only a carefully vetted and justified request by a non-human user.

CAUTIONS

Some usage may be in the form of dynamic SQL so carefully verify usage requirements in source code as well as in DBA_DEPENDENCIES

DBMS_SQL_TRANSLATOR_EXPORT Package Information

AUTHID CURRENT_USER

Dependencies

ALL_OBJECTS	DBMS_ASSERT	DBMS_SYS_SQL
ALL_TAB_PRIVS	DBMS_LOB	SQLTXL$
DBA_PRIV_AUDIT_OPTS	DBMS_SQL	USER_OBJECTS
DBA_SYS_PRIVS	DBMS_SQL_TRANSLATOR	USER_OBJ_AUDIT_OPTS

Documented No

Exceptions

Error Code	Reason
ORA-24252	SQL translation profile does not exist

First Available 12.1.0.1

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/prvtsqll.plb

Subprograms

AUDIT_EXP	CREATE_EXP	GRANT_EXP
AUDIT_SYSPRIVS_EXP	DROP_EXP	GRANT_SYSPRIVS_EXP

AUDIT_EXP

Audit SQL Translator Export dbms_sql_translator_export.audit_exp( objid IN NUMBER, version IN VARCHAR2, new_block OUT BINARY_INTEGER) RETURN VARCHAR2;

DECLARE

       nbo    PLS_INTEGER;

       RetVal VARCHAR2(100);

      BEGIN

        RetVal := dbms_sql_translator_export.audit_exp(100, '19.3.0', nbo);

        dbms_output.put_line('Return: ' || RetVal);

        dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));

      END;

      /

AUDIT_SYSPRIVS_EXP

Audit SQL Translator SYS Privileges Export dbms_sql_translator_export.audit_sysprivs_exp( version IN VARCHAR2, new_block OUT BINARY_INTEGER) RETURN VARCHAR2;

DECLARE

       nbo    PLS_INTEGER;

       RetVal VARCHAR2(100);

      BEGIN

        RetVal := dbms_sql_translator_export.audit_sysprivs_exp('19.3.0', nbo);

        dbms_output.put_line('Return: ' || RetVal);

        dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));

      END;

      /

CREATE_EXP

Create an export dbms_sql_translator_export.create_exp( objid IN NUMBER, version IN VARCHAR2, new_block OUT BINARY_INTEGER) RETURN VARCHAR2;

exec dbms_sql_translator.create_profile('UW_SQLTRANS', TRUE);

      

      col txlrowner format a10

      col txlrname format a10

      col audit$ format a40

      

      SELECT *

      FROM sys.sqltxl$;

      

      DECLARE

       nbo    PLS_INTEGER;

       retVal VARCHAR2(100);

      BEGIN

        retVal := dbms_sql_translator_export.create_exp(94548, '19.3.0', nbo);

        dbms_output.put_line('Return: ' || RetVal);

        dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));

      END;

      /

DROP_EXP

Drop an export dbms_sql_translator_export.drop_exp( objid IN NUMBER, version IN VARCHAR2, new_block OUT BINARY_INTEGER) RETURN VARCHAR2;

DECLARE

       nbo    PLS_INTEGER;

       RetVal VARCHAR2(100);

      BEGIN

        RetVal := dbms_sql_translator_export.drop_exp(100, '19.3.0', nbo);

        dbms_output.put_line('Return: ' || RetVal);

        dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));

      END;

      /

GRANT_EXP

Grant SQL Translator Export Privileges dbms_sql_translator_export.grant_exp( objid IN NUMBER, isdba IN BINARY_INTEGER, grantor OUT VARCHAR2, version IN VARCHAR2, new_block OUT BINARY_INTEGER) RETURN VARCHAR2;

DECLARE

       nbo    PLS_INTEGER;

       Grntr  VARCHAR2(30);

       RetVal VARCHAR2(100);

      BEGIN

        RetVal := dbms_sql_translator_export.grant_exp(100, 1, Grntr, '19.3.0', nbo);

        dbms_output.put_line('Return: ' || RetVal);

        dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));

      END;

      /

GRANT_SYSPRIVS_EXP

Grant SQL Translator SYS Export Privileges dbms_sql_translator_export.grant_sysprivs_exp( version IN VARCHAR2, new_block OUT BINARY_INTEGER) RETURN VARCHAR2;

DECLARE

       nbo    PLS_INTEGER;

       RetVal VARCHAR2(100);

      BEGIN

        RetVal := dbms_sql_translator_export.grant_sysprivs_exp('19.3.0', nbo);

        dbms_output.put_line('Return: ' || RetVal);

        dbms_output.put_line('NewBlk: ' || TO_CHAR(nbo));

      END;

      /