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:
|Creating and Applying a VPD Policy|
|Testing the Policy Implementation|
|Checking the Audit Violations|
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. OE is 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:
|HR_sec||Creates database users (from a list of employees in the hr.employees table) and database roles.|
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 ( dba_fga_audit_trail).|
Befor you perform this tutorial, you should:
Perform the Installing Oracle Database 10g on Windows tutorial.
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:
(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. Open the 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:
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.
(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:
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.
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:
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 emp_role. From your SQL*Plus window, run the following script:
@c:\wkdir\02_fga_cradminusers connect system/oracle;
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;
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:
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;
You now add the policy to the oe.cust_payment_info table. From your SQL*Plus window, execute the following script:
The 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:
After establishing policies to tables, you can now test them by performing the following steps:
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;
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;
Now you can test LSMITH access by executing the following script:
@c:\wkdir\06_fga_test_policy_smith conn LSMITH/welcome1;
Now you can test SYS access by executing the following script:
@c:\wkdir\06_fga_test_policy_sys conn / as sysdba;
And finally, you can test the OE user access by executing the following script:
@c:\wkdir\06_fga_test_policy_oe conn OE/oe;
The audit_admin user can check for any access violations by performing the following steps:
Execute the following script to check the dba_fga_audit_trail for the CUST_PAYMENT_INFO table.
@c:\wkdir\07_fga_chk_audit_violations conn audit_admin/welcome1;
Now that you have tested your policies, you can drop the users and the policies by performing the following steps:
Execute the following script:
@c:\wkdir\08_fga_cleanup conn sec_admin/welcome1;
In this tutorial, you learned how to:
|Create and apply a VPD policy|
Test the policy
|Check for audit violations|
Place your cursor on this icon to hide all screenshots.