Auditing Customer Data

Audit Trail Architecture

You enable audit trails for an individual field, but the entire object that contains the field is versioned and stored to ensure consistency with other members of the storable class. When fields marked for auditing are created or modified, copies are made of the top-level storable class to which the field belongs and of that object's subclasses. The audit trail is composed of the versioned and stored object copies, which are called shadow objects. It is created at the end of a transaction, prior to transaction commit.

For example, if a field marked for auditing in the /payinfo/cc object is modified in the first, fifth, seventh, and eighth revision of the original object, the shadow objects shown in Figure 17-1 are created in the audit trail:

Figure 17-1 /payinfo/cc Shadow Objects

Description of "Figure 17-1 /payinfo/cc Shadow Objects"

The revision number is the revision number of the object's Portal object ID (POID).

When you mark a field for auditing, the Storable Class Editor calls the PCM_OP_SDK_SET_DD opcode. This opcode creates a shadow class for the top-level storable class in which the auditable field is modified and for all of that storable class's subclasses.

The auditability of a field is specified in the PIN_FLD_AUDITABLE meta-level field in the data dictionary. The value 1 (AUDIT_ENABLED) indicates the field is marked for auditing, and the value 0 (AUDIT_DISABLED) indicates the field is not marked for auditing.

The following example shows the input flist of the PCM_OP_SDK_SET_DD opcode after the PIN_FLD_DEBIT_NUM field is marked for auditing in the /payinfo/cc storable class. The PIN_FLD_AUDITABLE meta-level field is set to 1, which indicates that the PIN_FLD_DEBIT_NUM field is marked for auditing.

0 PIN_FLD_OBJ_DESC      ARRAY [133] allocated 20, used 4
1     PIN_FLD_NAME            STR [0] "/payinfo/cc"
1     PIN_FLD_DESCR           STR [0] "Credit Card payment
                                       information class."
1     PIN_FLD_OBJ_ELEM      ARRAY [0] allocated 20, used 14
2         PIN_FLD_FIELD_TYPE      INT [0] 9
2         PIN_FLD_FIELD_NAME      STR [0] "PIN_FLD_CC_INFO"
2         PIN_FLD_DESCR           STR [0] "Array to hold the
                                           credit card specific
                                           information. There
                                           can be only one
                                           array element. The
                                           array element id is not
                                           significant."
2         PIN_FLD_ORDER           NUM [0] 0.000000
2         PIN_FLD_OBJ_ELEM      ARRAY [0] allocated 20, used 8
3             PIN_FLD_FIELD_TYPE      INT [0] 5
3             PIN_FLD_FIELD_NAME      STR [0] "PIN_FLD_ADDRESS"
...
...
2         PIN_FLD_OBJ_ELEM      ARRAY [4] allocated 20, used 8
3             PIN_FLD_FIELD_TYPE      INT [0] 5
3             PIN_FLD_FIELD_NAME      STR [0] "PIN_FLD_DEBIT_NUM"
3             PIN_FLD_DESCR           STR [0] "Credit card
                                               number."
3             PIN_FLD_ORDER           NUM [0] 0.000000
3             PIN_FLD_LENGTH          INT [0] 30
3             PIN_FLD_AUDITABLE    INT [0] 1
3             PIN_FLD_CREATE_PERMISSION    STR [0] "Required"
3             PIN_FLD_MOD_PERMISSION    STR [0] "Writeable"
3             PIN_FLD_SM_ITEM_NAME    STR [0] "debit_num"
...
...

After a shadow class is created, it is not deleted even if auditing is later disabled. For example, if you disable auditing for the PIN_FLD_DEBIT_NUM field in the /payinfo/cc object, the existing audit trail for that field is retained and accessible to you if you need it.

About Shadow Objects

Shadow objects use an au prefix. For example, a change to a field marked for auditing in the /payinfo/cc object results in the following shadow objects: /au_payinfo, /au_payinfo/cc, /au_payinfo/inv, /au_payinfo/subord, and so on. The shadow object is a replica of the original object, so it stores the POID the object had when auditing occurred. The unique revision number of the POID is used to extract audit trail information from the database.

The shadow object contains the same fields as the original object plus the PIN_FLD_AU_PARENT_OBJ field that captures information about the audited storable class. The PIN_FLD_AU_PARENT_OBJ field is a pointer to the revision of the original object copied to the audit trail; its value is derived from the original object field.

You specify the tablespace for the audit trail schema when you install BRM. The default tablespace for a shadow object is the same as that of its original object.

The tablespace name for a shadow object is the same as that of its original object with the addition of an au prefix. There is a 32-character limit on tablespace names. If the addition of the au prefix exceeds the 32-character limit, the shadow object tablespace name is truncated, causing its tablespace name to be different than that of the original object.

Fields Marked for Auditing by Default

Table 17-1 shows the fields that are marked for auditing by default in BRM, the top-level storable class associated with those fields, the event objects created when those fields are modified, and the BRM activity being audited:

Table 17-1 Fields Marked by Default for Auditing

Top-Level Storable Class	Audited Fields	Event Object Created	BRM Activity Audited
/deal	All fields	/event/audit/price/deal	Changes to internal BRM pricing components
/payinfo	PIN_FLD_DEBIT_NUM and PIN_FLD_DEBIT_EXP	/event/audit/customer/payinfo/cc	Changes to customer credit card numbers and expiration dates
/plan	All fields	/event/audit/price/plan	Changes to internal BRM pricing components
/product	All fields	/event/audit/price/product	Changes to internal BRM pricing components
/rate	All fields	/event/audit/price/rate	Changes to internal BRM pricing components
/rate_plan	All fields	/event/audit/price/rate_plan	Changes to internal BRM pricing components
/rate_plan_selector	All fields	/event/audit/price/rate_plan_selector	Changes to internal BRM pricing components

Enabling Auditing for a Field

You enable and disable auditing for fields using the Storable Class Editor.

Performance tips:

Keep an audit trail only for the BRM activity that is absolutely necessary for your business. Enabling an audit trail decreases system performance significantly.
Do not mark fields in the /account object for auditing. Because the /account object is large and is modified by many types of system activity, it is not recommended for auditing. Mark the /purchased_product and /purchased_discount objects for auditing to audit charge offers and disount offers for an account.
If you mark an array or substruct for auditing, changes to any field in that array or substruct trigger auditing. To track changes to only one field in an array or substruct, mark only that field for auditing and not the entire array or substruct.

Accessing Audit Trail Information

You can access audit trail information manually by using the testnap utility to retrieve specific revisions of an object given the object's POID. You can also write your own application to access audit trail changes in the database.

To access audit trail data, you must:

Obtain the account number of the customer for whom you need audit trail data (as shown in Billing Care).
Obtain the general time period in which the event that was audited occurred. For example, the date or month the customer changed his credit card number.
Know the name of the event object BRM generates for each type of auditing. For example, when a customer changes the credit card number on his account, BRM generates the event object /event/audit/customer/payinfo/cc.

For the names of the event objects generated for default auditing, see "Fields Marked for Auditing by Default".

Use the customer's account number to obtain the /account object associated with the account number.
Browse the event objects for the /account object in the general time period to locate the event. For example, to find a changed credit card number, browse the events to locate the /event/audit/customer/payinfo/cc event created at the time the customer changed his credit card number.
Use the event object to obtain the POID of the original object that was audited. For example, the /event/audit/customer/payinfo/cc event contains the POID of the /payinfo/cc object that was audited when the customer's credit card number was changed. The POID provides the revision number of the object when it was audited. This number is stored in the shadow object.
Retrieve the shadow objects by calling the PCM_OP_READ_OBJ opcode with one of the following flags. (For examples of how to do this by using the testnap utility, see "Using testnap to Retrieve Shadow Objects".)
- Use the PCM_OPFLG_USE_POID_GIVEN flag to send a request to the BRM database Data Manager (DM) to run a search-and-read operation for the exact POID you specify (the POID of the shadow object in the audit trail).
- Use the PCM_OPFLG_USE_POID_PREV flag to send a request to the BRM database DM to run a search-and-read operation for the shadow object POID that contains a revision number preceding the revision number you specify in your POID.
- Use the PCM_OPFLG_USE_POID_NEXT flag to send a request to the BRM database DM to run a search-and-read operation for the shadow object POID that contains a revision number that is one or more numbers higher than the revision number you specify in your POID.
- Use the PCM_OPFLG_USE_POID_NEAREST flag to send a request to the BRM database DM to run a search-and-read operation for the exact audit trail revision number that you specify (the POID of the shadow object), and if the exact POID is not found, to obtain the POID with a revision number that precedes the revision number you specify in your POID.

Using testnap to Retrieve Shadow Objects

The following code samples show how to retrieve shadow objects by using testnap. This example accesses the audit trail of the /payinfo/cc object given the object's POID near the time auditing occurred.

# Flists and opcode for testnap to access the audit-trail
# of the /payinfo/cc object.
#
# Syntax: xop PCM_OP_READ_OBJ flags buffer
#
# where the buffer contains the following flist with the
# flags as described below:
# 
# 
0 PIN_FLD_POID POID [0] 0.0.0.1 /payinfo 10001 0
  
  
# NOTE: Replace the database number, the POID_ID, and 
# the version as required.
  
#Use the following flags:
  
# PCM_OPFLG_USE_POID_GIVEN (0x0040): To access the exact revision.
# e.g. "xop PCM_OP_READ_OBJ 0x0040 1" on the buffer flist 
# 0 PIN_FLD_POID POID [0] 0.0.0.1 /payinfo 10001 3
# This retrieves audit trail with revision 3, or NOT_FOUND.
  
# PCM_OPFLG_USE_POID_PREV (0x2000): To access the 
# previous revision.
# e.g. "xop PCM_OP_READ_OBJ 0x2000 1" on the buffer flist 
# 0 PIN_FLD_POID POID [0] 0.0.0.1 /payinfo 10001 3
# This retrieves audit trail with revision 2, assuming revision
# 2 is the previous revision to 3 in the audit-trail. Otherwise,
# the next revision lower than revision 3 is retrieved.
  
# PCM_OPFLG_USE_POID_NEXT (0x4000+): To access the next revision.
# e.g. "xop PCM_OP_READ_OBJ 0x4000 1" on the buffer flist 
# 0 PIN_FLD_POID POID [0] 0.0.0.1 /payinfo 10001 3
# This retrieves audit trail with revision 4, assuming revision
# 4 is the next revision to 3 in the audit trail. Otherwise, the
# next revision higher than revision 3 is retrieved.
  
  
# PCM_OPFLG_USE_POID_NEAREST (0x8000): To access the nearest
# revision.
# e.g. "xop PCM_OP_READ_OBJ 0x8000 1" on the buffer flist 
# 0 PIN_FLD_POID POID [0] 0.0.0.1 /payinfo 10001 3
# This retrieves audit trail with revision 3, if the revision 3
# exists; otherwise, it retrieves a revision preceding
# revision 3.

Purging Archived Audit Data

Use the purge_audit_tables.pl Perl script to remove unwanted audit data from your audit tables by moving older rows to history (archive) tables. Purging the audit tables improves system performance and reduces memory usage,

Note:

The purge_audit_tables.pl script does not delete objects from the database; it only purges the object rows stored in a table.

To purge objects from audit tables:

Open the BRM_home/sys/archive/oracle/purge_audit_tables.conf file.
1. In the storage_clause entry, specify the tablespace for the history tables.
2. In the time entry, specify the column name to be used for comparing the cutoff date specified in the purge_audit_tables.pl script's -d parameter.
3. In the cutoff_for_purge entry, specify the percentage based on which it will invoke the archiveindirect mode rather than the archivedirect method to archive the tables.
  
  For example, if the cutoff_for_purge value is 70, and a table contains more then 70% data that must be archived, temporary tables are used to transfer the data efficiently (archiveindirect mode). If the table contains less then 70% data that must be archived, the data is transferred directly to the history tables (archivedirect mode).
For more information about the configuration entries, see the purge_audit_tables.conf file in the BRM_home/sys/archive/oracle directory.
With a text editor, open the purge_audit_tables.pl script.
In the first line of the script, replace __PERL__ with the location of the Perl executable.
Run the purge_audit_tables.pl script. See "purge_audit_tables.pl" in BRM System Administrator's Guide for more information.

Note:

To run in debug mode, set the environment variable ARCHIVE_DEBUG at the system prompt before you run the script. As the script runs, processing data, including the functions that are called, is printed to the screen.