Database

Created May 23, 2024 by Mumtaj Yasmin

Unit Objective

This unit give readers how to create function and policy. This unit explain applying policy, policy attaching with function. It also explains application context concepts.

4.1 Introduction

The Virtual Private Database (VPD) is introduced in oracle 8i version. This is special feature which will control the database access. The purpose of VPD is whenever object privilege and role is not sufficient to fulfill the requirement of the user security then VPD policy control the access. In the VPD policy includes SELECT, INSERT, UPDATE, DELETE, INDEX statement. It controls user directly or indirectly accessing any schema objects such as views, tables or synonyms in the database. Using Predicate to the view it will restrict the user to access the row, it is called Row Level security as RLS. The package name DBMS_RLS will support for implementing this feature. VPD is not supporting TRUNCATE OR ALTER statement i.e Data Definition Language statement. It is using Application context feature to control the user transaction views in the database.

4.2 Components of an Oracle Virtual Private Database Policy

There is two components, first to create function and then create policy. For Creating a Function to generate the Dynamic WHERE Clause (predicate). Using predicate define the restriction to apply for the policy. The function generally created in administration own schema. (i.e. SYS).

4.2.1. Create Function

CREATE TABLE USER1(USERID NUMBER(4) PRIMARY KEY,USERNAME VARCHAR2(15));

INSERT INTO USER1 VALUES(11,'ARVIND');

INSERT INTO USER1 VALUES(12,'SINDUJA');

INSERT INTO USER1 VALUES(13,'RAJU');

CREATE TABLE PROJECT(USERID NUMBER(4),PROJECT_NAME VARCHAR2(20),NO_OF_HOURS NUMBER(3));

INSERT INTO PROJECT VALUES(11,'MOBILE APPLICATION',30);

INSERT INTO PROJECT VALUES(11,'ONLINE SHOPPING',40);

INSERT INTO PROJECT VALUES(12,'HR APPLICATION',25);

INSERT INTO PROJECT VAlUES(12,'TELE BUY',35);

Before creating function create two tables in HR schema USER1 and PROJECT.

CREATE FUNCTION

The function must have the following behavior

1) It must take as arguments a schema name and an object (table, view, or synonym) name as inputs.

Define input parameters to hold this information, but do not specify the schema and object name themselves within the function. The policy that you create with the DBMS_RLS package. You must create the parameter for the schema first, followed by the parameter for the object.

2) It must provide a return value for the WHERE clause predicate that will be generated.

The return value for the WHERE clause is always a VARCHAR2 data type.

3) It must generate a valid WHERE clause., in that its WHERE clause is the same for all users who log on.

But in most cases, you may want to design the WHERE clause to be different for each user, each group of users, or each application that accesses the objects you want to protect. For example, if a manager logs in, the WHERE clause can be specific to the rights of that particular manager. You can do this by incorporating an application context, which accesses user session information, into the WHERE clause generation code.

4) It must not select from a table within the associated policy function.

Although you can define a policy against a table, you cannot select that table from within the policy that was defined against the table.

Creating a Policy to Attach the Function to the Objects You Want to Protect

4.3 Creating a Policy to Attach the Function to the Objects

Creating a Policy to Attach the Function to the Objects You Want to Protect object. To attach a policy to a table, view, or synonym, you use the DBMS_RLS.ADD_POLICY procedure. You need to specify the table, view, or synonym to which you are adding a policy, and a name for the policy. You can also specify other information, such as the types of statements the policy controls (SELECT, INSERT, UPDATE, DELETE, CREATE INDEX, or ALTER INDEX).

You can enforce Oracle Virtual Private Database policies for SELECT, INSERT, UPDATE, INDEX, and DELETE statements. If you do not specify a statement type, by default, Oracle Database specifies SELECT, INSERT, UPDATE, and DELETE but not Index. Enter any combination of these statement types by using the statement_types parameter in the DBMS_RLS.ADD_POLICY procedure. Enclose the list in a pair of single quotation marks.

In the Virtual Private Database policy, you must ensure that the statement_types parameter includes all three of the INSERT, UPDATE, and DELETE statements for the policy to succeed. Alternatively, you can omit the statement_types parameter. You can enforce Oracle Virtual Private Database policies on index maintenance operations by specifying INDEX with the statement_types parameter.

create policy

After creating policy, login HR schema using PROJECT table, it will show only USERID 12 records i.e 2 rows only. It don‘t show all the records in the PROJECT table, once you policy is attach with the function RETURN_VAL:='USERID=12' this predicate WHERE condition is applied to the table. That is a reason it is showing only user 12 values.

4.4 Drop Function

For deleting the function, write drop function statement

DROP FUNCTION <FUNCTION_NAME>

DROP FUNCTION GET_PROJECT;

4.5 Drop Policy

EXEC DBMS_RLS.DROP_POLICY ('schema name', 'object name', 'policy name');

SQL>EXEC DBMS_RLS.DROP_POLICY ('HR', 'PROJECT', 'PROB_project');

For example

HR-schema name

PROJECT-object name

PROB_project - policy name

All parameter should be enclosed by signal quotes.

4.6 Column Masking

Set up a policy for each set of columns that has a different rule not for each user, for each set of columns.

The following considerations apply to column-masking:

* Column-masking applies only to SELECT statements.

* Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates.

* For applications that perform calculations, or do not expect NULL values, use standard column-level Oracle Virtual Private Database, specifying SEC_RELEVANT_COLS rather than the SEC_RELEVANT_COLS_OPT column-masking option.

* Do not include columns of the object data type (including the XMLtype) in the sec_relevant_cols setting. This column type is not supported for the sec_relevant_cols setting. * Column-masking used with UPDATE AS SELECT updates only the columns that users are allowed to see.

Display Emp table

create Function

Column-level policies enforce row-level security when a query references a security-relevant column. You can apply a column-level Oracle Virtual Private Database policy to tables and views, but not to synonyms. To apply the policy to a column, specify the security-relevant column by using the SEC_RELEVANT_COLS parameter of the DBMS_RLS.ADD_POLICY procedure. This parameter applies the security policy whenever the column is referenced, explicitly or implicitly, in a query.

sys login

create policy

display table

With column-masking behavior, all rows display, even those that reference sensitive columns. The sensitive columns display as NULL values. To enable column-masking, set the SEC_RELEVANT_COLS_opt parameter of the DBMS_RLS.ADD_POLICY procedure.

drop policy

column masking

For some queries, column-masking may prevent some rows from displaying. Because the column-masking option was set, this query may not return rows if the salary column returns a NULL value. This example deptno 20,10 values sal column values are NULL

Display column masking

4.7 SYS_CONTEXT

The following statement returns the name of the user who logged onto the database:

CONNECT OE/OE 
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') 
   FROM DUAL;

SYS_CONTEXT ('USERENV', 'SESSION_USER')
------------------------------------------------------
OE

SELECT SYS_CONTEXT ('<name_space>','<parameter>', <length>) from DUAL;

SYS_CONTEXT function returns the value of parameter associated with the context namespace. This function can be used in both SQL and PL/SQL statements.Context namespaces are always stored in the schema SYS . If you omit schema , then Oracle Database uses the current schema. package. To create a context namespace, you must have CREATE ANY CONTEXT system privilege. The parameter name can be any string. It is not case sensitive, but it cannot exceed 30 bytes in length. Oracle provides a built-in namespace called USERENV, which describes the current session.

4.8 Oracle Virtual Private Database Policy Types

Policy types control how Oracle Database caches Oracle Virtual Private Database policy predicates. Setting a policy type for your policies, because the execution of policy functions can use a significant amount of system resources. Minimizing the number of times that a policy function can run optimizes database performance. You can choose from five policy types: DYNAMIC, STATIC, SHARED_STATIC, CONTEXT_SENSITIVE, and SHARED_CONTEXT_SENSITIVE. To specify the policy type, set the policy_ type parameter of the DBMS_RLS.ADD POLICY procedure.

policy type

4.9 Application Context

Application context helps you apply fine-grained access control because you can link function-based security policies with applications. Oracle provides a built-in application context namespace, USERENV, which provides access to predefined attributes. These attributes are session primitives which is information that the database automatically captures about a user session. For example, the IP address from which a user connects, the user name, and the proxy user name (in cases where a user connection is proxies through a middle tier), are all available as predefined attributes through the USERENV application context.

Such usage removes the repeated overhead of querying the database each time access to application attributes is needed.

Step 1: Create User Accounts and Sample Tables

1.Start SQL*Plus and log on as a user who has administrative privileges.

conn sys as sysdba

Enter password: sys

2.Create the following administrative user, who will administer the Oracle Virtual Private Database policy.

The following SQL statements create this user and then grant the user the necessary privileges for completing this tutorial.

GRANT CREATE SESSION, CREATE ANY CONTEXT, CREATE PROCEDURE, CREATE TRIGGER, ADMINISTER DATABASE TRIGGER TO sysadmin_vpd IDENTIFIED BY sysad;

GRANT EXECUTE ON DBMS_SESSION TO sysadmin_vpd;

GRANT EXECUTE ON DBMS_RLS TO sysadmin_vpd;

3.Create the following user accounts:

GRANT CREATE SESSION TO arvind IDENTIFIED BY arvind123;

GRANT CREATE SESSION TO induja IDENTIFIED BY induja123;

GRANT CREATE SESSION TO raju IDENTIFIED BY raju123;

4.Check the status of the sample user SCOTT, who you will use for this tutorial:

SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'SCOTT';

If the DBA_USERS view lists user SCOTT as locked and expired, then enter the following statement to unlock the SCOTT account and create a new password for him:

ALTER USER SCOTT ACCOUNT UNLOCK IDENTIFIED BY tiger;

5.Connect as user SCOTT, and then create and populate the customers table.

CONNECT scott/tiger

CREATE TABLE USER1(USERID NUMBER(4) PRIMARY KEY,USERNAME VARCHAR2(15));

INSERT INTO USER1 VALUES(11,'ARVIND');

INSERT INTO USER1 VALUES(12,'INDUJA');

INSERT INTO USER1 VALUES(13,'RAJU');

CREATE TABLE PROJECT(USERID NUMBER(4),PROJECT_NAME VARCHAR2(20),NO_OF_HOURS NUMBER(3));

INSERT INTO PROJECT VALUES(11,'MOBILE APPLICATION',30);

INSERT INTO PROJECT VALUES(11,'ONLINE SHOPPING',40);

INSERT INTO PROJECT VALUES(12,'HR APPLICATION',25);

INSERT INTO PROJECT VALUES(12,'TELE BUY',35);

INSERT INTO PROJECT VALUES(13,'WATER BILL',40);

When you enter the user email addresses, enter them in upper-case letters. Later on, when you create the application context PL/SQL package, the SESSION_USER parameter of the SYS_CONTEXT function expects the user names to be in upper case. Otherwise, you will be unable to set the application context for the user.

6.User sysadmin_vpd will need SELECT privileges for the user1 table, so as user SCOTT, grant him this privilege.

GRANT SELECT ON user1 TO sysadmin_vpd;

7.Create and populate the project table.

GRANT SELECT ON project TO arvind;

create sysadmin_vpd user

create user1 and project table

grant select privilege

Step 2: Create a Database Session-Based Application Context

1.Connect as user sysadmin_vpd.

CONNECT sysadmin_vpd/sysad

2.Enter the following statement:

CREATE OR REPLACE CONTEXT pro_ctx USING pro_pkg;

This statement creates the pro_ctx application context. Remember that even though user sysadmin_vpd has created this context and it is associated with the sysadmin_vpd schema, the SYS schema owns the application context.

Step 3: Create a PL/SQL Package to Set the Application Context

As user sysadmin_vpd, create the following PL/SQL package, which will set the database session-based application context

CREATE OR REPLACE PACKAGE pro_pkg IS

PROCEDURE set_userid;

END;

CREATE OR REPLACE PACKAGE BODY pro_pkg IS

PROCEDURE set_userid

userno NUMBER; // userno variable hold the userid.

BEGIN

SELECT userid INTO userno FROM scott.user1 // SELECT statement to copy the userid information in the variable userno

WHERE username = SYS_CONTEXT('USERENV', 'SESSION_USER');

DBMS_SESSION.SET_CONTEXT('pro_ctx', 'userid', userno);

EXCEPTION

WHEN NO_DATA_FOUND THEN NULL;

END set_userid;

END;

Uses a WHERE clause to find all the USERIDs that match the username of the user who is logging on. For example username of "ARVIND" userid 11 is matching.
Sets the pro_ctx application context values by creating the userid attribute and then setting it to the value stored in the userno variable.
Add a WHEN NO_DATA_FOUND system exception to catch any no data found errors that may result from the SELECT statement

create package

Step 4: Create a Logon Trigger to Run the Application Context PL/SQL Package

CREATE TRIGGER pro_trig AFTER LOGON ON DATABASE

BEGIN

sysadmin_vpd.pro_pkg.set_userid;

END;

Step 5: Create a PL/SQL Policy Function to Limit User Access

CREATE OR REPLACE FUNCTION get_profun(

schema_p IN VARCHAR2,

table_p IN VARCHAR2)

RETURN VARCHAR2

usernum VARCHAR2(400);

BEGIN

usernum:='USERID=11';

RETURN usernum;

END;

Step 6: Create the New Security Policy

BEGIN

DBMS_RLS.ADD_POLICY (

object_schema => 'SCOTT',

object_name =>'PROJECT',

policy_name => 'PRO_policy',

function_schema => 'SYSADMIN_VPD',

policy_function => 'get_PROFUN',

statement_types => 'select');

END;

create Trigger, function, policy

Step 7: Test the New Policy

CONNECT ARVIND

Enter password: ARVIND123

SELECT * FROM SCOTT.PROJECT;

Test the session

4.10 Drop package

Specify BODY to drop only the body of the package. If you omit this clause, then the database drops both the body and specification of the package. Specify the schema containing the package. If you omit schema, then the database assumes the package is in your own schema.

DROP PACKAGE SYSADMIN_VPD.PRO_pkg;

DROP PACKAGE BODY SYSADMIN_VPD.PRO_PKG - it will drop only package body

4.11 Drop trigger

DROP TRIGGER SYSADMIN_VPD.PRO_trig;

4.12 Drop Context

Drop the context in the SYS login session not in sysadmin_vpd session it will gives error.

DROP CONTEXT PRO_ctx;

Drop trigger, package, context

Unit Summary

This unit described purpose of virtual private database, three important aspects of virtual private database is row level security (RLS), column masking and application context.

Practical Exercise

Exercise 1

Create two table Technician and Lab table in scott schema

Tno	Tname	Specialized
111	INDUJA	ORACEL
112	LAKSHMI	.NET
113	AMBU	NETWORK

create table technician(tno number(5),tname varchar2(20),specialized varchar2(25));

insert into technician values(111,'INDUJA','ORACLE');

insert into technician values(112,'LAKSHMI','.NET');

insert into technician values(113,'AMBU','.NETWORK');

COMMIT;

[Hint: Tname should be in capital letters. i.e. “INDUJA”]

Lab Table

Labno	Tno	Subject	No_of_hours
222	111	Data warehousing	4
231	112	Asp.net	4
234	111	Database Administration	4
218	112	Visual basic .net	5
425	113	Network administration	5

CREATE TABLE LAB(LABNO NUMBER(5),TNO NUMBER(5),SUBJECT VARCHAR2(30),NO_OF_HOuRS NUMBER(5));

INSERT INTO LAB VALUES(222,111,'Data warehousing',4);

insert into lab values(231,112,'ASP.NET',4);

INSERT INTO LAB VALUES(234,111,'Database Administration',4);

insert into lab values(218,112,'Visual Basic .net',5);

insert into lab values(425,113,'Network Administration',5);

COMMIT;

Create function “Get_lab” using condition as it will display only tno = 111. Login sys and create policy “Lab_policy” use object name as “lab table”. Login scott, display lab table. Drop Policy "Lab_policy" and drop function "Get_lab".

----------------------------------------------------------------------------------------------------------

Exercise 2- Apply column Masking

Create function “Get_lab” using condition as it will display only tno = 111. Create policy “Lab_cmask_policy” , in the policy apply column masking for subject column. use object name as “lab table”. Login HR, display all the rows of lab table. Drop Policy "Lab_cmask_policy" and drop function "Get_lab".

---------------------------------------------------------------------------------------------------------

Questions

1. What is the purpose of SYS_CONTEXT function?

2. Explain Difference between Row level security and column masking?

3. Distinguish SEC_RELEVANT_COLS_opt and SEC_RELEVANT_COLS?

Video Tutorial Link

There are 3 youtube tutoiral are related to this unit are listed below

Youtube tutorial for Row Level security

youtube tutorial for Column Masking

Application Context

Lecture Notes

Lecture Notes: Virtual Private Database

References

[1] https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1855489000346413353

[2] http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm

Return to top

Learning Goals

Database

Unit Objective

4.1 Introduction

4.2 Components of an Oracle Virtual Private Database Policy

4.2.1. Create Function

4.3 Creating a Policy to Attach the Function to the Objects

4.4 Drop Function

4.5 Drop Policy

4.6 Column Masking

4.7 SYS_CONTEXT

4.8 Oracle Virtual Private Database Policy Types

4.9 Application Context

4.10 Drop package

4.11 Drop trigger

4.12 Drop Context

Unit Summary

Practical Exercise

Questions

Video Tutorial Link

Lecture Notes

References

Standards