Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Example of VPD implementation in Oracle 10g/11g

Example of VPD implementation in Oracle 10g/11g

In the article we will have a look at the concepts and basic example of VPD implementation. VPD allows implementation of Fine Grained Access Control (FGAC) on tables and views. Idea behind FGAC is implementing a mechanism of row level access control for a user session as opposed to an access granted by Oracle roles and privileges. With FGAC a user access to a table or view can be limited only to rows that associate to particular attributes if the user accesses certain columns. The application contest and VPD policy are used. Application context is used to set the attributes to a user. VPD policy uses a policy function associated with the table returning a predicate depending on the attributes set with the application context restricting the user access to table. The VPD policy also specifies the security relevant columns that while accessed enforce the policy. Depending on the user attributes, unless the user is sys or with exempt access policy privilege, RDBMS automatically re-writes each SQL adding a predicate returned by the policy function.

In the example three users will be created. After that an application context and the corresponding package setting the user attributes will be created. In the example the procedure implementing the context will set the attribute to the user name that is used after verification against the employees table. After logon trigger will set the attribute on user logon. A security function will be associated with the policy defined on the employees table. The security function will return a predicate restricting table access only to rows for the particular employee_id as defined by the attribute of the application context.

The hr.employees table will be used and the three users will have their attributes set by the application context procedure at logon as determined by the tables’ data. The detailed steps follow.

Continue reading

February 1, 2011 Posted by | oracle | 2 Comments