Oracle Instead Of Trigger Exploits
Versions 8.1.7 - 20c

Security Advisory
If you have never heard of an INSTEAD OF TRIGGER before ... that does not mean you do not have any in your database.

In the version 9.2 documentation Oracle wrote: "INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement."

Let's restate that from a security standpoint. An INSTEAD OF trigger provides a transparent way to do something not visible to auditing that may or may not be doing what the application was originally intended to do.

No Oracle Database should ever have an INSTEAD OF trigger that has not been thoroughly vetted and for which any code modification should be thoroughly reviewed.
 
Recommended Security Rules

 NEVER
  • Allow an INSTEAD OF trigger to be deployed if you have not reviewed every line of code, reviewed the code for the possibility of SQL Injection, and reviewed every line of code called by the INSTEAD OFtrigger.
 WITH GREAT CARE
  • Review the source code of an INSTEAD OF trigger for security issues such as calls to PL/SQL objects that may obscure the actual intent.
 CAUTIONS
  • INSTEAD OF triggers are another match in the Oracle arsenal. Use the match to light a candle or use the match to burn light Molotov Cocktails. We recommend using these incredibly powerful tools but like any other powerful tool ... using it wisely.
 
How Oracle Works
This demo shows one way Oracle imagined this capability would be used. There are additional examples of proper use in the demonstration section of this page. In the following example we demonstrate how an INSTEAD OF trigger can be used to perform an invaluable action that would be impossible any other way.

The demo is a simplified example from code written in a highly secure environment to protect intellectual property and to make it far harder for an attacker to subvert the business logic and exfiltrate identifying information.


conn sys as sysdba@pdbdev

GRANT create table TO uwclass;
GRANT create view TO uwclass;
GRANT create procedure TO uwclass;
GRANT create trigger TO uwclass;

conn uwclass/uwclass

CREATE TABLE "UWCLASS"."hidden_credit_cards"(
cc_holder VARCHAR2(30),
cc_token  VARCHAR2(40),
cc_numbr  VARCHAR2(19));

CREATE OR REPLACE VIEW uwclass.credit_cards AS
SELECT cc_holder, '****-****-****-' || SUBSTR(cc_numbr,16,4) AS CC_NUMBR
FROM "UWCLASS"."hidden_credit_cards";

SQL> desc hidden_credit_cards
ERROR:
ORA-04043: object hidden_credit_cards does not exist


SQL> desc "hidden_credit_cards"

SQL> desc "hidden_credit_cards"
Name        Null? Type
----------- ----- ---------------
CC_HOLDER         VARCHAR2(30)
CC_TOKEN          VARCHAR2(40)
CC_NUMBR          VARCHAR2(19)

Security by Obscurity should never be the only security but it should be part of a comprehensive secuirty solution: It does, after all fools 95% of the people 100% of the time.

SQL> desc credit_cards
Name        Null? Type
----------- ----- ---------------
CC_HOLDER         VARCHAR2(30)
CC_NUMBR          VARCHAR2(19)

The view credit cards has only two columns "CC_HOLDER" and "CC_NUMBER" we are going to stuff 4 values into that view and simultaneously generate a credit card token. In a real-world deployment all PL/SQL would be encrypted.

CREATE OR REPLACE PROCEDURE magic_happens(c IN VARCHAR2, ioVal OUT VARCHAR2)
AUTHID CURRENT_USER IS
BEGIN
  SELECT STANDARD_HASH(c)
  INTO ioVal
  FROM dual;
END magic_happens;
/

CREATE OR REPLACE TRIGGER iot_demo
INSTEAD OF INSERT ON credit_cards
FOR EACH ROW
DECLARE
 ioVal VARCHAR2(60);
BEGIN
  magic_happens(:NEW.cc_numbr, ioVal);
  INSERT INTO "hidden_credit_cards"
  (cc_holder, cc_token, cc_numbr)
  VALUES
  (:NEW.cc_holder, ioVal, :NEW.cc_numbr);
END;
/

SQL> INSERT INTO credit_cards VALUES ('Morgan', '3761-0040-1234-5678');

1 row created.

COMMIT;

Commit complete;

SQL> SELECT * FROM credit_cards;

CC_HOLDER  CC_NUMBR
---------- -------------------
Morgan     ****-****-****-5678

SQL> SELECT * FROM "hidden_credit_cards";

CC_HOLDER  CC_TOKEN                                 CC_NUMBR
---------- ---------------------------------------- -------------------
Morgan     DBB8E8F4CA4A974DEFE24DD79307FAC96049D95F 3761-0040-1234-5678

conn sys as sysdba@pdbdev

REVOKE create table FROM uwclass;
REVOKE create view FROM uwclass;
REVOKE create procedure FROM uwclass;
REVOKE create trigger FROM uwclass;

DROP VIEW uwclass.credit_cards;
DROP TABLE uwclass."hidden_credit_cards" PURGE;
DROP PROCEDURE uwclass.magic_happens;

Not a bad little trick. The application sees a view emulating a table that has 2 columns and calls a stored procedure where all kinds of magic is obfuscated and the insert takes place in a table not visible to the application user but the view is updated as part of the same transaction so it looks like everything worked as it superficially should have.
An exploit that takes advantage of this package to end-run security, testing, and auditing. We hope you are already thinking ... "and nasty things could happen inside that stored procedure too ... and no one would know," and you would be absolutely correct. The trigger can call functions, package objects, stored procedures, anything it wishes.Those objects can do good things like in the demo above, they can create detailed audit trails, they can write to the alert and system logs, but they can also corrupt data, copy data, or pull 1% of all deposits where the gross amount exceeds $50,000 and put 1% into my personal bank account. If I did that in any major financial institution I expect my weekly take would exceed my current annual income. I also expect that there wouldn't be a place on earth far enough away to allow me to enjoy that money before I gave it to an attorney in the hope of getting into accommodations more appealing than the average Federal penitentiary.

So, as you can already see ... an INSTEAD OF TRIGGER is incredibly powerful ... but it could be used to alter transactions or create a phony audit trail. Just for the academic challenge we wrote one based on the fraud pulled by Volkswagen when they faked exhaust emissions on diesel cars.

Remember how they did it. They wrote code to detect when the car was being tested and made it be a "good" citizen while it polluted in real-world driving conditions? Our INSTEAD OF TRIGGER looked at database characteristics to determine whether it was in a test or production environment. If it thought is was in "test" it behaved like a model corporate citizen. When it thought it was in a production environment it turned into a thief.

The point of this explanation is not that you should do something stupid and send us a post card from you and your cellmate tell us about your attorney's new BMW. Rather it is to remind you that organized crime families and state actors are already doing this and you need to read the next part of this section to learn how to detect it and stop them.
Find INSTEAD OF TRIGGERS The good news is that this is easy. First you need to find them and then, if you don't know why they exist, you need to review their source code.

SELECT owner, trigger_name, table_name, triggering_event
FROM dba_triggers
WHERE trigger_type = 'INSTEAD OF'
ORDER BY 1,3,2;

Try the following on one of your production databases

SQL> SELECT owner, COUNT(*)
  2  FROM dba_triggers
  3  WHERE trigger_type = 'INSTEAD OF'
  4  GROUP BY owner
  5  ORDER BY 1;

OWNER    COUNT(*)
------ ----------
MDSYS          47
WMSYS          43
XDB             2

While 99% of Oracle DBAs weren't watching Oracle built 92 of these triggers into version 19.3.

Here's how you recover the trigger's source code:


SQL> SELECT trigger_body
  2  FROM dba_triggers
  3  WHERE owner = 'UWCLASS'
  4* AND trigger_name = 'IOT_DEMO';

TRIGGER_BODY
---------------------------------------------------
DECLARE
 ioVal VARCHAR2(60);
BEGIN
  magic_happens(:NEW.cc_numbr, ioVal);
  INSERT INTO "hidden_credit_cards"
  (cc_holder, cc_token, cc_numbr)
  VALUES
  (:NEW.cc_holder, ioVal, :NEW.cc_numbr);
END;

Ostrich's don't really hide their head in the sand when confronting danger and you shouldn't either. Become an expert on finding and identifying these and other potential threats.
 
INSTEAD OF TRIGGER Information
Data Dictionary Objects
ALL_TRIGGERS DBA_TRIGGERS USER_TRIGGERS
CDB_TRIGGERS TRIGGER$  
Documented in SQL Reference Manual Yes
Object Privileges DML access must be given to the view of which the trigger is associated
System Privileges
ALTER ANY TRIGGER CREATE TRIGGER DROP ANY TRIGGER
CREATE ANY TRIGGER    
Syntax CREATE OR REPLACE [<EDITIONABLE | NONEDITIONABLE>] TRIGGER <trigger_name>
INSTEAD OF <DELETE | [OR] INSERT | [OR] UPDATE>
ON [NESTED TABLE <nested_table_column> OF ] <[schema_name.]non_editioned_view_name>
REFERENCING NEW AS <synonym> OLD AS <synonym> PARENT AS <synonym>
[FOR EACH ROW]
[<FORWARD | REVERSE> CROSSEDITION]
[<FOLLOWS | PRECEDES] <[scema_name.]trigger_name]
[<ENABLE | DISABLE>]
DECLARE
 <variable definitions>
BEGIN
  <trigger_code>
EXCEPTION
  <exception clauses>
END <trigger_name>;
/
 
Demonstration Setup
Demo Tables
conn uwclass/uwclass@pdbdev

CREATE TABLE employee (
employee_no   VARCHAR2(8),
last_name     VARCHAR2(25) NOT NULL,
first_name    VARCHAR2(10) NOT NULL,
dept_code     VARCHAR2(3)  NOT NULL,
active_flag   VARCHAR2(1)  DEFAULT 'Y',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE         DEFAULT SYSDATE);

CREATE TABLE permission_code (
pcode             VARCHAR2(2),
pcode_description VARCHAR2(40) NOT NULL,
mod_user_id       VARCHAR2(30) DEFAULT USER,
mod_user_date     DATE         DEFAULT SYSDATE);

CREATE TABLE user_role (
dept_code     VARCHAR2(3),
pcode         VARCHAR2(2),
access_level  VARCHAR2(1)  DEFAULT 'R',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE         DEFAULT SYSDATE);

CREATE TABLE user_permission (
employee_no   VARCHAR2(8),
pcode         VARCHAR2(2),
access_level  VARCHAR2(1)  DEFAULT 'R',
mod_user_id   VARCHAR2(30) DEFAULT USER,
mod_user_date DATE         DEFAULT SYSDATE);

CREATE TABLE dept_code (
dept_code VARCHAR2(3),
dept_name VARCHAR2(30));

CREATE TABLE test (
test VARCHAR2(20));
Demo Data
INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('5001', 'Mark', 'Townsend', 'LCR', 'Y');

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('3996', 'Dacko', 'Carol', 'ESR', 'Y');

INSERT INTO employee
(employee_no, last_name, first_name, dept_code, active_flag)
VALUES
('6842', 'Morgan', 'Daniel', 'ADM', 'Y');

INSERT INTO permission_code
VALUES ('BO', 'BILLING OPTIONS', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CL', 'CLASS CODES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CR', 'CREWS', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CT', 'CREW TYPES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('CU', 'CUSTOMER TYPES', USER, SYSDATE);

INSERT INTO permission_code
VALUES ('DH', 'WORKORDER DASH NUMBERS', USER, SYSDATE);

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ADM', 'ADMINISTRATION');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('COO', 'COORDINATOR');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESE', 'ELECTRICAL SERVICE');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ESR', 'ELECTRICAL SERVICE REP');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('ENG', 'ENGINEER');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('LCR', 'LINE CREW');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('MCR', 'METER CREW');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('NWE', 'NETWORK ENGINEER');

INSERT INTO dept_code (dept_code, dept_name)
VALUES ('SKA', 'SKETCH ARTIST');

INSERT INTO user_role
(dept_code, pcode, access_level)
SELECT r.dept_code, p.pcode, 'R'
FROM dept_code r, permission_code p;

INSERT INTO user_permission
(employee_no, pcode, access_level)
SELECT e.employee_no, r.pcode, r.access_level
FROM employee e, user_role r
WHERE e.dept_code = r.dept_code;

COMMIT;
Non Key-Preserved Relational Views
-- the word "view" is used in naming for demo purposes only
CREATE OR REPLACE VIEW role_permission_view AS
SELECT r.dept_code, r.pcode, p.pcode_description, r.access_level
FROM user_role r, permission_code p
WHERE r.pcode = p.pcode;

desc role_permission_view

col data_type format a15

SELECT column_name, data_type, data_length
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';

col type format a30

SELECT column_name, nullable, data_type || '(' ||  data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';

SELECT column_name, DECODE(nullable, 'N', 'NOT NULL', NULL) "Null?", data_type || '(' ||  data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'ROLE_PERMISSION_VIEW';

-- this will fail
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');

-- this will fail too
UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';

-- another relational view
CREATE OR REPLACE VIEW employee_permission_view AS
SELECT e.employee_no, e.first_name || ' ' || e.last_name NAME, e.dept_code, r.pcode, r.access_level DEFACCLVL, u.access_level, p.pcode_description
FROM employee e, user_role r, user_permission u, permission_code p
WHERE e.dept_code = r.dept_code
AND e.employee_no = u.employee_no
AND r.pcode = u.pcode
AND r.pcode = p.pcode
ORDER BY 1,3;

desc employee_permission_view

SELECT column_name, DECODE(nullable, 'N', 'NOT NULL', NULL) "Null?", data_type || '(' ||  data_length || ')' TYPE
FROM user_tab_cols
WHERE table_name = 'EMPLOYEE_PERMISSION_VIEW';

SELECT * FROM employee_permission_view;

-- this will fail too
DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';
 
Demo 1
Instead Of Insert Trigger
CREATE OR REPLACE TRIGGER ioft_insert_role_perm
INSTEAD OF INSERT
ON role_permission_view
FOR EACH ROW
DECLARE
 x INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO x
  FROM permission_code
  WHERE pcode = :NEW.pcode;

  IF x = 0 THEN
    INSERT INTO permission_code
    (pcode, pcode_description, mod_user_id, mod_user_date)
    VALUES
    (:NEW.pcode, 'New Code', USER, SYSDATE);
  END IF;

  SELECT COUNT(*)
  INTO x
  FROM dept_code
  WHERE dept_code = :NEW.dept_code;

  IF x = 0 THEN
    INSERT INTO dept_code
    (dept_code, dept_name)
    VALUES
    (:NEW.dept_code, 'New Dept');
  END IF;

  INSERT INTO user_role
  (dept_code, pcode, mod_user_id)
  VALUES
  (:NEW.dept_code, :NEW.pcode, 'Morgan');

  INSERT INTO test
  (test)
  VALUES
  ('Z');
END ioft_insert_role_perm;
/

SELECT *
FROM permission_code
WHERE pcode = 'DM';

SELECT *
FROM dept_code
WHERE dept_code = 'DAN';

SELECT *
FROM user_role
WHERE dept_code = 'DAN';

SELECT * FROM test;

-- insert works
INSERT INTO role_permission_view
(dept_code, pcode, pcode_description, access_level)
VALUES
('DAN', 'DM', 'Morgan', 'W');

-- view results
SELECT *
FROM permission_code
WHERE pcode = 'DM';

SELECT *
FROM dept_code
WHERE dept_code = 'DAN';

SELECT *
FROM user_role
WHERE dept_code = 'DAN';

SELECT * FROM test;
 
Demo 2
Instead Of Update Trigger
CREATE OR REPLACE TRIGGER ioft_role_perm
INSTEAD OF UPDATE
ON role_permission_view
FOR EACH ROW
BEGIN
  UPDATE user_role
  SET access_level = :NEW.access_level,
      mod_user_id = USER,
      mod_user_date = SYSDATE
  WHERE dept_code = :OLD.dept_code
  AND permission_code = :OLD.permission_code;
END ioft_role_perm;
/

SELECT trigger_name, trigger_type, action_type,
description
FROM user_triggers;

SELECT * FROM employee_permission_view;

UPDATE role_permission_view
SET access_level = 'W'
WHERE dept_code = 'SKA';

SELECT * FROM employee_permission_view;

UPDATE employee_permission
SET access_level = 'Z';
 
Demo 3
Instead of Delete Trigger
/* what does it mean to delete LCR from employee_permission_view?
Does it mean delete the LCR department from the dept_code table?
Does it mean delete all employees that are in department LCR?
Does it mean set to null the dept_code for employees in department LCR? */


SELECT * FROM employee_permission_view;

SELECT * FROM dept_code;

SELECT * FROM employee;

-- let's delete the parent record and set the child to null and update two other columns
CREATE OR REPLACE TRIGGER ioft_emp_perm
INSTEAD OF DELETE
ON employee_permission_view
FOR EACH ROW
BEGIN
  DELETE FROM dept_code
  WHERE dept_code = :OLD.dept_code;

  UPDATE employee
  SET dept_code = NULL,
      mod_user_id = USER,
     mod_user_date = SYSDATE
  WHERE dept_code = :OLD.dept_code;

  DELETE FROM test
  WHERE test = 'Z';
END ioft_emp_perm;
/

SELECT * FROM employee_permission_view;

DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';

desc employee

DELETE FROM employee_permission_view
WHERE dept_code = 'LCR';
 
Instead-Of Trigger with Referencing Clause
Referencing Clause with Nested Tables
conn scott/tiger@pdbdev

CREATE OR REPLACE TYPE emp_type AS OBJECT (
empno    NUMBER(4),
ename    VARCHAR2(10),
job      VARCHAR2(9),
mgr      NUMBER(4),
hiredate DATE,
sal      NUMBER(7, 2),
comm     NUMBER(7, 2));
/

CREATE OR REPLACE TYPE emp_tab_type AS TABLE OF emp_type;
/

CREATE OR REPLACE TYPE dept_type AS OBJECT (
deptno NUMBER(2),
dname  VARCHAR2(14),
loc    VARCHAR2(13),
emps   emp_tab_type);
/

CREATE OR REPLACE VIEW dept_or OF dept_type
WITH OBJECT IDENTIFIER (deptno) AS
SELECT deptno, dname, loc, CAST(MULTISET(
  SELECT empno, ename, job, mgr, hiredate, sal, comm
  FROM emp
  WHERE emp.deptno = dept.deptno) AS emp_tab_type)
FROM dept;
/

CREATE OR REPLACE TRIGGER dept_emplist_tr
INSTEAD OF UPDATE ON NESTED TABLE emps OF dept_or
REFERENCING NEW AS NEW PARENT AS PARENT
FOR EACH ROW
BEGIN
  dbms_output.put_line('New: ' || :NEW.job);
  dbms_output.put_line('Parent: ' || :PARENT.dname);
END;
/

set serveroutput on

UPDATE TABLE (
SELECT p.emps
FROM dept_or p
WHERE deptno = 10)
SET ename = LOWER(ename);
 
Object-Relational View Instead Of Trigger
Object View Instead Of Trigger
-- for demo table and data: See Object-Relational Views link at page bottom

INSERT INTO ov_empdept
(empno, ename, dept)
VALUES
(4, 'D. Morgan', t_dept(7, 'MKT', 'Houston'));

CREATE OR REPLACE TRIGGER ioft_ov_empdept
INSTEAD OF INSERT
ON ov_empdept
FOR EACH ROW
BEGIN
  INSERT INTO emp
  (empno, ename, job, mgr, hiredate,
  sal, comm, deptno)
  VALUES
  (:NEW.empno, :NEW.ename, :NEW.job, :NEW.mgr, :NEW.hiredate,
  :NEW.sal, :NEW.comm, :NEW.dept.deptno);

  INSERT INTO dept
  (deptno, dname, location)
  VALUES
  (:NEW.dept.deptno, :NEW.dept.dname, :NEW.dept.location);
END ioft_ov_empdept;
/

INSERT INTO ov_empdept
(empno, ename, dept)
VALUES
(4, 'D. Morgan', t_dept(7, 'MKT', 'Houston'));

COMMIT;

SELECT * FROM ov_empdept;

SELECT * FROM emp;

SELECT * FROM dept;

-- demo corrected with the help of Kent Williamson. Thanks.

Related Topics
STORED PROCEDURE
TABLE TRIGGER
VIEW