fine grained oracle auditing concepts – for the new oracle dba





this is from my personal repository of google referenced articles..

Fine Grained Auditing (FGA) is an excellent solution here because of its minimal performance overhead. If you are already familiar with FGA, please skip ahead to the Strategy section.

FGA was introduced in Oracle9i Database to record an audit trail when a user selects from a table, not just changes it. This was a landmark feature as there was no other way a SELECT activity could be recorded. Regular auditing enabled on SELECT statements merely recorded who issued a statement on an object, but not what was done.

In addition to details such as username, terminal, and time of the query, FGA records the SQL statement that was executed as well as the SCN number of that instance of time. This allows you to see not only the actual statement issued by the user, but the actual values the user saw, by reconstructing the data using flashback queries. In Oracle Database 10g Release 1, this facility was extended to cover other DML statements such as INSERT, UPDATE, and DELETE.

Here’s a small example. Suppose there is a table named ACCOUNTS in the schema called BANK.

Name Null? Type

—————- ——– ————

ACCT_NO NOT NULL NUMBER

CUST_ID NOT NULL NUMBER

BALANCE NUMBER(15,2)

To enable auditing on it, you will need to define an FGA policy on it using the supplied package DBMS_FGA.

begin

dbms_fga.add_policy (

object_schema=>’BANK’,

object_name =>’ACCOUNTS’,

policy_name =>’ACCOUNTS_ACCESS’

);

end;

After the above code is executed, the FGA is active on the table ACCOUNTS. That’s itthere’s no need to change any initialization parameter or bounce the database.

The above code takes a shared lock on the table while executing, so you could do it online. Now, if a user named ARUP selects from the table by issuing:

select * from bank.accounts;

the action is immediately recorded in the audit trail known as FGA Audit Trail. You can check it with:

select timestamp,

db_user,

os_user,

object_schema,

object_name,

sql_text

from dba_fga_audit_trail;

TIMESTAMP DB_USER OS_USER OBJECT_ OBJECT_N SQL_TEXT

——— ——- ——- ——- ——– —————————

08-FEB-06 ARUP arup BANK ACCOUNTS select * from bank.accounts

The view shows many other columns as well.

You can customize FGA in a variety of ways. For instance, if you want to record when a user selects the column BALANCE and only when the balance is more than 20,000, you could add additional parameters while defining the FGA policy as:

begin

dbms_fga.add_policy (

object_schema =>’BANK’,

object_name =>’ACCOUNTS’,

policy_name =>’ACCOUNTS_ACCESS’,

audit_column => ‘BALANCE’,

audit_condition => ‘BALANCE >= 20000’

);

end;

Separating Roles

One of the most contentious issues in database security is that of role separation. To administer a database, the DBA must have certain privileges, which is often rolled into a role like SYSDBA or even DBA. However, this role also has powers such as granting privileges to others and selecting all data from any table. This super-user privilege goes against the grain of most regulations such as Sarbanes-Oxley and GLBA because it allows a single person to become too powerful.

The DBA role is a purely technical one that relates to the physical database, and which is generally assigned to a pool of people. If the same people also have the ability to see and change any user data, then this creates a serious potential security risk of data vulnerability. Accountability is questionable as the DBA can eliminate any data, even the audit trail. However, most compliance requirements demand a clear separation of duties and abilities. Thus, the privileges required to administer the physical database should not include those to query or modify user data.

Traditionally, Oracle Database has not supported that separation. Therefore, in 2006, Oracle announced two revolutionary tools (both in beta at the time of this writing). The first, Oracle Database Vault (an option to Oracle Database 10g Release 2 EE) allows separation of privileges by creating "realms" of authorization. For instance, the DBA role will no longer have carte blanche to query and modify any table. Instead, the role will be limited to a specific domain of privileges, which is implemented as a realm. Thus, Database Vault does away with the concept of an omnipotent super-user who can erase all traces.Similarly, Oracle Audit Vault, a standalone product, provides a secure auditing facility that can be in a separate realm outside the DBA’s control or that of the application manager; the audit trail can be controlled only by a security administrator. Using these two tools, the three relevant roles hereadministering the physical database, managing object privileges, and managing the audit trailcan be completely separated, the end state demanded by almost all compliance provisions.

Another feature of FGA is that it can call a stored procedure in addition to the recording in the audit trail when the conditions are satisfied. This offers enormous advantage in certain cases involving additional processing, such as sending emails. Such SPs are called handler modules of the FGA policy. Remember, FGA can be activated by SELECT statements, which can then activate these handler modules. In a way, that makes handler modules "trigger on select" statements.

Oracle Database 10g introduced more FGA enhancements as well, such as these two noteworthy features:

Bind variables. Using FGA you can capture bind variables in statements using a parameter in DBMS_FGA.ADD_POLICY procedure.

audit_trail => DB_EXTENDED

You can put this in the database initialization parameter file so that FGA records it all the time.

Relevant columns. Consider these two statements:

select balance from accounts where account_no = 9995;

select sum(balance) from accounts;

The first one clearly asks for a specific piece of sensitive information identifiable to a customer, something you may want to audit. The second one is more benign, whereby the user does not find any specific sensitive data about a customer. In your security policy you may want to record the first statement, but not the second. This will help limit the size of the trail.

You can do that by using another parameter, audit_column_opts => DBMS_FGA.ALL_COLUMNS, while defining the policy:

begin

dbms_fga.add_policy (

object_schema => ‘ANANDA’,

object_name => ‘ACCOUNTS’,

policy_name => ‘ACCOUNTS_SEL’,

audit_column => ‘ACCOUNT_NO, BALANCE’,

audit_condition => ‘BALANCE >= 20000’,

statement_types => ‘SELECT’,

audit_column_opts => DBMS_FGA.ALL_COLUMNS

);

end;

The default is DBMS_FGA.ANY_COLUMNS, which triggers an audit trail whenever any of the columns is selected.

Here we have merely scratched the surface of FGA. For a more complete understanding of FGA with additional examples, please refer to my three-part article series "Fine Grained Auditing for Real-World Problems. " You will also find extensive discussions on FGA in my book Oracle PL/SQL for DBAs (2005, O’Reilly Media).

Strategy
The biggest advantage of FGA over regular auditing is it does not require any specific initialization parameters and therefore does not need a database bounce. You can enable or disable FGA policies at will on objects.

Specifically, you should be looking for sensitive columns, and optionally, sensitive values in those columns. First, you need to formulate a policy for auditing. Here is a typical example of such a policy:

In the table SALARIES:

  • Audit when someone SELECTs only the columns SALARY and BONUS. Do not audit when other columns are selected.
  • Audit even when the user selects only one column such as SALARY alone, without any identifying information such as EMPNO.
  • Audit whenever anyone selects any column from the table for EMPNOS below 1,000, which are reserved for executive management.
  • Do not audit when the user SAP_PAYROLL_APP selects. This is the account user id used by the payroll processing application and auditing this will generate volumes of data and fill up the trail.
  • HR assistants Monica and Adam regularly check and adjust the salary of employees in the low grades whose salary falls under 1000. Their SELECT statements for salaries under 1000 should not be audited.
  • In addition to audit, execute a stored procedure enqueue_message that sends an email to a security officer.

With this in mind, you should build two different FGA policies.

First, the universal policy for EMPNO >= 1000 and for columns SALARY and BONUS only.

begin

dbms_fga.add_policy (

object_schema => ‘ACCMAN’,

object_name => ‘SALARY’,

policy_name => ‘SAL_SEL_UNIV’,

audit_column => ‘SALARY, BONUS’,

statement_types => ‘SELECT’,

audit_option => ‘EMPNO >= 1000 and USER NOT IN(”SAP_PAYROLL_APP”, ”MONICA”, ”ADAM”)’,

handler_module => ‘ENQUEUE_MESSAGE’

);

end;

Second, build the all-columns policy for EMPNO < 1,000.

begin

dbms_fga.add_policy (

object_schema => ‘ACCMAN’,

object_name => ‘SALARY’,

policy_name => ‘SAL_SEL_EXEC’,

statement_types => ‘SELECT’,

audit_option => ‘EMPNO < 1000’,

handler_module => ‘ENQUEUE_MESSAGE’

);

end;

Third, add the special policy for MONICA and ADAM.

begin

dbms_fga.add_policy (

object_schema => ‘ACCMAN’,

object_name => ‘SALARY’,

policy_name => ‘SAL_SEL_SPEC’,

audit_column => ‘SALARY, BONUS’,

statement_types => ‘SELECT’,

audit_option => ‘EMPNO >= 1000 AND SAL <= 1000 and USER IN

(”MONICA”, ”ADAM”) AND USER != ”SAP_PAYROLL_APP”’,

handler_module => ‘ENQUEUE_MESSAGE’

);

end;

As you can see, the conditions in the audit_option are mutually exclusive, so only one policy will be in effect and only one record will be written when any user attempts the SELECT statements.

Using this strategy, you can build a set of FGA policies. You can then enable and disable policies at will without impacting operation.

Implications
There are four serious implications:

  1. If the handler module, if defined, throws errors while the selection is made, it will cause different behavior in different versions of Oracle:
    • In Oracle9i Database, it silently stops retrieving that row without reporting an error. So, if there are 100 rows and 4 of them satisfied the audit condition, the handler module fire 4 times and each time it will fail. The query will return only 96 rows, without reporting any error and you ill never know that it happened. This obviously leads to inaccurate results.
    • In Oracle Database 10g Release 1, it will ignore the errors in the handler module and retrieve all 100 rows as expected.
    • In Oracle Database 10g Release 2, it will report the error on the user’s session performing the query without returning any rows, not even the 96 rows that did not satisfy the audit condition and didn’t execute the handler function.

Therefore, test the FGA handler module thoroughly before implementing it.

  1. The audit trail tableFGA_LOG$is in the SYSTEM tablespace. As more FGA entries are generated, the tablespace fills up, which may cause database to halt.
  2. The audit trail is written to a table, although asynchronously. This induces a transaction as well as I/O, which adds to the overall I/O in the database. If your database is I/O bound, you will see a performance impact on the entire database as a result of FGA.
  3. The audit trails are written asynchronously using autonomous transactions. Therefore even if a user rolls back the transaction, the trail entry is not deleted, leading to false positives. If you are thinking about using FGA as a foolproof mechanism to identify users, you should be aware of these false positives.

Action Items

  1. Identify sensitive tables and columns.
  2. Identify degree of sensitivity to accesse.g., salary below 500 is OK.
  3. Put all possible combinations on a piece of paper and then combine them into a pieces of WHERE conditions (predicates) in such a way that any given condition will be satisfied by a single predicate; not more.
  4. Build the FGA policy from those predicates.
  5. Enable FGA policies.
  6. After some time, analyze the FGA audit trail files.
  7. Build a purge schedule and purge the FGA trail table.

4.2 Activate a Virtual Private Database

Background
If you are already familiar with Application Contexts and Virtual Private Database (also known as Row Level Security or Fine Grained Access Control) you may skip this section and jump straight to the Strategy Section.

Virtual Private Database (VPD) is a large topic, so I will just cover the basics here. For background refer to my Oracle Magazine article on the topic; and as with FGA, more information can be found in my book.

Suppose you have a table called ACCOUNTS with the following data:

SQL> select * from accounts;

ACCNO ACC_NAME ACC_BAL

———- ——————– ———-

1 John 1000

2 Jill 1500

3 Joe 1200

4 Jack 1300

You want to make sure only people with proper authorization should see account balances allowed for their levelsthat is, Level 1 should not see balances of more than 1,000, Level 2 not more than 1,200, and Level 3 should see all. You have another table to show users and their levels.

SQL> select * from userlevels;

USERNAME USERLEVEL

—————————— ———-

CLERK1 1

CLERK2 2

CLERK3 3

To hold the user’s level when they first login, you will need to create an application context:

create context user_level_ctx using set_user_level_ctx;

and its associated trusted procedure:

create or replace procedure set_user_level_ctx

(

p_level in number

)

as

begin

dbms_session.set_context (

‘USER_LEVEL_CTX’,

‘LEVEL’,

p_level

);

end;

Then you need to create a login trigger to set the proper application context.

create or replace trigger tr_set_user_level

after logon

on database

declare

l_level number;

begin

select userlevel

into l_level

from arup.userlevels

where username = user;

set_user_level_ctx (l_level);

exception

when NO_DATA_FOUND then

null;

when OTHERS then

raise;

end;

This sets the stage for setting the user levels in the application context attributes. Let’s test to make sure:

SQL> conn clerk1/clerk1

Connected.

SQL> select sys_context(‘USER_LEVEL_CTX’,’LEVEL’) from dual;

SYS_CONTEXT(‘USER_LEVEL_CTX’,’LEVEL’)

————————————–

1

SQL> conn clerk2/clerk2

Connected.

SQL> select sys_context(‘USER_LEVEL_CTX’,’LEVEL’) from dual;

SYS_CONTEXT(‘USER_LEVEL_CTX’,’LEVEL’)

————————————–

2

SQL> conn clerk3/clerk3

Connected.

SQL> select sys_context(‘USER_LEVEL_CTX’,’LEVEL’) from dual;

SYS_CONTEXT(‘USER_LEVEL_CTX’,’LEVEL’)

————————————–

3

As you can see, each user ID properly sets the levels. Now, you can build the VPD on the table. The whole VPD infrastructure can be controlled using the supplied PL/SQL package DBMS_RLS ; the rules governing which rows should be shown are controlled by a concept called a policy. A policy applies a "predicate" (a WHERE condition) to all the queries on the table, effectively restricting the access to rows. The WHERE condition is generated by a function called a policy function. So, first we have to create the policy function that returns a WHERE condition to be applied to the queries.

create or replace function get_acc_max_bal

(

p_schema in varchar2,

p_obj in varchar2

)

return varchar2

as

l_ret varchar2(2000);

begin

select

case userlevel

when 1 then ‘acc_bal <= 1000’

when 2 then ‘acc_bal <= 1200’

when 3 then null

else

‘1=2’

end

into l_ret

from userlevels

where username = USER;

return l_ret;

end;

then add the policy:

begin

dbms_rls.add_policy (

object_name => ‘ACCOUNTS’,

policy_name => ‘ACC_MAX_BAL’,

policy_function => ‘GET_ACC_MAX_BAL’,

statement_types => ‘INSERT, UPDATE, DELETE, SELECT’,

update_check => TRUE

);

end;

At this time the table is protected. When CLERK1 logs in and selects from the table:

SQL> select * from arup.accounts;

ACCNO ACC_NAME ACC_BAL

———- ——————– ———-

1 John 1000

Clerk1 sees only ACCNO 1, with balance 1000. Since he is not authorized to see anything above that account balance, the other accounts are invisible to him. But when Clerk2 logs in:

SQL> conn clerk2/clerk2

Connected.

SQL> select * from arup.accounts;

ACCNO ACC_NAME ACC_BAL

———- ——————– ———-

1 John 1000

2 Joe 1200

She can see ACCNO 2 as well. The balance of ACCNO 2 is 1200, within the authorized limit for Clerk2.

Using this technique you can build a sort of restricted view into the table. This will be a very handy tool in Project Lockdown.

Strategy
The key is to find out what information is to be protected from all parties and on which columns. This sounds much easier than it actually is. Collecting this information requires business knowledge, or at least collaboration with someone more familiar with those processes. Once you identify the tables and columns, you should be able to implement the VPD policy as shown in the examples in the Background section above.

What if you want to let some users have unrestricted access to the tables even though the VPD is in effect? The role EXEMPT ACCESS POLICY does exactly that.

grant exempt access policy to ananda;

From that point on, ANANDA will bypass all access policies defined on all tables.

It is probably unacceptable to allow a user to bypass all access restrictions, however; a better solution is to code it in the policy function itself. A good example is the schema owner of the tableyou definitely want it to see all the rows of the table it owns without restriction. You can code it in the policy function as follows:

create or replace function get_acc_max_bal

(

p_schema in varchar2,

p_obj in varchar2

)

return varchar2

as

l_ret varchar2(2000);

begin

if (p_schema = USER) then

l_ret := NULL;

else

select

case userlevel

when 1 then ‘acc_bal <= 1000’

when 2 then ‘acc_bal <= 1200’

when 3 then null

else

‘1=2’

end

into l_ret

from userlevels

where username = USER;

end if;

return l_ret;

end;

This version of the function returns NULL when the owner of the table logs in (p_schema = USER) and therefore provides unrestricted access to the table. You can, of course, make any changes in the function to allow more users to bypass the VPD policy.

The biggest challenge in VPD is putting the restriction on the child tables. Your limiting condition may be on a column called, say, ACC_BAL; but all other child tables may not have the column. So how can you restrict those tables?

For example, here is a table called ADDRESSES, which contain the customer addresses. This table does not have a column called ACC_BAL, so how can you put the same restriction on this table, as on ACCOUNTS? There are two ways:

  • Add a column ACC_BAL on the table ADDRESSES. This column may be updated through a trigger on the main table ACCOUNTS. Now you can define a policy on the table using the same policy function used on ACCOUNTS.
  • Use a different policy function for ADDRESSES. In this function the return value should be

ACCNO in (SELECT ACCNO FROM ACCOUNTS)

This is the predicate used in the policy restriction. So, the address will be displayed only of the account exists on ACCOUNTS, and since the table ACCOUNTS is restricted anyway, the table ADDRESSES will be automatically restricted.

You have to choose between these two approaches based on your situation.

Implications
There are several potentially damaging implications:

  • VPD works by rewriting queries to add the additional predicate. The user queries may have been well written and perhaps well tuned, but the introduction of the additional predicate does throw a wrench into that since the optimization plan may change. You should carefully consider the potential impact and mitigate the risk by building indexes.
  • Materialized views work by selecting the rows from the underlying tables. If the schema owner of the view does not have unrestricted access to the table, only those rows that satisfy the VPD policy will be refreshed, rendering the view inaccurate.
  • If you have set up replication, the users doing the propagation and reception should have unrestricted access to the table, else they will replicate only part of a table.
  • If you load the table using Direct Path Insert (INSERT with the APPEND hint), then you cannot have a VPD policy on the table. Either you should disable the policy temporarily or do the insert using a user that has unrestricted access to the table.
  • Direct Path Exports bypass the SQL Layer; hence the VPD policy will not be applied. Therefore, when you export a table using DIRECT=Y option, Oracle ignores it and exports it using the conventional path. This may add to the overall execution time.

Action Plan

This is a complex and fluid plan:

  1. Identify the tables to be protected by VPD.
  2. Identify the columns of those tables that need to be protected.
  3. Identify the restricting condition, e.g. Salaries > 1000.
  4. Identify how to establish privilegesfor example, do users have authority levels or roles? You may want to divide users into three groups with certain levels of authority associated with them. Or, perhaps you want to assign access restrictions on groups of users based on rolemanagers can access all rows, clerks can access SALARY > 2000, and so on.
  5. Decide how you will pass the privileges to the policy functionthrough a package variable, through an application context, or through a static table.
  6. Identify if further indexes need to be created.
  7. Create additional indexes.
  8. Identify child tables and decide on a scheme to enable the restriction on themvia a new column or the IN condition.
  9. Re-identify the need for indexes based on your decision above.
  10. Create indexes.
  11. Build the policy function.
  12. Create the policy (but as disabled).
  13. On a light-activity time of day, enable the policy and test accessing the table from a regular user account to make sure that the policy works. If it does not work, check trace files to find the error.
  14. If it works, enable the policy.
  15. Monitor performance.
  16. Identify further needs to build indexes, use outlines, and so on.
  17. Iterate for all tables.

4.3 Mask Sensitive Columns

Background
Imagine that you’re an amateur database hacker. The database in this case contains medical records and the information you are looking for is diagnosis codes. What column would you look for? Probably one named DIAGNOSIS, DISEASE, or something similar.

As you can see, sensitive columns with obvious names are a crucial security issue.

Strategy
When adversaries have no prior knowledge of the contents of your database, they will not decipher the meaning of your columns if they have nonintuitive names. This is a strategy known as "security by obscurity." Even seasoned adversaries who deliberately force into the database will still need to track down the column names before they can do anything else. Since they may have limited timethey almost always dothey will usually move on to the next opportunity. Of course, using obscure column names makes development harder as well.

There is an alternative to making this tradeoff, however: column masking, in which you hide the contents of the column and expose it only to legitimate users.

There are two column-masking approaches: by using a view and by using VPD.

Using a View. This method is applicable to any version of Oracle but usually the only choice when your database release is Oracle9i or earlier.

Suppose your table looks like this:

SQL> desc patient_diagnosis

Name Null? Type

——————- —— ————-

PATIENT_ID NUMBER

DIAGNOSIS_ID NUMBER

DIAGNOSIS_CODE VARCHAR2(2)

DOCTOR_ID NUMBER(2)

BILLING_CODE NUMBER(10)

The rows look like this:

SQL> select * from patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE

———- ———— — ———- ————

1 1 01 1 1003

1 2 02 1 1003

1 3 11 1 1003

2 1 11 1 1005

2 2 41 2 1005

In this case, you want to hide the values of the column DIAGNOSIS_CODE.

create view vw_patient_disgnosis

as

select

patient_id,

diagnosis_id,

doctor_id,

billing_code

from patient_diagnosis

/

Then you can create a synonym PATIENT_DIAGNOSIS for the view VW_PATIENT_DIAGNOSIS and grant select on the view instead of the table. The view hides the column DIAGNOSIS_CODE.

This is a rather simplistic solution, so instead of masking the column for all users, you may want to create role-based obscuritywhen the user is a manager, show the protected columns; else don’t. You can do so by passing an application context or a global variable to designate the role of the user. If the application context attribute were IS_MANAGER, you could use:

create or replace view vw_patient_disgnosis

as

select

patient_id,

diagnosis_id,

decode(

sys_context(‘USER_ROLE_CTX’,’IS_MANAGER’),

‘Y’, DIAGNOSIS_CODE, null

) diagnosis_code,

doctor_id,

billing_code

from patient_diagnosis;

This is a more flexible view that can be granted to all the users and the contents of the view will be dynamic based on the user’s role.

Using VPD. Oracle Database 10g introduced a feature that makes VPD even more useful: There is no need to create a view. Rather, the VPD policy can suppress its display. In this case, the VPD policy function will look like this:

1 create or replace function pd_pol

2 (

3 p_schema in varchar2,

4 p_obj in varchar2

5 )

6 return varchar2

7 is

8 l_ret varchar2(2000);

9 begin

10 if (p_schema = USER) then

11 l_ret := NULL;

12 else

13 l_ret := ‘1=2’;

14 end if;

15 return l_ret;

16 end;

Now create the policy function:

1 begin

2 dbms_rls.add_policy (

3 object_schema => ‘ARUP’,

4 object_name => ‘PATIENT_DIAGNOSIS’,

5 policy_name => ‘PD_POL’,

6 policy_function => ‘PD_POL’,

7 statement_types => ‘SELECT’,

8 update_check => TRUE,

9 sec_relevant_cols => ‘DIAGNOSIS_CODE’,

10 sec_relevant_cols_opt => dbms_rls.all_rows

11 );

12 end;

Note line numbers 9 and 10. In line 9, we mention the column DIAGNOSIS_CODE as a sensitive column. In line 10, we specify that if the column is selected, all rows are displayed; but the column value is shown as NULL. This effectively masks the column. From the policy function, note that the predicate applied is NULL when the owner of the table selects from itso, the VPD restrictions are not applied and the column is shown.

Remember, there is no way to "replace" a policy. If an old policy exists you would have to drop it first.

begin

dbms_rls.drop_policy (

object_schema => ‘ARUP’,

object_name => ‘PATIENT_DIAGNOSIS’,

policy_name => ‘PD_POL’

);

end;

Now you can test it.

SQL> conn arup/arup

Connected.

SQL> select * from patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE

———- ———— — ———- ————

1 1 01 1 1003

1 2 02 1 1003

1 3 11 1 1003

2 1 11 1 1005

2 2 41 2 1005

Note that the DIAGNOSIS_CODE column values are shown, since ARUP is the owner of the table and should see the values. Now, connect as another user who has select privileges on the table and issue the same query.

SQL> set null ?

SQL> conn ananda/ananda

SQL> select * from arup.patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID D DOCTOR_ID BILLING_CODE

———- ———— – ———- ————

1 1 ? 1 1003

1 2 ? 1 1003

1 3 ? 1 1003

2 1 ? 1 1005

2 2 ? 2 1005

Note how the column DIAGNOSIS_CODE shows all null values.

This method is much more elegant, even apart from the fact that there is no view to be created on the table, no synonym to be created to point to the view, and no additional grants to be maintained. If you need to have different policies to show this value to different people, you can easily modify the policy function (line 11) to add further checks. For instance, your policy may say that less sensitive diagnosis codes such as those for common cold be exposed to all users. So, your policy function will look like the following, assuming the DIAGNOSIS_CODE for common cold is "01".

1 create or replace function pd_pol

2 (

3 p_schema in varchar2,

4 p_obj in varchar2

5 )

6 return varchar2

7 is

8 l_ret varchar2(2000);

9 begin

10 if (p_schema = USER) then

11 l_ret := NULL;

12 else

13 l_ret := ‘diagnosis_code=”01”’;

14 end if;

15 return l_ret;

16* end;

Note line 13, where we added the additional predicate to show only when the diagnosis code is "01" and nothing else. Now, test it.

SQL> conn arup/arup

Connected.

SQL> select * from arup.patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE

———- ———— — ———- ————

1 1 01 1 1003

1 2 02 1 1003

1 3 11 1 1003

2 1 11 1 1005

2 2 41 2 1005

SQL> conn ananda/ananda

Connected.

SQL> select * from arup.patient_diagnosis;

PATIENT_ID DIAGNOSIS_ID DI DOCTOR_ID BILLING_CODE

———- ———— — ———- ————

1 1 01 1 1003

1 2 ? 1 1003

1 3 ? 1 1003

2 1 ? 1 1005

2 2 ? 2 1005

Note the diagnosis code is "01" for patient id 1 and diagnosis id 1, which is the only allowed diagnosis code; so it is shown clearly. All others have been shown as null and are effectively masked.

Implications
If you want to mask sensitive columns and the programs do not even mention them, there will be no implications.

If you use the view approach, where the sensitive column is simply removed from the view, it might pose a problem where the programs use a construct like SELECT * FROM TABLE …. Since the columns have not been explicitly named, the absence of one column will affect the program execution. But this is not the issue with the modified view approach where the column is still present but NULLed.

There is one very important implication you should be aware of. Suppose you have a column called CONTRACT_AMOUNT, which is shown if less than a certain value, say $500. If more than $500, then the column shows NULL. The table has three rows with values 300, 300, and 600 in the column. Prior to column masking, if a user issues the query

select avg (contract_amount) from contracts;

he will get 400 (the average of 300, 300, and 600). After column masking, the value of the column will be NULL for the record where the value is $600, so the user will see the values as 300, 300, and NULL. Now the same query would show 200 (300, 300, and NULL). Note the important difference: The value shown is 200; not 400.

Be aware of this important difference column masking can introduce.

Action Plan

  1. List all sensitive columns on all sensitive tables.
  2. Decide on sensitive columns to be masked.
  3. Decide on the privilege scheme.
  4. If you are on Oracle Database 10g Release 1 or later, choose the VPD approach.
    ELSE
    Choose the view-based approach.
  5. If you choose the VPD approach:
    1. Create policy functions for each table.
    2. Create policies for each table. This will help you control masking on specific tables.
  6. If you choose the view-based approach:
    1. Create views on each table, typically named VW_<table_name>.
    2. Create a synonym (the same name as the table) pointing to the view.
    3. Revoke privileges made to the users on the table.
    4. Re-grant the privileges to the view.
    5. Recompile all dependent invalid objects.

4.4 Encrypt Sensitive Data

Background
As I mentioned previously in this series, security is like protecting yourself on a cold winter day with layers of clothing, versus wearing the bulkiest winter jacket available. But building layered defenses may not deter the most determined adversary, and certainly won’t always prevent a legitimate user from stealing corporate assets. The last line of defense in such a case is encryption, by which the data is accessible to the user (or the adversary) but only with a key. Without the key, the data is useless. If you protect the key, you will protect the data.

Remember, encryption is not a substitute for other layers of security. You must have those defenses in place regardless.

Again, encryption is a vast topic, but I’ll try to give you an actionable overview here.

Oracle provides two types of encryption:

  1. Encryption APIs such as the packages dbms_obfuscation_toolkit and dbms_crypto (in Oracle Database 10g Release 1 and later). Using these packages you can build your own infrastructure to encrypt data. This is the most flexible approach, but rather complex to build and manage.
  2. Transparent Data Encryption , a feature of Oracle Database 10g Release 2 and later, obviates manual key management. The database manages the keys but as the name suggests, the encryption is transparentdata is stored in an encrypted manner only. When selected, it will be in clear case.

I recommend that you review the Oracle documentation (Chapter 17 of the Oracle Database Security Guide and Chapter 3 of the Oracle Database Advanced Security Administrator’s Guide, respectively) before proceeding to the Strategy section.

Strategy
The choice between regular encryption and Transparent Data Encryption is a vital one. (In releases prior to Oracle Database 10g Release 2, however, only the former is available.)

  • In either case you will have to identify the tables, and more specifically the columns, to be encrypted. It’s probably not a good idea to encrypt all columns as encryption routines do burn CPU cycles.
  • Next, pick an encryption algorithm. A typical choice is Triple Data Encryption Standard with 156-bit encryption (DES3). However, starting with Oracle Database 10g Release 1, you have access to the newer, faster, and more secure Advanced Encryption Standard (AES) algorithm that operates with a 128-bit long key.
  • Oracle9i Database provided dbms_obfuscation_toolkit; with Oracle Database 10g Release 1, you have access to dbms_crypto , a much better utility. The older package is still available, but avoid it if you are building encryption infrastructure for the first time.
  • At this point you have to decide between TDE and your own routine (if applicable). The table below may help you decide.
  Transparent Data Encryption User-built Encryption
Flexibility MinimalFor instance, if the column SALARY in SALARIES table is encrypted, then any user with access to the table will be able to see the data clearly. You can’t place selective control on that column based on user roles and levels. The data in the database is encrypted but is decrypted when accessed. RobustFor instance, you may define the column to be shown in clear text only if the user is a manager; and encrypted otherwise. This will ensure the same application sees the data differently based on who is using them. This flexibility can also be expanded to other variables, such as time of day or the client machine accessing the database.
Setup MinimalThis facility is truly transparentthere is nothing to do but issue this command (provided all other one-time jobs have been executed, such as building the wallet):

ALTER TABLE SALARIES MODIFY (SALARY

 

Author: admin