This chapter contains the following topics:
Section 2.1, "Understanding Audit and Electronic Signatures Records"
Section 2.3, "Setting Up JD Edwards EnterpriseOne for Auditing and Electronic Signatures"
JD Edwards EnterpriseOne provides the capability to select specific columns in a table for auditing. You can configure JD Edwards EnterpriseOne to generate an audit table when table records are inserted, updated, or deleted. The audit records contain data such as:
Before and after values.
Time and date of the transaction.
The user who made the modification.
In addition, you can configure JD Edwards EnterpriseOne interactive and batch applications to require an electronic signature approval when a user tries to change the data on an application or submit a report. A record of the approval is recorded in the Signature table (F9500005). The table records this information:
Approver of the change.
Reason for the approval.
Approver's user ID.
User's role.
Date and time of the approval.
You can view the information in the audit and electronic signature tables using the View Audit/Signature Information application (P9500005) or by generating reports (R9500004, R9500005, R9600006). The reports display all the audit and signature information in an easy-to-read PDF file that can be printed to hard copy or saved in digital format.
Before you configure JD Edwards EnterpriseOne for auditing and electronic signatures:
Use Security Workbench (P00950) to secure the audit applications (P9500001, P9500002, P9500003, and P9500005) in JD Edwards EnterpriseOne to ensure that only the appropriate users have access.
Create a backup of the database before configuring audit tables and make sure that the restore process works.
Plan the audit:
Research and determine which tables and columns need to be audited.
Research and determine if database triggers and database views are currently in use for the tables that you plan to audit. If this is the case, you must manually remove those objects before auditing is configured. Once auditing is configured, those objects must be manually enabled.
Research and determine which applications, forms, and reports require electronic signature approvals.
Research and determine which roles are required for electronic approval. If needed, set up additional roles in JD Edwards EnterpriseOne.
See "Setting Up Roles" in the JD Edwards EnterpriseOne Tools Security Administration Guide.
Plan for additional disk space for audit tables.
Make sure that the proposed audited table names are not called tablename_ADT.If they are, rename them.
You must perform the tasks in this section before you can configure JD Edwards EnterpriseOne tables and applications for auditing and electronic signatures.
This section contains the following topics:
Section 2.3.1, "Understanding Database Considerations for Auditing"
Section 2.3.2, "Understanding Database Triggers for Audited Tables"
Section 2.3.4, "Configuring an Oracle Database for Auditing"
Section 2.3.5, "Configuring a DB2 for IBM i Database for Auditing"
Section 2.3.6, "Configuring a IBM DB2 for LUW (Linux, UNIX, Windows) Database for Auditing"
This section discusses:
Database sizing.
Table conversions.
Database driver considerations.
Relational database management system (RDBMS) interface management.
When auditing is enabled in JD Edwards EnterpriseOne, the size of the database increases dramatically. In addition to creating six new tables, an audit table is created for every table that has a column flagged for audit. You can use the following formula to calculate the increase in database size:
Audit table record size = 102 characters + size of all audited columns, including primary key for an audited table
For every record inserted or deleted on a table that is being audited, the system inserts a record in an audit table. For each update transaction on a table that is being audited, the system inserts two records in the audit table: one for the before image and one for the after image. Use the following formula for update transactions:
Database size increase per audited table = audit table record size × 2 (before and after image) × number of transactions
If you perform a table conversion when auditing is turned on, the tool audits the conversion itself, slowing the table conversion process. To prevent this from happening, disable auditing before performing a table conversion.
The database driver level implements triggers on the database tables to provide auditing of selected tables, for both changes made through JD Edwards EnterpriseOne database middleware and changes made by third-party software. The auditing includes the recording of signature information when this feature is activated. The auditing information is passed to the drivers from JDB using additional table columns. Database level table triggers generate the auditing information for third-party changes.
The database triggers record auditing information for every INSERT and DELETE statement. For UPDATE statements, the triggers record auditing information only if one or more of the audited columns have a changed data value. Modifying a non-audited column does not generate any audit information.
For a SQL server, a column cannot be dropped if it is:
A replicated column.
Used in an index.
Used in CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraints.
Associated with a default defined with the DEFAULT keyword or bound to a default object.
Bound to a rule.
This table lists the RDBMS interface associated with each database:
| Database | RDBMS Interface |
|---|---|
| Oracle | Enterprise Manager |
| SQL Server | Query Analyzer |
| DB2 for IBM i | Primitive Support |
| IBM DB2 for LUW (Linux, UNIX, Windows) | Control Center |
The database triggers for audited tables write records to the audit tables (shadow tables) for Insert and Delete functions. For Insert, the after image is stored in the audit table. For Delete, the before image is stored in the audit table.
For the Update function, the database triggers only write records to audit tables when either the primary key columns or the specified audited columns are changed. A record is written in the shadow table when the new value is different from the current value. No record is written if they are the same. If the trigger is executed for update, before and after images of the record change are recorded in the audit table.
The trigger templates for all databases are stored in the Database Object Template Information table (F986112).
Each audited table has five triggers, three of which are ROW level triggers (Before for Insert and Update, After for Delete) and two of which are STATEMENT level triggers (Before/After). Each signature table has one trigger and each audit table has one package.
Each audited table has three ROW level (After) triggers. Two triggers are created for each signature table.
Each audited table has three ROW level (After) triggers. Two triggers are created for each signature table.
Each audited table has three triggers, which are the STATEMENT level triggers (After). One trigger is created for each signature table.
This table contains the trigger naming convention for each database type:
| Database | Object Name | Object Attribute |
|---|---|---|
| Oracle | Fxxxx_CFRA_RDA | Row Level Delete Trigger |
| Fxxxx_CFRA_RIA | Row Level Insert Trigger | N/A |
| Fxxxx_CFRA_RUA | Row Level Update Trigger | N/A |
| Fxxxx_CFRA_SUA | Statement Level Update Trigger | N/A |
| Fxxxx_CFRA_SUB | Statement Level Update Trigger | N/A |
| Fxxxx_ADT_CFRA_PKG | Package | N/A |
| F95xxxxx_CFRS_RIUB | Signature/Audit Header/Audit Detail Table Insert and Update Triggers | N/A |
| DB2 for IBM i and IBM DB2 for LUW (Linux, UNIX, Windows) | Fxxxx_CFRA_RDA | Row Level Delete Trigger |
| Fxxxx_CFRA_RIA | Row Level Insert Trigger | N/A |
| Fxxxx_CFRA_RUA | Row Level Update Trigger | N/A |
| F95xxxxx_CFRS_RIB | Signature / Audit Header / Audit Detail Table Insert Triggers | N/A |
| F95xxxxx_CFRS_RUB | Signature / Audit Header / Audit Detail Table Update Triggers | N/A |
| SQL Server | Fxxxx_CFRA_RDB | Statement Level Delete Trigger |
| Fxxxx_CFRA_RIB | Statement Level Insert Trigger | N/A |
| Fxxxx_CFRA_RUB | Statement Level Update Trigger | N/A |
| F95xxxxx_CFRS_RIB | Signature/Audit Header/Audit Detail Table Insert and Update Triggers | N/A |
Make sure that you install 21 CFR Part 11 objects based on the JD Edwards EnterpriseOne release level.
Sign on to a client machine.
Create a non-Julian database data source for system tables.
For example, create "System - B9NJ" data source for non-Julian tables for JD Edwards EnterpriseOne 8.11.
Generate tables using Object Management Workbench (OMW) for the respective data sources (except F9500005).
The System Data Source name is based on the JD Edwards EnterpriseOne release level; for example, "SYSTEM - B11" for JD Edwards EnterpriseOne 8.11 and "SYSTEM - 812" for JD Edwards EnterpriseOne 8.12.
| Tables | Data Sources |
|---|---|
| F9500001 | System Data Source |
| F9500002 | System Data Source |
| F9500003 | Non-Julian System Data Source |
| F9500004 | Non-Julian System Data Source |
| F9500005 | Local - XXXX |
| F9500006 | Non-Julian System Data Source |
| F986112 | System Data Source |
Create Object Configuration Manager (OCM) mappings for *PUBLIC in the System and Server Map using this information:
| Tables | Data Sources |
|---|---|
| F9500001 | System Data Source |
| F9500002 | System Data Source |
| F9500003 | Non-Julian System Data Source |
| F9500004 | Non-Julian System Data Source |
| F9500005 | System Data Source |
| F9500006 | Non-Julian System Data Source |
| F986112 | System Data Source |
Map these objects to the appropriate data source:
| Objects | Data Sources |
|---|---|
| B9500001 | LOCAL |
| B9500003 | LOCAL |
| B9500005 | SERVER |
| B986112 | LOCAL |
| R9500004 | LOCAL |
| GT9500006 | Object Librarian |
Open Microsoft Windows Explorer and double-click the dbtemplates.exe, which is located in this directory:
C:\B9\system\bin32\
You must grant the owners of the audit tables (F9500003, F9500004, and F9500006) and audited tables (for example PRODDTA, DD812) the privileges listed in this task.
Start SQL*PLUS and connect to the instance as SYS.
For each owner, enter the following three commands:
GRANT CREATE PROCEDURE TO owner
GRANT CREATE TRIGGER TO owner
GRANT SELECT ON SYS.V_$SESSION TO owner
Note:
The third command must be directly granted to the owner. Granting it to a role to which the owner belongs does not work.In an auditing configuration, when auditing is disabled for a table, all working columns will be dropped in the table through the SQL operation ALTER TABLE DROP COLUMN. In DB2 for IBM i, an Inquiry Message will be issued for this SQL operation when the column contains data. The purpose of the Inquiry Message is to obtain the user's response to carry on the operation or to cancel the operation. However, the system cannot send this Inquiry Message to the user for a response and this causes the system to automatically cancel the SQL operation. The consequence is failure to disable auditing for the table.
To resolve this issue, perform these setup steps:
Add the following entry in the system reply list for the Inquiry Message - CPA32B2:
ADDRPYLE SEQNBR(3283) MSGID(CPA32B2) RPY('I')
If you are using iSeries version V5R2 or prior, modify the job description QDFTJOBD that is the *JOBD for iSeries User QUSER by changing the value for its Inquiry Message Reply option:
CHGJOBD JOBD(QGPL/QDFTJOBD) INQMSGRPY(*SYSRPYL)
If you are using iSeries version V5R3 or later, modify the job description QDFTSVR that is the *JOBD for iSeries User QUSER by changing the value for its Inquiry Message Reply option:
CHGJOBD JOBD(QGPL/QDFTSVR) INQMSGRPY(*SYSRPYL)
Note:
Whenever you change the configuration of the iSeries, you must delete the SQL packages.To delete the SQL packages:
Stop services with this command:
ENDNET
Delete the SQL packages with this command:
WRKOBJ *ALL/*ALL *SQLPKG
Delete all *SQLPKG objects whose name does not start with "Q".
Start the service with this command:
STRNET
To configure a IBM DB2 for LUW (Linux, UNIX, Windows) database for auditing, you must set up the privileges described in this section.
Assign at least one of the following privileges to the owners of the audited tables:
SYSADM or DBADM
ALTER privilege on the table on which the trigger is defined, or ALTERIN privilege on the schema of the table and one of the following privileges:
-CREATEIN privilege on the schema.
-IMPLICIT_SCHEMA authority on the database.
Assign the following privileges to all of the JD Edwards EnterpriseOne database proxy users:
SELECT privilege on the table.
SELECT privilege on any other table or view that the trigger is referencing.
Necessary privileges to execute the trigger SQL statement.
Before upgrading to a new version of JD Edwards EnterpriseOne or applying software updates that contain table changes, you must turn off the auditing and electronic signature functions. JD Edwards EnterpriseOne must be made unavailable to users, and all the audit tables and configuration tables (F9500001 through F9500006) must be archived.
Note:
Audit history will not be migrated when JD Edwards EnterpriseOne is upgraded. You must reconfigure the table audit and electronic signature settings.Make sure that users are signed off from the JD Edwards EnterpriseOne installation that is configured for auditing and electronic approval.
Create a backup of the tables that are being audited, shadow audit tables, and auditing configuration tables in a different data source.
Manually remove any custom database views or triggers.
If a custom table trigger already exists, it will be deleted when auditing is enabled or disabled.
Disable auditing using the Configuration Application (P9500001).
This application performs these functions:
Drops native database triggers on Fxxxx_ADT.
Drops database view Fxxxx.
Drops CFR columns for Fxxxx_ADT.
Renames Fxxxx_ADT to Fxxxx.
Perform the JD Edwards EnterpriseOne upgrade.
Enable auditing using the P9500001 application.
This application performs these functions:
Renames target table to Fxxxx_ADT.
Adds CFR columns to Fxxxx_ADT.
Creates database view Fxxxx (original structure)
Creates native database triggers to Fxxxx_ADT.
Note:
Remember that before you enable auditing, you must perform certain tasks on the JD Edwards EnterpriseOne servers and database servers, as previously discussed in this chapter.Recreate and manually enable the custom database view or triggers removed in step 3.