In order to implement Virtual Private Databases (VPD).The follwing steps as follwed


create a user to act as the schema owner
=====================================
SQL> conn system/manager as sysdba;
Connected.
SQL> show user
USER is "SYS"
SQL> create user mohan1 identified by mohan1 default tablespace users temporary
tablespace temp quota 2m on users;

User created.

SQL> grant connect,resource to mohan1;

Grant succeeded.

SQL> create user allwyn identified by allwyn default tablespace users temporary
tablespace temp quota 2m on users;

User created.

SQL> create user nancy identified by nancy default tablespace users temporary ta
blespace temp quota 2m on users;

User created.

SQL> grant connect,resource to allwyn;

Grant succeeded.

SQL> grant connect,resource to nancy;

Grant succeeded.

SQL> grant execute on dbms_rls to public;

Grant succeeded.

SQL> conn mohan1/mohan1;
Connected.
SQL> create table users (id number(10) not null,ouser varchar2(30) not null, fir
st_name varchar2(30),last_name varchar2(30));

Table created.

SQL> create table user_data(info varchar2(30),user_id number(30) not null);

Table created.

SQL> grant select,insert on user_data to allwyn,nancy;

Grant succeeded.

Create an Application Context
=============================:
SQL> conn system/manager as sysdba;
Connected.
SQL> show user
USER is "SYS"
SQL> grant create any context,create public synonym to mohan1;

Grant succeeded.

SQL> conn mohan1/mohan1;
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
USERS TABLE
USER_DATA TABLE

SQL> insert into users values(1,'allwyn','pothula','allwyn');

1 row created.

SQL> insert into users values(2,'nancy','pothula','nancy');

1 row created.

SQL> commit;

Commit complete.

SQL> show user
USER is "MOHAN1"
SQL> create context MOHAN1 USING MOHAN1.context_package;

Context created.

SQL> create or replace package context_package as procedure set_context;
2 end;
3 /

Package created.

SQL> create or replace package body context_package is
2 procedure set_context is
3 v_ouser varchar2(30);
4 v_id number;
5 begin
6 dbms_session.set_context('MOHAN1','SETUP','TRUE');
7 v_ouser:=SYS_CONTEXT('USERENV','SESSION_USER');
8 begin
9 select id into v_id from users where ouser=v_ouser;
10 dbms_session.set_context('MOHAN1','USER_ID',v_id);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_Session.Set_Context('MOHAN1','USER_ID', 0);
14 END;
15
16 DBMS_Session.Set_Context('MOHAN1','SETUP','FALSE') ;
17 END Set_Context;
18 END Context_Package;
19 /

Package body created.

SQL> GRANT EXECUTE ON MOHAN1.context_package to public;

Grant succeeded.


Create Login Trigger
====================:
SQL> conn system/manager as sysdba;
Connected.
SQL> create or replace trigger mohan.set_security_context after logon on databas
e
2 begin
3 MOHAN1.context_package.set_context;
4 end;
5 /

Trigger created.


Create Security Policies
========================:

SQL> conn mohan1/mohan1;
Connected.
SQL> create or replace package security_package as
2 function user_data_insert(owner varchar2,objname varchar2) return varchar2
;
3 function user_data_select(owner varchar2,objname varchar2) return varchar2
;
4 end security_package;
5 /

Package created.

SQL> create or replace package body security_package is
2 function user_data_insert(owner varchar2,objname varchar2) return varchar2
is
3 predicate varchar2(2000);
4 begin
5 predicate:= '1=2';
6 if (sys_context('userenv','session_user')='MOHAN1') then
7 predicate:=NULL;
8 else
9 predicate:= 'user_id=sys_context("MOHAN1","USER_ID")';
10 end if;
11 return predicate;
12 end user_data_insert;
13 function user_data_select(owner varchar2,objname varchar2) return varchar2
is
14 predicate varchar2(2000);
15 begin
16 predicate:= '1=2';
17 if (sys_context('userenv','session_user')='MOHAN1') then
18 predicate:=NULL;
19 else
20 predicate:= 'user_id=sys_context("MOHAN1","USER_ID")';
21 end if;
22 return predicate;
23 end user_data_select;
24 end security_package;
25 /

Package body created.

SQL> GRANT EXECUTE ON MOHAN1.Security_Package TO PUBLIC;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM Security_Package FOR MOHAN1.Security_Package;

Synonym created.

SQL> SELECT * FROM USERS;

ID OUSER FIRST_NAME
---------- ------------------------------ ------------------------------
LAST_NAME
------------------------------
1 allwyn pothula
allwyn

2 nancy pothula
nancy


Apply Security Policies to Tables
=================================:


SQL> begin
2 dbms_rls.add_policy('MOHAN1','USER_DATA','USER_DAT A_INSERT_POLICY','MOHAN1'
,'SECURITY_PACKAGE.USER_DATA_INSERT','INSERT', TRUE);
3 dbms_rls.add_policy('MOHAN1','USER_DATA','USER_DAT A_SELECT_POLICY','MOHAN1
','SECURITY_PACKAGE.USER_DATA_SELECT','SELECT', TRUE);
4 END;
5 /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM USERS;

ID OUSER FIRST_NAME
---------- ------------------------------ ------------------------------
LAST_NAME
------------------------------
1 allwyn pothula
allwyn

2 nancy pothula
nancy


SQL> SELECT * FROM USERS;

ID OUSER FIRST_NAME
---------- ------------------------------ ------------------------------
LAST_NAME
------------------------------
1 allwyn pothula
allwyn

2 nancy pothula
nancy




Finally, test that the VPD is working correctly or not:
================================================== ==



SQL> conn allwyn/allwyn;
Connected.
SQL> insert into mohan1.user_data values('allwyn',1);
insert into mohan1.user_data values('allwyn',1)
*
ERROR at line 1:
ORA-28113: policy predicate has error



When test the vpd is working or not ,i got the above erro rORA-28113: policy predicate has error
.Please any one where I made mistake

Thanks in advance

MOHAN