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 is 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 lesson. 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 EMPLOYMENTasselect * 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
The other columns data apart from the one mentioned above are available to all the users 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);
BEGINSELECT USER INTO user_var FROM dual;IF user_var=’HR’THENpredicate_var:=’1=1′;ELSEpredicate_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 SYSType 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 HR7. Query the HR.EMPLOYMENT table.Use the following querySelect employee_id,first_name,last_name,salary,commission_pct from EMPLOYMENTwhere 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. This time, grant user Tayo access privilege to SELECT on the EMPLOYMENT table by typing the following queryGrant 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 EMPLOYMENTwhere 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.
Leave a Reply