Fine Grained Access Control in Oracle 10g

Fine-Grained Access is also known as a virtual private database (VPD) because it implements row-level security, essentially giving user access to his or her own private database. Fine-grained means at the individual row level.

SYS_CONTEXT

SELECT sys_context('', '');
FROM dual;

System defined:
Eg:
Attribute_Name Attribute_Value
USERENV IP_ADDRESS
USERENV SESSION_USER
USERENV CURRENT_SCHEMA
USERENV DB_NAME
USERENV TERMINAL

To View:
SELECT sys_context(‘USER_ENV’,’IP_ADDRESS’)
FROM dual;

Create your own context by

CREATE OR REPLACE CONTEXT name_of_context
USING package_name;

Eg:
CREATE CONTEXT my_context
USING my_pack;

• At the time of the creation of the context, it is not necessary to exist the package.
• You should have the CREATE ANY CONTEXTprivilege.
• To view your context use, dba_context



Set the context using

DBMS_SESSION.SET_CONTEXT(‘context_name’,’attribute_name’,’attribute_value’);

Note: This has to be in a package body.

CREATE OR REPLACE PACKAGE my_pack
IS

procedure set_application_context;

END;

CREATE OR REPLACE PACKAGE BODY my_pack
IS

procedure set_application_context
IS
BEGIN
DBMS_SESSION.SET_CONTEXT('my_context','name','GR10’);
END;

END;

You may get “ORA-01031: insufficient privileges” error while running the above.
You should:
SQL> conn sys as sysdba
SQL> grant execute on dbms_session to imran;


And log in as

SQL> CONN imran/imran@orcl
SQL> execute my_pack.set_application_context;

SQL> SELECT sys_context(‘my_context’,’name’)
FROM dual;

SYS_CONTEXT('MY_CONTEXT','NAME')
-------------------------------------------
GR10

Real Scenario of implementing Fine Grained Access

Steps to be followed:
1. Set up a driving context.
2. Create the package associated with the context you defined in the step1. In the package:
a. Set the context.
b. Define the predicate.
3. Define the policy.
4. Set up a logon trigger to call the package at logon time and set the context.
5. Test the policy.



1. Set up a driving context.

CREATE CONTEXT user_group_context
USING emp_group_pack;

2. Create the package

CREATE OR REPLACE PACKAGE emp_group_pack
IS

procedure show_app_context;
procedure set_application_context;
Function the_predicate (p_schema Varchar2,p_name Varchar2)
Return varchar2;
END;



CREATE OR REPLACE PACKAGE BODY IMRAN.emp_group_pack
IS
vc_context_name CONSTANT varchar2(30) := 'USER_GROUP_CONTEXT';
vc_attribute_name CONSTANT varchar2(30) := 'GROUP_ID';

procedure set_application_context
IS
vc_user VARCHAR2(50);
BEGIN
SELECT user INTO vc_user
FROM dual;

DBMS_SESSION.SET_CONTEXT(vc_context_name,vc_attribute_name,user);
END;

procedure show_app_context
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(' Context Name:'||vc_context_name||' Context attribute:'||vc_attribute_name||
' Attribute Value:'||sys_context(vc_context_name,vc_attribute_name));

END;


Function the_predicate (p_schema Varchar2,p_name Varchar2)
Return varchar2
IS
vc_restriction VARCHAR2(100);
vc_context Varchar2(30) := SYS_CONTEXT(vc_context_name,vc_attribute_name);
BEGIN

IF vc_context like 'USER%' THEN
vc_restriction := ' GROUP_ID = '||SUBSTR(vc_context,5);
END IF;

RETURN vc_restriction;

END;

END;
/



3. Define the policy.

DBMS_RLS.ADD_POLICY( Object_schema IN VARCHAR2 := NULL,
Object_name IN VARCHAR2,
Policy_Name IN VARCHAR2,
Function_Schema IN VARCHAR2,
Policy_Function IN VARCHAR2,
Statement_Types IN VARCHAR2 := NULL,
Update_check IN BOOLEAN := FALSE,
Enable IN BOOLEAN := TRUE );


SQL> conn sys as sysdba
Enter password: ***
Connected.
SQL> BEGIN
2 DBMS_RLS.ADD_POLICY
3 ( 'IMRAN',
4 'EMP',
5 'GROUP_ID_POLICY',
6 'IMRAN',
7 'EMP_GROUP_PACK.THE_PREDICATE',
8 'SELECT,UPDATE,DELETE',
9 TRUE,
10 TRUE);
11 END;
12 /

To Drop the policy:

BEGIN
DBMS_RLS.DROP_POLICY
( 'IMRAN',
'EMP',
'GROUP_ID_POLICY'
);
END;


SQL> create user USER3
2 identified by USER3;

User created.

SQL> grant connect,resource to user3;

Grant succeeded.


SQL> conn sys/sys as sysdba
Connected.
SQL> create or replace trigger set_id_on_logon
2 after logon on database
3 BEGIN
4 imran.EMP_GROUP_PACK.set_application_context;
5 END;
6 /

Trigger created.

SQL> conn user1/user1

SQL> select *
2 from imran.emp;

EMP_ID NAME SAL GROUP_ID
---------- ------------------------------ ---------- ----------
200 aaa 5000 1
100 King 24000 1
101 Dehan 17000 1
110 Stepens 20500 1