Added to NDS Applications, version 7.5.2
The virtual private database is an alternative database administration option. The following instructions explain the setup procedure that must be followed in order to implement the virtual private database. The database administrator must already have a good understanding of Oracle's Virtual Private Database features and principles before setting up the virtual private database for NDS Applications.
When migrating existing data to use the virtual private database, it is recommended that the user contact NDS Professional Services for consultation on the scope and challenges of the migration.
The multi-client fulfillment capabilities of NDS can be implemented using Oracle's Row-Level Security/Virtual Private Database (VPD) capabilities available in Oracle Enterprise Server. This eliminates the need to maintain separate database schemas for each client, with the attendant costs of administration.
Implementation of the virtual private database is instance wide. A separate schema is used to store and control the access for all other schemas in the instance.
While configuring and using the virtual private database is fairly straightforward, migrating a separate-schema fulfillment system to a single schema presents some challenges. Careful treatment of the data is necessary to prevent data loss during the migration. It is recommended that the user contact NDS Professional Services for consultation before attempting the migration.
1. A master application database must be prepared, optionally with merged client data.
2. A database user is required to contain the security context table and procedure code. The space requirements for this user are very small. Program UTCC, Master Context Maintenance, is used to maintain the context table, to allow this the security user may be set up as a synonym user pointing to tables in the 'master' schema. No log in should be created as program UTCC, Master Context Maintenance, is to be run standalone.
3. Using program MUNU, Maintain Oracle Users, create grants and synonyms for the security user and application database. This allows the security user to run NDS forms such as program UTCC, Master Context Maintenance.
4. The table NDS_CONTEXT_CONTROL needs to be created under the security schema using the script MAKECTXT.SQL. The master user should be specified for user, application user specified for schema and tenant id.
5. SELECT must be granted to PUBLIC on NDS_CONTEXT_CONTROL in the security schema.
6. NDS_CONTEXT_CONTROL needs to be dropped from each database to be retained as a master database. A synonym pointing to the security user's nds_context_control should be created in its place.
7. Create the package PROC_CTX in the security schema by running proc_ctx.sql.
8. Connected as system, create the necessary context, policy procedure, and login trigger by running the script nds_ctx_sys.sql. Supply the security username when prompted.
9. Connected as system, create policies for the application tables by running the script nds_ctx_newpolicy.sql. Supply the application username when prompted.
10. Using program UTCC, Master Context Maintenance, connected as the security user, create context entries for each client database user. The application username should be supplied as the schema, with client username specified as user and tenant id.
11. Any problems with the System or security user objects will likely result in the inability to login to Oracle, except as System. Access may be restored if necessary by dropping the system objects ctx_sys_drop.sql
* Database access will be affected for all users within the instance during the implementation steps described above. This should be performed off-hours.
* It is important that the context entries are set up correctly initially and are not changed after system use has begun.
* The application table owner should not have a user context entry in program UTCC, Master Context Maintenance, and should never be used to access the database, except when running upgrade scripts. Doing otherwise may corrupt data.
* The ONLY users which are allowed to access and manipulate data are those users with entries in program UTCC, Master Context Maintenance.
To implement the virtual private database for the fulfillment environment in NDS Applications, the Tenant ID is the element which identifies the master or client environment. NDS_CONTEXT_CONTROL maps database users to the tenant to which they belong. Customer master entries for fulfillment clients should equate to the tenant id values. This makes the fulfillment scheme independent of the actual user or schema names involved.