Oracle Security Virtual Private Database Policy Oracle Security Tips by Burleson Consulting

This is an excerpt from the bestselling book "Oracle Privacy Security Auditing", a complete Oracle security reference with working Oracle security scripts.
 

---

Virtual Private Database (VPD) Policy

The VPD uses a filtering mechanism to filter out rows that do not match the required authorization of the user. This filter can be either dynamic or static. Static filters are not that useful, but can be used in some cases to exclude a set list of provider ids.

However, in real life the filter is going to be different and dynamic. User Nathan may be promoted or relocated to a different group, and thus he no longer handles provider 1234567, but the providers 4567891 and 5678912. This change in business should be immediately reflected in the view, and should not require a recreation of the view. Therefore, a dynamic filter is necessary. This filter is enforced by the RDBMS using a concept called a policy.

Figure 5.2 Policy Applied to a Table in VPD

Refer to Fig 5.2 for an explanation of the policy. Here the table CLAIMS is subjected to a policy that restricts the tables to those rows that satisfy the restriction. The restriction is controlled by a function such as the following.

create or replace function get_auth_providers
(
    p_schema_name    in varchar2,
    p_object_name    in varchar2
)
return varchar2
is
    l_ret        varchar2(2000);
begin
    -- if the user calling it is the same as the owner,
    -- we want to return all rows, so a where condition
    -- of 1=1 will always return TRUE.
    if p_schema_name = user
    then
        l_ret := '1=1';
    else -- if the user is different from the owner
        for prov_rec in
        (
            select provider_id
            from provider_view_policy
            where username = user
        ) loop
            l_ret := l_ret||','||prov_rec.provider_id;
        end loop;
        l_ret := ltrim(l_ret,',');
        l_ret := 'PROVIDER_ID IN ('||l_ret ||')';
    end if;
    return l_ret;
end;
/

This function returns the list of providers the current user is authorized to see. Let's see how it dynamically changes the output.

Connect NATHAN/*
Select
    get_auth_providers ('CLAIM_SCHEMA', 'CLAIMS')
From
    dual;

The output is:

PROVIDER_ID IN (1234567)

But when CLARA logins and executes the same query:

Connect CLARA/*
Select
    get_auth_providers ('CLAIM_SCHEMA', 'CLAIMS')
From
    dual;

The output is:

PROVIDER_ID IN (3456789,2345678)

Note how the output changed. The output returns the list of providers for which the logged in user is authorized.

What do we do with this function? We will apply it as a limit to the rows retrieved from a table. In other words, we will set it up as a security policy for the table CLAIMS. The following does this:

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name     => 'CLAIMS',
    policy_name      => 'CLAIM_VIEW_POLICY',
    function_schema  => 'CLAIM_SCHEMA',
    policy_function  => 'GET_AUTH_PROVIDERS',
    statement_types  =>
        'INSERT, UPDATE, DELETE, SELECT',
    update_check     => TRUE,
    enable           => TRUE,
    static_policy    => FALSE
);
end;
/

For the time being, let's not ponder the explanation or the meaning of these parameters, they will be explained in detail later. We have added a policy to the table claims owned by CLAIM_SCHEMA. This policy will append the output of the function get_auth_providers to any query of SELECT, INSERT, UPDATE, or DELETE on claims.

When user NATHAN logs on and issues a query to select from the table as follows:

connect nathan/*
select * from claims
/

The output comes as:

C SSN       MEMBER_I PROVIDER_I CLAIM_AM PAID_AMOUNT S
- --------- -------- ---------- -------- ----------- -
1 123456789    12345    1234567     1200        1000 P
2 234567891    12345    1234567     1800        1700 P

Note, only the claims from provider 1234567 are displayed. The select statement was on the table CLAIMS, not a view that had the filtering condition, and the user did not apply a filter using a where clause. Oracle RDBMS automatically applied a where clause to the query to limit the output.

Remember, when NATHAN executed the function get_auth_providers() earlier, he got the output as:

PROVIDER_ID IN (1234567)

This string was applied automatically as a where clause to all the queries NATHAN submitted to the claims table. So the query:

select * from claims;

Was transformed to:

select * from
    (select * from claims)
where PROVIDER_ID IN (1234567);

Note how the filtering condition was automatically placed. When Clara issues the same statement:

connect CLARA/*
select * from claims;

C SSN        MEMBER_   PROVIDER_ CLAIM_AM PAID_AMOUNT S
- ---------- --------- --------- -------- ----------- - -
3 234567891    12345     2345678     1400        1300 P
4 345678912    23456     3456789     2000        1900 P
5 345678912    23456     3456789     2500        2400 P

She gets the claims for which she is authorized, not the others. The filtering condition was again applied automatically.

This filtering applies to updates and deletes as well. Say the user Nathan issues a statement:

update
    claims
set
    paid_amount = 1200;

2 rows updated.

Note, only the 2 rows to which Nathan has privileges are updated, not all 5. What if he tries to update a row that exists but for which he does not have authorization? The table has a claim of CLAIM_ID 3, but since the provider of the claim is 2345678, a provider Nathan does not have permission to see, the claim shouldn't be visible to him.

update
    claims
set
    paid_amount = 1200
where
     claim_id = 3;

0 rows updated.

Note how the rows weren't even reported as present. It is as if the rows are never there, as far as Nathan is concerned.

Finally, the issue of inserts comes up. When Nathan tries to insert a record with a provider id for which he is not authorized (e.g. 2345678), he gets an error.

insert into
    claims
values (
    6,
    '123456789',
    34567891,
    2345678,
    1200,
    1100,
    'P'
);
                         *
ERROR at line 1:
ORA-28115: policy with check option violation

Note the new type of error, ORA-28115: policy with check option violation, used to indicate this type of error.

By now, you should have a great deal of appreciation for the value of this tool. You didn't create a view or a trigger. You have defined a rule for viewing (implemented through a function), and the filtering condition is applied automatically to any query the users issue against the table.

The Package dbms_rls

With this example in place, let's explore the main implementing package dbms_rls, and in particular the procedure add_policy. It takes the following parameters.

PARAMETER	DESCRIPTION
OBJECT_SCHEMA	The owner of the object against which this policy is applied. If specified as NULL, the current user or the current schema (if defined) is used.
OBJECT_NAME	This specifies the name of the object, i.e. the table or view, or a synonym on which this policy is defined.
POLICY_NAME	Name of the policy
FUNCTION_SCHEMA	The name of the user who owns the function used as a policy enforcer, e.g. the function get_auth_providers().
POLICY_FUNCTION	The name of the function used to enforce the policy security.
STATEMENT_TYPES	A policy can be restricted to apply for a certain statement only, e.g. SELECT. This may be useful when different types of policies are defined for different actions, such as one for SELECT, one for DELETE, etc. The possible values are SELECT, INDERT, UPDATE, DELETE. The value can also contain a combination of these, separated by comma, e.g. 'SELECT, UPDATE'. The default value is all of the statement types.
UPDATE_CHECK	In the example given above, we have made the policy to apply even to the new value put by the update statement. The default is FALSE, i.e. the policy does not check for the value replaced by the update statement. It takes Boolean values TRUE or FALSE.
ENABLE	Specifies whether the policy is enabled or not. It takes Boolean value and the default is TRUE.
STATIC_POLICY	In the example given above, we have seen that policy function returns a where clause that may be different based on who invokes it. This makes the function dynamic. If the policy function was such that the where condition would be the same regardless of who accesses it, then this value should be TRUE. The default is FALSE, and that is the case in almost all cases of use.

Table 5.3 Parameters for dbms_rls_add_policy

This is a rather simple usage of this great tool. In real life, however, you would probably face a great deal of complexity in defining the security policy. For instance, the access policy of claims could be defined differently for different types of access. Nathan can view the records of providers 1234567, 2345678 and 3456789, but can create a claim record for provider 1234567 only, and update those for providers 1234567 and 2345678, and delete no records.

Another rule may specify that access be restricted based on the claim amount, in addition to the list of authorized providers. Nathan may access claims of $1,500 or less, Judy may access $5,000 or less, and so on and so forth. Keeping this requirement in mind, we created a new table called claim_provider_policy to record the permissions as follows.

USERNAME                   VARCHAR2(20)
ACCESS_TYPE                VARCHAR2(10)
PROVIDER_ID                NUMBER

The records were inserted as follows for NATHAN.

USERNAME             ACCESS_TYP PROVIDER_ID
-------------------- ---------- -----------
NATHAN               SELECT         1234567
NATHAN               SELECT         2345678
NATHAN               SELECT         3456789
NATHAN               INSERT         1234567
NATHAN               UPDATE         1234567
NATHAN               UPDATE         2345678

Similar records may be created for other users, such as Judy and Clara. For the value enforcement, we can have another table called claim_value_policy defined as:

USERNAME                   VARCHAR2(20)
MAX_CLAIM_AMOUNT           NUMBER

The typical records may be similar to the following:

USERNAME             MAX_CLAIM_AMOUNT
-------------------- ----------------
JUDY                             5000
NATHAN                           1500
CLARA                            2500

Now the function to enforce the policy will need to change, too. Since we have separate enforcements for each type of access, we will have to have different functions. Here is a function for SELECT policy.

create or replace function select_auth_claims
(
    p_schema_name    in varchar2,
    p_object_name    in varchar2
)
return varchar2
is
    l_ret        varchar2(2000);
begin
    if p_schema_name = user
    then
        l_ret := '1=1';
    else -- if the user is different from the owner
        for prov_rec in
        (
            select provider_id
            from claim_provider_policy
            where username = user
            and access_type = 'SELECT'
        ) loop
            l_ret := l_ret||','||prov_rec.provider_id;
        end loop;
        l_ret := ltrim(l_ret,',');
        l_ret := 'PROVIDER_ID IN ('||l_ret ||')';
    end if;
    return l_ret;
end;
/

Similarly, the INSERT policy is defined as follows:

create or replace function insert_auth_claims
(
    p_schema_name    in varchar2,
    p_object_name    in varchar2
)
return varchar2
is
    l_ret        varchar2(2000);
begin
    if p_schema_name = user
    then
        l_ret := '1=1';
    else -- if the user is different from the owner
        for prov_rec in
        (
            select provider_id
            from claim_provider_policy
            where username = user
            and access_type = 'INSERT'
        ) loop
            l_ret := l_ret||','||prov_rec.provider_id;
        end loop;
        l_ret := ltrim(l_ret,',');
        l_ret := 'PROVIDER_ID IN ('||l_ret ||')';
    end if;
    return l_ret;
end;
/

The update function is defined as:

create or replace function update_auth_claims
(
    p_schema_name    in varchar2,
    p_object_name    in varchar2
)
return varchar2
is
    l_ret        varchar2(2000);
begin
    if p_schema_name = user
    then
        l_ret := '1=1';
    else -- if the user is different from the owner
        for prov_rec in
        (
            select provider_id
            from claim_provider_policy
            where username = user
            and access_type = 'UPDATE'
        ) loop
            l_ret := l_ret||','||prov_rec.provider_id;
        end loop;
        l_ret := ltrim(l_ret,',');
        l_ret := 'PROVIDER_ID IN ('||l_ret ||')';
    end if;
    return l_ret;
end;
/

Finally, we will create a function to check the maximum claim amount for the claims.

create or replace function check_claim_value_policy
(
    p_schema_name    in varchar2,
    p_object_name    in varchar2
)
return varchar2
is
    l_ret        varchar2(2000);
    l_max_claim_amount number;
begin
    l_ret := NULL;
    if p_schema_name = user
    then
        l_ret := '1=1';
    else -- if the user is different from the owner
         begin
            select max_claim_amount
            into l_max_claim_amount
            from claim_value_policy
            where username = user;
            l_ret := 'CLAIM_AMOUNT <= '||
                l_max_claim_amount;
        exception
            when NO_DATA_FOUND then
                l_ret := NULL;
            when OTHERS then
                raise;
        end;
    end if;
    return l_ret;
end;
/

These functions can be as complicated as you wish them to be. The objective is to find a filtering predicate to be applied to the queries on the table.

Now we have to define three different policies on the table.

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name      => 'CLAIMS',
    policy_name      => 'CLAIM_SELECT_POLICY',
    function_schema  => 'CLAIM_SCHEMA',
    policy_function  => 'SELECT_AUTH_CLAIMS',
    statement_types  => 'SELECT'
);
end;
/

For the INSERT statements:

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name      => 'CLAIMS',
    policy_name      => 'CLAIM_INSERT_POLICY',
    function_schema  => 'CLAIM_SCHEMA',
    policy_function  => 'INSERT_AUTH_CLAIMS',
    statement_types  => 'INSERT',
    update_check     => TRUE
);
end;
/

For the UPDATE statements:

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name     => 'CLAIMS',
    policy_name      => 'CLAIM_UPDATE_POLICY',
    function_schema  => 'CLAIM_SCHEMA',
    policy_function  => 'UPDATE_AUTH_CLAIMS',
    statement_types  => 'UPDATE',
    update_check     => TRUE
);
end;
/

For the claim value check:

begin
dbms_rls.add_policy (
    object_schema    => 'CLAIM_SCHEMA',
    object_name      => 'CLAIMS',
    policy_name      => 'CLAIM_VALUE_POLICY',
    function_schema  => 'CLAIM_SCHEMA',
    policy_function  => 'CHECK_CLAIM_VALUE_POLICY',
    statement_types  =>
        'SELECT, INSERT, DELETE, UPDATE',
    update_check     => TRUE
);
end;
/

Note how we have defined several policies on a single table. During a select query, two policies are applied – claim_value_policy and select_auth_claims. During execution, the predicates retuned by both queries are applied to the table. If NATHAN issues a query:

select * from claims

It is rewritten to:

select * from
(select * from claims)
where
(
    provider_id in (1234567,2345678,3456789)
    AND
    claim_amount <= 1500
)

* Important: When more than one policy is defined on a table, the output of all the policies are AND’d to arrive at the final where clause.

After the policies are defined, it's now time to test the setup.

connect nathan/*

select * from claim_schema.claims;

C SSN        MEMBER_ID PROVIDER_ID  Amou PAID_AMOUNT S
-- --------- ---------- ----------- ----- ----------- -
1 123456789      12345     1234567  1200        1200 P
3 234567891      12345     2345678  1400        1300 P

See how both the policies claim_value_policy and select_auth_claims have been applied to the table, and the filtering predicates from both the policies have been applied to the query? The same effect occurs when UPDATE and INSERT occur on the table, too.

 

This is an excerpt from the book "Oracle Privacy Security Auditing". You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle security and auditing scripts.