# Virtual Private Database (VPD) in PPAS 9.1

##### [Vibhor Kumar](/content/blog/author/vibhor-kumar/index.html)

February 10, 2012

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1

Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below:

**1\. Create an Application Context**  
**2\. Create security policies functions**  
**3\. Apply security policies to tables**

Lets see how user can implement it in Advanced Server.

1\. Setup an environment as given below:

```
 CREATE user merry identified by edb;
 CREATE user john identified by edb;
 CREATE TABLE public.john_merry(userid varchar2(200),val numeric);
 grant select,update,delete,insert on john_merry to john;
 grant select,update,delete,insert on john_merry to merry;
```

2\. Now create a Policy Function as given below:

```
 CREATE OR REPLACE FUNCTION verify_user (
 p_schema VARCHAR2,
 p_object VARCHAR2
 )
 RETURN VARCHAR2
 IS
 BEGIN
 RETURN 'userid = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
 END;
```

3\. Apply Security Policy using Policy Functions as given below:

```
 DECLARE
 v_object_schema VARCHAR2(30) := 'public';
 v_object_name VARCHAR2(30) := 'john_merry';
 v_policy_name VARCHAR2(30) := 'secure_data';
 v_function_schema VARCHAR2(30) := 'enterprisedb';
 v_policy_function VARCHAR2(30) := 'verify_user';
 v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
 v_update_check BOOLEAN := TRUE;
 v_enable BOOLEAN := TRUE;
 BEGIN
 DBMS_RLS.ADD_POLICY(
 v_object_schema,
 v_object_name,
 v_policy_name,
 v_function_schema,
 v_policy_function,
 v_statement_types,
 v_update_check,
 v_enable
 );
 END;
```

Now, lets see how it works.

First insert some records for John and Merry as given below:

1\. Connect as John user and do some insert as john, as given below:

```
 edb=> insert into john_merry values('john',1);
 INSERT 0 1
 edb=> insert into john_merry values('john',2);
 INSERT 0 1
 edb=> insert into john_merry values('john',3);
 INSERT 0 1
```

2\. Now connect as merry and insert some records as merry:

```
 edb=> insert into john_merry values('merry',1);
 INSERT 0 1
 edb=> insert into john_merry values('merry',2);
 INSERT 0 1
 edb=> insert into john_merry values('merry',3);
 INSERT 0 1
```

Ok. Now we have some data for both users. lets see how VPD works:

1\. Connect as John User and try to insert some record for merry:

```
 edb=> insert into john_merry values('merry',4);
 ERROR: policy with check option violation
 DETAIL: Policy predicate was evaluated to FALSE with the updated values
```

hmm this is expected, VPD security policy won’t allow me to do this activity.

2\. Now try to update Merry data as John User:

```
 edb=> update john_merry set val=4 where val=3 and userid='merry';
 UPDATE 0
```

No rows updated. This is expected since as per security policy merry’s data is not visible to john. However, john can update his record as given below:

```
 edb=> update john_merry set val=4 where val=3 and userid='john';
 UPDATE 1
```

3\. Now, lets execute SELECT query on table to view data:

```
 edb=> select current_user;
 current_user
 --------------
 john
 (1 row)

edb=> select * from john_merry ;
 userid | val
 --------+-----
 john | 1
 john | 2
 john | 4
 (3 rows)
```

As you can see, john can see his records not merry’s. Similary user can try for Merry. Below is a snapshot of SELECT query executed by merry:

using dumb terminal settings.

```
 edb=> select current_user;
 current_user
 --------------
 merry
 (1 row)

edb=> select * from john_merry ;
 userid | val
 --------+-----
 merry | 1
 merry | 2
 merry | 3
 (3 rows)
```

Interesting!!