Technical Note

Using Virtual Private Database in an Oracle HTML DB Application
Authors: Scott Spendolini (product manager), Sergio Leunissen (senior product manager), and David Knox (chief engineer)
Publication Date: October 2004

Securing an application begins with applying proper access control rules at the database level. One of the ways the Oracle Database helps you implement access control rules is through Fine Grained Access (FGA) control or Virtual Private Database (VPD). In this article, we will look at how an application developed in Oracle HTML DB can take advantage of this feature.

VPD allows you to restrict access to records based on a security policy that you implement in a PL/SQLfunction. The PL/SQL function contructs a WHERE clause based on conditions defined by the security administrator. The function is then registered with the tables or views you want to protect using the built in DBMS_RLS  package. When a query is issued on a protected table or view, VPD effectively appends the string returned from the policy function to the original SQL statement.

This technical note explains an example of using a VPD policy in an HTML DB application.  The instructions assume you have SQL*Plus access to your own database.

• Oracle HTML DB
• Oracle Database 10g with the SCOTT schema installed

Setting up the Security Administration Schema

To begin, we'll create two schemas; one schema will be our security schema, and the other will be our data schema. The first schema will be used by the security administrator, whose job is to apply the security to our application and data schemas. Note that the SYS user has to grant execute privileges to this schema  on the DBMS_RLS package.

To create the security adminstrator's schema:

• Connect as SYSTEM
• Execute the following:

CREATE USER vpd_admin IDENTIFIED BY
akf7d98s2 DEFAULT
TABLESPACE users TEMPORARY TABLESPACE temp

• Grant the necessary privileges to perform the steps in this technical note by executing the following:

GRANT CREATE SESSION TO vpd_admin;
GRANT CREATE PROCEDURE TO vpd_admin<>;

• Connect as SYS and grant the security adminstrator necessary privileges needed for registering VPD policies:

GRANT EXECUTE ON DBMS_RLS TO vpd_admin;

Setting Up the Data Schema

Next, we'll create the schema to store our data.  It is not necessary to separate the PL/SQL and privileges needed to implement the VPD policies into a different schema than the schema that holds the data. It is, however, a good practice. We'll create a copy of the SCOTT.EMP table inside the DATA_SCHEMA.  For testing purposes, SELECT privilege on the newly created emp table will be granted to SCOTT.

To create the data schema:

• connect as SYSTEM and execute the following:

CREATE USER data_schema
IDENTIFIED BY laskjdf098ksdaf09
DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

• Grant necessary privileges for DATA_SCHEMA to be allowed to create a table by executing the following:

GRANT CREATE TABLE TO
data_schema;
ALTER USER data_schema QUOTA 1
m ON "USERS";

• Create a table based on a copy of SCOTT.EMP:

CREATE TABLE data_schema.emp AS SELECT * FROM scott.emp;

• Grant SELECT privilge to the SCOTT schema for testing:

GRANT SELECT ON data_schema.emp TO scott;

Creating the Policy Function

Next, we'll define the access policy as the security administrator.  The policy function will limit the records returned to those owned by the user running a query. The invoking user may either be a database user or an Oracle HTML DB application user. The function does this by matching the user's name to the value in the ENAME column. We'll see later how this code satisfies our security requirements within an Oracle HTML DB application.

To create the policy function:

• Connect as the security adminstrator user, VPD_ADMIN:

connect vpd_admin/akf7d98s2

• Execute the following:

CREATE OR REPLACE FUNCTION user_only (
                               p_schema IN VARCHAR2 DEFAULT NULL,
                               p_object IN VARCHAR2 DEFAULT NULL)
                       RETURN VARCHAR2
AS
BEGIN
RETURN 'ename = nvl(v(''APP_USER''),USER)';
END;
/

Registering the Policy Function

Note that the EXECUTE privileges on the policy function were intentionally not granted to anyone. The database will execute this function on behalf of the user.  Now, we need to register this function such that it is applied every time a query is run on the EMP table. The registration is done by invoking the DBMS_RLS package as our security administrator.

• To register the policy function, execute the following as VPD_ADMIN:

BEGIN
DBMS_RLS.add_policy
         (object_schema => 'data_schema',
          object_name => 'EMP',
          policy_name => 'EMP_SEL_POL',
          function_schema => 'vpd_admin',
          policy_function => 'USER_ONLY',
          statement_types => 'SELECT');
END;
/

The policy created above states than any SELECT statements on EMP table in the DATA_SCHEMA schema will cause the database to execute the USER_ONLY function located in the VPD_ADMIN schema.  The string returned by the USER_ONLY function will be appended to the query that was originally run.

Testing the Policy Function in SQL*Plus

To test the policy function:

• connect as the SCOTT user

connect scott/tiger

• Run the following query:

SELECT empno, ename, job, sal
   FROM data_schema.emp;

You should see the following results:

EMPNO        ENAME       JOB          SAL
----------   ----------  ---------    ----------
7788         SCOTT       ANALYST      3000

Only one row was returned because the query executed by the database after applying the VPD policy was:

SELECT empno, ename, job, sal
FROM data_schema.emp
WHERE ename = nvl(v('APP_USER'),USER)

We'll get to the meaning of v('APP_USER') later . Suffice it to say here that, when this query was run outside of the context of Oracle HTML DB, the function v('APP_USER') returned NULL and that therefore ename was matched to the value of USER for each row.

Testing the Policy Function in an Oracle HTML DB Application

To create a workspace:

• Log in as the Oracle HTML DB  Service Administrator, using a URL that looks like this:

http://your_server.com/pls/some_dad/htmldb_admin

• Under Manage Workspaces, click Create New Workspace.
• Enter a Workspace Name and click on Next .
• Choose to Re-use an existing schema, and enter DATA_SCHEMA for the Schema Name.
• Click on Next.
• In the Administrator Username field, enter admin.
• In the Password field, enter a password.
• In the Email field, enter an email address.
• Click on Next.
• Review your selections, and then click on Provision.

To log in to the workspace:

• Click on the Logout icon in the upper right corner of the page.
• Click on the Login link.
• Enter the Workspace Name, Username , and Password you just created and click on Login.

You should now see the "Welcome to HTML DB" screen, with icons for the Application Builder, SQL Workshop, Data Workshop, and Administration.The quickest way to test the VPD policy in an Oracle HTML DB application is to create one on the existing EMP table.

To quickly create an application:

• Click on Create Application.
• Select Based on Existing Tables.
• Click on Next >.
• Select DATA_SCHEMA as the Table/View Owner.
• Click on Next >
• In the first field under Table/View Names, enter EMP.
• Click on Next >.
• Click on Finish to create your application.
• Click on Run Application to run your application.
• Log in with the username admin and the password you chose when you created the Workspace.
• Click on the Emp link under Report and Edit; you should get a "No Data Found" message.

The Policy was again applied to the SELECT statement on the EMP table, and as you logged in as the application user admin, no rows were returned according to the VPD policy in effect.

To see the same data returned in the report as we saw in SQL*Plus, we'll have to create an application user SCOTT. The application we created using the application wizard by default uses Oracle HTML DB authentication, based on a repository of users associated with the workspace the application is part of. We can use the workspace administration facilities to create a new user.

Note that we'll be creating an application user here, not a database schema. Oracle HTML DB gives you the flexibility to maintain application users wherever you need, including an LDAP repository or Single Sign-On. For this demonstration, we'll stick with the Oracle HTML DB supplied authentication mechanism.

To create a new application user in the workspace:

• While the application is running, click on Edit Application at the bottom of the page.
• Under the Tasks region, click on Manage Developers, and then click on Create >.
• Create a user called SCOTT, specify a password and a valid e-mail address, and then click on Create User.
• Click on the Build icon in the navigation bar, and then click on Run.
• Click on the Logout link, and then log back in as the SCOTT user you just created.
• Click on the Emp link under Report and Edit; you should now see one row.

Note how the same policy is applied whether the data is accessed directly from SQL*PLUS or through a report in an Oracle HTML DB application.

A bit more on the function call v('APP_USER') in the VPD function. APP_USER is a built-in session state variable that always contains the username of the user currently running an Oracle HTML DB application, regardless of how this user was authenticated. To make sure the VPD policy works as desired in both SQL*Plus and inside an Oracle HTML DB application, we used the NVL around the v('APP_USER') function.

You have just implemented a VPD policy in your HTML DB application.