"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);
Posts
No comments:
Post a Comment