| 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. |