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