Date: 01-Oct-2004
Authors: Scott Spendolini and Sergio Leunissen

Securing an application begins with applying proper access control rules at the database level. One of the ways in which the Oracle database helps you implement access control rules is through Fine Grained Access Control or Virtal Pricate 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 builds up a where clause based on conditions such as who the user is executing the query. The function is then registered against the tables or views you want to protect using the built in DBMS_RLS  package. When a query is issued against the 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.

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 be the security administrator, whose job it 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.

system@10G> -- create security admin user
system@10G> CREATE USER vpd_admin IDENTIFIED BY akf7d98s2 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.

system@10G> -- grant privileges needed for example
system@10G> GRANT CREATE SESSION TO vpd_admin;

Grant succeeded.

system@10G> GRANT CREATE PROCEDURE TO vpd_admin;

Grant succeeded.

system@10G> conn sys

Enter password:
Connected.

sys@10G> -- need execute privilege on DBMS_RLS
sys@10G> GRANT EXECUTE ON DBMS_RLS TO vpd_admin;

Grant succeeded.

Setting Up the Data Schema
Next, we'll create the schema to store our data.  Separating the PL/SQL and privileges needed to implement the VPD policies into a different schema than the schema that holds the data is not mandatory. It is, however, a good practice. We'll create a copy of the SCOTT.EMP table inside the DATA_SCHEMA. Privilege to select on this table is granted to the SCOTT user for testing purposes below.

system@10G> -- create data schema
system@10G> CREATE USER data_schema
2 IDENTIFIED BY laskjdf098ksdaf09
3 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp;

User created.

system@10G> GRANT CREATE TABLE TO data_schema;

Grant succeeded.

system@10G> ALTER USER data_schema QUOTA 1 m ON "USERS";

User altered.

system@10G> -- create table in data schema.
system@10G> CREATE TABLE data_schema.emp AS
2 SELECT * FROM scott.emp;

Table created.

system@10G> -- grant privs to SCOTT for testing
system@10G> GRANT SELECT ON data_schema.emp TO scott;

Grant succeeded.

Creating the Policy Function
We next connect as our security administrator and create the function for our VPD. We are going to use a very simple example here � the function will limit the records returned to be only those of the invoking user. The invoking user will either be a database user or an HTML DB 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 the HTML DB environment.

system@10G> connect vpd_admin/ akf7d98s2

Connected.

vpd_admin@10G> -- create function for VPD.
vpd_admin@10G> CREATE OR REPLACE FUNCTION user_only (
2 p_schema IN VARCHAR2 DEFAULT NULL,
3 p_object IN VARCHAR2 DEFAULT NULL)
4 RETURN VARCHAR2
5 AS
6 BEGIN
7 RETURN 'ename = nvl(v(''APP_USER''),USER)';
8 END;
9 /

Function created.

Registering the Policy Function
Note that execute privileges on the function is not and should not be granted to anyone. The database will execute this function on behalf of the user. We next need to apply a VPD policy for our EMP table. The registration is done by invoking the DBMS_RLS package as our security administrator.

vpd_admin@10G> BEGIN
2 DBMS_RLS.add_policy
3 (object_schema => 'data_schema',
4 object_name => 'EMP',
5 policy_name => 'EMP_SEL_POL',
6 function_schema => 'vpd_admin',
7 policy_function => 'USER_ONLY',
8 statement_types => 'SELECT');
9 END;
10 /

PL/SQL procedure successfully completed.

This policy will be applied to the EMP table owned by DATA_SCHEMA. Any SELECT statements on this table will cause the database to execute the USER_ONLY function located in the VPD_ADMIN schema. The resulting string is effectively appended to the original query.

Testing the Policy Function
We can test our VPD policy by logging in via SQL*Plus to the SCOTT schema. A query on the table is transparently re-written and the results are constrained to be only SCOTT's record.

vpd_admin@10G> connect scott/tiger

Connected.
scott@10G> SELECT empno, ename, job, sal
2 FROM data_schema.emp;

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

Now, let's create a new Workspace in HTML DB, based on the example data.

  • Sign on to the Administration Console of your HTML DB instance
  • Typically, the URL would be: http://your_server.com/some_dad/htmldb_admin
  • Under Manage Workspaces , select 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 , and then click on Next
  • Enter an Administrator Username of �admin�, a Password of your choice, and your Email , and click on Next
  • Review your selections, and then click on Provision

Once your new workspace is provisioned, we will need to sign on to it.

  • Click on the Logout icon in the upper right corner of the page
  • Click on the Login link
  • Enter the Workspace Name , Username and Pas sword which 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. Now we need to build a quick application in order to test the Policy in HTML DB.

  • Click on Create Application
  • Select Based on Existing Tables and click on Next >
  • Select DATA_SCHEMA as the Table/View Owner and click on Next >
  • Enter EMP in the 1st text box for Table/View Names and click on Next >
  • Give your Application a Name and then click on Next >
  • Click on Finish to create your application
  • Click on Run to run your application
  • Sign on with the username admin and the same 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 since you are signed on to HTML DB as a user called �ADMIN�, no rows were returned, as there are no users called �ADMIN� in the EMP table.

Next, we'll create the SCOTT user in HTML DB. Note this does NOT create a database schema (user) SCOTT. SCOTT in this instance is an HTML DB user only and there is no dependency on the SCOTT database schema. That is, the SCOTT database schema does not have to exist for the SCOTT HTML DB user to exist.

  • 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 KING user you just created
  • Click on the Emp link under Report and Edit - you should now see one row

This works because the Policy is still applied to the SELECT statement. Recall the VPD function returns 'ename =  nvl(v(''APP_USER''),USER)'. With SQL*Plus, we were logged in as SCOTT. The above predicate resulted in �ename = �SCOTT'�.

Now in HTML DB, SCOTT's identity is provided to us by the HTML DB built-in �v� function. The v(�APP_USER') call will return the identity of the currently logged in user to HTML DB. Thus, when the function is called from within our HTML DB application, it evaluates to SCOTT. If this function is called from outside HTML DB (i.e., from SQL*Plus), it will return a NULL. We used the NVL around the v(�APP_USER') call to ensure that our policy is enforced for both the HTML DB and SQL*Plus.

Summary

For this example of using VPD, we kept our function � user_info � extremely simple. You can add as much PL/SQL logic to this function as you need to, in order to achieve your business goals.

Additional Resources

Oracle 10g Platform Security

Discuss this how-to in the OTN Sample Code Discussion Forum.