Monday, September 1, 2008

Column-Level VPD

Column-level VPDs will display nulls in the specified columns based on the user's login IDs. This applies only to queries.

--+--------------------------------------------------------------------------+
--| Clean up everything not dropped by cascade so no errors are posted.
--+--------------------------------------------------------------------------+

Connect System/System As Sysdba
Drop Public Synonym Emp;
Drop Public Synonym VPDC_Security_Pkg;
Drop Public Synonym VPDC_Context_Pkg;
Drop Trigger VPDCTest.Security_Trigger;
Drop Package VPDCTest.VPDC_Context_Pkg;
Drop Package VPDCTest.VPDC_Security_Pkg;
Drop Context VPDCTest;

Drop User VPDCTest Cascade;
Drop User Johnson Cascade;
Drop User Tobias Cascade;

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

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

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

Conn VPDCTest/VPDCTest

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 (7566,'JONES','MANAGER',7839,'2-APR-1981',2975,NULL,20);
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 (7876,'ADAMS','CLERK',7788,'12-JAN-1983',1100,NULL,20);
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;

--+--------------------------------------------------------------------------+
--| Johnson and Tobias can select from the transaction table.
--| The logon trigger will select from the Mgr_Logon.
--+--------------------------------------------------------------------------+

Grant Select 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 VPDCTest
Using VPDCTest.VPDC_Context_Pkg;

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

Create Or Replace Package
VPDC_Context_Pkg
As
Procedure Set_Context;
End;
/
Create Or Replace Package Body
VPDC_Context_Pkg
Is
Procedure Set_Context Is
v_MgrName Varchar2(30);
v_DeptNo Number;
Begin
--+--------------------------------------------------------------------+
--| VPDCTest is the namespace(?) of the application context.
--| SETUP is set to True by Set_context
--+--------------------------------------------------------------------+
Dbms_Session.Set_Context('VPDCTEST','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;
--+--------------------------------------------------------------------+
--| VPDCTest is the namespace(?) of the application context.
--| USER_ID is set equal to the value of v_DeptNo
--+--------------------------------------------------------------------+
Dbms_Session.Set_Context('VPDCTEST','USER_ID',v_DeptNo);
Exception
When No_Data_Found Then
Dbms_Session.Set_Context('VPDCTEST','USER_ID',0);
End;
--+--------------------------------------------------------------------+
Dbms_Session.Set_Context('VPDCTEST','SETUP','FALSE');
End Set_Context;
End VPDC_Context_Pkg;
/
show errors
--+--------------------------------------------------------------------------+
--| Allow anyone to execute this as 'VPDC_Context_Pkg'
--+--------------------------------------------------------------------------+
Grant Execute On VPDCTest.VPDC_Context_Pkg To Public;
Create Public Synonym VPDC_Context_Pkg For VPDCTest.Context_Package;

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

Create Or Replace Trigger
VPDCTest.Security_Trigger
After Logon On Database
Begin
VPDCTest.VPDC_Context_Pkg.Set_Context;
End;
/

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

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

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

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

Create Or Replace Package Body VPDC_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 'VPDCTEST' 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') = 'VPDCTEST')
Then Predicate := Null;
Else Predicate := 'DeptNo = SYS_CONTEXT(''VPDCTEST'',''USER_ID'')';
End If;

Return Predicate;
End Select_Security_Function;

End VPDC_Security_Pkg;
/
Show Errors
--+--------------------------------------------------------------------------+
--| Allow anyone to execute this package as 'VPDC_Security_Pkg'
--+--------------------------------------------------------------------------+
Grant Execute On VPDCTest.VPDC_Security_Pkg To Public;
Create Public Synonym VPDC_Security_Pkg For VPDCTest.VPDC_Security_Pkg;

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

--+--------------------------------------------------------------------------+
--| Add a policy for selecting
--+--------------------------------------------------------------------------+

Conn VPDCTest/VPDCTest

Begin
Dbms_Rls.Add_Policy
( object_schema => 'VPDCTEST',
object_name => 'EMP',
policy_name => 'EMP_SELECT_POLICY',
function_schema => 'VPDCTEST',
policy_function => 'VPDC_SECURITY_PKG.SELECT_SECURITY_FUNCTION',
sec_relevant_cols => 'SAL,HIREDATE',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS );
end;
/
--+--------------------------------------------------------------------------+
--| Connect as Johnson and Tobias and see what gets selected.
--+--------------------------------------------------------------------------+

Conn System/System as Sysdba
Create Public Synonym EMP For VPDCTest.Emp;

Conn Johnson/Sam
Select * From Emp Order by Deptno;

Conn Tobias/Lynn
Select * From Emp Order by Deptno;

No comments:

Post a Comment