Virtual Private Database: A practical approach
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 take
1.
Login as user HR.
2.
Create a table called EMP
3.
Login as user SYS
4.
Create VPD policy function
5.
Create the VPD policy.
6.
Login as user HR
7.
Query the HR.Emp table.
To confirm the VPD
is working
8.
Create a new user Tayo.
9.
Grant the user Tayo (SELECT) access to the
HR.Emp table
10.
Login with user Tayo
11.
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 SYS
4. 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'
THEN
predicate_var:='1=1';
ELSE
predicate_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 HR
7. Query the HR.Emp table.
Use the following query
Select *
from EMP;
As you can see from the screenshot that the HR user can view the
EMP table
Now
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 query
create user tayo
identified by oracle
default tablespace
users
temporary
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 query
Grant 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.
Click here to view tutorial on Column Level VPD
Comments
Post a Comment