Virtual Private Database: A practical approach: Column Level VPD
Column
Level VPD
In my previous post we did a practical on how to perform
Row level restriction using VPD policies.
This practical will focus on the Column level
restriction. And as said earlier we will be using the HR sample schema.
In this tutorial we are going to secure some columns in the
HR table “EMPLOYMENT” data.
Scenario:
Your organization requires you to hide sensitive columns
on the EMPLOYMENT table. This table keeps employee data. Management are concerned
about information leaking on staff salary and commission. You are to create a VPD
policy to the EMPLOYMENT table to prevent all database users from seeing the SALARY and COMMISSION column in the EMPLOYMENT table. Only the owner of the table HR
is allowed to see the columns.
Assumptions
This tutorial assumes that you should have completed the
first practical lesion. It is a prerequisite to this practical lesson. If you haven't click here to begin
These are the following steps to take
1.
Login as user HR.
2.
Create a table called EMPLOYMENT
3.
Login as user SYS
4.
Create VPD policy function
5.
Create the VPD policy.
6.
Login as user HR
7.
Query the HR. EMPLOYMENT table.
To confirm the VPD
is working
8.
Login as user SYS
9.
Grant the user Tayo (SELECT) access to the HR. EMPLOYMENT
table
10.
Login with user Tayo
11.
Query the HR. EMPLOYMENT table and observe the
output
So let’s begin…
1. Login as User HR and
2. Create a table called EMPLOYMENT.
This table will be a direct replica of the EMPLOYEES
table
Type the following query to create the EMPLOYMENT table:
create table EMPLOYMENT
as
select * from
employees;
3. Login as user SYS
4. Create VPD policy function
We need a VPD policy function so that no database user other than
“APPS” can see the data for below columns-
- SALARY
- COMMISSION_PCT
A function needs to be created to define the restrictions
that you want to enforce. Usually, the security administrator creates this
function in his or her own schema.
Logon as the SYS account and type the following PLSQL VPD
policy function.
Note that sys user
is creating the VPD function on behalf of user HR in this practical class
CREATE OR REPLACE FUNCTION HR.EMPLOYMENT_COL_RES_FUNC (schema_var
VARCHAR2, obj_var VARCHAR2)
RETURN VARCHAR2 AS
user_var VARCHAR2(100);
predicate_var VARCHAR2(1000);
BEGIN
SELECT USER INTO user_var FROM dual;
IF user_var='HR'
THEN
predicate_var:='1=1';
ELSE
predicate_var:='1=2';
END IF;
RETURN predicate_var;
END EMPLOYMENT_COL_RES_FUNC;
/
5. Create the VPD policy.
Let us now create the VPD policy to use the function we
have just created.
We are going to create the policy EMPLOYMENT_COL_RES_POLICY by using the ADD_POLICY procedure in the DBMS_RLS package.
Login as user SYS
Type the following query
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => 'HR',
object_name => 'EMPLOYMENT',
policy_name => 'EMPLOYMENT_COL_RES_POLICY',
function_schema => 'HR',
policy_function =>
'EMPLOYMENT_COL_RES_FUNC',
statement_types => 'SELECT',
sec_relevant_cols
=> 'SALARY, COMMISSION_PCT),
sec_relevant_cols_opt => dbms_rls.all_rows
);
END;
/
The Sec_relevant_cols enables
column-level Virtual Private Database (VPD), which enforces security policies
when a column containing sensitive information is referenced in a query.
Applies to tables and views, but not to the synonyms. Specify a list of commas-
or space-separated valid column names of the policy-protected object. Default
is all the user-defined columns for the object.
The sec_relevant_cols_opt
on the other hand is used with sec_relevant_cols to display all rows for
column-level VPD filtered queries (SELECT only), where sensitive columns appear
as NULL. Default is set to NULL, which allows the filtering defined with
sec_relevant_cols to take effect. Set to dbms_rls. ALL_ROWS to display all
rows, but with sensitive column values, which are filtered by
sec_relevant_cols, displayed as NULL.
6. Login as user HR
7. Query the HR.EMPLOYMENT table.
Use the following query
Select
employee_id,first_name,last_name,salary,commission_pct from EMPLOYMENT
where
commission_pct is not null;
As you can see from the screenshot that the HR user can view the salary
and the commission_pct columns in the EMPLOYEMENT table.
Now let’s create a new user and see if he can see those columns in
the same table
8. Log in as the SYS user.
9. Grant the user Tayo (SELECT) access to the HR.
Grant select on
HR.EMPLOYMENT to Tayo;
Note
that user tayo had already been created in the last practical class. If you
missed it click here
9. Login with user Tayo
10. Query the HR.EMPLOYEMENT table by typing the following
query:
Select
employee_id,first_name,last_name,salary,commission_pct from EMPLOYMENT
where
commission_pct is not null;
As you can see from the screenshot that though the user Tayo is
able to query the table, there is no data in the Salary and Commission_pct
Columns. It is hidden since the VPD policy only allows the owner HR to view the
restricted columns in the EMPLOYMENT table.
Conclusion:
VPD enables you to control access to table columns and rows by
database users. VPD policy groups and driving application context allows for
selective hiding of certain application table columns for different application
users. It should however be noted that the policy function should not have
complex logic as that might cause some performance issue in the database.
I hope this tutorial was very helpful.
For further reading you can visit:
Comments
Post a Comment