Database Controls

It is very critical to maintain database integrity and availability. This can be ensured using the following controls:

? Establish and enforce definition standards. In the context of databases, definition standards refer to a set of rules and guidelines that dictate how data is defined, organized, and managed. These standards ensure consistency, accuracy, and efficiency in the way data is stored, retrieved, and manipulated.

? Establish and implement data backup and recovery procedures to ensure database availability. Backups protect against data loss due to hardware failures, software corruption, human errors, and cyber-attacks like ransomware. Regular backups ensure that a business can quickly resume operations after data loss incidents, minimizing downtime and financial impact. Effective recovery procedures allow for quick restoration of data, minimizing operational interruptions and productivity losses. Having a well-planned recovery process reduces the costs associated with data loss, including lost revenue, legal penalties, and reputational damage.

? Establish the necessary levels of access controls, including privileged access, for data items, tables and files to prevent inadvertent or unauthorized access. It limits data access to authorized users only, reducing the risk of unauthorized access and data breaches and allows setting specific permissions for different roles, ensuring that users only have access to the data necessary for their role. It helps in complying with various regulations and standards such as GDPR, HIPAA, and SOX, which often require stringent access controls.

? Establish controls to ensure that only authorized personnel can update the database. To attain this, we need to define roles with specific permissions and assign users to these roles based on their job functions. We also need to enforce strong, complex passwords that are regularly updated and need to implement additional verification methods beyond just a password (e.g., SMS codes, authenticator apps, biometric verification). Encryption for sensitive data to protect it from unauthorized access, even if it is intercepted or accessed inappropriately and Maintenance of detailed logs of all access and modification activities, including who performed the action, what was changed, and when it happened.

? Establish controls to handle concurrent access problems, such as multiple users desiring to update the same data elements at the same time (i.e.,

transaction commit, locking of records/files). These controls include locking of resources (e.g., rows, tables) as soon as a transaction begins, preventing other transactions from accessing them until the lock is released, allowing transactions to proceed without locking resources initially but checks for conflicts before committing changes, rolling back if conflicts are detected, ensuring serializability by dividing the transaction process into two phases – acquiring all the required locks (growing phase) and then releasing them (shrinking phase), assigning timestamps to transactions and ensures that they are executed in a timestamp order to avoid conflicts etc.

? Establish controls to ensure accuracy, completeness and consistency of data elements and relationships in the database. It is important that these controls, if possible, be contained in the table/columns definitions. In this way, there is no possibility that these rules will be violated because of programming flaws or through the usage of utilities in manipulating data.

? Use database checkpoints at junctures in the job stream that minimize data loss and recovery efforts to restart processing after a system failure. They serve as junctures where the state of the database is saved to disk, minimizing the amount of work needed to recover after a crash.

? Perform database reorganization to reduce unused disk space and verify defined data relationships. Database reorganization involves various tasks aimed at improving database performance, optimizing storage, and maintaining data integrity. database reorganization can be done using Defragmentation, Partitioning, Data Compression, Index Reorganization and Rebuilding, Archiving and Purging, Schema Optimization etc.

? Follow database restructuring procedures when making logical, physical and procedural changes. Database restructuring involves modifying the database schema, data organization, or both to improve performance, scalability, manageability, or to meet changing business requirements. We can do it using normalization and denormalization. Normalization splits tables to eliminate redundancy and ensure data integrity. This typically involves organizing data into multiple related tables, while denormalization combines tables to reduce the complexity of queries and improve read performance, particularly for reporting and analytical purposes.

? Use database performance reporting tools to monitor and maintain database efficiency (e.g., available storage space, buffer size, CPU usage, disk storage configuration and deadlock conditions). Database performance reporting tools are essential for monitoring, analyzing, and optimizing database performance. These tools provide insights into various aspects of database operations, including query performance, resource utilization, and system health. Some popular database performance reporting tools are SolarWinds Database Performance Analyzer (DPA), Microsoft SQL Server Management Studio (SSMS), Oracle Enterprise Manager, Redgate SQL Monitor, Dynatrace etc.

? Minimize the ability to use non-system tools or other utilities (i.e., those outside security control, to access the database).