IT auditing and controls: Database technology and controls

A database management system (DBMS) is a complex set of software programs that control the organization, storage and retrieval of data in a database. It also controls the security and integrity of the database. Understanding how database management systems work is essential for anyone looking to pursue their ISACA CISA certification.

This article will not explain database technology, which would involve a lengthier technical discussion. Instead, this serves as an introduction for IT auditors, covering some of the core concepts they need to grasp before performing a DBMS audit.

Earn your CISA, guaranteed!

Get your CISA live online or on-site, backed with an Exam Pass Guarantee!

View Pricing

Essential database terminology and definitions

But first, to understand DBMS, there are some database terminologies and definitions you need to understand:

• Concurrency control: Concurrency control involves the class of controls used in database management systems (DBMS) to ensure that transactions are processed in an atomic, consistent, isolated and durable manner (ACID). This implies that only serial and recoverable schedules are permitted and that committed transactions are not discarded when undoing aborted transactions.
• Data structure: Data structure refers to the relationships among files in a database and among data items within each file.
• Database: A database is a stored collection of related data organizations and individuals need to meet their information processing and retrieval requirements.
• Database administrator (DBA): The DBA is an individual or department responsible for the security and information classification of the shared data stored on a database system. This responsibility includes the design, definition and maintenance of the database, including cloud database management, security compliance and performance tuning.
• Database specifications: These are the requirements for establishing a database application. They include field definitions, field requirements and reporting requirements for the individual information in the database.
• Foreign key: A foreign key is a value that represents a reference to a tuple (a row in a table) containing the matching candidate key value (in the relational theory, it would be a candidate key, but in real DBMS implementations, it is always the primary key). The problem of ensuring that the database does not include any invalid foreign key values is, therefore, known as the referential integrity problem. The rule that maintains the integrity of relationships between tables is known as a referential constraint. The relation (table) that contains the foreign key is referred to as the referencing relation, and the relations that contain the corresponding candidate key are the referenced relation or target relation.
• Normalization: This is the elimination of redundant data, which is key for those looking to design efficient and maintainable databases.
• Repository: The repository is a central database that stores and organizes data.
• Transaction log: A transaction log is a manual or automated log of all updates to data files and databases.
• Tuple: A tuple is a row in a database table.

Three basic types of database management systems

When we speak about Database Management Systems (DBMS), there are three basic types:

Hierarchical
A hierarchical database is one structured in a tree/root or parent/child relationship. Each parent can have many children; however, each child may have only one parent.

Network
In a network, the basic data modeling construct is called a set. A set is formed by an owner record type, a member record type and a name. A member record type can have that role in more than one set, so a multi-owner relationship is allowed. An owner record type can also be a member or owner in another set. Usually, a set defines a 1:N relationship, although one-to-one (1:1) is permitted. A disadvantage of the network model is that such structures can be highly complex and difficult to comprehend, modify or reconstruct in case of failure.

Relational
This model is based on set theory and relational calculations. A relational database allows for the definition of data structures, storage/retrieval operations and integrity constraints. In such a database, the data and relationships among these data are organized in tables.

Here's a slightly more detailed breakdown:

• A table is a collection of rows, also known as tuples, and each tuple in a table contains the same number of columns.
• Columns, called domains or attributes, correspond to fields.
• Tuples are equal to records in a conventional file structure. In a NoSQL database, it's more common to use the term "record" or "data item" when referring to the equivalent of a tuple.

Earn your CISA, guaranteed!

Get your CISA live online or on-site, backed with an Exam Pass Guarantee!

View Pricing

Characteristics of relational tables

Relational tables have the following characteristics:

• Values are atomic
• Each row is unique
• Column values are of the same kind
• The sequence of columns is insignificant
• The sequence of rows is insignificant
• Each column has a unique name

Some of the advantages of the relational model over the hierarchical and network model are that it is easier:

• For users to understand and implement a physical database system
• To convert from other database structures
• To implement projection and join operations
• To create new relations for applications
• To implement access control over sensitive data
• To modify the database

Essential controls for database integrity and availability

When auditing the controls of a database, the auditor would check to see that the following controls have been implemented and maintained to ensure database integrity and availability:

• Definition standards
• Data backup and recovery procedures
• Access controls
• Only authorized personnel can update the database
• Controls to handle concurrent access problems such as multiple users trying to update the same record at the same time
• Controls are needed to ensure the accuracy, completeness and consistency of data elements and relationships
• Checkpoints to minimize data loss
• Database re-organizations
• Monitoring database performance
• Capacity planning
• Who can access the database without going through the application?

The critical issue of DBA access control

When we speak of who can access the database, we have already identified one of the major audit concerns, which is: what access does the DBA have? The DBA basically has the "keys to the kingdom" and can do anything (read, write, change, delete). What you have to make sure of is that someone is watching and monitoring (logging) the actions the DBA takes, and that the DBA can't either de-activate or access the log.

It's no surprise that access control is the number one issue with database management systems. It's also important to audit disaster recovery and restoration, patch management, change management, incident logging and all the other issues that could impact security or the ease with which a team could perform a root cause analysis in the event of a problem.

Data integrity testing: Relational and referential

Auditors also have to perform some type of data integrity testing. Data integrity testing is a set of substantive tests (NOTE: substantive, not compliance testing) that examine the accuracy, completeness, consistency and authorization of data presently held in a system. There are two common types of data integrity tests: relational and referential.

Relational integrity tests are performed at the data element and record-based levels. They are enforced using data validation routines built into the application or by defining the input condition constraints and data characteristics at the table definition in the database stage. Sometimes, it is a combination of both.

Referential integrity tests define existing relationships between entities in different tables of a database that the DBMS maintains. Referential integrity checks involve ensuring that all references to a primary key from another table actually exist in their original table.

The ACID principle: Four online data integrity requirements

Concerning data integrity in online transaction processing systems, there are four online data integrity requirements, collectively known as the ACID principle.

The "A" stands for atomicity, and from a user's perspective, a transaction is either completed in its entirety or not at all.

The "C" stands for consistency. Basically, all integrity conditions in the database are maintained with each transaction, taking the database from one consistent state into another consistent state.

The "I" stands for isolation. Each transaction is isolated from other transactions, and hence, each transaction only accesses data that is part of a consistent database state.

The "D" stands for durability. If a transaction has been reported back to a user as complete, the resulting changes to the database survive subsequent hardware or software failures.

Get your guide to the top-paying certifications

With more than 448,000 U.S. cybersecurity job openings annually, get answers to all your cybersecurity salary questions with our free ebook!

Get Your Copy

Final thoughts on database auditing

As a parting comment, I would be remiss if I didn't mention how the database gets populated in a test environment. Many times, as I have audited databases, I have found that the production environment was being copied to the test environment to ensure an accurate copy so that changes would not fail once they were moved to production. At least that was the logic presented by the client. They often fail to realize that the security controls during testing are significantly weaker than in production. This could present a problem because there is a mirrored, unprotected copy sitting there for all to see.

As an auditor, you should recommend sanitizing the data before using it in a test.

I hope you've enjoyed this brief overview of DBMS and have an appreciation of some things you might check as an auditor.

Advance your IT auditing career

As an auditor, you may be interested in other roles in the cybersecurity world. If so, Infosec's Cybersecurity certification and skills roadmap can help you understand how to leverage certifications and get the skills you need to build a rewarding career.

Speaking of careers, you can start discovering your earning potential with the Cybersecurity salary guide. Infosec's webinar on the ISACA career path: The highest paying certifications in the industry is a good complement, outlining which certifications give you the highest return on your educational investment.

Ready to start prepping for your CISA certification? The  ISACA CISA Boot Camp gives you everything you need to know to pass your exam and start your career in only five days.