2 Preparing JD Edwards EnterpriseOne for Auditing and Electronic Signatures

This chapter contains the following topics:

2.1 Understanding Audit and Electronic Signatures Records

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.

2.2 Prerequisites

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.

2.3 Setting Up JD Edwards EnterpriseOne for Auditing and Electronic Signatures

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:

2.3.1 Understanding Database Considerations for Auditing

This section discusses:

  • Database sizing.

  • Table conversions.

  • Database driver considerations.

  • Relational database management system (RDBMS) interface management.

2.3.1.1 Database Sizing

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

2.3.1.2 Table Conversions

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.

2.3.1.3 Database Driver Considerations

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.

2.3.1.4 RDBMS Interface Management

This table lists the RDBMS interface associated with each database:

Database RDBMS Interface
Oracle Enterprise Manager
SQL Server Query Analyzer
DB2 for i Primitive Support
DB2 for Linux, UNIX, and Windows Control Center

2.3.2 Understanding Database Triggers for Audited Tables

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).

2.3.2.1 Oracle

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.

2.3.2.2 DB2 for i

Each audited table has three ROW level (After) triggers. Two triggers are created for each signature table.

2.3.2.3 DB2 for Linux, UNIX, and Windows

Each audited table has three ROW level (After) triggers. Two triggers are created for each signature table.

2.3.2.4 SQL Server

Each audited table has three triggers, which are the STATEMENT level triggers (After). One trigger is created for each signature table.

2.3.2.5 Trigger Naming Conventions

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 i and DB2 for Linux, UNIX, and 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

2.3.3 Setting Up a Data Source for Audit Tables

Make sure that you install 21 CFR Part 11 objects based on the JD Edwards EnterpriseOne release level.

  1. Sign on to a client machine.

  2. 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.

  3. 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

  4. 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

  5. Map these objects to the appropriate data source:

    Objects Data Sources
    B9500001 LOCAL
    B9500003 LOCAL
    B9500005 SERVER
    B986112 LOCAL
    R9500004 LOCAL
    GT9500006 Object Librarian

  6. Open Microsoft Windows Explorer and double-click the dbtemplates.exe, which is located in this directory:

    C:\B9\system\bin32\
    

2.3.4 Configuring an Oracle Database for Auditing

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.

  1. Start SQL*PLUS and connect to the instance as SYS.

  2. 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.

2.3.5 Configuring SQL Server 2005/2008/2012/2014 Database for Auditing (Release 9.2.2)

To configure SQL Server 2005, 2008, 2012, or 2014 database server for use with EnterpriseOne table auditing, you must set up the privileges described in this section.

  1. Log in to the SQL Server 2005, 2008, 2012, or 2014 database server as the sa user (as the administrative user).

  2. Run the following SQL commands if you want to enable auditing in the production path code:

    use [JDE_PRODUCTION]

    GO

    GRANT ALTER TO [PRODDTA]

    GO

    use [JDE_PRODUCTION]

    GO

    GRANT CREATE TABLE TO [PRODDTA]

    GO

    use [JDE_PRODUCTION]

    GO

    GRANT CREATE VIEW TO [PRODDTA]

    GO

    use [JDE_PRODUCTION]

    GO

    GRANT ALTER ANY DATABASE DDL TRIGGER TO [PRODDTA]

    GO

  3. For every additional path code you want to enable auditing, run the above SQL commands using the database name and database user name that applies to that path code.

2.3.6 Configuring a DB2 for i Database for Auditing

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 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:

  1. Add the following entry in the system reply list for the Inquiry Message - CPA32B2:

    ADDRPYLE SEQNBR(3283) MSGID(CPA32B2) RPY('I')
    
  2. If you are using IBM i version V5R2 or prior, modify the job description QDFTJOBD that is the *JOBD for IBM i User QUSER by changing the value for its Inquiry Message Reply option:

    CHGJOBD JOBD(QGPL/QDFTJOBD) INQMSGRPY(*SYSRPYL)
    
  3. If you are using IBM i version V5R3 or later, modify the job description QDFTSVR that is the *JOBD for IBM i 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 IBM i, you must delete the SQL packages.

2.3.6.1 Deleting SQL Packages

To delete the SQL packages:

  1. Stop services with this command:

    ENDNET
    
  2. Delete the SQL packages with this command:

    WRKOBJ *ALL/*ALL *SQLPKG
    

    Delete all *SQLPKG objects whose name does not start with "Q".

  3. Start the service with this command:

    STRNET
    

2.3.7 Configuring a DB2 for Linux, UNIX, and Windows Database for Auditing

To configure a DB2 for Linux, UNIX, and Windows database for auditing, you must set up the privileges described in this section.

  1. 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.

  2. 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.

2.4 Preparing an Auditing and Electronic Signature Configuration for a JD Edwards EnterpriseOne Upgrade

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.
  1. Make sure that users are signed off from the JD Edwards EnterpriseOne installation that is configured for auditing and electronic approval.

  2. Create a backup of the tables that are being audited, shadow audit tables, and auditing configuration tables in a different data source.

  3. 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.

  4. 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.

  5. Perform the JD Edwards EnterpriseOne upgrade.

  6. 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.
  7. Recreate and manually enable the custom database view or triggers removed in step 3.