Contributed April 16, 2001 by rshields@Pershing.com  

DB2 Audit Program

A. Risk Area
Segregation of Duties

Background
In a DB2 environment, segregation of duties requires the areas of database operation, maintenance, design and security be separated among different functional units.

Audit Objective(s)
1. The reassignment of responsibilities associated with a database environment should be formalized. 
2. DB2 standard profiles should be assigned to individuals according to their responsibilities.
3. A formal method of resolving data definition disputes should be established.
4. Database administration activities should be restricted to those required by that function. 
5. The DB Administrator should be independent of the system development and operation areas.
A.1
Key Control(s)
The operation of a database usually involves the computer operators, database administrator, analyst and programmer, users, system administrators and systems programmers specializing in the technical operation of DB2.  Responsibilities should be divided to ensure no one individual can control processing, especially in a highly centralized environment.  In a DB2 environment, responsibilities could be segregated as follows: operation of the Database to the Computer Operations and/or Scheduling areas, the maintenance (including installation, backup and recovery) to the System Programming or Database administration areas, the database logical design and physical design responsibilities separated into different areas of Database Administration or Data Administration and the security administration functions to the Security Administration area separate from the Database Administration.

Control Understanding

A.2
Key Control(s)
Data Administration/Assignment of Responsibilities: a database environment involves maintaining data independently of the application systems that use it.  Responsibilities formerly performed by the systems analysts and programmers are now divided between them and the database administration function.  In addition, some user responsibilities are transferred to the database administration function.

Control Understanding

A.3
Key Control(s)
The database administration function performs and controls the implemented database.  The function usually includes structuring the schema, determining what features in DB2 are used, and working with users to optimize application and database interfaces.  Database administration also frequently manages the data dictionary.

Control Understanding

A.4
Key Control(s)
Rotation of Duties - rotating functions can reduce the possibility any one individual will be tempted to abuse data.  In the database administration function, technical people can be shifted from one function of the database to another.  This measure is only a recommendation to improve control when the DB2 group is big enough to implement rotation.

Control Understanding

A.5
Key Control(s)
Data Importance and Business Impact Assessment: a risk management exercise will identify and assess the severity of risk in a database environment and will allow a rational approach to assign control procedures.  This exercise should be carried over by users, database analysts and security personnel and can be updated whenever conditions change (e.g., when a new application is proposed).

Control Understanding

A.6
Key Control(s)
Ensure policies, procedures and standards exist to support the day-to-day maintenance of the database in the event primary operations personnel are unavailable.

Control Understanding

B. Risk Area
Data Accuracy and Completeness

Background
The database administrator should ensure accuracy and completeness of data entered into the database. Because of the multiple uses of data, additional resources should be allocated to the validation of input.  The validation rules should be specified with the data definition in the data dictionary/catalog.

Audit Objective(s)
1. The validation rules for each data element should be documented in the data dictionary.
2. Procedures should be established to ensure the consistency of redundant data elements.
3. The DBA should provide a database verification procedure to ensure the consistency between internal index structures and data.
B.1
Key Control(s)
Adequacy of Programmed Input Validation Checks: the need for reliability is increased in a database environment because many programs use the same data for processing.
Input validation checks include reasonableness, limit, range, and anticipation checks comparisons to acceptable values in a table, and cross-reference checks between fields.  DB2 has the possibility of documenting validation checks in the data catalog/dictionary.

Control Understanding

B.2
Key Control(s)
Data Error Handling: data error handling concerns the procedures for and timeliness of examining and correcting detected errors.  This process can be partly manual and partly automated.  The automated procedures use default attributes, which make corrections automatically and then produce messages that ask for verification of correctness.

Control Understanding

B.3
Key Control(s)
Application System Testing reviews the application system's interface with the database.  The testing verifies that DB2 provides accurate and complete data to the application system and that data is properly returned to the database.

Control Understanding

B.4
Key Control(s)
Output Control: multiple users request common information from databases; information can be requested by application programs, utility programs, and query transactions.  Database information can be updated by independent groups, therefore, output controls must not only restrict access to authorized individuals but communicate the reliability and timeliness of the data to users.

Control Understanding

B.5
Key Control(s)
The Database Verifier: DB2 data base verifier is the CHECK utility that reviews database integrity to ascertain designated paths through the data base are complete.  Verifiers are normally used after a database is reorganized or recovered.

Control Understanding

C. Risk Area
Lost Updates

Background
Database technology can permit multiple users to access and update the same data element concurrently.  DB2 has a mechanism to lock and restrict current data usage.  Data base applications must determine appropriate controls are established to ensure the proper sequencing of events.

Audit Objective(s)
1. The DBMS should have a lockout feature to prevent concurrent updates to a single data item.
2. A procedure should exist to ensure the proper sequencing of events in database applications.
C.1
Key Control(s)
Concurrent Data ensures that data elements are not processed in error as a result of multiple users processing the same element concurrently.  Locking prevents concurrent users from accessing data.  In DB2 the mechanism for locking is IRLM.  The COMMIT command is used to release resources under the lock mechanism.

Control Understanding

C.2
Key Control(s)
Deadlock Detection and Resolution: deadlock occurs when two users engage in a processing situation in which each is holding a data element wanted by the other and will not release that data element until receiving the data element the other user is holding.

Control Understanding

D. Risk Area
Inadequate Audit Trails

Background
Database technology has two audit trails: one for the applications using the database and one for operations to reconstruct processing.  The audit trail substantiates previous processing if its integrity is questioned and reconstructs processing if problems occur.  It includes application ID, transaction ID, authorization ID, date, beginning and ending time, control counts and field totals, and terminal ID or job name.  The database audit trail should contain sufficient information to reconstruct transaction processing.  The audit trail needed to accomplish these objectives may be quite extensive.

Audit Objective(s)
1. The audit trail for database application processing should be clearly identified and documented.
2. Also, the retention period for each part of the database audit trail has to be determined.
3. All audit trails should be recorded and audit logs should be maintained.  The "trace" feature should be set to "Yes", and trace classes should be selected to ensure all information required about an audit trail is recorded.
4. The audit trail should trace source transactions to control totals and control totals back to the initiating transactions.
5. The audit trail should provide the evidence needed to reconstruct transaction processing.
6. Audit Trails should be reviewed by the application area, the DB Administrator and/or the Security Officer.
7. Audit Trail logs should be protected against deletion or modification.
D.1
Key Control(s)
Audit Trails: an audit trail in a database environment consists of the application and database log audit trail.  The objective of the audit trail is to substantiate transaction processing, support financial or other critical totals, and provide a means of reconstructing the database after a system crash or major processing error.  The database administration function should assist in the design of the audit trails because the data from both is often needed to prove or restore processing integrity.

Control Understanding

D.2
Key Control(s)
Audit trail trace and records: audit trails definition and logs should be recorded and adequately protected against manipulation.  DB2 powerful (SYSADM) can have access to modify any DB2 member.

Control Understanding

D.3
Key Control(s)
Audit Trails Definition: data elements audit trails are optional, they are defined during design phase (database and applications).  Users, control groups and security officer should participate to define which audit trails are going to be used and on which data elements.

Control Understanding

D.4
Key Control(s)
Ensure the level of system and object auditing in place within the database environment is consistent with business requirements.  Determine if auditing has been enabled on the database.  Determine the level of system auditing in place and whether it is appropriate.  Determine the level of object auditing in place and whether it is appropriate.  Determine the audit trail is reviewed on a regular basis.  Determine storage requirements for audit logs are appropriate.

Control Understanding

E. Risk Area
Unauthorized Access

Background
The basic unit of functional security in DB2 is the authorization ID.  A single user may own one or more authorization ID's or several users may share a single authorization ID.  In turn, each authorization ID is granted privileges to use any one of the varied DB2 capabilities.  Each privilege applies to the ways a user can affect a DB2 database object, whether it's a single element in a table or an entire database.

Audit Objective(s)
1. Users profiles and data access rules should be established.
2. The use of user profiles should be enforced through automated techniques. 
3. The database security function should be established.
4. A methodology for designing database controls should be defined and established.
5. Procedures should exist to identify and report errors, omissions, and fraud to management.
6. Key operations commands should be recorded and guidelines should exist to investigate risky, abnormal or extraordinary usage.
7. The security of the database environment has to be reviewed and tested regularly.
8. Sensitive DB2 functions (SYSADM, SYSCTRL, SYSOPR, DBADM, DBCTL and DBMAINT) activity should be tracked daily or on a periodic basis by the security officer in order to ensure security and data are not compromised.
E.1
Key Control(s)
Access Authorization: access control ensures only authorized individuals access database resources.  Access control in a database environment is usually automated through user profiles, which provide an index showing which resources an individual is authorized to access and can be as specific as the situation warrants.
In a DB2 environment the security measures are only of value if the active data base and the DB2 system data sets are properly protected from access outside the DB2 system (RACF, TOP SECRET, etc.).  Two possible sources of access are trough an online editor and through the execution of a batch job.
Protection of the DB2 system data sets from outside sources should be accomplished by the use of an external and global security system (if not VSAM or MVS passwords are used, but additional control considerations should be evaluated).

Control Understanding

E.2
Key Control(s)
Security Officer Function: the security function, performed by an individual or group, develops user profiles which becomes the specification for designing access-authorization systems.  The function is responsible for documenting database security and monitoring the security procedures that enforce access specifications.  The security officer usually is external to the DP function.
Also, the Security Officer or security administration group should perform reviews to ensure security conditions are not deactivated or modified during DB2 starting or stopping situations.
The security officer should also include within his/her responsibilities the periodic tracking of sensitive profiles, user actions and unsuccessful access attempts logs.

Control Understanding

E.3
Key Control(s)
Security Profile: the security profile matches user needs with database resources.  The profile can be by a user and database resource.  A user profile indicates the resources accessible to that user; a data base resource profile indicates the users that can access a designated resource.  The objective of these profiles is to implement management's security policy through data base technology.

Control Understanding

E.4
Key Control(s)
Passwords/Identification and Authentication: passwords are personal identifiers used to gain access to database resources.  Passwords are changed periodically, assigned to only one individual, and well protected.  The passwords/password file should be encrypted. 
The usage of passwords should be used for a DB2 environment by means of an external security package (RACF, TOP SECRET, etc.).  If native DB2 security controls are being utilized, the following should be verified:
Ensure all users defined to the database are appropriate and valid, have database privileges which are consistent with business requirements, and are subject to appropriate password controls.
* Determine whether the database privileges granted to each user are appropriate.
* Determine whether passwords are regularly changed for all users.
* Determine whether  the default passwords associated with package delivered ID's have been changed since installation.
* Determine groups are used for secondary authorization to ensure users are restricted to data which is required to perform their job functions.
* Determine appropriate authorization procedures are in place for granting administrative authorities (SYSADM, SYSCTRL, SYSOPR, DBADM) to individuals.
* Ensure all users have only one unique identifier.
* Ensure all accounts have descriptive names identifying the purpose or owner of the account.
* Ensure all temporary user/consultant accounts have an expiration date and special purpose/generic log-on accounts are secured to prevent unauthorized access.
* Ensure a process exists and is used to re-verify the need for special access privileges to the database on a periodic basis to ensure the privileges are still needed, justified and approved by management.

Control Understanding

E.5
Key Control(s)
Personal Privacy Requirements: procedures must be established to ensure data collected about an individual for one reason is not used for another purpose without the consent of that individual.  In addition, procedures governing the appropriate retention and destruction of such records must be implemented.

Control Understanding

E.6
Key Control(s)
Data Element Ownership: one individual, job position or organization area should be accountable for each data element.  The security system should ensure its compliance and data integrity.  To avoid potential decision conflicts, the Data Base Administrator should have conflict resolution responsibility.
The User is responsible for defining the meaning of the data element, the access rules (who is authorized to use) and the security rules (what functions they can perform: read, update, delete).
In a DB2 system, the "creator" of a table has all capabilities over all data contained in the table.  By default, the authorization ID of the creator is the owner of the table data and may authorize others for access to the table.  This features should be adapted to the security and access policies and procedures in the Corporation.

Control Understanding

E.7
Key Control(s)
DB2 utilities: DB2 provides System Administrators, Data Base Administrators and System Operators with a number of utilities to monitor and control the DB2 database.  All utilities run as standard MVS batch jobs.  Each utility may be executed by using the DB2 Utilities panel, using the DSNU CLIST under TSO, or using supplied JCL procedure.  Since each utility can affect portions of DB2 data, each utility should be reviewed, evaluated and special processing control considerations taken.  For example, the utility REPAIR should only be used as a last resort.
The user may create and attach a number of software applications, which perform various functions in connection with DB2.  These applications may include security software, performance monitoring, job accounting, edit and validation routines and encryption routines.  These applications may assist DB2 in controlling access to data, preventing system degradation, identification of problem applications, and assuring data accuracy and security.  Because these applications are not part of the DB2 system, their functions cannot be controlled by DB2.  The user must include internal controls over the application functions.

Control Understanding

E.8
Key Control(s)
Ensure the access restrictions applied to database objects are appropriate to the nature and use of the object.  Identify relevant database objects.  Identify the level of access granted to selected objects.

Control Understanding

E.9
Key Control(s)
Verify standards are defined which details the response which should be taken when an unauthorized individual gains access to the database.

Control Understanding

F. Risk Area
Service Level of DBMS Function

Background
The database administrator must verify the database software functions properly, each new DB2 release should be thoroughly tested by the database administrator before it is put into operation.

Audit Objective(s)
1. Each new release of DB2 software should be thoroughly tested.
2. A maintenance contract should exist for DB2.
3. DB2 procedures should be established and the staff trained to identify DB2 problems. 
4. Backup procedures should be developed for use in the event of a DB2 failure. 
5. As part of the installation procedure, a user-developed automated facility should retrieve the above installation information, record it in a history file, and produce a report for management review.
6. Procedures should be established to monitor the service level to users.
7. Users should be encouraged, by the use of such techniques as varying change-out rates, to shift their non-urgent processing to off-peak times.
8. The organization should explicitly identify the options available to improve degraded service (e.g., data base reorganization).
9. The capacity plan should include adequate provisions for a DB2 installation and implementation.
F.1
Key Control(s)
System Testing reviews the DB2 implementation as well as new releases.  Interfaces with other systems should be tested.  These tests can be submitted to an approval process before implementation.  Whether the software changes are major (such as for version or level upgrades to provide new software functions or enhance existing ones) or minor (to fix a specific problem), procedures are established to the DB2 functions involved.

Control Understanding

F.2
Key Control(s)
Database Standards: database standards govern the documentation of data, the implementation of DB2, and the use of DB2 interfaces.  The standards address the use and structure of passwords, definition of data interface of the data dictionary of the DB2, reorganization, restart and recovery, organization of the schema, and procedures for deviating from the standards.

Control Understanding

F.3
Key Control(s)
System Documentation ensures the successful operation of a database environment and the continuity of processing.  In a database environment, this documentation should cover the data dictionary, schema and sub-schema, reorganization, database verifier, and operational performance of the DBMS.  In online databases, documentation should also address security, privacy, and access capabilities and restrictions.

Control Understanding

F.4
Key Control(s)
Restart/Recovery Procedures: data base integrity can be lost independently of application system failures because of problems in hardware, DB2 operation, and reorganization or as a result of users interacting with the database or its operating mechanism. 
Recovery procedures provide the documentation and step-by-step tasks that must be performed to recover from a database failure.  The procedures generally begin at a point of known integrity and then, using the before-and-after transaction images from the DB2 log, reconstruct processing from the point of known integrity to the point of known failure.
Backup data is not needed for production purposes but is accumulated for use in a database failure.  Backup data includes the DB2 log, copies of the database at fixed points in time, recovery procedure documentation, and application system logs.

Control Understanding

F.5
Key Control(s)
Data Definition/Centralized Coordination of the Data Model: the Data Model is important in establishing performance levels and flexibility to extend applications.  The Data Model should account for the present and future information needs of the organization.  The information should be organized and centrally coordinated to optimize use of the database.

Control Understanding

F.6
Key Control(s)
Maintenance Plan: maintenance occurs on both database hardware and software.  Because the data in the database is accessible to the maintenance personnel during maintenance, procedures should be designed to minimize the risk of intentional or unintentional data base access or modification.

Control Understanding

F.7
Key Control(s)
Operation Documentation: the operation of the database involves several new functions, including organization and reorganization of the database and generation and maintenance of the DB2.  These activities are all technical procedures requiring documentation.  The procedures can be performed using software packages, which also require documentation.  The documentation ensures continuity of processing during problems or personnel turnover.

Control Understanding

F.8
Key Control(s)
Personnel Training: training personnel ensures the individuals controlling or interfacing with DB2 have the appropriate skills.  Everyone involved in data base technology needs some training to optimize and control the database.

Control Understanding

F.9
Key Control(s)
Remote Data Transmission: the terminal, or remote site, must establish control over data that is transmitted to the central site.  This activity typically involves message counts as well as control over the value (e.g.,dollars) transmitted.  In addition, the control also provides assurance that the message is transmitted accurately.

Control Understanding

F.10
Key Control(s)
Central Data Transmission: Central data transmission control ensures the accuracy and completeness of the communications systems for the entire network.  The central function polls the terminals and maintains control by terminal to ensure that users receive control information at the end of a transmission period.  The central data transmission control is generally implemented using vendor-purchased software.

Control Understanding

F.11
Key Control(s)
Database Malfunction Reporting: malfunction reporting is the formalization of data base error reporting.  Malfunction reporting usually identifies problems or malfunctions, their cause, and recommended courses of action.  In some organizations, this function is performed by a group of specialists who perform the error analysis but not the correction.

Control Understanding

F.12
Key Control(s)
Processing Performance Standards: to realize promised performance, a database organization must establish specific processing performance standards and plans to achieve such standards.  This process requires the continual monitoring of actual performance versus standards.
In DB2 the SQL EXPLAIN statement gives information about access paths.  Table access paths and index usage reports may be produced after the invocation of the EXPLAIN statement.  The DBA should review these reports and statistics and should highlight potential inefficiencies.  It can be used (during an online TSO session in DB2I or as a parameter during binding or rebinding).
The DB2PRT utility and the DB2 Performance Monitor (DB2PM) may also be used to retrieve performance monitoring information from SMF records.
In order to properly monitor the performance of the DB2 system, the DBA must assess the impact of applications at design time, run the RUNSTATS/STOSPACE utilities on a planned basis, and set-up pre-developed queries on the DB2 Catalog and reporting from SMF and/or RMF.
The COMMIT statement releases locks which improves performance.  The DBA should recommend the Development area its usage and review adherence to this recommendation.

Control Understanding

F.13
Key Control(s)
Ensure the backup strategy in use will allow the recovery of data in a timeframe consistent with business requirements.  Determine whether the database is being operated in automatic archiving mode.  Determine whether full operating system backups are being taken of database files at regular and appropriate intervals, and that these backups are kept for an appropriate period in an appropriate location.

Control Understanding

G. Risk Area
Design and Development

Background
Database documentation addresses data items and the database structure.  Many organizations use such documentation aids as a DDS (Data Dictionary System).

Audit Objective(s)
1. There should exist a DDS (Data Dictionary System).
2. The information in the DDS should be the same as that used in practice.
3. Data documentation standards should be developed.
4. The organization time-dependent data should be considered into accounting periods.
5. A procedure should exist or established (e.g., time stamping transactions) to ensure that each transaction can be readily identified with the appropriate accounting period.
6. There should exist procedures to avoid and to control duplications.
7. The System Development Life Cycle (SDLC) of an application using DB2 should incorporate a data base design methodology as part of the process.
G.1
Key Control(s)
Program Modification and Maintenance: Program version numbers should be used to ensure that the proper change is installed in the correct version an that the proper program version is placed into operation at the correct time.  Program changes and production versions of the program are controlled by version numbers.

Control Understanding

G.2
Key Control(s)
Application System Testing: Application that access and process DB2 data should be fully tested.  The testing verifies that the DB2 provides accurate and complete data to the application system and that the data is properly returned to the DB2.

Control Understanding

G.3
Key Control(s)
DDS (Data Dictionary System): [NOTE: In a DB2 environment (at least up to DB2 v.2.r.3) there is a Catalog (see background and negative impact sections).]  A DDS is a documentation tool for defining data and its use.  The objective of the DDS is to standardize the definition and use of data and to define data organization, reorganization, and access and other operation information needed to implement a data base environment.

Control Understanding

G.4
Key Control(s)
ACTIVE DDSs: [NOTE: In a DB2 environment (at least up to DB2 v.2.r.3) there is a Catalog (see background and negative impact sections).] An active DDS connects the DDS to the DBMS in a production environment.  A DDS can be offline and used solely as a documentation tool.  In an active mode, however, application systems cannot access or define data unless definitions are processed through the DDS.  An active DDS is one of the strongest controls in a database environment because it can enforce access rules and standardized definitions.  This concept may not be completely practical with currently available software.

Control Understanding

G.5
Key Control(s)
The Formal Design Process: A formal design process outlines all the steps and procedures in the development of a system.  The objective of the process is to use procedures that minimize effort and maximize the probability of building a successful system.
The major phases of database design are conceptual, logical and physical.  Conceptual design includes the determination of the structure of and relationships between entities and is not concerned with the structural requirements and design constraints of an individual DBMS.
Physical design includes adjusting the logical design to the limitations of the individual DBMS and to the hardware configuration (i.e. physical data placement by disk and channel, I/O buffer management, etc.) in order to optimize performance.

Control Understanding

G.6
Key Control(s)
System Implementation Standards: System implementation standards govern the methods for documenting systems and data, data model, developing program specifications; writing, testing, and converting programs; approving program changes; and performing the other related procedures necessary for the successful implementation of a data base application system.  Using an SDLC designed specifically for database environments helps to ensure accuracy and integrity because the processing of each transaction affects a data element is standardized by specified procedures.  If CASE tools are used, they should be implemented according to the existing S.D.L.C.

Control Understanding

G.7
Key Control(s)
Personnel Training: Development personnel should be trained to optimize applications interfacing with DB2 and an appropriate usage of DB2 resources.

Control Understanding

G.8
Key Control(s)
Processing Intent: The intent of processing should be clearly defined at the transaction level and should explain the purpose of each transaction and the processing that occurs on that transaction.  This documentation should be located so that the processing intent for a single transaction is easy to obtain.

Control Understanding

G.9
Key Control(s)
Conceptual Data Independence: Conceptual data independence implies that data needs are developed independently of the application systems.  This independence does not mean that application systems do not feed input to data creation but that the authority to create and modify data is independent of the applications.

Control Understanding

H. Risk Area
Cessation of Processing

Background
As organizations rely more heavily on automated systems, their ability to continue processing without those systems diminishes.  This environment  poses two challenges to the data base administration function.  First, the database administrator must ensure the continuity of processing of the automated systems.  Second, the database administrator must develop procedures for conducting business when the automated systems are not operational.  How completely these two tasks are performed can determine the success of the data base installation.

Audit Objective(s)
1. The impact of a database failure on the organization should be assessed.
2. There should exist procedures to continue business during a database failure. 
3. Sufficient steps should be taken to ensure that data base integrity can be restored after a database failure.  
4. The organization should document the sequence of actions necessary to restore applications after a data base failure.
5. The DBA must keep counts of physical records, pages and table spaces in the database.
H.1
Key Control(s)
Reorganization Utilities: The reorganization utilities are the documentation of the step-by-step tasks needed to restructure the database.  The physical and logical database must be restructured to meet changing user needs.  In addition, the space allocation formula may need to be changed to allocate additional space for changing user requirements.  Periodically, changes are combined and the database is reorganized.  Because of the complexity of reorganization, utility programs are needed.
In a DB2 environment, it is recommended that the RUNSTATS utility be run following a reorganization.  This ensure the subsequent binding of application plans will have the most current information available in the DB2 Catalog.  Also after reorganization, the CHECK utility may be invoked to verify consistency between indexes and table data.

Control Understanding

H.2
Key Control(s)
Application System Failure Procedures: Failures in a database environment can be attributed to the DBMS and application system.  In an application system failure, data base processing can be only partially completed.  The procedures include steps to reinitiate processing, determine the integrity of the application data queues, and back out of or finish partially completed processing.

Control Understanding

H.3
Key Control(s)
Backup Databases: Backup databases are copies of databases at restart points, where database integrity is ensured and recovery can commence.  The backup database is usually created with a dump utility that copies the image of the database onto a new file.
When a database image copy (back-up) is made using the COPY utility, DB2 records information about the image copy in the SYSIBM.SYSCOPY, DB2 Catalog table.
When creating a new full image copy, old entries in the DB2 Catalog containing image copy information may be deleted as an option.  If the option is specified, information concerning only the one or two most recent image copies is kept in the DB2 Catalog.

Control Understanding