Virtual Private Database (VPD) is an Oracle database security technology, enabling row level access control.
With VPD we are able to control users access to data related to them only and preventing from them to access data they are not authorized to see.
For additional information about VPD take a look at Virtual Private Database in Oracle8i and Oracle9i.
VPD can be used with Oracle Applications 11i, and I'm going to show a very simple example.
So let's assume, for example purpose only, that we would like to prevent some users to view requests that finished with status "ERROR".
The steps to implement VPD are:
1) Create a profile to control which users can see error requests
2) Set profile value to 'N' at site level.
2) Create new context
3) Create context package
4) Create policy package
5) Add policy to concurrent request table
Create profile
To control which users will be restricted and which are not, create a new profile, name - XX_HIDE_ERROR_CONCURRENTS.
Set value at site level to 'N' and for specific user to 'Y':
declare
status boolean;
begin
status := fnd_profile.SAVE(X_NAME => 'XX_HIDE_ERROR_CONCURRENTS'
,X_VALUE => 'N'
,X_LEVEL_NAME => 'SITE');
status := fnd_profile.SAVE(X_NAME => 'XX_HIDE_ERROR_CONCURRENTS'
,X_VALUE => 'Y'
,X_LEVEL_NAME => 'USER'
,X_LEVEL_VALUE => '10083');
end;
/
commit;
Create new context:
CREATE CONTEXT XX_APPS_CONTEXT USING APPS.XX_CONTEXT_PKG;
Create context package:
Create the package used by the new context.
This package will set the context for users according to the user level profile value.
CREATE OR REPLACE PACKAGE XX_CONTEXT_PKG AS
PROCEDURE Set_Context;
END;
/
CREATE OR REPLACE PACKAGE BODY XX_CONTEXT_PKG IS
PROCEDURE Set_Context IS
BEGIN
DBMS_SESSION.Set_Context('XX_APPS_CONTEXT', 'HIDE_ERROR_CONCURRENTS', fnd_profile.VALUE('XX_HIDE_ERROR_CONCURRENTS'));
END Set_Context;
END;
/
Create policy package
Now, we need to create a policy package.
In this package we determine the policy for accessing data, according to the user context we determine how to access the table.
CREATE OR REPLACE PACKAGE XX_POLICY_PACKAGE AS
FUNCTION Select_Concurrent_Requests (Owner VARCHAR2, Objname VARCHAR2)
RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY XX_POLICY_PACKAGE IS
FUNCTION Select_Concurrent_Requests (Owner VARCHAR2, Objname VARCHAR2) RETURN VARCHAR2 IS
predicate VARCHAR2(500);
BEGIN
predicate := null;
if (sys_context('XX_APPS_CONTEXT','HIDE_ERROR_CONCURRENTS') = 'Y') then
predicate := 'STATUS_CODE != ''E''';
end if;
RETURN predicate;
END Select_Concurrent_Requests;
END;
/
Add policy to concurrent request table
Applying the policy on the relevant table.
begin
dbms_rls.add_policy('APPS'
,'FND_CONCURRENT_REQUESTS'
,'XX_SELECT_CONC_REQ_POLICY'
,'APPS'
,'XX_POLICY_PACKAGE.Select_Concurrent_Requests'
,'SELECT');
end;
/
From now on, each access to this table will involve context value check and the data is secured.
Let's check if it's work...
Set the current session to my APPS user and execute the Set_Context procedure.
begin fnd_global.apps_initialize(10083, 0, 0); end;
/
begin XX_context_pkg.Set_Context; end;
/
Query all requests that finished with error status should return 0 rows:
select count(*) from fnd_concurrent_requests where status_code='E';
COUNT(*)
----------
0
If we change the session owner:
begin fnd_global.apps_initialize(10084, 0, 0); end;
/
begin XX_context_pkg.Set_Context; end;
/
And execute the same query as before:
select count(*) from fnd_concurrent_requests where status_code='E';
COUNT(*)
----------
157
To drop the policy from the table issue this command:
begin
dbms_rls.drop_policy('APPS'
,'FND_CONCURRENT_REQUESTS'
,'XX_SELECT_CONC_REQ_POLICY');
end;
/
VPD & Oracle Apps
To set the context for each APPS user connect to the system, we can insert into CUSTOM.pll this line only:
begin XX_context_pkg.Set_Context; end;
For Each user connecting to Oracle Apps Forms, the context will be set and security will be on.
This is on VPD & Oracle Apps in a nutshell, The solution is very depents on specific requirements of security but this is a good starting point.
For any questions, impressions or additional information you are welcome to leave a comment.
Aviad |
