Re: Anyone using VPD and Forms for security?

Timbo <tjbacs_nospam_at_attbi.com> wrote in message news:<3DD519D1.2070002_at_attbi.com>...
> Hi,
>
> Using 9iR2 database, app server, developer suite.
>
> We are developing an application that has approx 200 users,
> and will grow. Utilizing Forms and Reports for bulk of
> development. The users have different functions they will
> perform in the system. All inserts/updates will be stamped
> with the user's id.
>
> We are considering 2 security paths:
>
> 1. A user login form that validates off a users table. Once
> validated, the userid and items are placed as global variables
> and referenced by the other forms. No need for individual
> database users, as the user that forms and the app server uses
> to connect to the db will be used. So one user, one db schema.
>
>
> 2. Virtual Private Database - VPD From the little I understand of
> this, each user of the application will be a database user. This will
> require more maintainance on the db side to have the users access the
> same schema.
>
> My questions are:
>
> What are others doing?
>
> Does VPD integrate with Forms and Reports relatively seamlessly?
>
> Which would be easier to maintain?
>
> Thanks for any guidance.
>
> Tim

Tim, I think the concept of VPD is to build security into the objects, instead of the apps. You will need to change the schema but it is an one time job and it works for all applications, including sqlplus access. You may or may not need to change (minor) your apps, depending on how it is designed to begin with.

There is little maintenance once it is built. There is definitely zero maintenance in the apps, unless you changed your security policy.

VPDs are sometimes used by ASPs who put all customers data in one schema set and impose security by a column carrying something like a company_id. This company_id is mapped at user log in. This company_id mapping is not performed by ASP's application, instead it is performed by VPD. Therefore at code development, no security issue needs to be considered. This also eliminate the possibility of security bugs in the code.

If someone enters: select salary from emp; what Oracle actually parses is: select salary from emp where emp_id = your_id; but if you query v$sql, you will not see the appended filter and you certainly won't see them in tkprof either. This filter is auto suffixed to every single SQL against that table.

I have never used VPD on forms and reports but I am sure it works because basically it is just an added filter to "all" sql access, be it a form or report, it should not make any difference.

As to your last question. I'd say if your security concern is only in one area, then maintaining it in apps is probably easier. But if your concern is across many tables and across all apps, including dba access thru sqlplus, then VPD is a better choice. That's why VPD is a good security option for application service providers.

Hope this helps. Received on Fri Nov 15 2002 - 16:08:10 CST