The preceding example can help you get started, but there is much more to RLS than this. The security policies can be much more complex, and the policy functions typically return dramatically different strings based on user authorizations.
There are some similarities between RLS and some of the view examples you read about in Chapter 10. Row-level security is enforced by a PL/SQL function, and the role of the PL/SQL function is the same as it was when used in the view examples. The difference between RLS and views is how the PL/SQL is invoked.
The policy functionís job is to return a string (varchar2) that will serve as a predicate or a where clause to the original query. In effect, the original query is modified, the predicate string is attached, and the query is executed. For example, a simple query select * from EMP might be augmented by an RLS policy function that returns the predicate ename = USER. The effective SQL that is then executed will be select * from EMP where ename = USER. Youíll see a diagram of this process later in Figure 11-1.
The PL/SQL functions are registered to tables, views, or synonyms by invoking the DBMS_ RLS.ADD_POLICY procedure. The DBMS_RLS package is not granted to everyone; administrators will require direct execute privileges on the package. The ADD_POLICY procedure requires, at minimum, the name of the object to which the policy will be applied, a name for the policy, and the name of a PL/SQL function that will implement the security policy.
The policies can be applied to SELECT, INSERT, UPDATE, DELETE, and INDEX statements. The index affects CREATE INDEX and ALTER INDEX DDL commands. Whenever a user directly or indirectly accesses a protected table, view, or synonym, the RLS engine is transparently invoked, the PL/SQL function registered will execute, and the SQL statement will be modified and executed.Benefits
RLS is very flexible and very granular. By default, the policy applies to all DML statements. The ADD_POLICY procedure accepts a STATEMENT_TYPES parameter that allows the administrator to specify which DML operations the policy is to apply. This granularity also allows the database to apply separate policies based on the DML type. For example, the database can easily support a policy to allow all records for SELECT statements; an INSERT, UPDATE policy to restrict records to a userís department on insert and update operations; and a DELETE policy that restricts DELETE operations to only the userís record.
Multiple policies also can be applied to the same object: the database logically ANDs the policies together. That is, if there is one policy that returns ename = USER and another policy (on the same object for the same DML) that returns sal > 2000, the database will automatically add both policies, effectively generating where ename = USER and sal > 2000.
The security from VPD derives from the fact that the predicates are used to restrict records returned by the original query, regardless of how the query was issued or who issued the query. This record filtering provides consistent row-level security that is guaranteed to work irrespective of the applications interacting with the data. The entire process is transparent to the application originally issuing the query. One of the strongest arguments for VPD is that the security is tightly fixed to the data it protectsóitís consistent, centrally managed, and it canít be bypassed.
To understand why this is desirable, look at an alternative security model in which the application implements the record filtering. A particular challenge arises when the same data is required by multiple applications. In this case, the security about the data has to be replicated to all the applications. Varying programming languages, COTS applications, and design models often make this an arduous job at best.
The databaseís ability to support security for the data at both an object level and within the object (intra-object) are crucial for ensuring consistent and constant security. Programming languages are born and die within a few years. Applications change in functionality, design, and use even more frequently than that. A well-defined database schema will invariably outlive both. Therefore, a proper security model at the database is paramount to ensuring overall data security. By using features like VPD, the database implements the security policies and thus any application using the data will have the security policies automatically applied.Setup
You now have the background to understand why VPD is a good tool, now you will see more examples of how to use it. In the next example, youíll solve the challenge posed in the ďViewing ProblemsĒ section in Chapter 10: a user is allowed to see all records; to insert and update records only within their department; and to delete only their individual record. You could build this with three views, but your application code would have to switch between views based on the type of operation it wishes to perform, and you would have to write Instead-of triggers. VPD will allow you to enforce two policies. Thus, any currently written SQL doesnít have to be altered as the security policy is created, altered, or deleted.Setting the Application Context
For this example, the RLS policy will be applied to the PEOPLE table. To enforce the security just described, two policies will be needed: the first will manage the insert and update operations restricting records to the userís department; the second will manage delete operations to ensure the user can only delete their own records.
Restricting insert and updates to the userís department requires knowing what department the user belongs to. To make this as efficient as possible, the userís department number will be stored in an application context. While this is being done for illustrative purposes, application contexts are not required to implement RLS. A lookup table is created to support the population of the context values. The application context setup will involve three steps: creating an application context for the department number, building the namespace manager program to populate the context with the appropriate value, and invoking the namespace manager automatically when the user logs on.
The following code sample for setting up the application context is similar to an example given in Chapter 9. More details about why the code is written as it is are given in that chapter. The code is shown here again as a convenience.
The security manager will set the context values. As such, select privileges are required on the PEOPLE table. The security manager then creates the lookup table.
scott@KNOX10g> -- Recreate the people table to include all rows from EMP
The namespace manager program will set the context based on the userís department number as stored in the LOOKUP_DEPT table:
sec_mgr@KNOX10g> -- Create namespace for application context
To populate the context value automatically, a logon trigger will be used:
sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager
Test the context by logging in as the SCOTT user:
sec_mgr@KNOX10g> CONN scott/tiger
blog comments powered by Disqus