Virtual Private Database
Using the secure views technique outlined in Chapter 10 can go a long way with quickly and affordably securing data in APEX applications and other applications. However, it does have some limitations that cannot be easily overcome.
Virtual Private Database (VPD) is a no-cost feature of the Oracle Database Enterprise Edition that dynamically adds WHERE clauses to any SQL statement used against a table or view. Managed by a simple PL/SQL function, VPD is a robust way to restrict which rows and columns a given user can view. In addition to restricting SELECT statements, VPD can be configured to also restrict INSERT, UPDATE, and DELETE statements, offering a full breadth of data protection.
Despite that it uses its own session management, APEX is fully compatible with VPD and can easily and quickly be configured to work with it. This chapter will provide a general overview of VPD and how to configure it to work in an APEX environment.
Take a moment and think about what technologies were used to build applications 15 or 20 years ago. It was likely some sort of host-based system, possibly even a mainframe. Chances are really good that it was a character-based application because the mouse was not as commonplace then as it is today. Many of these systems were written in COBOL, ADA, or even C. Regardless of which language was used, many had no choice but to deal with data security within the application itself.
Now, think about systems from 10 or so years ago. Many systems of that time were mostly client-server, where the data resided on a server and the client was installed on each individual workstation. While these systems looked better than their character-mode forefathers, they still had the same fundamental security architecture; data security was mostly built into the client.
Fast-forward to today’s web-based and even smartphone-based systems. These systems are, in a way, a hybrid of host-based and client-server computing, because processing does occur on both the client and the server. The main difference between the two is that any code that is executed on the client is easily and seamlessly distributed from the application or web server. And much like with the older systems, data security is almost always managed from within the application itself.
It’s not too difficult to spot the trend in application development over the last 20 or so years. Time after time, data security gets folded into the application code. And since it seems that the longevity of any specific technology is just a few years, all of that work eventually gets scrapped and rebuilt in the next technology.
Consider a payroll system that is 20 years old. While there have been numerous tax law changes over the years, the basic business and security rules of almost any payroll system have remained constant. However, as the technologies used to build the payroll system have changed, the same data security rules have had to be rewritten in the new technology every time, with few changes to the basic security rules themselves. These changes not only are costly but also introduce risk that the rules are not properly migrated to the new technology.
Wouldn’t it make more sense to apply data security to tables and views rather than to the application? This way, once the security rules are defined and applied, it no longer matters which technology is being used to access the data because the security occurs at the database layer, not within the application. This is exactly the premise that VPD takes when it comes to securing your data: do it once at the database layer and never have to do it again. The data is what lives forever, not the application code.
VPD Basics
VPD—sometimes referred to as fine-grained access control—essentially will automatically append conditions to a SQL query’s WHERE clause as it executes, thus dynamically changing the results returned. How and what changes when a query is executed is determined by a VPD policy, which is essentially a PL/SQL function that returns the WHERE clause to be appended to the query.
Developers can create policies that have different business rules for SELECTs, INSERTs, UPDATEs, and DELETEs. Alternatively, they can create a single policy that handles all four DML transaction types at once, or any combination thereof. Policies can also be grouped together and applied all at once.
While the most common use of VPD is to filter rows based on some value or business rule, VPD can also be applied to columns within a query. Column-level policies can be applied in one of two ways: show only those records that match predefined criteria or show all records but mask data in columns that don’t match a defined criteria.
For example, if a VPD policy were created that allowed them to see only records in their department, a simple query on the EMP table would return only the rows where the DEPTNO column value was the same as the currently logged in user. Thus, only a subset of all records would ever be returned. If the VPD policy was created and set to show all records but obfuscate the SAL and COMM columns for records that had a different DEPTNO than the user who was logged in, all rows would be returned, but those with a different DEPTNO would simply have NULLs displayed in the SAL and COMM columns.
VPD quite often uses application contexts as a parameter in the dynamic WHERE clause that it generates. This is done for both security and performance reasons. As discussed in Chapter 10, APEX can work perfectly fine with application contexts, provided they are set properly in the Initialization PL/SQL Code region in an application’s Security Attributes section. Refer to the previous chapter for more details.
While there are more features and information about implementing VPD, they are out of scope for this book. To learn more, take a look at Chapter 7 of the Oracle Database Security Guide (Part #B28531-06).
Configuring VPD to work with APEX is relatively simple. Essentially the steps are the same as they are for implementing VPD outside of an APEX environment. In fact, if an application context is not used, then the steps are the same as they would be for a non-APEX implementation.
To illustrate VPD in an APEX environment, you’ll go through a simple example that filters queries on the EMP table to show only the record associated with the currently logged in APEX user. As a first step, create a simple APEX application with one page. On that page, create a report—interactive or standard—that contains the SQL illustrated in Listing 12-1.
Listing 12-1. A Simple SQL Statement on the EMP Table
SELECT * FROM EMP
When running this APEX application, all 14 records will be returned because there is not yet any WHERE clause or VPD function to restrict the report.
Next, the VPD policy function needs to be created. To add an additional layer of security, the VPD policy function can be created in a separate schema. This is done namely to prevent a developer from maliciously altering the business rules of the policy function, assuming that the developer does not have DBA rights or access to the schema in which the policy function is created in. For this example, the policy function will be created in the same schema that the EMP table resides in.
One more important point about VPD policy functions is that they must have two IN parameters of type VARCHAR2 that will be passed the schema and object name to which the policy function will be applied. These parameters can be called anything at all but must be the first and only two parameters passed to the function.
Listing 12-2 illustrates a VPD policy function that restricts the result based on the currently signed on APEX user. Note how double quotes had to be used when making reference to the APEX v function. This is done so that the quotes are included in the string rather than being treated as delimiters.
Listing 12-2. A VPD Policy Function Used to Limit Results by User Name
CREATE OR REPLACE FUNCTION limit_by_username
(
p_schema IN VARCHAR2 DEFAULT NULL,
p_objname IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
AS
BEGIN
-- Return the SQL
RETURN 'ename = v(''APP_USER'')';
END limit_by_username;
/
At this point, running the report will still yield the same results because the VPD policy function must be associated with the EMP table by using the DBMS_RLS packages. Access to the DBMS_RLS packages are not granted by default, so a DBA will need to grant EXECUTE to your APEX parse-as schema before the policy can be registered. The grant can be done via a simple SQL statement, as illustrated in Listing 12-3.
Listing 12-3. SQL Executed as a DBA Used to Grant Access to DBMS_RLS to a Schema
GRANT EXECUTE ON SYS.DBMS_RLS TO MY_SCHEMA
/
Once the grant has been made, the policy can be registered using the DBMS_RLS.ADD_POLICY API, as outlined in Listing 12-4.
Listing 12-4. Using DBMS_RLS.ADD_POLICY to Register the VPD Policy Function
BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => 'ENKITEC',
object_name => 'EMP',
policy_name => 'LIMIT_BY_APP_USER',
policy_function => 'LIMIT_BY_APP_USER',
function_schema => 'ENKITEC'
);
END;
/
Once the policy is applied, let’s take a look at the results of the report in the APEX application. If you log in as the user KING and run the report in the APEX application, you see only your own row, as illustrated in Figure 12-1.
Figure 12-1 . Report on EMP after the VPD policy function has been applied
If you log in as any user who does not have a corresponding record in the EMP table, you will see no rows, because the VPD policy function does not yield any matches based on the dynamic WHERE clause. Also, if you run the same query—SELECT * FROM EMP—in SQL*Plus connected to the database as any user, there will also be no rows returned. This is because the APEX v function does not return a valid value outside of APEX, which causes the WHERE clause to filter out all records.
A simple alteration to the policy function will allow it to work both inside and outside of APEX. Instead of comparing ENAME to the result of v('APP_USER') only, an NVL can be used to first check to see whether APP_USER has a value and, if it does not, default to the currently signed on database user. Listing 12-5 shows the updated line of the policy function.
Listing 12-5. Updated Line of the Policy Function to Enable It to Work Both with and Without APEX
-- Return the SQL
RETURN 'ename = NVL(v(''APP_USER''),user)';
Once the policy function is modified, there is no need to reregister it with the DBMS_RLS package. The changes will take place immediately without any further action on the part of the developer. In APEX, there should be no difference in results. However, in SQL*Plus, there may or may not be a row returned. This will depend on whether your schema name has a corresponding ENAME record in the EMP table. If it does, then that record will be returned. If not, then no records will be returned.
Chances are that no records will be returned in SQL*Plus. If this is indeed the case, adding or even altering a record in SQL*Plus will also be blocked by the VPD policy function because when the policy function was created, it was applied to all types of DML transactions—INSERTs, UPDATEs, DELETEs, and SELECTs.
Attempting to update the EMP table directly with SQL*Plus will not yield an error but rather simply state that no rows were updated, as illustrated in Listing 12-6.
Listing 12-6. Attempting to Update a Table Protected by a VPD Policy Function
SQL> update emp set ename = 'ENKITEC';
0 rows updated.
Thus, the VPD policy function needs to be disabled for any type of transaction to occur. This is also done via the DBMS_RLS packages, specifically the DROP_POLICY API. Calling DROP_POLICY does not drop the corresponding policy function but rather disassociates it from the object that it was originally associated with. Listing 12-7 shows an example of how to disable or drop a VPD policy function.
Listing 12-7. Dropping a VPD Policy Function
BEGIN
DBMS_RLS.DROP_POLICY
(
object_schema => 'ENKITEC',
object_name => 'EMP',
policy_name => 'LIMIT_BY_APP_USER'
);
END;
/
Once the policy is disabled, normal DML operations on the EMP table can resume. Reenabling the VPD policy is done by simply calling the DBMS_RLS.ADD_POLICY API, as referenced in Listing 12-4. There is no need to re-create the policy function itself.
Column Masking and Obfuscation
In addition to filtering which records are returned from a query, VPD can also filter or obfuscate data in a column or columns. There are two types of column masking: the first type will simply remove the specified column or columns entirely from the query, whereas the second type will display NULLs in place of values that meet the policy function’s criteria. When using column masking and VPD, the policy function used can be associated only with a SELECT statement. An error will occur if the policy function is registered with either INSERT, UPDATE, or DELETE.
For the next example, a new policy will be added to the EMP table that will be used to illustrate the column relevance feature of VPD. This policy will be applied only when either the SAL or COMM column is present in a query on the EMP table.
To illustrate this example, create a new APEX page. On that page, create two standard reports side-by-side. Both of these reports will contain simple queries based on the EMP table. The first report should use the SQL from Listing 12-8, and the second report should use the SQL from Listing 12-9.
Listing 12-8. SQL for the First Report
SELECT ename, sal, comm FROM emp
Listing 12-9. SQL for the Second Report
SELECT ename, deptno FROM emp
Notice that in both SQL statements there is no WHERE clause. The VPD policy function and the application context will take care of that for you. When the new APEX page is run, each report should show all selected columns of all records in the EMP table, as illustrated in Figure 12-2.
Figure 12-2 . The results of two simple SQL statements on the EMP table
Once the APEX reports are created, the next step is to create a new VPD policy function. The new function will act similar to the first but with a slight change. Initially, it will remove the entire record if the corresponding DEPTNO does not match that of the current user. Then, with a slight alteration to how you register the policy function, it will show all of the records but display NULLs for the SAL and COMM columns where the corresponding DEPTNO does not match that of the current user. This policy will also use an application context, which will be configured a little later. Listing 12-10 shows the new policy function, limit_by_deptno.
Listing 12-10. The limit_by_deptno Policy Function
CREATE OR REPLACE FUNCTION limit_by_deptno
(
p_schema IN VARCHAR2 DEFAULT NULL,
p_objname IN VARCHAR2 DEFAULT NULL
)
RETURN VARCHAR2
AS
l_sql VARCHAR2(255);
BEGIN
-- Set the SQL to compare DEPTNO to the application context
l_sql := 'deptno = SYS_CONTEXT(''EMP_VPD_CTX'',''DEPTNO'')';
-- Return the SQL
RETURN l_sql;
END limit_by_deptno;
/
The next step is to register the policy function, as illustrated in Listing 12-11. This is done by calling the same API as in the previous exercise, with one minor difference. By passing in a list of columns to the sec_relevant_cols parameter, you can tell the VPD function that it needs to execute only when either of those columns is present in the query. Thus, if a simple SELECT ename FROM EMP query was issued against the table, the VPD policy function would not execute, and all records would be returned.
Listing 12-11. Setting the limit_by_deptno VPD Policy Function
BEGIN
DBMS_RLS.ADD_POLICY(
object_schema => 'ENKITEC',
object_name => 'EMP',
policy_name => 'LIMIT_BY_DEPTNO',
policy_function => 'LIMIT_BY_DEPTNO',
function_schema => 'ENKITEC',
sec_relevant_cols => 'SAL,COMM'),
END;
/
At this point, the VPD policy function is created and registered. Because the policy is now triggered only when either the SAL or COMM column is present in the query, it is being applied only to the first report. And because the application context it uses is neither created nor set in APEX, the first report returns no data, as illustrated in Figure 12-3. The second report, on the other hand, is not impacted by the policy because it includes only the ENAME and DEPTNO columns.
Figure 12-3 . Running the reports without setting the application context
As your business rules and corresponding policy functions get more sophisticated, it is a good idea to consider using an application context to store values used in the function itself. As outlined in Chapter 10, an application context is a server-side component designed to securely and efficiently store value-attribute pairs in memory. These value-attribute pairs can easily and efficiently be accessed by calling the SYS_CONTEXT API within a SQL query.
Application contexts are typically set at the beginning of a database session and then last throughout the duration of that session. Since APEX sessions are distinct from database sessions, the application context must be set before every page view and asynchronous transaction. While this seems redundant and inefficient, it is the only way to properly instrument APEX to work with application contexts. Fortunately, it is quite easy to do.
As mentioned in the previous chapter, a schema needs to have the CREATE ANY CONTEXT system privilege be able to create an application context. Alternatively, a schema with DBA privileges can create the context but must refer to the associated procedure with the SCHEMA.PROCEDURE notation. In this example, it is assumed that the ENKITEC schema does have CREATE ANY CONTEXT and can use the SQL in Listing 12-12 to create the application context. Oracle will not report an error if an application context is created that references a package or procedure that does not yet exist, as in this case.
Listing 12-12. SQL Used to Create an Application Context
CREATE CONTEXT emp_vpd_ctx USING emp_vpd_ctx_pkg;
Next, the package—shown in Listing 12-13—that will set and unset the application context must be created. The set_ctx procedure will set the DEPTNO attribute of the EMP_VPD_CTX context based on the currently logged in user. That value is then in turn referenced in the limit_by_deptno VPD policy function and is used to filter the results based on the currently signed on user’s DEPTNO. The unset_ctx procedure will simply remove any value stored in the EMP_VPD_CTX context.
Listing 12-13. The emp_vpd_ctx_pkg Package, Which Is Used to Manage the Application Context
CREATE OR REPLACE PACKAGE emp_vpd_ctx_pkg
AS
PROCEDURE set_ctx
(
p_user_name IN VARCHAR2
);
PROCEDURE unset_ctx;
END emp_vpd_ctx_pkg;
/
CREATE OR REPLACE PACKAGE BODY emp_vpd_ctx_pkg
AS
PROCEDURE set_ctx
(
p_user_name IN VARCHAR2
)
IS
l_deptno NUMBER;
BEGIN
-- Fetch the DEPTNO based on the currently signed on APP_USER
SELECT deptno INTO l_deptno FROM emp
WHERE UPPER(ename) = UPPER(p_user_name);
-- Set the Context
dbms_session.set_context(
namespace => 'EMP_VPD_CTX',
attribute => 'DEPTNO',
value => l_deptno);
EXCEPTION
WHEN no_data_found THEN
-- If no data is found, then clear the context
dbms_session.clear_context('EMP_VPD_CTX'),
END set_ctx;
PROCEDURE unset_ctx
IS
BEGIN
-- Clear the context
dbms_session.clear_context('EMP_VPD_CTX'),
END unset_ctx;
END emp_vpd_ctx_pkg;
/
As a final step, the APEX application must set and unset the context before every page view, whether the page view is done traditionally or asynchronously. This can be done by editing the security attributes of your application, which can be found in the Shared Components section. At the bottom of that page, there is a section called Database Session. In that section, there are two fields where you can call PL/SQL before and after an APEX session.
To reference the package that manages the application context, set the value of the Initialization PL/SQL Code option to emp_vpd_ctx_pkg.set_ctx(p_user_name => :APP_USER); and the value of Cleanup PL/SQL Code to emp_vpd_ctx_pkg.unset_ctx;, as illustrated in Figure 12-4. Then apply your changes.
Figure 12-4 . Setting the Initialization SQL Code and Cleanup PL/SQL Code options to enable an application context
Now that all the required components are in place, run the APEX application, and make sure to sign on with a user name that has a corresponding entry in the ENAME column of the EMP table. For this example, a user called KING was created and used. Based on which user you log in as, the results will vary. In all cases, the only records that show up in the first report will be those that have the same DEPTNO as the user account that you are logged in to, as illustrated in Figure 12-5.
Figure 12-5 . Running the APEX application as KING with VPD column relevance rules applied
Now that everything is working with the VPD and application context, you can alter the way you register the policy function slightly to change how it behaves. Instead of removing rows that don’t meet the criteria, you can choose to include those rows with the columns specified set to NULL. This would prove useful if you needed a system that showed customer data but did not want to expose Social Security numbers to a specific group of users.
To do this, first drop the policy function as per Listing 12-14.
Listing 12-14. SQL Used to Drop the limit_by_deptno VPD Policy
BEGIN
DBMS_RLS.DROP_POLICY
(
object_schema => 'ENKITEC',
object_name => 'EMP',
policy_name => 'LIMIT_BY_DEPTNO'
);
END;
/
Next, all that is required is to reregister the policy function like before but this time using an additional parameter, as illustrated in Listing 12-15. By passing DBMS_RLS.ALL_ROWS to the sec_relevant_cols_opt parameter of DBMS_RLS.ADD_POLICY, the way that the policy function is applied changes. All rows will be returned for all records, but values in the SAL and COMM columns will appear only if the rule set in the policy is met. No code needs to change anywhere else in the policy or package required to manage the application context.
Listing 12-15. SQL for Adding a VPD Policy Function That Returns All Records but Obfuscates Sensitive Data
BEGIN
DBMS_RLS.ADD_POLICY
(
object_schema => 'ENKITEC',
object_name => 'EMP',
policy_name => 'LIMIT_BY_DEPTNO',
policy_function => 'LIMIT_BY_DEPTNO',
function_schema => 'ENKITEC',
sec_relevant_cols => 'SAL,COMM',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
);
END;
/
Once the new VPD policy is added, rerun the APEX application. Now, both reports should show all records. The first report will show only SAL and COMM values for records that have the same DEPTNO as the user who is logged in, as illustrated in Figure 12-6.
Figure 12-6 . Results of the new VPD policy where sensitive data is hidden
Managing VPD in Oracle Enterprise Manager
If the command line is not for you, you can configure and manage Oracle VPD via Oracle Enterprise Manager (OEM). OEM provides a GUI interface that masks most of the complexity of registering policy functions. Policies can be quickly created and removed by simply filling in the blanks, reducing the potential for error and decreasing the time it takes to manage VPD. The policy functions themselves still have to be created using an external tool, such as SQL*Plus or SQL Developer. In addition to managing VPD from OEM, the creation and management of application contexts can be done here, too.
By centralizing the management of VPD, a DBA can take ownership of the task of managing the components of VPD all through a familiar interface. This not only simplifies the DBAs job but also decreases the potential for error because the GUI will provide more guidance and error checking than the command line.
To manage either the application contexts or VPD, simply click the Server tab on the home page of the database that you are managing in OEM. On the next page, there will be several options in the Security region, as illustrated in Figure 12-7. Your list may vary, depending on which options are installed in your database.
Figure 12-7 . The Security options on the Oracle Enterprise Manager Server page
To register a VPD policy function, click Virtual Private Database. From this page, any registered function can be queried based on its schema name, object name, or owner. VPD functions can also be registered by clicking the Create button and filling out the corresponding details. Figure 12-8 shows the parameters required to replicate the most recent example in this section. Note that in some fields the SCHEMA.OBJECT notation is required.
Figure 12-8 . Registering a VPD policy function with Oracle Enterprise Manager
Summary
While there’s no way to tell just how long APEX will be a viable application development platform, it is almost certain that your data will outlive it. Therefore, taking the time to implement access control rules at the lowest level possible—the database—makes the most sense. Virtual Private Database provides a secure, easy-to-use construct that allows developers to ensure that data access rules are always applied, regardless of the technology used to access the data. VPD rules are also flexible and can easily be adapted to fluid business rules. VPD can easily be configured to work with APEX’s session state constructs, making it an ideal complement to APEX.