Hide sensitive data with Oracle 10g column masking

by  Bob Watkins

Takeaway: Oracle 10g's Virtual Private Database (VPD) feature has the ability to automatically mask out (set to NULL) certain columns in the results of a query on a row-by-row basis. This tip shows how to create a VPD policy that requests the column masking option.

<!---->

Oracle's Virtual Private Database (VPD) feature (also called Fine Grained Access Control) provides row-level security checking for DML statements such as SELECT. A PL/SQL policy function is associated with a table; the function can examine the current user context and return additional WHERE clause conditions (predicates) to be added to the query. A user or application might write:

SELECT * FROM employees;

But Oracle will actually see the statement as (for example):

SELECT * FROM employees

WHERE department_id = 60;

Therefore, only the allowed rows (those in department 60) would be returned by the query.

The new option in Oracle 10g is that you can request that Oracle return all rows the query would normally return and not just the authorized ones. However, certain columns (called "security relevant" columns) in the unauthorized rows will display as NULL instead of their actual data. All other columns will be shown as usual.

There are two things you must include in a VPD policy to enable column masking. First, you must designate certain columns as "security relevant" to create a column-level policy. Second, you must include the ALL_ROWS option to request that all rows be returned. The combination of the two parameters causes column masking to occur.

Listing A shows a policy function called rls_dept. It returns the predicate "department_id = 60," which means that for queries against the EMPLOYEES table, only rows for Department 60 will be returned. (In reality, this function would not return a static value, but would determine who the current user is and return the correct department number for that user.)

Listing B shows how that function is applied to create column masking. The ADD_POLICY procedure in the package DBMS_RLS creates a new policy called "restrict_dept_policy." The sec_relevant_cols parameter indicates that the salary and commission_pct columns are "security relevant." A query containing them is subject to the policy function, whereas a query without them is not. Finally, the sec_relevant_cols_opts parameter is set to the constant ALL_ROWS.

Column masking is only applied to SELECT statements, but it is enforced regardless of which client is used to access the database, whether it's SQL*Plus, a .NET application, or some other tool.

Miss a tip?

Check out the Oracle archive, and catch up on our most recent Oracle tips.

Bob Watkins (OCP, MCDBA, MCSE, MCT) is a computer professional with 25 years of experience as a technical trainer, consultant, and database administrator. Visit Bob's site

<newselement></newselement>