The goal of this tutorial is to show how fine-grained auditing can be used to detect access to a "honey token" in a table.
Approximately 30 minutes
This tutorial covers the following topics:
Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)
Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and view only the screenshot associated with that step.
Policies you establish with fine-grained auditing can monitor data access based on content. Using policies, you can specify the columns and conditions that you want audit records for. Conditions can include limiting the audit to specific types of DML statements used in connection with the columns that you specify. You can also provide the name of the routine you want called when an audit event occurs. This routine can notify or alert administrators or handle errors and anomalies.
This tutorial shows how fine-grained auditing is used to detect access to a "honey token" in a table. In this tutorial, a VPD policy is created that limits access to rows by credit card vendor. The honey token is a credit card number that is never selected. It fails the usual verifications, and the number cannot be related to any vendor, so authorized access to this row is not possible.
The VPD policy excludes
OE, the owner of this table.
granted full access, so when
OE (or a hacker who obtained this identity) executes
select * from oe.cust_payment_info
the honey token is selected and the FGA policy records this event. In addition, you could invoke an event handler
(not included in this tutorial), to send an email to
audit_admin about the access to this special row.
This tutorial separates duties between the following users:
||Creates database users (from a list of employees in the
||Creates and applies the VPD and FGA policies according to the company's access control and auditing policies.|
|audit_admin||Queries the audit record in the database
Befor you perform this tutorial, you should:
1. Perform the Installing Oracle Database 10g on Windows tutorial.
2. Download and unzip the fga.zip file into your working directory (c:\wkdir).
In this section, you update your sqlnet.ora, create an encrypted wallet (
ewallet.p12), open the wallet, and create the master key for TDE. Perform the following steps:
1. (OPTIONAL: This topic should be performed only if you have not performed this section in a previous lesson)
You need to update your
sqlnet.ora file to include an entry for ENCRYPTED_WALLET_LOCATION.
c:\oracle\product\10.2.0\db_1\network\admin\sqlnet.ora file using either Notepad or Wordpad. Add
the following entry to the end of the file:
ENCRYPTION_WALLET_LOCATION= (SOURCE=(METHOD=FILE)(METHOD_DATA= (DIRECTORY=c:\oracle\product\10.2.0\db_1\)))
Save your changes and close the file.
Note: Any directory can be picked for the encrypted wallet, but the path should not point to the standard
obfuscated wallet (
cwallet.sso) created during DB installation.
2. (OPTIONAL: This topic should be performed only if you have not performed this section in a previous lesson) Next, you need to open the wallet and create the master encryption key. Select Start > Programs > Oracle - OraDb10g_home1 > Application Development > SQL Plus. Enter /nolog for the User Name and click OK. Then enter the following command:
@c:\wkdir\fga00_dbsetup connect / as sysdba alter system set key identified by "welcome1";
The above alter command does the following:
ewallet.p12), the wallet is opened, and the master key for created/recreated.
Note: only users with the
'alter system' privilege can create a master key or open the wallet.
The master key should only be created once, unless you want to re-encrypt your data with a new encryption key !!!
For later sessions, you do not want to use the command given above; you need the wallet to be open (it has been closed when you shut down your database), but you don't want to create a new master key. Then the command is:
alter system set wallet open identified by "welcome1";
The master encryption key is necessary because each table has its own encryption key. These column keys are stored in the database. Since the wallet can only store a limited number of keys and is not very scalable, the column keys are encrypted with the master key. This way, you can have as many column keys as needed, with only a small number of master keys stored in the wallet (including retired keys, that you may need one day to decrypt data from an old backup-tape). By default, the command above generates a key using the Advanced Encryption Standard with 192 bits (AES192). 3DES could also be used, or a smaller or bigger number of bits for the AES encryption.
3. In this tutorial, you create a set of users and roles to demonstrate how FGA works. You create a customer payment information table and populate it with credit card numbers. The credit card number associated with "Honey Token" is impossible, so this row is never selected by authorized users. It is only selected by administrative users who are exempt from access policies or intruders who became administrative 'insiders'. This allows for highly focused auditing on this table. From your SQL*Plus window, execute the following script:
@c:\wkdir\01_fga_poptabl conn oe/oe create table cust_payment_info (first_name varchar2(11), last_name varchar2(10), order_number number(5), credit_card_number varchar2(16) ENCRYPT); insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446959708812985'); insert into cust_payment_info values ('Chris', 'White', 10002, '5122358046082560'); insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595968943757920'); insert into cust_payment_info values ('Mike', 'Anderson', 10004, '4929889576357400'); insert into cust_payment_info values ('Annie', 'Schmidt', 10005, '4556988708236902'); insert into cust_payment_info values ('Elliott', 'Meyer', 10006, '374366599711820'); insert into cust_payment_info values ('Celine', 'Smith', 10007, '4716898533036'); insert into cust_payment_info values ('Steve', 'Haslam', 10008, '340975900376858'); insert into cust_payment_info values ('Albert', 'Einstein', 10009, '310654305412389');
4. SYSTEM creates three admin users:
HR_sec, sec_admin and
audit_admin. HR_sec creates the role
' emp_role' and then grants system and object privileges to the
From your SQL*Plus window, run the following script:
@c:\wkdir\02_fga_cradminusers connect system/oracle; create user sec_admin identified by welcome1; create user audit_admin identified by welcome1; create user HR_sec identified by welcome1; grant connect, create user, drop user, create role, drop any role to HR_sec; connect HR_sec/welcome1; create role emp_role; connect / as sysdba; grant execute on dbms_rls to sec_admin; grant execute on dbms_fga to sec_admin; grant select on dba_fga_audit_trail to audit_admin; connect system/welcome1; grant connect to emp_role; grant create procedure to sec_admin; connect OE/oe; grant select on oe.cust_payment_info to emp_role;
HR_sec creates the database users and grants the
emp_role to them. From your
SQL*Plus window, run the following script:
@c:\wkdir\03_fga_crdbusers connect HR_sec/welcome1; Prompt create user Janette King (JKING) (access to Card_A) grant emp_role to JKING identified by welcome1; Prompt create user Lindsay Smith (LSMITH) (access to Card_V) grant emp_role to LSMITH identified by welcome1; Prompt create user Louise Doran (LDORAN) (access to Card_M) grant emp_role to LDORAN identified by welcome1; Prompt grant emp_role to sec_admin: grant emp_role to sec_admin; Prompt grant emp_role to audit_admin: grant emp_role to audit_admin; connect hr/hr; grant select on hr.employees to sec_admin;
In this section, you define and limit access to the rows with a VPD policy. You check if the person who logs into the database is an employee, and then you limit access to the cust_payment_info table by credit card number.
Card_A begins with '34' or '37':
Card_V begins with '4'
Card_M begins with '5
Perform the following steps:
1. You first need to create a policy function to create the where-clause. From your SQL*Plus window, execute the following script:
@c:\wkdir\04_fga_crpolicy_function conn sec_admin/welcome1; create or replace function f_policy_oe_cust_payment_info -- Function must have the following parameters (schema in varchar2, tab in varchar2) -- Function will return a string that is used as a WHERE clause return varchar2 as v_manager_id number:=0; is_employee number:=0; v_user varchar2(20); out_string varchar2(70) default '1=2 '; begin -- get session user v_user := lower(sys_context('userenv','session_user')); -- Is the user an employee? begin select manager_id into v_manager_id from hr.employees where lower(email) = v_user; is_employee:=1; exception when no_data_found then is_employee:=2; end; -- create where clause when user is authorized to see parts of the table if is_employee=1 and lower(v_user)='jking' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''34%'' or CREDIT_CARD_NUMBER like ''37%'''; elsif is_employee=1 and lower(v_user)='lsmith' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''4%'''; elsif is_employee=1 and lower(v_user)='ldoran' and v_manager_id=146 then out_string := out_string ||'or CREDIT_CARD_NUMBER like ''5%'''; elsif is_employee=2 and lower(v_user)='oe' then out_string := '1=1'; end if; return out_string; end; /
2. You now add the policy to the
oe.cust_payment_info table. From your SQL*Plus window, execute the following script:
@c:\wkdir\05_fga_applypolicy begin dbms_rls.add_policy('oe','cust_payment_info','ac_cust_payment_info','sec_admin', 'f_policy_oe_cust_payment_info',policy_type => dbms_rls.context_sensitive); end; /
sec_admin user adds an FGA policy to the sensitive table that captures access to the "Albert Einstein". From your SQL*Plus window, execute the following script:
@c:\wkdir\05_fga_applypolicy_waudit begin DBMS_FGA.ADD_POLICY ( object_schema => 'OE', object_name => 'cust_payment_info', policy_name => 'fga_cust_payment_info', audit_condition => 'credit_card_number = 310654305412389', audit_column => NULL, handler_schema => NULL, handler_module => NULL, enable => TRUE, statement_types => 'UPDATE, DELETE, SELECT', audit_trail => DBMS_FGA.DB + DBMS_FGA.EXTENDED); end; /
After establishing policies to tables, you can now test them by performing the following steps:
1. To test the access from the
JKING user, execute the following script from your SQL*Plus window.
@c:\wkdir\06_fga_test_policy_king conn JKING/welcome1; col CREDIT_CARD_NUMBER heading Card_A format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
2. Now you can test the policy for the
LDORAN user by executing the following script:
@c:\wkdir\06_fga_test_policy_doran conn LDORAN/welcome1; col CREDIT_CARD_NUMBER heading Card_M format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
3. Now you can test
LSMITH access by executing the following script:
@c:\wkdir\06_fga_test_policy_smith conn LSMITH/welcome1; col CREDIT_CARD_NUMBER heading Card_V format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
4. Now you can test
SYS access by executing the following script:
@c:\wkdir\06_fga_test_policy_sys conn / as sysdba; col CREDIT_CARD_NUMBER heading 'All Cards' format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
5. And finally, you can test the OE user access by executing the following script:
@c:\wkdir\06_fga_test_policy_oe conn OE/oe; col CREDIT_CARD_NUMBER heading 'All Cards' format a17; select * from oe.CUST_PAYMENT_INFO order by CREDIT_CARD_NUMBER;
audit_admin user can check for any access violations by performing the following steps:
1. Execute the following script to check the
dba_fga_audit_trail for the
@c:\wkdir\07_fga_chk_audit_violations conn audit_admin/welcome1; col DB_USER format a10; col extended_timestamp heading 'ext. Time' format a35; select DB_USER, extended_timestamp, SQL_TEXT from dba_fga_audit_trail where object_name='CUST_PAYMENT_INFO' order by extended_timestamp;
Now that you have tested your policies, you can drop the users and the policies by performing the following steps:
1. Execute the following script:
@c:\wkdir\08_fga_cleanup conn sec_admin/welcome1; begin DBMS_FGA.DROP_POLICY ( object_schema => 'OE', object_name => 'cust_payment_info', policy_name => 'fga_cust_payment_info'); end; / drop function f_policy_oe_cust_payment_info; conn oe/oe drop table cust_payment_info; conn HR_sec/welcome1; drop role emp_role; drop user JKING cascade; drop user LSMITH cascade; drop user LDORAN cascade; conn system/welcome1 drop user sec_admin cascade; drop user audit_admin cascade; drop user HR_sec cascade;
In this tutorial, you learned how to:
Create and apply a VPD policy
Test the policy
Check for audit violations