Audit of Database Administration

This document is an HTML formatted version of a printed document.
The printed document may contain agency comments, charts, photographs,
appendices, footnotes and page numbers which may not be reproduced in this
electronic version. If you require a printed version of this document
contact the United States Securities and Exchange Commission, Office of
Inspector General, Mail Stop 11-7, 450 Fifth Street N.W., Washington, D.C.
20549 or call (202) 942-4460.

Inspector GeneralSecurities and Exchange Commission

EXECUTIVE SUMMARY

The Securities and Exchange Commission (SEC), Office of Inspector General (OIG), tasked Tichenor & Associates to perform agreed-upon procedures on the information system controls in the database environments of SEC’s Office of Information Technology (OIT). The procedures were to determine the efficiency and effectiveness of controls related to the administration of the OIT database environment using ADABAS and Sybase database management software. Also, we were to assess the operational efficiency of the database environments.

We found that OIT management and administrative personnel were aware of all the conditions noted below and have already implemented some improvements in these areas. For example, in the last year, OIT has established the role of database administrator as a result of a recent organizational restructuring. However, they acknowledged that they are still sorting out the database administration relationships with the objective of establishing sound information system controls over the SEC’s data assets.

RESULTS IN BRIEF

Our review disclosed that the information systems controls of OIT’s database environments would benefit from improvements in some respects, as follows:

Better written policies and procedures for management of SEC data assets and database technical environments.

Stronger separation of duties among the data architecture, data administration, database administration and the applications development functions.

Improved procedures for the management of data in the SEC database environments.

Better policies and procedures to control the database change authorization process.

Improved control over access to the data contained within the SEC database environments.

Updated written procedures to ensure both physical and logical recovery of the SEC database environments.

Better written procedures to ensure data integrity in the database environments.

Establish policies and procedures to increase the effective and efficient operation of the SEC database environments.

Improve the plan to solve the impending year 2000 issues related to SEC database environments by including more specific tasks and milestones for completion.

These matters are discussed in greater detail in the Findings and Recommendations section of our report.

EXIT CONFERENCE

On September 11, 1997, an exit conference was conducted with attendees from the SEC OIG, OIT and Tichenor & Associates. The findings and recommendations of the report were discussed. Verbal changes were offered and, where applicable, the draft report was revised to accommodate the changes.

Inspector General Securities and Exchange Commission

We have performed the procedures which were agreed to by the Securities and Exchange Commission (SEC), Office of Inspector General (OIG), solely to assist the OIG in evaluating the information systems controls of the SEC’s Office of Information Technology (OIT) database environments using ADABAS and Sybase database management software.

SCOPE AND METHODOLOGY

This agreed-upon procedures engagement was performed in accordance with the Comptroller General’s Government Auditing Standards and standards established by the American Institute of Certified Public Accountants (AICPA). We also used standards established by the Information Systems Audit Control Association (ISACA) and applicable industry standards defining best practices for database control, administration and operational efficiency. The sufficiency of those procedures is solely the responsibility of the SEC OIG and SEC management. Consequently, we make no representation regarding the sufficiency of these procedures either for the purpose for which this report has been requested or for any other purpose.

Information system control for a database environment is a process to provide management with reasonable, but not absolute, assurance that organizational objectives for efficiency and effectiveness are being achieved. The management of OIT is responsible for establishing and maintaining a system of information system controls over the database environment. In fulfilling their responsibility, estimates and judgments by management are required to assess the expected benefits and related costs of control policies and procedures against loss from unauthorized use or disposition.

We were not engaged to, and did not, perform an examination. The objective of an examination would be an expression of an opinion on the efficiency and effectiveness of OIT’s information system control structure taken as a whole, which would include such areas as system development, operations, and applications. Accordingly, we do not express such an opinion. Had we performed additional procedures, other matters might have come to our attention that would have been reported. Because of inherent limitations in any system of controls, errors and irregularities may nevertheless occur and not be detected. Also, projection of any evaluation of the control system to future periods is subject to the risk that procedures may become inadequate because of changes in conditions or that the effectiveness of the design and operation of the policies and procedures may deteriorate.

To perform the agreed-upon procedures, we engaged the services of an independent Certified Information Systems Auditor (CISA) to design the review plan, supervise and assist in the performance of the procedures and develop findings and recommendations from the results of the fieldwork. These tasks were performed by the CISA under the review of our firm personnel who are knowledgeable in these matters.

We performed our procedures at the facilities of OIT located at the SEC Operations Center, 6432 General Green Way, Alexandria, Virginia, during the period from April 29, 1997, to July 15, 1997. We performed the procedures listed below to evaluate the effectiveness and efficiency of information system controls in the database environments reviewed as they existed at the conclusion of our fieldwork on July 15, 1997.

Our procedures were used to evaluate the:

implementation of software release updates;

separation of duties within the data administration, data architecture, and database administration functions;

ability of the database administrators to backup one another;

implementation and maintenance of the data dictionaries;

monitoring of database performance and capacity planning;

controls established to ensure the successful migration of new applications, application enhancements, and software corrections into the production environments; and

guidelines and controls over the backup and recovery of the SEC database environments.

Our procedures included interviews of key OIT personnel, review of relevant policy documents, observation of relevant processes and testing of records of database maintenance for completeness and accuracy. We selected judgmental samples of certain records for review. However, we did not infer conclusions about controls examined based on statistical analysis. Instead, we applied auditor professional judgment to the results of our testing to form conclusions about the adequacy of OIT’s management controls based on our experience and the experience of our specialist.

SEC OIT COMMENTS

We provided SEC officials with a revised draft of this report on September 30, 1997, for review and comment. In the SEC response of November 5, 1997, the Associates Executive Director, SEC OIT, indicated that the SEC generally agreed with the findings and recommendations but identified some specific areas where they did not agree. We reviewed their comments and generally found that the explanations offered and the information provided did not require that we revised our findings. However, we did revise Finding 11 to more clearly state the reported condition. We summarized SEC OIT’s position and our response and added this to Finding 11 in our report. We have also included SEC OIT’s response memo as Appendix I.

BACKGROUND

Data managed for the SEC by the Office of Information Technology (OIT) is maintained using two database management system (DBMS) products. The first product, ADABAS, is used to support the SEC applications operating on an IBM mainframe. The second product, Sybase, is used to support applications developed and maintained by OIT in a client-server environment. Sybase is also the database management system supporting the EDGAR application. EDGAR is the system used to collect and maintain information from corporate filings to the SEC and is currently administered by a contractor on-site at the SEC Operations Center.

FINDINGS AND RECOMMENDATIONS

OIT’s technical oversight of the SEC database environment is grouped into five categories: database administration, data description and change control; data access and concurrency control; database availability and recovery; and database integrity. We found control weaknesses in OIT’s administration of data assets, database change authorization, segregation of duties between the data and database administration (DBA) functions; and the continuity of services resulting from the absence of staff or the turnover of contract employees.

DATABASE ADMINISTRATION

Administrative policies and procedures should clearly assign and define the responsibilities for controlling the data assets (actual data) and administering the database environment (DBMS software and associated procedures). Proper segregation of duties should ensure that any one person should not have the authority to use or maintain elements of the database and also maintain the data base structure.

Roles and responsibilities for database administration have not yet been defined in a comprehensive set of policies and procedures.

Documented policies, which identify the roles and responsibilities of individuals responsible for data management, database architecture, and database administration services, ensure the integrity and security of information maintained in corporate database environments. This documentation also provides the foundation for efficient maintenance of database environments and guidance for the determination of user and organizational data access requirements.

We found that the existing documents related to these areas included the DBA Transition Plan and the Implementation of the DBA Function (which apply to the ADABAS environment), the OIT Sybase Development Handbook, and documentation for the EDGAR application. With the exception of those pertaining to the EDGAR database environment, we determined that these documents contained minimal information specific to procedural controls in the SEC database environment. They contained operational parameters of a general nature and did not clearly define the roles of SEC employees.

We concluded that a recent organizational change resulted in an incomplete transfer of knowledge between the previous and current DBA’s. Consequently, none of the documentation was updated or completed by the previous administrators to fully document the processes for which the responsibilities were transferred. As the alternative to adequate ADABAS documentation, vendor product documentation serves as primary reference for the database administrator in performing the daily operation and maintenance of the ADABAS environment.

We also concluded that the documentation of roles and responsibilities of database administration is not being improved because that effort is secondary to the objective of converting and moving existing ADABAS mainframe applications to the Sybase client/server environment. In the current climate, OIT management has placed their operational objectives ahead of improved documentation for database structures.

RECOMMENDATION

We recommend that OIT:

Develop and publish an internal directive that establishes the roles and responsibilities of existing positions in the SEC database environments. This document should set priorities on all the database functions being performed for the conversion to the client/server as well as improving the current mainframe environment in preparation for the conversion.

Applications developers are sharing in duties of database design and maintenance that are more appropriately reserved to separate individuals.

The responsibilities of database administrator, data administrator, and data architect should be segregated among different individuals within OIT. Also, these functions should be distinctly defined because the responsibilities of each of these positions exist as a control on the others.

We found that the applications group holds the unofficial responsibility for the maintenance of the SEC data dictionaries and for the integrity of the data resident in the database environments. However, if the application developers have the ability to directly modify the data dictionary, unnecessary redundancy of data may result when the same data is created and maintained in several locations within the database.

Applications developers and users of the data should have no responsibility for database structural maintenance, access, design and design documentation. However, we found that two individuals in the applications development organization provide support to developers in the areas of logical database design, data management, data architecture, and applications development and support. These individuals also work closely with the vendor consultants on-site at the SEC in the areas of data architecture and data administration.

Other areas of OIT do respect the segregation of database duties. OIT mainframe developers and analysts rely on the designated database administrators for the physical database design and construction within their respective environments. The client/server developers also use DBAs to resolve administrative and data issues. Further, these developers also submit formal, documented, requests to the DBAs for the database services within their database environments.

RECOMMENDATIONS

We recommend that OIT:

Define roles and responsibilities for the separate positions of database administration, data architecture, and data administration.

Provide the positions of database administration, data architecture, and data administration with adequate independence to objectively perform control duties and responsibilities.

Revise the duties of applications support personnel to remove those related to database maintenance, access, design and design documentation.

Organizational ownership and responsibility for the SEC EDGAR database environment has been contracted.

A contract employee performs the function of database administrator for the EDGAR database. This requires OIT to rely on the contractor to control functions that should be under the direct oversight of OIT management and performed in a manner consistent with established OIT standards. Additional controls would be necessary for SEC to retain control while contracting this function.

RECOMMENDATIONS

We recommend that OIT:

Assign an OIT employee to maintain a thorough understanding and knowledge about the SEC data maintained in contractor supported environments, such as standard naming conventions across environments to support the enterprise-wide data model. The employee would also ensure that database administration tools and documentation developed by contractors at SEC expense, such as database maintenance logs, database startup and shutdown procedures and naming conventions, are shared with other similar areas of OIT.

Sufficient cross training between OIT database administrators has not been provided.

OIT management has not provided adequate cross training of substitutes for the employees currently serving as the ADABAS and Sybase database administrators. In the absence of either the SEC ADABAS or Sybase DBAs, the functions are performed by contractor employees of the software vendor, or, for ADABAS, OIT staff from the Systems Software Branch. However, no other SEC employee can provide backup for the Sybase function. Although the EDGAR application is maintained in Sybase, there is no knowledge sharing or transfer between the SEC Sybase and the Edgar DBAs. This results in the inability of these individuals to provide support in the other’s environments.

RECOMMENDATION

We recommend that OIT:

Develop a contingency plan for absence of the DBAs and provide adequate cross training such as rotational assignments for each DBA and designated substitutes.

DATA DESCRIPTION AND CHANGE CONTROLS

The accuracy of information captured and maintained within a database environment relies on adequate control procedures for the creation and modification of data elements and their associated validation criteria. Controls in this area would normally include a complete and accurate data element dictionary, identification of, and requirements for, protecting sensitive data elements, and guidelines, which govern data element change control.

Data elements are not completely defined and documented in the current data dictionaries.

The establishment of a complete and accurate data dictionary sets the standard which ensures uniformity of data elements and provides a tool to the applications programmers to determine the most efficient and effective access to existing information in the database. Adequate data description documentation also provides the basic information required by an enterprise to develop an accurate, enterprise-wide data model. The enterprise model cannot be successfully established without identifying all data elements currently used to support the business applications.

We found that OIT has not maintained the data dictionaries for the ADABAS and Sybase environments. Also, no data administrator has been designated to be responsible for maintaining the data dictionaries.

Alternatively, the EDGAR data dictionary, provided by the EDGAR database administrator, is populated and is adequately maintained by the EDGAR support staff.

RECOMMENDATION

We recommend that OIT:

Update the current data dictionary for all data elements. OIT should also identify a unique owner/organization responsible for the integrity of the data element.

Procedures to control changes in database environments are not formal or consistent.

Changes to the database environment should be monitored to ensure changes do not negatively impact the owners of the data. Once procedures are established to ensure that requests for data administration services are properly completed and authorized, the organization must implement the requests in a timely and efficient manner.

We found that systems developers have created problems by independently modifying the development database. These unauthorized changes triggered failures in applications produced by other developers within OIT. Though the developers have been instructed to request all database changes through the DBAs, they still retain the ability to initiate these types of changes through their own security access levels.

The EDGAR contractor has documented policies and procedures for properly controlling requests for Sybase database administration services. Additionally, the contractor has developed and implemented a database for capturing, tracking, and reporting system change requests. However, all requests are sequentially numbered but only approved requests are tracked for the EDGAR application. This weakens the control value of sequential numbering because an approved but unrecorded request appears to be a disapproved request.

In contrast, similar controls have not been fully implemented for requests for database administration services for OIT ADABAS and Sybase environments. We found that OIT personnel submit forms requesting changes to various elements of databases. However, these requests are not always signed by an authorizing official and, in some cases, no record is made of the disposition of the request. Also, the forms are not filed, numbered, or summarized and reviewed. This information is critical to OIT management to properly assess the functionality of the database environments to determine, for example, whether the same requests appear on a regular basis or DBAs respond in an acceptable amount of time.

RECOMMENDATIONS

We recommend that OIT:

Improve existing forms, policies, and procedures for requesting changes to the SEC database environment so they are consistent across all OIT-managed database environments.

Evaluate the system used by the EDGAR support team for applicability to the SEC managed ADABAS and Sybase environments. If possible, OIT should track all requests for database administration services through the same application. However, this may not be possible due to the contractual support arrangement of the EDGAR environment. At a minimum, OIT should track service requests for all OIT managed SEC database environments through an on-line tracking system.

Number and track all requests for database administration services for the EDGAR environment regardless of approval status. This will guarantee that no requests remain unresolved and will provide an accurate count of the total number of requests submitted by both the EDGAR contractor and the SEC.

DATA ACCESS AND CONCURRENCY CONTROLS

Data, particularly sensitive data, should be protected by an efficient and effective methodology to limit access to the database for authorized purposes only. This includes both direct access by employees and access by production programs.

Existing procedures for controlling access to data are not consistently applied.

Access to data should be strictly controlled through a formal request processed by a designated Security Administrator. The Security Administrator should enforce written policies that are consistent organization-wide. We found that the separate OIT branches do not agree on the point of the control of user access to the database environment. OIT personnel attributed responsibility for this function to either the System Operations group, the requesting SEC office, or the computer specialists within each SEC organization. We attribute this inconsistency to a lack of formal security policies and procedures that generally apply to SEC. Some control functions are present, but in some cases, they have not been fully implemented.

For example, where control functions do exist, we found that they were not consistently applied. We reviewed the file of authorized users to the Sybase production environment and found a number of the fields contained no information, such as the full user name. Also, the file includes several user IDs which are not associated with a specific SEC employee. No documentation was provided which defines the available access levels or the values assigned to the user records.

The Sybase database administrator is in the process of establishing groups within the Sybase environment to improve security and access control. Additionally, a security software package has been installed on the mainframe to assist in controlling access to the ADABAS applications, however, the product has not been fully implemented.

We recommend that OIT:

Establish the position of Security Administrator to enforce and monitor system access and to process user access requests to all SEC database environments.

DATABASE AVAILABILITY AND RECOVERY

Adequate procedures ensure that the database is available when the users require. This includes the timely recovery of the database when minor failures or major disasters occur.

Procedures for both physical and logical recovery of the SEC database environments are not complete.

Written guidelines ensure the availability of SEC databases and their associated applications in the event that recovery of those environments is required. OIT has implemented adequate systems redundancy to ensure the timely recovery and availability of the SEC database environments in the event of a minor systems failure.

However, documented steps to be followed, in the event that the recovery of the SEC database environment was required, have not been fully prepared. We reviewed existing documentation available to OIT personnel in the event of a system failure. These documents were not customized to the SEC database environment and were not approved by OIT management. We concluded that this documentation does not provide sufficient information to ensure the successful restoration of the SEC database environment in the event of a major systems failure, especially by persons not familiar with OIT environments.

OIT has a contingency plan, which includes the ADABAS environment. The current ADABAS DBA stated that no problems were identified with ADABAS during a recovery test in 1996. Also, OIT is preparing to simulate a full recovery of the ADABAS environment. At this time no written recovery procedures are available that are specific to OIT’s installation. The database administrator currently refers to the ADABAS Utilities Manual, which requires specific knowledge of the SEC’s ADABAS implementation.

There are no written policies and procedures to be followed in the event of a failure of the OIT-maintained Sybase production environment. Sybase DBAs must rely on their personal knowledge of the system to restore it. We also noted that there is no off-site backup function for Sybase servers. This makes the total environment vulnerable to a major disaster at the SEC Operations Center.

Scripts have been developed to perform routine database administration functions within the EDGAR environment. However, these scripts, and the procedures involved in executing them, have not been documented.

RECOMMENDATION

We recommend OIT:

Develop and implement a documented disaster recovery and contingency plan for each application within the SEC database environment. At a minimum, the plan should include provisions such as locations of backup application software, backup data, and contacts, among others.

DATABASE INTEGRITY

Organizations should establish controls to ensure that information maintained in database management systems is accurate and that all occurrences of like data are the same. The integrity of data can be achieved through the design of the database and stringent data management.

OIT policies and procedures require additional provisions to ensure the integrity of the data, which resides in the ADABAS and Sybase environments.

Data integrity and synchronization for the database tables are currently the responsibility of the applications development staff. But, no policies and procedures are in place to ensure that all occurrences of like data are correct and properly updated. We suspect that significant data redundancy has resulted from mistrust of the existing control system.

Further, the existing OIT database structures do not provide for data integrity checks. Data elements are typically defined only as alpha or numeric. Data validation and integrity checks are embedded within the applications and defined and managed by the development staff. Where available, developers are aware of standard routines, which will correctly process SEC data. However, documented policies and procedures have not been developed which identify the names, locations and use of these standard routines for the programmers. Developers do share this information with one another but only through informal interaction.

Also, there is no quality assurance (QA) group within OIT so the developers are responsible for testing their own programs. Unit testing is performed by the developers as a group and acceptance testing is completed by the business analysts prior to implementing the product in the production environment.

RECOMMENDATIONS

We recommend that OIT:

Inventory and document all data edit routines used by the applications developers. These routines should reside in specific libraries and directories accessible only to the configuration manager.

Establish and implement a configuration management policy. This policy is critical in application development organizations relying on contractors who are using standard software tools to more quickly create new applications.

EFFICIENCY AND EFFECTIVENESS OF DATABASE OPERATIONS

In addition to information system controls, we noted a number of issues that adversely impact the operation efficiency and effectiveness of the SEC database environments.

Policies and procedures which ensure the effective and efficient operation of the SEC database environments have not been established.

Vendor representatives are currently on-site at the OIT to provide on-going technical support for the SEC ADABAS and Sybase database administrators. ADABAS statistics on performance are captured on a daily basis and maintained electronically for four months. Currently the OIT mainframe is not processing at full capacity, therefore, historically, performance has not been an issue. However, documentation reviewed by the auditors did not provide adequate policies and procedures for the efficient operation and maintenance of the ADABAS environment. Also, no written policies and procedures are in place for capacity planning in the ADABAS environment.

Performance issues on the SEC Sybase databases are usually raised by the Business System Analyst. Due to the smaller size of the computer systems used by the applications developers, an adequate representation of the expected performance of applications, once they are migrated to the larger, more powerful production environment, can not be determined. There are no documented policies and procedures in place to monitor Sybase database utilization for capacity planning purposes. The audit function for Sybase has been activated in the production environment. However, historically there have been no performance issues on the production server.

The EDGAR database administrator does not monitor database performance. This function is performed by Production System Support and reported to the OIT in the EDGAR Project Monthly Progress Report. As with the other SEC database environments, performance has not been an issue in the EDGAR environment.

The SEC Sybase DBA receives product update information, noted problems, and software release data via the Internet. The Sybase DBA is responsible for loading all software updates for all Sybase servers with the exception of the EDGAR server.

The EDGAR DBA receives information on Sybase patches, known problems, product updates, etc., directly from the vendor either via fax or through the Internet. The EDGAR support group receives electronic mail and faxes concerning known problems and information about the Sybase software products. This way, the EDGAR staff can act to prevent potential database problems.

We found that the Sybase and ADABAS DBAs perform services in data administration with minimal communication concerning these activities on each of their respective operating environments. OIT is currently developing data models for the Sybase databases. However, these models must be developed in a fashion which will accommodate information converted from the ADABAS applications at some future date. Also, the organizational structure of OIT segregates the database administration, application development, hardware support, and systems software support by their respective hardware and software environments. This structure does not lend itself to interaction between the various players within the environments for sharing of current information.

RECOMMENDATION

We recommend that OIT:

Establish a mechanism where all the individuals responsible for database administration, data architecture, data maintenance, and database security receive distribution of all vendor product update, release, and known problems for all products used within the SEC database environment.

The plan to address year 2000 issues related to SEC database environments should be improved to include more specific tasks and milestones for completion.

The OIT plan to identify and resolve data and application issues requiring resolution to accommodate the year 2000 is in several related documents that are dynamic and changing. We found that the plan will not ensure that all SEC applications will accommodate transactions using dates in the year 2000 and beyond. The documentation does not include several important features. For example, migration strategies are referenced but not defined in the documents we reviewed. Also, critical target dates for conversion/validation are either not being set or have not been revised when they have not been met.

Further, OIT is relying on the migration of ADABAS applications to the Sybase client/server environment, among other strategies, to address some of the impending year 2000 dilemmas and a mandate to severely reduce mainframe operations. This is especially critical because the plan for this migration is incomplete and no ADABAS applications have been migrated to Sybase at this time.

At a minimum, the following types of enhancements will require design, testing, and implementation in order to ensure accurate cross-century reporting:

Existing date values stored in a two-digit format will require conversion to a four-digit year to incorporate the correct century.

Tables which store date information will require modification in order to store the larger date format.

Tables expanded to accommodate the larger date format will have to be reloaded into the new table structures.

The level of effort required by OIT to successfully complete these enhancements is difficult to estimate because the required modifications are unique to each application. The modifications to be made will likely be labor-intensive because all program code must be reviewed. At this time, OIT has not determined the number of lines of code existing in current applications.

In their response, OIT disagreed that their approach was not effective to address the issues of cross-country computing. They provided examples of certain actions they were taking. They also reiterated that they have a plan that they believe is adequate to address the problem. They did not address our recommendation of updating the plan to include an impact analysis and timeline for completion by March 1999. We revised our finding to state that the plan for addressing the Year 2000 issues could use improvements to more clearly acknowledge the scope and urgency of the challenge.

RECOMMENDATION

We recommend that OIT:

Develop and document a strategy which identifies the extent of the modifications required to successfully process and maintain dated information within its database environment into the next millennium. This strategy should, at a minimum, include:

An impact analysis which determines the level of effort required by OIT and contract resources to identify and implement the changes to the database environments and any affected processes;

An impact analysis which details the expected costs to identify and implement required enhancement to the database environments and affected processes; and

A timeline for the project which establishes milestones for completion of the required work and targets a project completion date no later than second quarter of fiscal year 1998.

DISTRIBUTION

This report is intended for the use of the OIG SEC and SEC management and should not be used by those who have not agreed to the procedures and taken responsibility for sufficiency of the procedures for their purposes. However, this report is a matter of public record and its distribution is not limited.