Database Auditing

1 Qbyte 360

Qbyte 360

Auditability and traceability are key for Financial Accounting, Land Management, and Production Accounting. The data captured and generated within Qbyte 360 is very valuable to customers for ongoing business management, analysis, and decisions. Understanding and accessing data changes is a part of the analysis.

Qbyte 360 has an extensive inventory of audit tables. Audit tables mirror the detail available in live tables, including capturing the update user, update date, and update source/program. The update source/program will identify if the record was updated through the application or a direct database update. The audit table captures the complete before record for updated and deleted records. Current records are not written to the Audit tables as they are available in the live application tables.

Qbyte Metrix Audit Capabilities

Qbyte Metrix audit functionality monitors and logs selected user database actions.

The Metrix Database Schema contains all tables accessed from within the Qbyte Metrix application (called live or base tables). Each record in these live tables captures detailed information when records are created and the latest change.
The Metrix-Audit Database Schema contains tables that track every update/delete made to certain Master and Monthly live tables.

Database Auditing takes two forms:

1) Data Change Auditing within live tables in the Metrix Database Schema:

When data is added to Master and Monthly tables, we populate audit information on many database tables within the Metrix Database Schema to capture the Creation Date/time, Creation Program (what program created it), and Creation User ID (who created it). Similarly, when we change master/monthly data, we populate audit information that captures the Update Date/time, Update Program (what changed it), and Update User ID (who changed it). For most Master and Monthly data, the Qbyte Metrix tables contain current data only (i.e. the latest updates).
Note: The creation program/update program can be one of the many services within Qbyte Metrix such as Battery Balance, FDC Load, etc. but it can also be external database tools like SQL Developer, etc.
Tables containing calculated data that are more temporary in nature usually contain the Creation Date/time, Creation User ID, and a Job ID.
For Historical data, the tables generally contain the latest information by Production Dates or the latest information by Accounting Date.
Qbyte Optix data extracts can sometimes be used to retrieve the latest data change auditing information.
Database queries can also be used to retrieve the latest data change auditing information for all Qbyte Metrix tables.

2) Data Change Auditing within audit tables in the Metrix Audit Database Schema:

Most Master and Monthly entered data tables in the Metrix Database Schema (live tables) have a partnering audit table that holds the history of row changes plus some additional metadata (such as the operation that was audited, the user performing the operation and the date/time of the operation).
Database triggers on the live tables populate audit tables with before records when data is updated or deleted. Records in these audit tables are only ever added and no capability to delete or update them has been provided either through the application or using direct database update tools.
A sequence generated Audit ID exists on both the audit and live tables. This Audit ID can be used for joining the audit and live tables to recreate the audit trail. There is a separate sequence for each table being audited.
Although we capture audit information for most tables used in Step Processing and other processing, we do not maintain separate audit tables for this calculated monthly data.
Currently, database queries provide the only method of accessing audit table data.
Appendix 1 contains a list of Audited Qbyte Metrix Tables listed by Category.
Appendix 2 contains an alphabetical list of Audited Qbyte Metrix Tables.

Example:

METRIX live TABLE: BATTERY_OIL_DISPOSITIONS

AUDIT TABLE: AUDIT_BATTERY_OIL_DISPOSITN

In addition to data fields related to Battery Oil Dispositions, the live table contains CREATE_USER, CREATE_PROGRAM, CREATE_DATE_TIME, LAST_UPDATE_USER, LAST_UPDATE_PROGRAM, LAST_UPDATE_DATE_TIME, and LAST_UPDATE_AUDIT_ID.
The CREATE_PROGRAM in the live table for oil dispositions can be BatteryBalanceOil, LoadFdcBatteryControllerService, etc. as dispositions can be created/updated through Battery Balance Oil, or loaded/updated via FDC.
If an Oil Disposition was deleted through Battery Balance, a copy of the record is written to the audit table (with audit metadata) before the record is deleted from the live table.
If an Oil Disposition was updated through Battery Balance, the before record is written to the audit table with a unique Audit ID, the record is updated in the live tables using the same Audit ID and the Last Update metadata fields are also updated. This Audit ID can be used for joining the audit and live tables to recreate the audit trail.
If the above Oil Disposition was updated a second time, the procedures are the same as the first update described above. However, since the live tables only contain the latest data for our oil disposition, the Audit ID of our oil disposition in the live table will match the latest audit table entry. The Audit ID from the first update no longer exists in the live table.

Appendix 1: Audited Qbyte Metrix Tables by Category

Qbyte Metrix Live Tables that have Audit Tables	METRIX_AUDIT Table Names	Category

Qbyte Metrix Live Tables that have Audit Tables	METRIX_AUDIT Table Names	Category
ANALYSIS_FACTOR_DETAILS	AUDIT_ANALYSIS_FACTOR_DETLS	Analysis
ANALYSIS_FACTORS	AUDIT_ANALYSIS_FACTORS	Analysis
ANALYSIS_USAGE_DETAILS	AUDIT_ANALYSIS_USAGE_DETLS	Analysis
ANALYSIS_USAGES	AUDIT_ANALYSIS_USAGES	Analysis
GAS_ANALYSIS_COMP_TOLERANCES	AUDIT_GAS_ANALYSIS_COMP_TOL	Analysis
GAS_ANALYSIS_TOLERANCES	AUDIT_GAS_ANALYSIS_TOLERANC	Analysis
GAS_COMPONENT_ANALYSES	AUDIT_GAS_COMPON_ANALYSES	Analysis
GAS_COMPONENT_ANALYSIS_FACTORS	AUDIT_GAS_COMPON_ANALYS_FCT	Analysis
GAS_COMPONENT_ANALYSIS_PRODS	AUDIT_GAS_COMPON_ANALYS_PRD	Analysis
GROUP_METER_MASTERS	AUDIT_GROUP_METER_MASTERS	Analysis
GROUP_METER_WELLS	AUDIT_GROUP_METER_WELLS	Analysis
CONTROL_GROUP_AMENDMENT_REQS	AUDIT_CTRL_GRP_AMEND_REQS	Control Group
CONTROL_GROUP_AMENDMENT_TAPES	AUDIT_CTRL_GRP_AMEND_TAPES	Control Group
CONTROL_GROUP_HIERARCHIES	AUDIT_CONTROL_GRP_HIERARCH	Control Group
CONTROL_GROUP_MASTERS	AUDIT_CONTROL_GROUP_MASTERS	Control Group
CONTROL_GROUP_PROCESS_STATES	AUDIT_CTRL_GRP_PRCSS_STATES	Control Group
COST_CENTRES	AUDIT_COST_CENTRES	Cost Centre
CI_COST_CENTRE_CROSS_REFS	AUDIT_CI_COST_CTR_CROSS_REF	Crown Invoice (Obsolete)
CI_ENTITY_OVERRIDES	AUDIT_CI_ENTITY_OVERRIDES	Crown Invoice (Obsolete)
CI_OWNER_OVERRIDES	AUDIT_CI_OWNER_OVERRIDES	Crown Invoice (Obsolete)
DOI_CROWN_INVOICE_EXCEPTIONS	AUDIT_DOI_CROWN_INV_EXCEPTN	DOI
DOI_FACILITY_CHARGE_EXCEPTIONS	AUDIT_DOI_FAC_CHRG_EXCEPTN	DOI
DOI_MASTERS	AUDIT_DOI_MASTERS	DOI
DOI_OWNERS	AUDIT_DOI_OWNERS	DOI
DOI_OWNERSHIP_EXCEPTIONS	AUDIT_DOI_OWNERSHIP_EXCEPT	DOI
ALLOCATED_FUEL_USAGE_WELLS	AUDIT_ALLOC_FUEL_USAGE_WELL	Facility
ALLOCATED_FUEL_USAGES	AUDIT_ALLOC_FUEL_USAGES	Facility
BATTERY_CONSUMPTION_FACTORS	AUDIT_BATTERY_CONSUMPT_FCTR	Facility
BATTERY_CONSUMPTION_VOLUMES	AUDIT_BATTERY_CONSUMPT_VOL	Facility
BATTERY_FACTORS	AUDIT_BATTERY_FACTORS	Facility
BATTERY_GAS_FLOW_FACTORS	AUDIT_BATTERY_GAS_FLOW_FCTR	Facility
BATTERY_GAS_FLOW_PROD_FACTORS	AUDIT_BATTERY_GF_PROD_FCTR	Facility
BATTERY_GOVT_PRODUCT_XREF	AUDIT_BATT_GOVT_PROD_XREF	Facility
BATTERY_MASTERS	AUDIT_BATTERY_MASTERS	Facility
BATTERY_OIL_DISPOSITIONS	AUDIT_BATTERY_OIL_DISPOSITN	Facility
BATTERY_OIL_RECEIPTS	AUDIT_BATTERY_OIL_RECEIPTS	Facility
BATTERY_OTHER_DISPOSITIONS	AUDIT_BATTERY_OTHER_DISPS	Facility
BATTERY_WATER_RECEIPTS	AUDIT_BATTERY_WATER_RECEIPT	Facility
DELIVERY_SYSTEM_MASTERS	AUDIT_DELIVERY_SYSTEM_MAST	Facility
DISPOSITIONS	AUDIT_DISPOSITIONS	Facility
FLOW_GROUP_MASTERS	AUDIT_FLOW_GROUP_MASTERS	Facility
GAS_RECEIPTS	AUDIT_GAS_RECEIPTS	Facility
GGS_CONSUMPTION_VOLUMES	AUDIT_GGS_CONSUMPTION_VOLUM	Facility
GGS_DISPOSITIONS	AUDIT_GGS_DISPOSITIONS	Facility
GGS_GAS_FLOW_FACTORS	AUDIT_GGS_GAS_FLOW_FACTORS	Facility
GGS_GAS_FLOW_PRODUCT_FACTORS	AUDIT_GGS_GAS_FLW_PROD_FCTR	Facility
GGS_MASTERS	AUDIT_GGS_MASTERS	Facility
INJECTION_CONSUMPTION_VOLUMES	AUDIT_INJECT_CONSUMPTION_VOL	Facility
INJECTION_DELIVERIES	AUDIT_INJECTION_DELIVERIES	Facility
INJECTION_DISPOSAL_PRODUCTS	AUDIT_INJ_DISPOSAL_PRODUCTS	Facility
INJECTION_DISPOSALS	AUDIT_INJECTION_DISPOSALS	Facility
INJECTION_FACTORS	AUDIT_INJECTION_FACTORS	Facility
INJECTION_MASTERS	AUDIT_INJECTION_MASTERS	Facility
INJECTION_PRODUCTIONS	AUDIT_INJECTION_PRODUCTIONS	Facility
INJECTION_RECEIPTS	AUDIT_INJECTION_RECEIPTS	Facility
INVENTORY_DISPOSITIONS	AUDIT_INVENTORY_DISPOSITNS	Facility
IPS_PROC_PARTICIPANT_OWNERS	AUDIT_IPS_PROC_PARTIC_OWNER	Facility
IPS_PROCESSING_ORDERS	AUDIT_IPS_PROCESSING_ORDERS	Facility
IPS_PROCESSING_PARTICIPANTS	AUDIT_IPS_PROCESSING_PARTIC	Facility
OVERRIDE_DETAILS	AUDIT_OVERRIDE_DETAILS	Facility
OVERRIDE_SUMMARIES	AUDIT_OVERRIDE_SUMMARIES	Facility
PARTNER_OP_BATTERY_FACTORS	AUDIT_PARTNER_OP_BTY_FACTOR	Facility
PARTNER_OP_BATTERY_PRICES	AUDIT_PARTNER_OP_BTY_PRICES	Facility
PARTNER_OP_BATTERY_TXNS	AUDIT_PARTNER_OP_BTY_TXNS	Facility
PLANT_CONSUMPTION_VOLUMES	AUDIT_PLANT_CONSUMPTION_VOL	Facility
PLANT_FACTORS	AUDIT_PLANT_FACTORS	Facility
PLANT_MASTERS	AUDIT_PLANT_MASTERS	Facility
PLANT_PRODUCT_FACTORS	AUDIT_PLANT_PRODUCT_FACTORS	Facility
PLANT_STREAMS	AUDIT_PLANT_STREAMS	Facility
PRODUCT_OUTSIDE_RECEIPTS	AUDIT_PROD_OUTSIDE_RECEIPTS	Facility
CHARGE_TYPES	AUDIT_CHARGE_TYPES	Facility Charges
FACILITY_CHARGES	AUDIT_FACILITY_CHARGES	Facility Charges
FC_CONTROL_GROUP_HIERARCHIES	AUDIT_FC_CONTROL_GROUP_HIER	Facility Charges
FC_COST_CENTRE_CROSS_REFS	AUDIT_FC_COST_CTR_CROSS_REF	Facility Charges
FC_ENTITY_EXCEPTIONS	AUDIT_FC_ENTITY_EXCEPTIONS	Facility Charges
FC_FORMULA_LINES	AUDIT_FC_FORMULA_LINES	Facility Charges
FC_FORMULA_LOOKUP_FACTORS	AUDIT_FC_FORMULA_LKUP_FACTR	Facility Charges
FC_FORMULA_MASTERS	AUDIT_FC_FORMULA_MASTERS	Facility Charges
FC_FROM_TO_EXCEPTIONS	AUDIT_FC_FROM_TO_EXCEPTIONS	Facility Charges
FC_GLOBAL_FACTOR_MASTERS	AUDIT_FC_GLOBAL_FACTOR_MAST	Facility Charges
FC_GLOBAL_FACTOR_VALUES	AUDIT_FC_GLOBAL_FACTOR_VALU	Facility Charges
FC_GLOBAL_FACTORS	AUDIT_FC_GLOBAL_FACTORS	Facility Charges
FC_OWNER_EXCEPTIONS	AUDIT_FC_OWNER_EXCEPTIONS	Facility Charges
FC_PURCHASER_EXCEPTIONS	AUDIT_FC_PURCHASER_EXCEPTN	Facility Charges
FI_ACCOUNT_DEFINITIONS	AUDIT_FI_ACCOUNT_DEFINITION	Financial
FI_BATCH_DEFINITIONS	AUDIT_FI_BATCH_DEFINITIONS	Financial
FI_ENTRY_DEFINITIONS	AUDIT_FI_ENTRY_DEFINITIONS	Financial
FI_FINANCIAL_COMPANIES	AUDIT_FI_FINANCIAL_COMPANIES	Financial
FI_SPECIFIC_TYPE_USERS	AUDIT_FI_SPECIFIC_TYPE_USER	Financial
FI_SUBMISSIONS	AUDIT_FI_SUBMISSIONS	Financial
FI_VOUCHER_DEFINITIONS	AUDIT_FI_VOUCHER_DEFINITION	Financial
FI_WIO_VENDOR_EXCEPTIONS	AUDIT_FI_WIO_VENDOR_EXCEPTN	Financial
FINANCIAL_GROUP_CONTROL_GROUPS	AUDIT_FINANCIAL_GRP_CTL_GRP	Financial
FINANCIAL_GROUP_MASTERS	AUDIT_FINANCIAL_GROUP_MASTR	Financial
FM_CROSS_REFERENCES	AUDIT_FM_CROSS_REFERENCES	Financial
AI_SUBSYSTEM_COMPONENTS	AUDIT_AI_SUBSYS_COMPONENTS	Infrastructure (not populated from Qbyte Metrix)
AI_SUBSYSTEMS	AUDIT_AI_SUBSYSTEMS	Infrastructure (not populated from Qbyte Metrix)
AU_JOB_STREAM_SCHEDULES	AUDIT_AU_JOB_STREAM_SCHEDS	Infrastructure (not populated from Qbyte Metrix)
AU_JOB_STREAM_STEP_PARAMETERS	AUDIT_AU_JOB_STR_STEP_PARMS	Infrastructure (not populated from Qbyte Metrix)
AU_JOB_STREAM_STEPS	AUDIT_AU_JOB_STREAM_STEPS	Infrastructure (not populated from Qbyte Metrix)
AU_JOB_STREAM_VOUCHERS	AUDIT_AU_JOB_STR_VOUCHERS	Infrastructure (not populated from Qbyte Metrix)
AU_JOB_STREAMS	AUDIT_AU_JOB_STREAMS	Infrastructure (not populated from Qbyte Metrix)
AU_JS_STEP_DEPENDENCIES	AUDIT_AU_JS_STEP_DEPENDS	Infrastructure (not populated from Qbyte Metrix)
AU_JSS_SCHEDULE_PARAMETERS	AUDIT_AU_JSS_SCHEDULE_PARMS	Infrastructure (not populated from Qbyte Metrix)
CODE_TYPES	AUDIT_CODE_TYPES	Infrastructure (not populated from Qbyte Metrix)
CODES	AUDIT_CODES	Infrastructure (not populated from Qbyte Metrix)
MODULES	AUDIT_MODULES	Infrastructure (not populated from Qbyte Metrix)
MULTIPLE_REPORT_PARAMETERS	AUDIT_MULTIPLE_REPORT_PARMS	Infrastructure (not populated from Qbyte Metrix)
OPTION_DESCRIPTIONS	AUDIT_OPTION_DESCRIPTIONS	Infrastructure (not populated from Qbyte Metrix)
REPORT_MODULE_PROPERTIES	AUDIT_REPORT_MODULE_PROPS	Infrastructure (not populated from Qbyte Metrix)
REPORT_PARAMETERS	AUDIT_REPORT_PARAMETERS	Infrastructure (not populated from Qbyte Metrix)
SYSTEM_DEFAULTS	AUDIT_SYSTEM_DEFAULTS	Infrastructure (not populated from Qbyte Metrix)
MARKET_MASTER_ALLOC_GROUPS	AUDIT_MARKET_MAST_ALLOC_GRP	Market Master/Pricing
MARKET_MASTER_DISPOSITIONS	AUDIT_MARKET_MAST_DISPOSITN	Market Master/Pricing
MARKET_MASTER_FACILITY_PRICES	AUDIT_MARKET_MAST_FAC_PRICE	Market Master/Pricing
MARKET_MASTER_PARTICIPANTS	AUDIT_MARKET_MAST_PARTICIP	Market Master/Pricing
MARKET_MASTER_PURCH_FACTORS	AUDIT_MARKET_MAST_PURCH_FCT	Market Master/Pricing
MARKET_MASTER_PURCH_PRICES	AUDIT_MARKET_MAST_PURCH_PRC	Market Master/Pricing
MARKET_MASTER_PURCHASERS	AUDIT_MARKET_MAST_PURCHASER	Market Master/Pricing
PARTICIPANT_GROUP_MASTERS	AUDIT_PARTICIPANT_GRP_MASTR	Market Master/Pricing
PARTICIPANT_GROUP_MEMBERS	AUDIT_PARTICIPANT_GRP_MEMBR	Market Master/Pricing
PARTICIPANT_GRP_MEMBER_OWNERS	AUDIT_PARTICIP_GRP_MEMB_OWN	Market Master/Pricing
MEAS_EDIT_ACKNOWLEDGEMENTS	AUDIT_MEAS_EDIT_ACKNOWLEDGEMNT	Measurement Edits
MEASUREMENT_EDIT_CUSTOM_PARMS	AUDIT_MEAS_EDIT_CUSTOM_PARMS	Measurement Edits
MEASUREMENT_EDIT_CUSTOM_TIERS	AUDIT_MEAS_EDIT_CUSTOM_TIERS	Measurement Edits
MEASUREMENT_EDIT_CUSTOM_XREFS	AUDIT_MEAS_EDIT_CUSTOM_XREFS	Measurement Edits
MEASUREMENT_EDIT_PARAMETERS	AUDIT_MEASUREMENT_EDIT_PARMS	Measurement Edits
MEASUREMENT_EDIT_TIERS	AUDIT_MEASUREMENT_EDIT_TIERS	Measurement Edits
MEASUREMENT_EDIT_XREFS	AUDIT_MEASUREMENT_EDIT_XREFS	Measurement Edits
MEASUREMENT_EDITS	AUDIT_MEASUREMENT_EDITS	Measurement Edits
OWNER_GOVERNMENTS	AUDIT_OWNER_GOVERNMENTS	Owners
OWNER_MASTERS	AUDIT_OWNER_MASTERS	Owners
OWNER_UPDATE_PARAMETERS