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.

  1. Create the users with the roles. VPD user scema will contain the employees table and the packages for the application context and the security function.

drop user user1 cascade;

create user user1 identified by user1 default tablespace example temporary tablespace temp;

grant connect, resource to user1;

drop user user2 cascade;

create user user2 identified by user2 default tablespace example temporary tablespace temp;

grant connect, resource to user2;

drop user user3 cascade;

create user user3 identified by user3 default tablespace example temporary tablespace temp;

grant connect, resource to user3;

drop user vpd cascade;

create user vpd identified by vpd default tablespace example temporary tablespace temp;

grant connect, resource, dba to vpd;

  1. Create and populate the vpd.employees table. The table content will be used to set the attributes of a user at logon later by the procedure of the package implementing the application context.

    drop table vpd.employees purge;

    create table vpd.employees as select * from hr.employees where 1=2;

    insert into employees values (300,’USER1′,’USER1′,’user1@oracle.com’,’6500000′,sysdate,58,1000,0.5,500,10);

    insert into employees values (400,’USER2′,’USER2′,’user2@oracle.com’,’6500001′,sysdate,59,2000,0.6,500,10);

    insert into employees values (500,’USER3′,’USER3′,’user3@oracle.com’,’6500002′,sysdate,60,3000,0.7,600,10);

    commit;

  2. Grant select privilege on the table to the users

    grant select on vpd.employees to user1;

    grant select on vpd.employees to user2;

    grant select on vpd.employees to user3;

  3. Create a package with procedure to set the application context vpd_context attribute empno to the employee_id.

    SQL> CREATE OR REPLACE PACKAGE app_security_context IS

    2 PROCEDURE set_empno;

    3 END;

    4 /

    Package created.

    SQL> CREATE OR REPLACE PACKAGE BODY app_security_context IS

    2 PROCEDURE set_empno

    3 IS

    4 empid NUMBER;

    5 BEGIN

    6 SELECT employee_id INTO empid FROM vpd.employees

    7 WHERE first_name = SYS_CONTEXT(‘USERENV’,’SESSION_USER’);

    8 DBMS_SESSION.SET_CONTEXT(‘vpd_context’, ’empno’, empid);

    9 END;

    10 END;

    11 /

    Package body created.

    SQL>

  4. Create application context vpd_context using the app_security_context package.

    CREATE CONTEXT vpd_context USING vpd.app_security_context;

  5. Create after logon trigger to set the empno attribute to the employee_id by invoking the app_security_context.set_empno procedure.

    CREATE OR REPLACE TRIGGER on_logon

    AFTER LOGON

    ON DATABASE

    BEGIN

    IF user in (‘USER1′,’USER2’) THEN

    vpd.app_security_context.set_empno();

    END IF;

    END;

    /

  6. Create a package with the security policy function

    SQL> CREATE OR REPLACE PACKAGE vpd_security AS

    2 FUNCTION empno_sec (D1 VARCHAR2, D2 VARCHAR2)

    3 RETURN VARCHAR2;

    4 END;

    5 /

    Package created.

    SQL> CREATE OR REPLACE PACKAGE BODY vpd_security AS

    2 FUNCTION empno_sec (D1 VARCHAR2, D2 VARCHAR2) RETURN VARCHAR2

    3 IS

    4 predicate VARCHAR2 (2000);

    5 BEGIN

    6 predicate := ’employee_id = SYS_CONTEXT(”vpd_context”, ”empno”)’;

    7 RETURN predicate;

    8 END;

    9 END;

    10 /

    Package body created.

    SQL>

  7. Create a policy on hr.employees table of type dynamic on select using vpd_security.empno_sec function.

    SQL> exec DBMS_RLS.ADD_POLICY( –

    > OBJECT_SCHEMA => ‘vpd’ ,-

    > OBJECT_NAME => ’employees’ ,-

    > POLICY_NAME => ‘vpd_policy’ ,-

    > FUNCTION_SCHEMA => ‘vpd’ ,-

    > POLICY_FUNCTION => ‘vpd_security.empno_sec’,-

    > STATEMENT_TYPES => ‘select’ ,-

    > POLICY_TYPE => DBMS_RLS.DYNAMIC ,-

    > LONG_PREDICATE => false ,-

    > SEC_RELEVANT_COLS => ‘SALARY,COMMISSION_PCT’);

    PL/SQL procedure successfully completed.

    SQL>

  8. Connect as users1/user2/user3 and issue the queries to verify VPD is working.

    SQL> connect user1/user1

    Connected.

    SQL> select * from vpd.employees;

    EMPLOYEE_ID FIRST_NAME LAST_NAME

    ———– ——————– ————————-

    EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY

    ————————- ——————– ——— ———- ———-

    COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

    ————– ———- ————-

    300 USER1 USER1

    user1@oracle.com 6500000 01-FEB-11 58 1000

    .5 500 10

    SQL> select first_name from vpd.employees;

    FIRST_NAME

    ——————–

    USER1

    USER2

    USER3

    SQL> select first_name, salary, commission_pct from vpd.employees;

    FIRST_NAME SALARY COMMISSION_PCT

    ——————– ———- ————–

    USER1 1000 .5

    SQL>

    SQL> connect user2/user2

    Connected.

    SQL> select * from vpd.employees;

    EMPLOYEE_ID FIRST_NAME LAST_NAME

    ———– ——————– ————————-

    EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY

    ————————- ——————– ——— ———- ———-

    COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

    ————– ———- ————-

    400 USER2 USER2

    user2@oracle.com 6500001 01-FEB-11 59 2000

    .6 500 10

    SQL> select first_name from vpd.employees;

    FIRST_NAME

    ——————–

    USER1

    USER2

    USER3

    SQL> select first_name, salary, commission_pct from vpd.employees;

    FIRST_NAME SALARY COMMISSION_PCT

    ——————– ———- ————–

    USER2 2000 .6

    SQL>

    SQL> connect user3/user3

    Connected.

    SQL> select * from vpd.employees;

    no rows selected

    SQL> select first_name from vpd.employees;

    FIRST_NAME

    ——————–

    USER1

    USER2

    USER3

    SQL> select first_name, salary, commission_pct from vpd.employees;

    no rows selected

    SQL>

  9. Grant ‘exempt access policy’ to user3 and compare the results while connected as sysdba and user3 to previous connect as user1/user2/user3.

    SQL> show user

    USER is “SYS”

    SQL> select * from vpd.employees;

    EMPLOYEE_ID FIRST_NAME LAST_NAME

    ———– ——————– ————————-

    EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY

    ————————- ——————– ——— ———- ———-

    COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

    ————– ———- ————-

    300 USER1 USER1

    user1@oracle.com 6500000 01-FEB-11 58 1000

    .5 500 10

    400 USER2 USER2

    user2@oracle.com 6500001 01-FEB-11 59 2000

    .6 500 10

    EMPLOYEE_ID FIRST_NAME LAST_NAME

    ———– ——————– ————————-

    EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY

    ————————- ——————– ——— ———- ———-

    COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

    ————– ———- ————-

    500 USER3 USER3

    user3@oracle.com 6500002 01-FEB-11 60 3000

    .7 600 10

    SQL> select first_name from vpd.employees;

    FIRST_NAME

    ——————–

    USER1

    USER2

    USER3

    SQL> select first_name, salary, commission_pct from vpd.employees;

    FIRST_NAME SALARY COMMISSION_PCT

    ——————– ———- ————–

    USER1 1000 .5

    USER2 2000 .6

    USER3 3000 .7

    SQL>

    SQL> connect user3/user3

    Connected.

    SQL> select * from vpd.employees;

    EMPLOYEE_ID FIRST_NAME LAST_NAME

    ———– ——————– ————————-

    EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY

    ————————- ——————– ——— ———- ———-

    COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

    ————– ———- ————-

    300 USER1 USER1

    user1@oracle.com 6500000 01-FEB-11 58 1000

    .5 500 10

    400 USER2 USER2

    user2@oracle.com 6500001 01-FEB-11 59 2000

    .6 500 10

    EMPLOYEE_ID FIRST_NAME LAST_NAME

    ———– ——————– ————————-

    EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY

    ————————- ——————– ——— ———- ———-

    COMMISSION_PCT MANAGER_ID DEPARTMENT_ID

    ————– ———- ————-

    500 USER3 USER3

    user3@oracle.com 6500002 01-FEB-11 60 3000

    .7 600 10

    SQL> select first_name from vpd.employees;

    FIRST_NAME

    ——————–

    USER1

    USER2

    USER3

    SQL> select first_name, salary, commission_pct from vpd.employees;

    FIRST_NAME SALARY COMMISSION_PCT

    ——————– ———- ————–

    USER1 1000 .5

    USER2 2000 .6

    USER3 3000 .7

    SQL>

  10. The example was tested for 10gR2 and 11gR2

Conclusion

VPD is exempted for sys user and for user with ‘exempt access policy’ privilege. At login time the user attributes are set as per the logic in the application context procedure. That is, only users listed in the employees table can see their own data. The security policy defined on hr.employees table is only on selects, policy type is dynamic and applies only if either salary or commission_pct columns are accessed.

February 1, 2011 - Posted by | oracle

2 Comments »

  1. Great article! Very well explain!

    Comment by Deepak Gupta | February 2, 2011 | Reply

  2. […] Example of VPD implementation in Oracle 10g/11g – 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 … […]

    Pingback by How To Create Tablespace In Oracle 11g Example | Insurance-Golds | May 1, 2016 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: