In our previous post, we learnt what the Oracle Virtual Database was all about. If you haven’t read it click here.
Without wasting much of your time, let get busy with our practicals
What is needed
- Working Oracle database (10g and above)
- HR sample schema.
- Sql work environment (SQL Navigator, Toad, etc)
Today’s tutorial will look at the following types of Virtual Private Databases:
- Row level VPD which restricts access to specific rows in a table
- Column Level VPD which restricts access to specific columns in a table
Row level VPD
As said earlier we will be using the HR sample schema.
In this tutorial we are going to secure the HR table “EMP” data. This table will contain the employees data.
Scenario:
Your organization requires you to hide a sensitive table (EMP) which keeps employee data from users of the database. You are to create a VPD policy to this object so that no database users can see the data except the owner HR user.
These are the following steps to take1. Login as user HR.2. Create a table called EMP3. Login as user SYS4. Create VPD policy function5. Create the VPD policy.6. Login as user HR7. Query the HR.Emp table.To confirm the VPD is working8. Create a new user Tayo.9. Grant the user Tayo (SELECT) access to the HR.Emp table10. Login with user Tayo11. Query the HR.Emp table.
So let’s begin:
1. Login as User HR
2. Create a table called EMP.
This table will be a direct replica of the EMPLOYEES table;
Type the following query to create the EMP table:
create table EMP
as
select * from employees;
3. Login as user SYS4. Create VPD policy function
To generate the dynamic WHERE clause (predicate), you must create a function (not a procedure) that defines the restrictions that you want to enforce. Usually, the security administrator creates this function in his or her own schema. In this tutorial we will be using the SYS user.
Logon as the SYS account and type the following PLSQL VPD policy function.
Note that sys user id creating the VPD function on behalf of user HR
CREATE OR REPLACE FUNCTION HR.EMP_NO_SEE_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’THENpredicate_var:=’1=1′;ELSEpredicate_var:=’1=2′;END IF;
RETURN predicate_var;END EMP_NO_SEE_FUNC;/
5. Create the VPD policy.
We are going to create the policy EMP_NO_SEE_POL by using the ADD_POLICY procedure in the DBMS_RLS package.
Login as user SYS and type the following query.
BEGIN
DBMS_RLS.ADD_POLICY (
object_schema => ‘HR’,
object_name => ‘EMP’, policy_name => ‘EMP_RESTRICT_POLICY’, function_schema => ‘HR’, policy_function => ‘EMP_NO_SEE_FUNC’, statement_types => ‘SELECT’ ); END;/
6. Login as user HR7. Query the HR.Emp table.Use the following querySelect * from EMP;
As you can see from the screenshot that the HR user can view the EMP tableNow let’s create a new user and and see if he can see any data in the table.
8. Create a new user Tayo.Log in as the SYS user.Create a new user Tayo by typing the following querycreate user tayo identified by oracledefault tablespace userstemporary tablespace temp;
Grant user Tayo privilege to login to the database by typing the following query:Grant connect to tayo;
9. Grant user Tayo access privilege to SELECT on the EMP table by typing the following queryGrant select on HR.EMP to Tayo;
10. Login with user Tayo
Query the HR.Emp table using the following query:Select * from HR.EMP;.
As you can see from the screenshot that though the user Tayo is able to query the table, there is no data in the EMP table as it is hidden since the VPD policy only allows the owner HR to view the contents of EMP table.
