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;
system@10G> -- grant privileges needed for example
system@10G> GRANT CREATE SESSION TO vpd_admin;
system@10G> GRANT CREATE PROCEDURE TO vpd_admin;
system@10G> conn sys
sys@10G> -- need execute privilege on DBMS_RLS
sys@10G> GRANT EXECUTE ON DBMS_RLS TO vpd_admin;
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;
system@10G> GRANT CREATE TABLE TO data_schema;
system@10G> ALTER USER data_schema QUOTA 1 m ON "USERS";
system@10G> -- create table in data schema.
system@10G> CREATE TABLE data_schema.emp AS
2 SELECT * FROM scott.emp;
system@10G> -- grant privs to SCOTT for testing
system@10G> GRANT SELECT ON data_schema.emp TO scott;
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
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
7 RETURN 'ename = nvl(v(''APP_USER''),USER)';
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.
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');
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
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.
Once your new workspace is provisioned, we will need to sign on to it.
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.
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.
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.
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.
Discuss this how-to in the OTN Sample Code Discussion Forum.