Guenadi N Jilevski's Oracle BLOG

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

Oracle 11g VPD

Oracle 11g VPD

In this article you will see how to use a VPD to provide a record-level security across applications tables. In VPD you attach security policies directly to tables, views and synonyms so there is no ways for users to bypass your security settings.

In a VPD any SQL used to access a table, view or synonym protected by VPD policy is dynamically modified to include a limiting condition such as where clause or and clause. The modification occurs transparently and the user sees only the data that passes the limiting conditions in the where clause. The find grained access allows a great deal of control over the access to application tables. VPD policies can be applied to select, insert, update, index and delete commands. You can create different security policies for each type of access, one for selects another for inserts and so on.

Implementing VPD requires the use of some advanced features such as procedures, triggers and PL/SQL.  VPD includes column-level VPD in which security policies are applied only when a particular column is accessed. Oracle also supports column masking, in which protected columns may display NULL values if their rows are returned. Additionally, auditing has been enhanced so that DBAs can perform fine-grained auditing of select, insert, update and delete commands.

Initial Configuration

VPD allows you to customize the way different users see the table data. VPD requires the use of a package DBMS_RLS.  From sysdba privileged account grant EXECUTE to the DBMS_RLS to all users. Create the users that will be used during the examples: PRACTICE that will own the tables, ADAMS abd BURLINGTON as illustrated below.

connect system/ as sysdba

grant execute on DBMS_RLS to public;

create user adams identified by adams;

create user Burlington identified by Burlington;

create user practice identified by practice;

grant resource to adams, burlington, practice;

grant create any context, create public synonym to practice;

Within the practice user create two tables the stock_account table and the stock_trx table. The stock_account table contains one row for each account that can perform stock trades. The stock_trx table contains one row for each stock bought or sold by that account holder.

connect  practice/practice

create table stock_account

( account                             number(10),

accountlongname           varchar2(50));

insert into stock_account values (

1234, ‘ADAMS’);

Insert into stock_account values (

7777, ‘BURLINGTON’);

create table stock_trx (

account                                number(10),

symbol                 varchar2(20),

price                      number(6,2),

quantity               number(6),

trx_flag                                varchar2(1));

insert into stock_trx values (1234,’ADSP’,31.75,100,’B’);

insert into stock_trx values (7777,’ADSP’,31.50,300,’S’);

insert into stock_trx values (1234.’ADSP’,31.55,100,’B’);

insert into stock_trx values (7777,’OCKS’21.75,1000.’B’);

commit;

Once the tables are created grant the ADAMS and BURLINGTON users access to them.

grant select, insert on stock_trx to ADAMS, BURLINGTON;

grant select on stock_account to ADAMS, BURLINGTON;

You now have the application configuration set. The practice schema owns the data table stock_trx and has a table that you can use to map usernames to account numbers that is, stock_account. With the configuration you can create an application context.

Create an application context

The next step in the VPD configuration is to create an application context.  The purpose of the context is to define the rules you will use to separate users. The context will be part of each session characteristics. Use the crate context command to specify the name of the package you will use to set the rules.

connect  practice/practice

create context practice using practice.context_package

Next create the package.

Create or replace package context_package as

Procedure set_context;

End;

/

Now create the package body.

create or replace package body context_package is

procedure set_context is

v_user                   varchar2(20);

v_id                        number;

begin

dbms_session.set_context(‘PRACTICE’,’SETUP’,’TRUE’);

v_user := SYS_CONTEXT(‘USERENV’,’SESSION_USER’);

begin

select account into v_id from stock_account where accountlongname = v_user;

dbms_session.set_context(‘PRACTICE’,’USER_ID’,v_id0;

exeption

WHEN NO_DATA_FOUND then

Dbms_session.set_context(‘PRACTICE’,’USER_ID’,0);

end;

dbms_session.set_context(‘PRACTICE’,’SETUP’,’FALSE’);

end set_context;

end context_package;

/

The account number is set as the USER_ID variable for the session if the user is account ho;der otherwise a value of 0 is set.

dbms_session.set_context(‘PRACTICE’,’USER_ID’,v_id);

You will be able to reference this context value in your programs and VPD restrictions will be based on the context setting.

Grant a public access to the package.

grant EXECUTE on practice.context_package to public;

create public synonym context_package for practice.context_package;

The rules for establishing the context for sessions are now ready to be activated.

Crate a logon trigger

In order for the context to be set within each user session create a trigger that will be executed each time a user logs into the database.

create or replace trigger practice.set_security_context

after logon on database

begin

practice.context_package.set_context;end;

/

Each time a user logs in Oracle will execute the set_context procedure within the practice user’s context_package package. This procedure will query the stock_account table and see if there is a matching account name. If there is a match then the session context will be altered to reflect the account value.

Test the context setting before going past this point in the implementation of a VPD. You can use the SYS_CONTEXT function of determine whether the configuration is working properly as shown below.

connect adams/adams

select SYS_CONTEXT(‘USERENV’,’SESSION_USER’) Username,

SYS_CONTEXT(‘PRACTICE’,’USER_ID’) ID from dual;

Username

ID

ADAMS

1234

The SYS_CONTEXT function show the power of the VPD configuration. For each user who logs in, Oracle will check the stock_account table and will use the matching account value to set a value for the session’s User_ID.   In this case, Oracle looked up the ADAMS user in the stock_account table, found its account value 1234 and set its session’s User_ID variable to that value. If you have that information, you can restrict access to rows in other tables based on the account data in the stock_account table. VPD allows you to restrict that access automatically with no need to create views. Every time a user attempts to access a table access can be restricted regardless of the tool used.

If you log in as a user not listed in the stock_account table the User_ID value will be 0.

Creating a security policy

So far the context and the logon trigger have been created. For the context package to have an effect on the users’ interaction with the database you need to create a security package that acts on the output of the context package.

First create the package header. In this example there are two type of actions supported against the stock_trx table, inserts and selects, so two functions will be created within the package.

connect practice/practice

create or replace package security_package as

function stock_trx_insert_security(owner varchar2, objectname varchar2) return varchar2;

function stock_trx_select_security(owner varchar2, objname varchar2) return varchar2;

end security_package;

/

Next create the package body. Both functions follow the same pattern, geared toward generating a predicate that will be applied every time a user queries the stock_trx table. If the session username is the owner of the table then the predicate is NULL. For all other users the User_ID context variable is evaluated and is passed as a limiting condition to any query. The object of these functions is to generate a predicate such as ‘where account=1234’ to be applied each time the table is queried by a user.

create or replace package body security_package is

function stock_trx_select_security(owner varchar2, objname varchar2) return varchar2 is

Predicate varchar2(2000);

Begin

Predicate := ‘1=2’;

If (SYS_CONTEXT(‘USERENV’,’SESSION_USER’) – ‘PRACTICE’ ) then

Predicate := NULL;

Else

Predicate := ‘ACCOUNT = SYS_CONTEXT(‘’PRACTICE’’,’’USER_ID’’)’;

End if;

Return Predicate;

end stock_trx_select_security;

function stock_trx_insert_security ( owner varchar2, objname varchar2) return varchar2 is

Predicate varchar(2000);

begin

Predicate := ‘1=2’;

If ( SYS_CONTEXT(‘USERENV’,’SESSION_USER’) = ‘PRACTICE’ ) then

Predicate := NULL;

Else

Predicate := ‘ACCOUNT = SYS_CONTEXT(‘’PRACTICE’’,’’USER_ID’’)’;

End if;

Return Predicate;

end stock_trx_insert_security;

end security_package;

/

Since this package will be executed by all the users who access the stock_trx table grant users the privilege to execute it and crate a public synonym.

grant execute on practice.security_package to public;

create public synonym security_package for practice.security_package;

Apply the security policy to tables

Now that security package exists you can relate it to the tables. To add a policy to a table use the ADD_POLICY procedure of the DBMS_RLS package as follows.

begin

dbms_rls.add_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_INSERT_POLICY’,’PRACTICE’,’SECURITY_PACKAGE.STOCK_TRX_INSERT_SECURITY’,’INSERT’,TRUE);

dbms_rls.add_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_SELECT_POLICY’,’PRACTICE’,’SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURITY’,’SELECT’ );

end;

/

The ADD_POLICY procedure is executed two times. In the first execution the stock_trx_insert_security function is specified for execution during attempts to insert rows into the stock_trx table. In the second execution the stock_trx_select_select_security function is applied to queries of the stock_trx table. You can have distinct rules for security for different operations against the table. The variables passed to the ADD_SECURITY procedure are the schema name, table name, the schema name and function name for the security function and the operation affected.

Test VPD

To see the results of the security policy query the table. The stock_trx table has four rows, two from the ADAMS account ‘1234’ and two from the BURLINGTHON account ‘7777’. Log in as one of those users and query the tables.

connect adams/adams

select * from practice.stock_trx;

ACCOUNT                           SYMBOL                                               PRICE                    QUANTITY                           T

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

1234                                       ADSP                                                     31.75                     100                                         B

1234                                       ADSP                                                     31.55                     100                                         B

Although the ADAMS user did not provide a where clause in his query Oracle applied a predicate to his query limiting the results to rows with the account value of 1234. What if the BURLINGTON  user attempts the same query?

connect burlington/burlington

select * from practice.stock_trx;

ACCOUNT                           SYMBOL                                               PRICE                    QUANTITY                           T

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

7777                                       ADSP                                                     31.50                     300                                         S

7777                                       OCKS                                                     21.75                     1000                                       B

The BURLINGTON user only sees the rows with account value of 7777 based on his row in the stock_account table.

What about other users? If they are not in the stock_account table they cannot retrieve rows from the stock_trx table.

connect system/manager

select * from practice.stock_trx;

no rows selected

In addition to selects, inserts werealso  limited by the security policy. User can only insert rows into stock_trx if the account value matches the user’s context settings. Attempts to insert other rows fail.

connect practice/practice

grant insert on stock_trx to adams;

insert into practice.stock_trx values (7777,’ADSP’,31.50,100,’B’);

ERROR at line 1:

ORA-28115: policy with check option violation

The ORA-28115 error indicates that the user attempted to insert a row that the user would not be able to select due to the VPD settings. You can establish several policies for the same table, view or synonym. All policies applied to a table are enforced with and syntax. If you have three policies applied to the table each one is applied to that table.

How to implement column-level VPD

You can implement VPD at the column level. If a column with secure data is referenced by a query you can use column-level VPD to apply a security or display the column with NULL values.  This approach is different from the row-based VPD solution shown earlier. In a column-level VPD all rows may be displayed but the columns may be shielded from the user. Column-level VPD can be applied to a table or a view. To use column level VPD specify a value for the sec_relevant_cols input parameters for the ADD_POLICY procedure of DBMS_RLS. When creating a policy you can use the following command.

begin

dbms_rls.add_policy

( object_schema=>’PRACTICE’,

object_name=>’STOCK_TRX’,

policy_name=>’STOCK_TRX_SELECT_POLICY’,

function_schema=>’PRACTICE’,

policy_function=>’SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURITY’,

sec_relevant_cols=>’Price’);

end;

/

By default the rows will be returned based on the security policy function when the Price column if referenced by a query. To use the column masking option tell Oracle to return all rows by setting the sec_relevant_col_opts parameter to DBMS_RLS.ALL_ROWS following the sec_relevant_cols parameter setting. When this version of the policy is used all rows will be returned by the query.  For the rows that the user would not normally be able to see the secure column Price will display NULL values. Column masking applies only to queries not to DML operations. Your applications must support the display of NULL values if you use column masking.

How to disable VPD

To remove a VPD functionality reverse the steps shown above.

  1. Remove the policy from the table using the DROP_POLICY procedure of the DBMS_RLS package.
  2. Drop the logon trigger
  3. Drop the other packages.

To drop a policy execute the DROP_POLICY procedure. Its three input variables are the schema name, the object name and the policy. The following example drops the STOCK_TRX_INSERT_POLICY.

dbms_rls.drop_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_INSERT_POLICY’);

Similarly you can drop the STOCK_TRX_SELECT_POLICY.

dbms_rls.drop_policy(‘PRACTICE’,’STOCK_TRX’,’STOCK_TRX_SELECT_POLICY’);

At this point the policy will be dropped but the login trigger will still remain and will be executing during each login. Remember to drop the trigger (in this case practice.set_security_context) so the database does not perform unnecessary work. Drop the security context trigger as shown bellow.

connect / as sysdba

select owner, trigger_name from dba_triggers where triggering_event like ‘LOGON%’;

OWNER                                                                                                TRIGGER_NAME

—————————                                                   ————————————————–

PRACTICE                                                                            SET_SECURITY_CONTEXT

drop trigger practice.set_security_context;

Trigger dropped.

If the VPD restrictions are no longer needed you can then drop the packages that set the application context.

How to use policy groups

You can create policy groups, add policies to a policy group and enable policies within a group.  You can create groups that integrate policies affecting the same tables. If multiple applications use the same tables you can use groups to manage the table-level policies that should be enabled during the application usage. All policies in a policy group can be applied at application run time. By default all policies belong to the SYS_DEFAULT policy group. Policies in the SYS_DEFAULT policy group will always be executed along with the policy group specified by the driving context. You cannot drop the SYS_DEFAULT policy group. To add a new policy group use the CREATE_POLICY_GROUP procedure.

dbms_rls.create_policy_group(

object_schema                 varchar2,

object_name                     varchar2,

policy_group                      varchar2);

You can then add a policy to the group using the ADD_GROUPED_POLICY procedure.

dbms_rls.add_grouped_policy(

object_schema                 varchar2,

object_name                     varchar2,

policy_group                      varchar2,

policy_name                      varchar2,

function_schema             varchar2,

policy_function                 varchar2,

statement_types              varchar2,

update_check                    boolean,

enabled                                boolean,

static_policy                       IN BOOLEAN FALSE,

policy_type                         IN BINARY_INTEGER NULL,

long_predicate                  IN BOOLEAN      FALSE,

sec_relevant_cols             IN VARCHAR2);

For example you can create a policy group named TRXAUDIT and then add the STOCK_TRX_SELECT_POLICY to it.

begin

dbms_rls.create_policy_group(‘PRACTICE’,’STOCK_TRX’,’TRXAUDIT’);

dbms_rls.add_grouped_policy(‘PRACTICE’,’STOCK_TRX’,’TRXAUDIT’,

‘STOCK_TRX_SELECT_POLICY’,’PRACTICE’,’SECURITY_PACKAGE.STOCK_TRX_SELECT_SECURE’);

End;

/

When the database is accessed the application initializes the driving context to specify the policy group that will be used. In the create context command specify the name of the procedure to use.

create context PRACTICE using PRACTICE.CONTEXT_PACKAGE;

PRACTICE.CONTEXT_PACKAGE executed the SET_CONTEXT procedure to set the application context.

DBMS_SESSION.SET_CONTEXT(‘PRACTICE’,’SETUP’,’TRUE’);

For policy group the third parameter passed to SET_CONTEXT will be the policy group name. For the application to invoke a specific context group rewrite CONTEXT_PACKAGE to support a third input variable (policy group) and add that to the SET_CONTEXT execution.

DBMS_SESSION.SET_CONTEXT(‘PRACTICE’,’SETUP’,<policy_group_>);

Within your application execute CONTEXT_PACKAGE and set the policy group value to TRXAUDIT. When the user queries the STOCK_TRX table the VPD restrictions in SYS_DEFAULT and the TRXAUDIT policy group will then be applied. You can remove a policy from a policy group using the DROP_GROUPED_POLICY , disble in using DISABLED_GROUPED_POLICY or re-enable it using ENABLE_GROUPED_POLICY.n Use DELETE_POLICY_GROUP to drop the policy group entirely.

January 15, 2010 - Posted by | oracle

4 Comments »

  1. VPD is nice. The issue with VPD is when it is going to complex security matrix on table required where you secure by 2 or more policies on different columns. There result of each predicate is different set of rows, so in standard way no result is returned by VPD. You have only 2 options with 11gR2 about how to handle this, but both are not nice:
    1. ) you will merge both filter options into one functions and put connect them by OR statement, this solution is then loosing masking some column values against the opposite rule, you simply share the whole set.
    2.) you can achieve the required effect, you can set sec_relevant_col_opts parameter to DBMS_RLS.ALL_ROWS, but this might result also in something like you receive 2 rows from your query, but will receive thousands of NULL only rows.

    Hope this will be handled by Real Application Security in 12c.

    Ladislav

    Comment by archenroot | August 28, 2014 | Reply

  2. Additionally the issue I mentioned can be solved by applying some NULL-filter function between the call and feedback, which can be done on the database or application side, still, it is wasting resources way.

    Comment by archenroot | August 28, 2014 | Reply

  3. Reblogged this on Black Hole Of My Memory.

    Comment by archenroot | December 29, 2014 | 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: