On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Monday, September 1, 2008

Virtual Private Database

A Virtual Private Database attaches security policies to objects so there is no way to bypass it.
"Fine Grained Access": Queries are dynamically modified to include a limiting where clause.
See Chapter 19 10g Reference for more information
Set Echo On Feedback On
--+--------------------------------------------------------------------------+
--| Clean up everything not dropped by cascade so no errors are posted.
--+--------------------------------------------------------------------------+

Connect System/System As Sysdba
Drop Public Synonym VPD_Security_Pkg;
Drop Public Synonym VPD_Context_Pkg;
Drop Trigger VPDTest.Security_Trigger;
Drop Package VPDTest.VPD_Context_Pkg;
Drop Package VPDTest.VPD_Security_Pkg;
Drop Context VPDTest;

Drop User VPDTest Cascade;
Drop User Johnson Cascade;
Drop User Tobias Cascade;

--+--------------------------------------------------------------------------+
--| Create two users and the VPDTest schema.
--+--------------------------------------------------------------------------+
Create User Johnson Identified By Sam;
Create User Tobias Identified By Lynn;
Create User VPDTest Identified By VPDTest
Default Tablespace Users
Temporary Tablespace Temp
Quota 2m On Users;

Grant Create Session To Tobias, Johnson, VPDTest;
Grant Create Table,
Create Procedure,
Create Any Context,
Create Public Synonym To VPDTest;

--+--------------------------------------------------------------------------+
--| Connect as VPDTest and create the table used for logon validation
--+--------------------------------------------------------------------------+

Conn VPDTest/VPDTest

Create Table Mgr_Logon
( DeptNo Number(10)
, MgrName Varchar2(50)
);
Insert Into Mgr_Logon Values (10,'JOHNSON');
Insert Into Mgr_Logon Values (20,'TOBIAS');

Select * From Mgr_Logon;

--+--------------------------------------------------------------------------+
--| Create the table that they want to read from.
--+--------------------------------------------------------------------------+

Drop Table Emp Cascade Constraints;
Create Table Emp (
Empno Number(4) Not Null,
Ename Varchar2(10),
Job Varchar2(9),
Mgr Number(4) ,
Hiredate Date,
Sal Number(7,2),
Comm Number(7,2),
Deptno Number(2) Not Null,
Constraint Emp_Empno_Pk Primary Key (Empno));

Insert Into Emp Values (7369,'SMITH','CLERK',7902,'17-DEC-1980',800,NULL,20);
Insert Into Emp Values (7499,'ALLEN','SALESMAN',7698,'20-FEB-1981',1600,300,30);
Insert Into Emp Values (7521,'WARD','SALESMAN',7698,'22-FEB-1981',1250,500,30);
Insert Into Emp Values (7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);
Insert Into Emp Values (7654,'MARTIN','SALESMAN',7698,'28-SEP-1981',1250,1400,30);
Insert Into Emp Values (7698,'BLAKE','MANAGER',7839,'1-MAY-1981',2850,NULL,30);
Insert Into Emp Values (7782,'CLARK','MANAGER',7839,'9-JUN-1981',2450,NULL,10);
Insert Into Emp Values (7788,'SCOTT','ANALYST',7566,'09-DEC-1982',3000,NULL,20);
Insert Into Emp Values (7839,'KING','PRESIDENT',NULL,'17-NOV-1981',5000,NULL,10);
Insert Into Emp Values (7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);
Insert Into Emp Values (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);
Insert Into Emp Values (7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);
Insert Into Emp Values (7902,'FORD','ANALYST',7566,'3-DEC-1981',3000,NULL,20);
Insert Into Emp Values (7934,'MILLER','CLERK',7782,'23-JAN-1982',1300,NULL,10);

Commit;

Select * From Emp;

Create Public Synonym EMP For Emp;
--+--------------------------------------------------------------------------+
--| Johnson and Tobias can select and insert from the transaction table.
--| The logon trigger will select from the Mgr_Logon.
--+--------------------------------------------------------------------------+

Grant Select, -
Insert On Emp To Johnson, -
Tobias;
Grant Select On Mgr_Logon To Johnson, Tobias;

--+--------------------------------------------------------------------------+
--| Create An Application Context: a set of application-defined attributes
--| that validates and secures an application
--| Attributes in context can only be changed in the package.
--+--------------------------------------------------------------------------+

Create Context VPDTest
Using VPDTest.VPD_Context_Pkg;

--+--------------------------------------------------------------------------+
--| This procedure gets the value to see
--+--------------------------------------------------------------------------+

Create Or Replace Package
VPD_Context_Pkg
As
Procedure Set_Context;
End;
/
Create Or Replace Package Body
VPD_Context_Pkg
Is
Procedure Set_Context Is
v_MgrName Varchar2(30);
v_DeptNo Number;
Begin
--+--------------------------------------------------------------------+
--| VPDTest is the namespace(?) of the application context.
--| SETUP is set to True by Set_context
--+--------------------------------------------------------------------+
Dbms_Session.Set_Context('VPDTEST','SETUP','TRUE');

--+--------------------------------------------------------------------+
--| USERENV: the namespace that describes the current session.
--| SESSION_USER: username by which the current user is authenticated.
--+--------------------------------------------------------------------+
v_MgrName := Sys_Context('USERENV','SESSION_USER');

--+--------------------------------------------------------------------+
--| Put DeptNo # into v_DeptNo based on last Name
--|
--| DeptNo MgrName
--| ------ ----------------
--| 10 JOHNSON
--| 20 TOBIAS
--+--------------------------------------------------------------------+
Begin
Select DeptNo
Into v_DeptNo
From Mgr_Logon
Where MgrName = v_MgrName;
--+--------------------------------------------------------------------+
--| VPDTest is the namespace(?) of the application context.
--| USER_ID is set equal to the value of v_DeptNo
--+--------------------------------------------------------------------+
Dbms_Session.Set_Context('VPDTEST','USER_ID',v_DeptNo);
Exception
When No_Data_Found Then
Dbms_Session.Set_Context('VPDTEST','USER_ID',0);
End;
--+--------------------------------------------------------------------+
Dbms_Session.Set_Context('VPDTEST','SETUP','FALSE');
End Set_Context;
End VPD_Context_Pkg;
/
show errors
--+--------------------------------------------------------------------------+
--| Allow anyone to execute this as 'VPD_Context_Pkg'
--+--------------------------------------------------------------------------+
Grant Execute On VPDTest.VPD_Context_Pkg To Public;
Create Public Synonym VPD_Context_Pkg For VPDTest.Context_Package;

--+--------------------------------------------------------------------------+
--| Create a logon trigger
--+--------------------------------------------------------------------------+
Connect System/System As Sysdba

Create Or Replace Trigger
VPDTest.Security_Trigger
After Logon On Database
Begin
VPDTest.VPD_Context_Pkg.Set_Context;
End;
/

--+--------------------------------------------------------------------------+
--| Test the context settings
--+--------------------------------------------------------------------------+
Connect Johnson/Sam

Select Sys_Context('USERENV','SESSION_USER') Username,
Sys_Context('VPDTEST','USER_ID') ID
From Dual;

--+--------------------------------------------------------------------------+
--| Create a security policy
--+--------------------------------------------------------------------------+
Connect VPDTest/VPDTest

Create Or Replace Package VPD_Security_Pkg As
Function Insert_Security_Function
(Owner Varchar2, ObjName Varchar2)
Return Varchar2;
Function Select_Security_Function
(Owner Varchar2, ObjName Varchar2)
Return Varchar2;
End VPD_Security_Pkg;
/

Create Or Replace Package Body VPD_Security_Pkg Is
--+-----------------------------------------------------------------------+
--| 'Predicate' is the condition in the Where clause
--+-----------------------------------------------------------------------+
Function Select_Security_Function
(Owner Varchar2, ObjName Varchar2)
Return Varchar2 Is
Predicate Varchar2(2000);
--+-----------------------------------------------------------------+
--| Set to an invalid condition
--| If the User is 'VPDTEST' return Null.
--| If not, look for DeptNo to equal the user_id acquired here:
--| Select DeptNo Into v_DeptNo From Mgr_Logon Where MgrName = v_MgrName;
--+-----------------------------------------------------------------+
Begin
Predicate := '1=2';

If (Sys_Context('USERENV','SESSION_USER') = 'VPDTEST')
Then Predicate := Null;
Else Predicate := 'DeptNo = SYS_CONTEXT(''VPDTEST'',''USER_ID'')';
End If;

Return Predicate;
End Select_Security_Function;

--+-----------------------------------------------------------------------+
--| Do the same for the Insert option
--+-----------------------------------------------------------------------+
Function Insert_Security_Function
(Owner Varchar2, ObjName Varchar2)
Return Varchar2 Is
Predicate Varchar2(2000);
--+-----------------------------------------------------------------+
Begin
Predicate := '1=2';

If (Sys_Context('USERENV','SESSION_USER') = 'VPDTEST')
Then Predicate := Null;
Else Predicate := 'DeptNo = SYS_CONTEXT(''VPDTEST'',''USER_ID'')';
End If;

Return Predicate;
End Insert_Security_Function;
End VPD_Security_Pkg;
/
Show Errors
--+--------------------------------------------------------------------------+
--| Allow anyone to execute this package as 'VPD_Security_Pkg'
--+--------------------------------------------------------------------------+
Grant Execute On VPDTest.VPD_Security_Pkg To Public;
Create Public Synonym VPD_Security_Pkg For VPDTest.VPD_Security_Pkg;

--+--------------------------------------------------------------------------+
--| dbms_rls package (RLS stands for row-level security)
--+--------------------------------------------------------------------------+
Conn System/System as Sysdba
Grant Execute On DBMS_RLS To Public;

--+--------------------------------------------------------------------------+
--| Add two policies: one for insert and one for select
--+--------------------------------------------------------------------------+

Conn VPDTest/VPDTest

Begin
Dbms_Rls.Add_Policy
( object_schema => 'VPDTEST',
objecT_NAME => 'EMP',
POLICY_NAME => 'EMP_SELECT_POLICY',
FUNCTion_schema => 'VPDTEST',
policY_FUNCTION => 'VPD_SECURITY_PKG.SELECT_SECURITY_FUNCTION',
statement_types => 'SELECT' ,
update_check => TRUE );
--+--------------------------------------------------------------+
--| An ORA-28113 will generate a trace file. Search for .trc file
--+--------------------------------------------------------------+
dbms_rls.add_policy
( object_schema => 'VPDTEST',
OBJECT_NAME => 'EMP',
POLICY_NAME => 'EMP_INSERT_POLICY',
FUNCTION_SCHEMA => 'VPDTEST',
POLICY_FUNCTION => 'VPD_SECURITY_PKG.INSERT_SECURITY_FUNCTION',
STATEMENT_TYPES => 'INSERT' ,
update_check => TRUE );
end;
/
--+--------------------------------------------------------------------------+
--| Connect as Johnson and Tobias and see what gets selected.
--+--------------------------------------------------------------------------+

Conn Johnson/Sam
Select * From Emp;

Conn Tobias/Lynn
Select * From Emp;

--+--------------------------------------------------------------------------+
--| The first insert should post an error as Sam has no right to insert dept 20
--+--------------------------------------------------------------------------+

CONN Johnson/Sam
Insert Into Emp Values (9876,'MICKEY','ANALYST',2323,'09-DEC-1992',3500,NULL,20);
Insert Into Emp Values (8765,'MINNIE','ANALYST',4521,'09-DEC-1992',3000,NULL,10);

No comments:

Post a Comment