Figure 5.1 Selective Viewing of Tables By A User 

Here, the table PROVIDERS contains all the providers the company deals with, but when the user NATHAN queries the table, he should see only the row for PROVIDER_ID 1234567, nothing else. Similarly, if he queries the table claims, only claims related to that provider should be visible. It should appear to Nathan that the database contains data about provider 1234567 only, no one else.

Another junior analyst, CLARA, handles providers 2345678 and 3456789. So, for Clara, the database should appear as if it contains only the data related to those provider ids, no others.

How do we achieve this? One way is to create views that offer a filtering condition applied to tables, eliminating all but the authorized rows. The users are given privileges on the view, not on the base table. This works fine for selects, but if the views are complicated, they need additional work for inserts, updates, and deletes. Besides, the proliferation of views complicates the data model.

Another option is to create separate tables in such a way that each user has a copy of the table for which he or she is authorized. The separation of data is the most appealing to security and privacy advocates, but is rather extreme and certainly not practical.

Judy, who is a senior analyst, should be able to see all the providers and their claims. So when she accesses the table, the view should be a union of all such segregated tables. This causes an extra burden on the system, and most of the queries are going to perform badly, due to inefficient joins for the UNION ALL operation.

How do we resolve the problem?

The solution is to somehow create several virtual tables from the original table so that each user sees only that virtual table for which he or she is authorized. For instance, in the above figure, each of these accessible views is created as a virtual table for each user. When the user NATHAN issues a statement:

select * from claims;

The rows corresponding to the provider 1234567, for which the user is authorized, come back, nothing else. Note, the user did not specify a where clause.  The query was intercepted and was rewritten automatically by the RDBMS as:

select * from claims where provider_id = 1234567;

No action was required from the user. Similarly, when the user CLARA issues a statement:

update claims
set paid_amount = claim_amount – 100;

It is rewritten as:

update claims
set paid_amount = claim_amount – 100

 

Note how the query was automatically rewritten from the query supplied by the user. The user does not control it, the RDBMS does. The information is presented to the user in such a way that the user does not even see anything for which he or she is not authorized.

This is analogous to a virtual set of tables for each named user, and therefore a virtual database. This setup is known as Virtual Private Database, since it offers a private view of the database to the users based on some filtering criteria.

In this chapter, we will discuss how to set it up and how to use it to enforce security and privacy requirements of HIPAA and other laws. We will also discuss how to use it in non-standard environments, as in cases where a named user is not defined.

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)

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
SEE CODE DEPOT FOR FULL SCRIPT 

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().

PARAMETER	DESCRIPTION
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.

PARAMETER	DESCRIPTION
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
            SEE CODE DEPOT FOR FULL SCRIPT
            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
            SEE CODE DEPOT FOR FULL SCRIPT
            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
            SEE CODE DEPOT FOR FULL SCRIPT
            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
            SEE CODE DEPOT FOR FULL SCRIPT
            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 returned 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)
SEE CODE DEPOT FOR FULL SCRIPT
    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.

Security of the Policy

We have defined a policy and enforced it using a function, but is the function itself secured? The cornerstones of this security mechanism are:

• The tables that define the policy
   

• The functions that enforce them
   

• The policies themselves

These items must be secured. In our example, we have used the CLAIM_SCHEMA user as the owner of both the tables and the functions. This need not be the case. We could have a separate owner called SECUSER that can own these objects. There is no need to grant any kind of privilege to anyone on these tables and functions. Since SYS creates these policies, there is no way another user can alter them. You could also give the SECUSER execute privilege on dbms_rls, if desired.

This makes the policy tamper-proof and secure. You can use policies to enforce all types of security in all types of access methods.

It is important to understand that the user SYS is exempt from being subjected to any type of security policy. This is required. Otherwise, if you define a policy incorrectly by mistake, sys can never do anything.

If you decide to have some special users not be subjected to the policies defined on the objects, they can be exempted as in the following

GRANT EXEMPT ACCESS POLICY TO SECUSER;

 

---

Download your Oracle scripts now: www.oracle-script.com The definitive Oracle Script collection for every Oracle professional DBA