SQL Attacks Descriptions & Explanations

SQL Attacks Descriptions & Explanations
Each of the audited databases on January 15, 2013 had 10 different attacks mounted against it. Below is a description of these attacks. Some of these attacks can be difficult to spot under any condition, but when running at the same time as the application, the challenge is multiplied.
To make things more challenging, these attacks were run through various accounts:
  • The DBA account (Tom)
  • The SYS account
  • The application account
  • The application itself. This is to simulate a SQL injection, or an intentional application flaw (like a back door).
Each attack has an explanation of what makes it simpler or harder to detect. To avoid duplication, simplifying or complicating factors from other attacks are not repeated.

Simple Access

The simplest attack is to run a SQL, like one of these:
select * from ccapp.cards;
select * from hrapp.salaries;
update hrapp.salaries set bonus = bonus*2 where emp=2;
To do this, you need access to the table. However, any DBA account, the SYS account, and the application account will always have that access granted.
Simplifying factors
  • Unlike the rest of the attacks, this attack has the SQL submitted to the database in plain text
Complicating factors
  • If submitted through a program like Toad, it will be hidden among hundreds of SQLs
  • If maliciously added to a Change Control script, it will be hidden among many valid changes to the database
  • If submitted through the application (via SQL Injection, malicious code in the application, a back door etc), it will be hidden among millions of valid SQLs issued by the application

Execute Immediate in an Anonymous Block

An anonymous block allows submitting PL/SQL code, and from it submitting a SQL internally to the database engine:
begin
  execute immediate ('update hrapp.salaries set bonus = bonus*2 where emp=2');
end;
/
To perform data theft, a local copy of the information can be made:
begin
  execute immediate ('create table mycopy as select * from hrapp.salaries');
end;
/
select * from mycopy;
Simplifying factors
  • While the code submitted to the database is in PL/SQL, it still contains the SQL to be executed in plain text
Complicating factors
  • Unlike PL/SQL code in procedures, triggers etc, an anonymous block leaves no traces of the code’s existence in the database.

Building a dynamic SQL

To avoid sending the SQL in plain text, this attack will concatenate the SQL inside the database engine:
declare
  s varchar2(100);
begin
  s := 'cre'||'ate ta'||'ble myc'||'opy as s';
  s := s || 'elect * fr'||'om h'||'ra'||'pp.sa'||'la'||'ri'||'es';
  execute immediate(s);
end;
/
select * from mycopy;
Simplifying factors
  • A person looking at it can relatively easily understand what it does
  • This is a rather unusual anonymous block to be submitted to the database
Complicating factors
  • Automatic parsing of this anonymous block will not detect the table name, the operation, or anything else that can raise suspicion.

Translate

To avoid making the SQL in the PL/SQL block readable to people, this attack will use the translate function to obfuscate the SQL:
declare
  s varchar2(100);
begin
  s := 'xhrqzt1ikqhh3lqsqkotl1ltz1lqsqkn4lqsqkn251vitkt1tdh46';
  s := translate(s, 'qwertyuiopasdfghjklzxcvbnm123456',
                  'abcdefghijklmnopqrstuvwxyz *.=23');
  execute immediate(s);
end;
/
The SQL that comes out of the above translate is
update hrapp.salaries set salary=salary*2 where emp=3;
Simplifying factors
  • This is an unusual anonymous block
  • The SQL it generates will run inside this block (and not executed later in a different context)
Complicating factors
  • It is impossible for software to decipher the meaning of this PL/SQL
  • It is extremely hard for a person to know the meaning of this PL/SQL

Translate in Procedure

To avoid having the malicious SQL execute inside the block, a simple procedure can be used:
create or replace procedure myproc(s in varchar2) is
  s1 varchar2(100);
begin
  s1 := translate(s, 'qwertyuiopasdfghjklzxcvbnm1234',
                   'abcdefghijklmnopqrstuvwxyz *.=');
  execute immediate(s1);
end;
/
exec myproc('ektqzt1zqwst1dneghn1ql1ltstez121ykgd1eeqhh3eqkrl');
The SQL that is executed by the above code is
create table mycopy as select * from ccapp.cards
Simplifying factors
  • The exec(..) PL/SQL call is a little unusual, but can be easily glanced over in the midst of complex SQLs
  • While unlikely to be detected, the procedure’s existence can be noted, and it’s creation is documented in the Oracle Redo Logs
  • In order for the procedure to successfully execute this SQL, the caller must be explicitly granted select any table and create any table or equivalent privileges. The DBA role alone is not sufficient. However, such grants to a DBA account will not be suspicious.
Complicating factors
  • The procedure creation contains no malicious SQL, only the means to execute one. The malicious SQL in exec(‘..’) can be executed in a different session, days or weeks after the procedure is created. Therefore, it might be extremely hard to associate the procedure creation with the malicious execution.
  • The malicious procedure creation can be introduced through a scheduled change control script and will be extremely hard to detect

View

Without PL/SQL code at all, a view can give access to a sensitive table without mentioning the sensitive table name:
create or replace view myview as select * from hrapp.salaries;
select * from myview;
Simplifying factors
  • Creation of a view that accesses a sensitive table is highly suspicious
  • The view’s existence can be easily detected, and it’s creation is documented in the Oracle Redo Logs
  • The view gives read-only access to the data
Complicating factors
  • Accessing the view can be done anytime after the view is created, and will likely pass undetected
  • The latest information is read from the sensitive table whenever the view is accessed. There is no need for additional DDLs or code to run once the view is created
  • To obfuscate the view’s creation, any of the other attacks can be used

Materialized View

An alternative to a view is a materialized view.
create materialized view myview as select * from hrapp.salaries;
select * from myview;
Simplifying factors
  • The materialized view might not have the latest information without refreshing
Complicating factors
  • While a view on a sensitive table is highly suspicious, a materialized view can sometimes be justified.
  • Refreshing a materialized view may not seem suspicious and can pass undetected.

Synonym

A synonym gives both read and write access to the table.
create synonym mysyn for hrapp.salaries;
select * from mysyn;
update mysyn set bonus=bonus*2 where emp=2;
Simplifying factors
  • A synonym for a sensitive table is highly suspicious
  • The synonym’s existence can be easily detected and it’s creation is documented in the Oracle Redo Logs.
Complicating factors
  • Once the synonym is created, complete read and write access to the table is possible without detection

SQL in a Trigger

This attack uses a trigger to make a copy of a table. When a delete is performed on mycopy, the trigger will immediately copy the salaries table to the mycopy table.
create table mycopy (salary number, first varchar2(50), last varchar2(50));
create or replace trigger mytrig after delete on mycopy
begin
  insert into mycopy (select s.salary, e.first, e.last
                          from hrapp.employees e, hrapp.salaries s 
                          where s.emp = e.id);
end;
/
delete from mycopy;
select * from mycopy;
Simplifying factors
  • In order to create the trigger and perform the delete, the calling user must have select any table or equivalent permission. The DBA role is not sufficient, but granting this permission to a DBA account will not seem suspicious.
  • The insert in the trigger will run as a child of the triggering delete instruction
Complicating factors
  • The trigger is on a private table owned by the attacker and the delete is issued against this private table

Data Duplication using a Trigger

Another way of using a trigger for data theft is to have the trigger duplicate all new data inserted into a table into another table.
create or replace trigger salary_processing
after insert on hrapp.salaries for each row
begin
  insert into tom.mycopy values(:new.emp, :new.salary, :new.bonus);
end;
/
Simplifying factors
  • The trigger needs to exist for a while since it only copies new data inserted into the table (and getting a significant amount of data can take time)
  • The trigger is installed on the sensitive table in the sensitive schema, making it easier to detect
Complicating factors
  • There is no SQL that will trigger suspicion. The duplication takes data from an authorized insert, and inserts it into a non-sensitive non-monitored table
  • The duplicating insert is run on every insert and will seem like a normal part of the application

Core Audit

With so many complex attacks, can Core Audit detect them all, and will you be able to find the needle in the haystack?
A good place to start is the Table Security New SQLs report. This report identifies SQLs that access sensitive tables and have not been seen before. Core Audit has many other reports that monitor many things, but this security report/alert is a good starting point for investigations.
To see how to perform such investigations and go from a line in the report to a full understanding of the attack, see the Detective Guide.