When you explore the simplest definition of a database management system (DBMS), one may find that it is a complex set of different software programs to manage a database that controls the collection, organization, storage, and retrieval of the data. Above these, it also controls the integrity and security of a database. In this article, we are not discussing database technology and DBMS practices but looking into some concepts for the IT auditors to understand while supporting a DBMS auditing process.
DBMS terminology
At the first point, to understand the audit of DBMS, you need to also understand some important database definitions and terminology as below:
- Concurrency Control – This is the set of controls used in DBMS to ensure that the transactions are effectively processed in an atomic, consistent, isolated, and durable way (ACID compliance). This also ensures that only recoverable and serial schedules are allowed and that the committed transactions are executed in full or aborted.
- DB – DB or database is primarily a collection of related data needed for enterprises to the server to their information storage, processing, and retrieval needs.
- Data Structure – This defines the relationships between various database files and the data items inside each file.
- Database Specifications – The set of requirements to establish a DBMS application. It includes field requirements, field definitions, and also reporting requirements for information in databases.
- Database Administrator (DBA) – A technical professional or department responsible for the administration and security of the database and allied systems. The primary responsibilities of DBAs include but are not limited to the design, maintenance, performance, and security of databases.
- Foreign Key – A value that represents the reference to a row in the given database table, which contains a matching key value.
- Normalization – refers to the elimination of any redundant data on databases.
- Repository – Centralized database that stores and organizes the enterprise data.
- Transaction log – Manual or automated log of updated data files and various databases.
- Tuple – The basic unit of a row in the database tables.
While we consider DBMS, there are three basic types: hierarchical databases, network DBs, and relational DBMS. Let us explore each as below. To better understand database models and which one suits you the best, you can also avail the assistance of reliable remote administration consultants like RemoteDBA.com.
Hierarchical DBMS
The types of databases are structured in tree-root or also called parent-child model relationship. In this model, each parent may have more than one child. However, each child may relate to only one parent.
Network DBMS
This is a database modeling that is a known asset. A set is made by an owner record, which has a member record type and also a name. Member records may have the role in various sets, so it can be a multi-owner relationship too. The owner records type can also be an owner or member on another set. Here, a set defines a 1:N relationship even though only one-to-one is allowed. The major drawback of the network DBMS model is that the structure may be complex and tough to comprehend, maintain, modify, reconstruct, etc.
Relational DB
This is a very common and longstanding model in database management based on relational calculations and set theory. Relational DBMS allows relational data structures, storage -retrieval, and some integrity constraints. In a relational database, data and relations among data are organized in tabled format. Tables consist of rows which are also called tuples. Each tuple will have a similar number of columns. The columns are also called attributes or domains, which correspond to different fields. Relational database tables have the below characteristics in general.
- The values in relational tables are atomic
- Each row will remain unique
- Each column values are of the same nature.
- Column sequence is insignificant
- Row sequence is insignificant
- Every column will have a unique name
Some major advantages of the RDBMS (relational database management systems) model over the former two models are:
- It is easy for the database users to implement and understand the physical structure of the database system.
- Easy to convert from other types of database structures
- Easy to implement operations like join and projection
- The creation of new relations for database applications is easy
- Can implement access control over the sensitive data
- Easy to modify the database
While auditing databases, one should check for the most important controls that are maintained to ensure the integrity and availability of data. Some of the essential controls are as below:
- Definition standards
- Procedures for data backup and recovery
- Access controls to ensure only authorized users are getting access to the database.
- Proper controls to handle concurrent access to databases to avoid issues related to multiple users simultaneously accessing and updating the same record.
- Controls to ensure completeness, consistency, and accuracy of data elements.
- Proper checkpoints to avoid data loss
- Reorganization of database
- Monitoring and tracking the database performance
- Database capacity planning.
Database access
While we consider database, as we know, one major audit concern is what level of access privileges does a DBA has? As DBA holds all the keys to the database kingdom, everyone assumes that they can do anything as write, read, edit, delete, etc., on data. However, what you should ensure while accessing these operations is that some are watching. There should be a monitoring mechanism for the actions of DBA too.
It is understood that Access Control is one of the most challenging aspects of database management. We should not also forget to audit the disaster recovery, restoration, change management, patch management, incident logging, and other vital issues an auditing process should cover.
Data integrity
Another major concern which the auditors need to address is to perform data integrity testing. This includes a custom set of substantive tests to examine the completeness, accuracy, authorization, and consistency of data in the DBMS. Data integrity tests are of two types as relational and referential.
- Relational data integrity tests are performed at the levels of elements and data records. This will enable the data validation routines for an application or define the input condition constraints and data characteristics at table definition.
Database Technology
- Referential integrity tests define the existence of various types of relationships between entities in various database tables. Referential integrity checks also ensure that the references to the primary key from another table exist in the original table.
To conclude, as an auditor, you need to have a fundamental understanding of these and, along with all these, make sure that the data is well sanitized before you use any type of audit test.
