BlueGecko Denmark

by

Oracle Fine Grained Audit – who have queried confidential data

The need for more sophisticated controls on access to sensitive data is becoming increasingly important as organizations address emerging security requirements around data consolidation, privacy and compliance. There are many examples of data breaches which have been perpetrated using stolen credentials, SQL injection or by insiders who are authorized to access the system and its data. Currently, hot topic news in Denmark is, where a popular week-magazine received sensitive information about famous people, from a highly trusted person, working for the Nordic provider of payment card transactions. Unfortunately this incident is not unique and happens far too often. Securing data requires a defense-in-depth approach. Both technical and administrative employees need to be involved to prevent and monitor unauthorized access to sensitive data.

This blog will discuss Oracle database security feature, Fine Grained Audit (FGA), that may help to discover unauthorized access of confidential data with the right setup.

With FGA, it is possible to create audit policies, that records if a user viewed (selected) data from a specific table or table column. Audit records include information about the operation that was audited, the user performing the operation, and the time of the operation. Audit trail records created by FGA can be captured and analyzed in Oracle Audit Vault and Database Firewall, automatically alerting the security team about possible malicious activity.

In next simple example, I will show how FGA can help to monitor unauthorized access of confidential data:

Fine Grained Audit example
1 Create user schema XXBANK:

sqlplus sys/xxx as sysdba
SQL> CREATE USER XXBANK IDENTIFIED BY xxbank;
SQL> GRANT CONNECT TO XXBANK;
SQL> GRANT DBA to XXBANK;
SQL> exit;
2 Create table XXBANK.TRANSAKTIONS and insert some rows:

sqlplus sys/xxx as sysdba
SQL> create table XXBANK.TRANSACTIONS
(
TRANSACTION_ID number,
TRANSACTION_TIME timestamp,
TRANSACTION_PLACE varchar2(40),
CREDIT_CARD_NUMBER varchar2(16),
CREDIT_CARD_HOLDER_NAME varchar2(30) not null,
PAYMENT_AMOUNT number,
primary key (TRANSACTION_ID)
);

SQL> insert into XXBANK.TRANSACTIONS values (1,to_timestamp(’02-APR-2014 10:45′,’DD-MON-YYYY HH24:MI’), ‘MC Donalds, Slagelse, DK’, ‘4599123466991234’,’Prins Henrik’, 55.50);
SQL> insert into XXBANK.TRANSACTIONS values (2,to_timestamp(’02-APR-2014 15:10′,’DD-MON-YYYY HH24:MI’), ‘Seven Eleven, KBH Vesterbro, DK’, ‘4510432145456789’,’Henrik Qvortrup’, 35.00);
SQL> insert into XXBANK.TRANSACTIONS values (3,to_timestamp(’02-APR-2014 22:30′,’DD-MON-YYYY HH24:MI’), ‘Mefisto, Aarhus, DK’, ‘3311345666772468’,’Bubber’, 225.00);
SQL> insert into XXBANK.TRANSACTIONS values (4,to_timestamp(’03-APR-2014 03:10′,’DD-MON-YYYY HH24:MI’), ‘Hanky Panky Bar, KBH Vesterbro, DK’, ‘4510432145456789’,’Henrik Qvortrup’, 1050.00);
SQL> insert into XXBANK.TRANSACTIONS values (5,to_timestamp(’03-APR-2014 08:30′,’DD-MON-YYYY HH24:MI’), ‘Burger King, Strøget KBH, DK’, ‘4599123466991234’,’Prins Henrik’, 70.50);
SQL> insert into XXBANK.TRANSACTIONS values (6,to_timestamp(’03-APR-2014 12:30′,’DD-MON-YYYY HH24:MI’), ‘Psykologcentret, KBH, DK’, ‘3311345666772468’,’Bubber’, 512.75);
SQL> insert into XXBANK.TRANSACTIONS values (7,to_timestamp(’03-APR-2014 16:39′,’DD-MON-YYYY HH24:MI’), ‘Starbucks, Toronto, Canada’, ‘4444362255449385’,’Prins Joachim’, 125.45);
SQL> insert into XXBANK.TRANSACTIONS values (8,to_timestamp(’04-APR-2014 10:17′,’DD-MON-YYYY HH24:MI’), ‘Fona, Valby, DK’, ‘4444123573596612’,’Bo Henriksen’, 10000.00);
SQL> commit;
SQL> exit;
3 Create an FGA policy on table XXBANK.TRANSACTIONS:

sqlplus sys/xxx as sysdba
SQL> begin
dbms_fga.add_policy (
object_schema=>’XXBANK’,
object_name=>’TRANSACTIONS’,
policy_name=>’TRANSACTIONS_ACCESS’
);
end;
/
SQL> exit
4 Do some sql-queries on sensitive data:

User APPS is looking at transactions done by members of the royal family:

sqlplus apps/xxx
SQL> select * from XXBANK.TRANSACTIONS where lower(CREDIT_CARD_HOLDER_NAME) like ‘%prins%’;

TRANSACTION_ID TRANSACTION_TIME
————– —————————————————————————
TRANSACTION_PLACE CREDIT_CARD_NUMB CREDIT_CARD_HOLDER_NAME PAYMENT_AMOUNT
—————————————- —————- —————————— ————–
1 02-APR-14 10.45.00.000000 AM
MC Donalds, Slagelse, DK 4599123466991234 Prins Henrik 55.55

5 03-APR-14 08.30.00.000000 AM
Burger King, Strøget KBH, DK 4599123466991234 Prins Henrik 70.50

7 03-APR-14 04.39.00.000000 PM
Starbucks, Toronto, Canada 4444362255449385 Prins Joachim 125.45

SQL> exit

User EMPLOYEE_1 is looking at transactions done by Bubber:

sqlplus employee_1/xxx
SQL> select to_char(TRANSACTION_TIME,’DD-MON-YYYY HH24:MI’) “Time”, TRANSACTION_PLACE from XXBANK.TRANSACTIONS where lower(CREDIT_CARD_HOLDER_NAME) like ‘%bubber%’;

Time TRANSACTION_PLACE
————————– —————————————-
02-APR-2014 22:30 Mefisto, Aarhus, DK
03-APR-2014 12:30 Psykologcentret, KBH, DK

SQL> exit
5 Check who has viewed sensitive information in the TRANSACTIONS table:

sqlplus sys/xxx as sysdba
SQL> set linesize 200
SQL> set pages 200
SQL> column TS format A20
SQL> column db_user format A10
SQL> column os_user format A10
SQL> column object_schema format A8
SQL> column sql_text format A150
SQL> select to_char(timestamp,’DD-MON-YYYY HH24:MI’) “TS”, db_user, os_user, object_schema, object_name, sql_text from dba_fga_audit_trail;

TS DB_USER OS_USER OBJECT_S
——————– ———- ———- ——–
OBJECT_NAME
————————————————————————————————————————
SQL_TEXT
————————————————————————————————————————
01-MAY-2014 07:25 APPS oracle XXBANK
TRANSACTIONS
select * from XXBANK.TRANSACTIONS where lower(CREDIT_CARD_HOLDER_NAME) like ‘%prins%’

01-MAY-2014 08:08 EMPLOYEE_1 oracle XXBANK
TRANSACTIONS
select to_char(TRANSACTION_TIME,’DD-MON-YYYY HH24:MI’) “Time”, TRANSACTION_PLACE from XXBANK.TRANSACTIONS where lower(CREDIT_CARD_HOLDER_NAME) like ‘%bubber%’

Blog Categories

Speaks til radio, tv og film

Contact Blue Gecko

Blue Gecko A/S
Slotsgade 21
DK – 4200 Slagelse
Denmark
Phone: (DK) +45 70 60 51 20

Networks