Sunday, August 12, 2007

Virtual Private Database (VPD) with Oracle Apps

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

9 comments:

Anonymous said...

hi ur post on VPD is extremely useful... we need to implement VPD in our company running with DB : 9i R2 and oracle 11i (11.5.10.2)

do we need to install any thing to make use of VPD or do any configuration...or is it just ok to follow the steps ( like create policy etc ) ... pls suggest..
ur reply will be highly useful

regards,
senthil

Aviad said...

Hi senthil,

You don't need to install or configure anything to implement VPD.
You can follow my post exactly and it will work.

Good luck!
Aviad

Anonymous said...

hi i tried executing the example what u gave but even after executing " Set the current session to my APPS user and execute the Set_Context procedure"

still im not getting zero for the query select count(*) from fnd_concurrent_requests where status_code='E';


and after "If we change the session owner:" step im getting the same result ...

pls help me regarding this ... there is a demo in my company reg this and ur help will be extremely useful :)


regards,
senthil

Aviad said...

Hi senthil,

Please open a new sql session, connect with apps and execute the folowing:

select fnd_profile.VALUE('XX_HIDE_ERROR_CONCURRENTS') from dual;
/
select fnd_profile.VALUE_SPECIFIC('XX_HIDE_ERROR_CONCURRENTS',[PUT_USER_ID]) from dual;
/
(where PUT_USER_ID is the user who are not authorized to see ERROR requests)

The 1st one should return 'N' and the 2nd 'Y'.

Anonymous said...

Hi Aviad,

select fnd_profile.VALUE('XX_HIDE_ERROR_CONCURRENTS') from dual;


is showing no value ...

SQL> select fnd_profile.VALUE('XX_HIDE_ERROR_CONCURRENTS') from dual;


FND_PROFILE.VALUE('XX_HIDE_ERROR_CONCURRENTS')
--------------------------------------------------------------------------------


i tried to figure out whether fnd_profile.save is working fine .. and here is the code and output ..


SQL> set serveroutput on
SQL> declare
status boolean;
begin
status := FND_PROFILE.SAVE('XX_HIDE_ERROR_CONCURRENTS', 'N', 'SITE');
if status = TRUE then
dbms_output.put_line('success 1st intialization');
else
dbms_output.put_line('some error occured in 1st intialization');
2 3 4 5 6 7 8 9 end if;
10 end;
11 /
commit;
some error occured in 1st intialization

PL/SQL procedure successfully completed.


-> so intialization is failing ..

do u have any idea y its failing ..?

pls help

regards,
senthil

Aviad said...

Maybe you didn't create the profile first?
Before setting a value to the profile XX_HIDE_ERROR_CONCURRENTS you should create it.
Log on to Applications -> Application Developer responsibility -> Create Profile -> User Profile Name should be XX_HIDE_ERROR_CONCURRENTS.

Aviad

Unknown said...

Hi,

Could you please tell me how to restrict the VPD from the application?

Thanks in Advance.

Unknown said...

Hi,

Could you please tell me how to restrict the VPD from the application?

Thanks in Advance.

Anonymous said...

hi .. thanks for this great post ..
can you advise us with the steps required to add (begin XX_context_pkg.Set_Context; end;)
to custom.pll ...
thanks :)