Oracle Database Vault and Peoplesoft

If you want to secure and close down your database to all the non-authorized people, especially not seeing the data inside the database, what’s better than Database Vault ? Nothing.
And this is more and more right, especially on financial database or even on HR system hosting the salary and a lot of confidential information about the employees of the company. So, what’s refrain to use Database Vault ? Nothing. Almost nothing. Till 10gR2, Database Vault was an additional option to download and install onto the Oracle home directory, from 11g, it is included in the standard package, no need anymore additional software install.
For Peoplesoft however, you’ll need an additional component dedicated to Peoplesoft to create all the required policies.

Installation
I’m not going to describe all the installation steps, but there are several constraints when installing Database Vault, have a dedicated Oracle home, have EM Console, have one database (declared in /etc/oratab) to name few (note, at least in 10gR2 ASM is not supported by DBVault), but you can find everything in the well documented link.

Implementation
Once the Database Vault has been installed, you’ll need to apply it onto the database, following the installation through the dvca (Database Vault Creation Assistant).
Some new users are created to manage the database vault policies, owner and manager (you can decide to have only the owner). Only them can allow other users to do something on the database.

Peoplesoft package
Once your database has been “vaulted”, you have to install the dedicated Peoplesoft packages pointed above in the download page.

Maintenance
Whether the installation process might fail for several reasons, and it is not always easy to figure out why (the messages are not really clear), as long as you read very carefully the document, this is rather simple. As soon has the database is vaulted and Peoplesoft packages installed, no one else than SYSADM (the Peoplesoft objects’ owner) can see the data (even SYS/SYSTEM are “blind”). And more than that, SYSADM cannot connect through SQL*Plus (or any other non-Peoplesoft tools) to see data. Happy ? Well, yes and no.
In the documentation coming within the Peoplesoft package, it is clearly explained that a new user – PSFTDBA – has been created and authorized to do all the maintenance tasks in place of SYSADM through SQL*Plus for instance.
PSFTDBA can create/drop/alter objects in SYSADM’s with the ALTER SESSION SET SCHEMA=SYSADM. But again to be respectful to the vault policies, PSFTDBA is not able to select data from SYSADM. Very easy.

But for who know the Peoplesoft project build, that is not so simple. Most of the time, the “project build” generate script within CTAS (Create Table As Select) which obviously cannot work in that database vault context. So what ?
The only one known workaround is to ask to the database vault owner (or manager) to deactivate the PSFTDBA restriction, he can then select data from SYSADM, run the script and reactivate the restriction… It is rather against the rules, isn’t it ? If we have to disable the vault to apply project, nothing refrain to see data anymore.

I’d be curious if someone has already implemented Database Vault on a Peoplesoft database and how they are managing it. Feel free to comment out.

Enjoy it,

Nicolas.