Five types of policies, column relevant policies, and column masking make VPD an even more powerful tool in the DBA's security toolbox
Virtual Private Database (VPD), also known as Fine Grained Access Control, provides powerful row-level security capabilities. Introduced in Oracle8i, it has become widely popular and can be found in a variety of applications ranging from education software to financial services.
VPD works by transparently modifying requests for data to present a partial view of the tables to the users based on a set of defined criteria. During runtime, predicates are appended to all the queries to filter any rows the user is supposed to see. For example, if the user is supposed to see only accounts of account manager SCOTT, the VPD setup automatically rewrites the query:
select * from accounts;
to:
select * from accounts
where am_name = 'SCOTT';
The DBA sets a security policy on the table ACCOUNTS. The policy has an associated function called policy function, which returns the string where am_name = 'SCOTT', which is applied as a predicate. If you are not familiar with the full functionality of the feature, I encourage you to read the Oracle Magazine article "Keeping Information Private with VPD."
Policy Types
The repeated parsing necessary to generate the predicate is overhead that you can trim in some situations. For example, in most real life cases the predicate is not as static as where am_name = 'SCOTT'; it's probably more dynamic based on who the user is, the authority level of the user, which account manager she reports to, and so on. The string created and returned by the policy function may become very dynamic, and to guarantee the outcome, Oracle must re-execute the policy function every time, wasting resources and reducing performance. This type of policy, where the predicate can potentially be very different each time it is executed, is known as a "dynamic" policy, and has been available in Oracle9i Database and prior releases..
In addition to retaining dynamic policy, Oracle Database 10g introduces several new types of policies based on how the predicate is constructed providing better controls for improving performance: context_sensitive, shared_context_sensitive, shared_static, and static. Now, let's what each policy type means and how to use it in appropriate situations.
Dynamic Policy. To retain backward compatibility, the default policy type in 10g is "dynamic"—just as it was in Oracle9i. In this case, the policy function is re-evaluated each time the table is accessed, for each row and for every user. Let's examine the policy predicate closely:
where am_name = 'SCOTT'
Ignoring the where clause, the predicate has two distinct parts: the portion before the equality operator (am_name) and the one after it ('SCOTT'). In most cases, the one after is more like a variable in that it is supplied from the user's data (if the user is SCOTT, the value would be 'SCOTT'.) The part before the equality sign is static. So, even though the function does have to evaluate the policy function for each row to generate the appropriate predicate, the knowledge about the static-ness of the before-part and dynamic-ness of the after-part can be used to improve performance. This approach is possible in 10g using a policy of type "context_sensitive" as a parameter in the dbms_rls.add_policy call:
policy_type => dbms_rls.context_sensitive
In another example scenario, we have a table called ACCOUNTS with several columns, one of which is BALANCE, indicating the account balance. Let's assume that a user is allowed to view accounts below a certain balance that is determined by an application context. Instead of hard-coding this balance amount in a policy function, we can use an application context as in:
create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'balance < sys_context(''vpdctx'', ''maxbal'')';
end;
The attribute MAXBAL of the application context VPDCTX can be set earlier in the session and the function can simply get the value at the runtime.
Note the example carefully here. The predicate has two parts: the one before the less-than sign and the other after it. The one before, the word "balance," is a literal. The one after is more or less static because the application context variable is constant until it is changed. If the application context attribute does not change, the entire predicate is constant, and hence the function need not be re-executed. Oracle Database 10g recognizes this fact for optimization if the policy type is defined as context sensitive. If no session context changes have occurred in the session, the function is not re-executed, significantly improving performance.
Static Policy. Sometimes a business operation may warrant a predicate that is more static. For instance, in the context-sensitive policy type example, we defined the maximum balance seen by a user as a variable. This approach is useful in the case of web applications where an Oracle userid is shared by many web users and based on their authority this variable (application context) is set by the application. Therefore web users TAO and KARTHIK, both connecting to the database as user APPUSER, may have two different values of the application context in their session. Here the value of MAXBAL is not tied to the Oracle userid, but rather to the individual session of TAO and KARTHIK.
In the static policy case the predicate is more predictable, as described below.
LORA and MICHELLE are account managers for Acme Bearings and Goldtone Bearings respectively. When they connect to the database, they use their own id and should only see the rows pertaining to them. In Lora's case, the predicate becomes where CUST_NAME = 'ACME'; for Michelle, where CUST_NAME = 'GOLDTONE'. Here the predicate is tied to their userids, and hence any session they create will always have the same value in the application context.
This fact can be exploited by 10g to cache the predicate in the SGA and reuse that in the session without ever re-executing the policy function. The policy function looks like this:
create or replace vpd_pol_func
(
p_schema in varchar2,
p_table in varchar2
)
return varchar2
is
begin
return 'cust_name = sys_context(''vpdctx'', ''cust_name'')';
end;
And the policy is defined as:
policy_type => dbms_rls.static
This approach ensures that the policy function is executed only once. Even if the application contexts are changed in the session, the function is never re-executed, making this process extremely fast.
Static policies are recommended for hosting your applications across several subscribers. In this case a single database has data for several users or subscribers. When each subscriber logs in, an after-logon trigger can set the application context to a value that is used in the policy function to very quickly generate a predicate.
However, defining a policy as static is also a double-edged sword. In the above example, we assumed that the value of the application context attribute VPDCTX.CUST_NAME does not change inside a session. What if that assumption is incorrect? If the value changes, the policy function will not be executed and therefore the new value will not be used in the predicate, returning wrong results! So, be very careful in defining a policy as static; you must be absolutely certain that the value will not change. If you can't make that assumption, better to define the policy as context sensitive instead.
Shared Policy Types. To reuse code and maximize the usage of parsed code, you might decide to use a common policy function for several tables. For instance, in the above example, we may have different tables for different types of accounts—SAVINGS and CHECKING—but the rule is still the same: users are restricted from seeing accounts with balances more than they are authorized for. This scenario calls for a single function used for policies on CHECKING and SAVINGS tables. The policy is created as context_sensitive.
Suppose this is the sequence of events:
1. Session connected
2. Application context is set
3. select * from savings;
4. select * from checking;
Even though the application context does not change between steps 3 and 4, the policy function will be re-executed, simply because the tables selected are different now. This is not desirable, as the policy function is the same and there is no need to re-execute the function.
New in 10g is the ability to share a policy across objects. In the above example, you would define the policy type of these policies as:
policy_type => dbms_rls.shared_context_sensitive
Declaring the policies as "shared" improves performance by not executing the function again in the cases as shown above.
Selective Columns
Now imagine a situation where the VPD policy should be applied only if certain columns are selected. In the above example with table ACCOUNTS, the rows are as follows:
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
2 TOM CONNOPHY 2000
3 ISRAEL D 1500
Michelle is not supposed to see accounts with balances over 1,600. When she issues a query like the following:
select * from accounts;
she sees:
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
3 ISRAEL D 1500
acctno 2, with balance more than 1,600, has been suppressed in the display. As far as Michelle is concerned, there are only two rows in the table, not three. When she issues a query such as:
select count(*) from accounts;
which simply counts the number of records from the table, the output is two, not three.
However, here we may decide to relax the security policy a bit. In this query Michelle can't view confidential data such as account balance; she merely counts all the records in the table. Consistent with the security policy, we may allow this query to count all the records whether or not she is allowed to see them. If this is the requirement, another parameter in the call to dbms_rls.add_policy in 10g allows that function:
sec_relevant_cols => 'BALANCE'
Now when the user selects the column BALANCE, either explicitly or implicitly as in select *, the VPD policy will kick in to restrict the rows. Otherwise all rows of the table will be selected, as in the query where the user has selected only the count of the total rows, not the column BALANCE. If the above parameter is set as shown, then the query
select count(*) from accounts;
will show three columns, not two. But the query:
select * from accounts;
will still return only two records, as expected.
Column Masking
Now let's add more requirements to our current example. Instead of suppressing the display of rows with a balance above the threshold, we may want to show all the rows while masking the balance column where the value is above the threshold. The security-relevant column is still BALANCE.
Michelle is not supposed to see accounts with balances over 1,600. When she issues a query like the following:
select * from accounts;
she would have seen only two rows, acctnos 1 and 3. But, instead, we may want her to see:
ACCTNO ACCT_NAME BALANCE
------ ------------ -------
1 BILL CAMP 1000
2 TOM CONNOPHY
3 ISRAEL D 1500
Note how all the rows are displayed but the value of the column BALANCE is shown as null (displayed as
sec_relevant_cols_opt => dbms_rls.all_rows
This tactic can be very useful in cases where only values of certain columns are important, and requires no complicated custom code. It is also a great alternative to requiring stored data encryption.
Conclusion
In Oracle Database 10g, VPD has grown into a very powerful feature with the ability to support a variety of requirements, such as masking columns selectively based on the policy and applying the policy only when certain columns are accessed. The performance of the policy can also be increased through multiple types of policy by exploiting the nature of the application, making the feature applicable to multiple situations.
Posts
No comments:
Post a Comment