6.3Database reliability and security

Since a database holds an organization's core operational data, its design must build in both reliability ("recoverable even if it breaks") and security ("accessible only to legitimate parties"). A DBA must be able to design the backup scheme and redundancy configuration by working backward from business requirements such as tolerable data loss (RPO) and recovery time (RTO), and to design access control, encryption, and auditing together based on the principle of least privilege. This section builds that design-judgment pattern.

6.3.1Backup and recovery

• A full backup duplicates the entire database as of a point in time. Restoration is simple, but taking the backup requires time and storage. A differential backup duplicates everything changed since the last full backup. Restoration needs only two generations ("full + the latest differential"), but the differential grows larger day by day. An incremental backup duplicates only what changed since the last backup (full or incremental). Each backup is small and fast, but restoration requires applying "full + every incremental in sequence," which tends to make restore time longer.
• Combined with write-ahead logging (WAL), a backup regimen that continuously retains transaction (archive) logs alongside full backups enables point-in-time recovery—restoring to any moment just before a failure. The choice of backup scheme is worked out backward from business requirements: the tolerable recovery time (RTO: Recovery Time Objective) and the tolerable data loss (RPO: Recovery Point Objective).

6.3.2RAID and hot standby

• RAID combines multiple disks to improve reliability and/or performance. RAID 1 (mirroring) duplicates the same data across two disks, tolerating the failure of one disk, but usable capacity is halved. RAID 5 distributes data and parity (error-correction information) across multiple disks, allowing recovery from the failure of a single disk (usable capacity equals disk count minus one). RAID 6 doubles the parity, tolerating two simultaneous disk failures. RAID improves reliability but is not a substitute for backups—it cannot rescue against logical mistakes such as accidental deletion or data corruption.
• Hot standby keeps a standby server always running (with data kept synchronized), so that if the active server fails, the system switches over immediately (automatically or manually). It offers a shorter switchover time (RTO) than cold standby, where the standby is not running. In a database, this presupposes a mechanism (an application of replication) that reflects changes on the active server to the standby in real time.

6.3.3Access control, encryption, and audit logging

• GRANT/REVOKE are SQL DCL (Data Control Language) statements that grant and revoke privileges. The basic policy is the principle of least privilege—granting a user or role only the minimum privileges needed, scoped by table, column, and operation (SELECT/INSERT/UPDATE/DELETE, etc.). Rather than giving developers full privileges on a production database, role-based access control (RBAC), granting only the operations required by the job, is the norm in practice.
• TDE (Transparent Data Encryption) encrypts data files and backup files at the disk level, so their content cannot be read even if stolen or improperly copied. It operates transparently to the application (no application changes needed), and it protects a different target than in-transit encryption (TLS)—TDE covers data at rest, TLS covers data in transit. Audit logging records who performed what operation on what data and when, used for detecting unauthorized access, after-the-fact tracing, and compliance.
• SQL injection is an attack in which an application concatenates user input directly into an SQL statement, allowing an attacker to inject unintended SQL (e.g., turning a condition permanently true with something like ' OR '1'='1, or appending an extra DROP statement) to bypass authentication or cause unauthorized data retrieval or tampering. The fundamental countermeasure is parameterized queries (using placeholders/bind variables), binding input values as data rather than as syntactic elements of the SQL statement, which prevents injection. Escaping input values and using a database connection account with least privilege serve as supplementary layers of defense.

Suppose a DBA at a financial services company is tasked with the reliability and security design for a new customer-information database. Business requirements demand a strict standard: "in the event of a failure, data loss must be within 5 minutes (RPO 5 minutes), and recovery within 30 minutes (RTO 30 minutes)." A simple once-daily full backup alone would lose changes made right up to the failure and fail to meet the RPO, so the DBA adopts point-in-time recovery via a full backup plus continuously retained transaction logs, enabling restoration to any moment just before the incident. For the tight 30-minute RTO, restoring from backup alone would never be fast enough, so the DBA introduces a hot standby configuration (a standby kept continuously synchronized, ready for immediate switchover), and for resilience against disk failure itself adopts RAID 5 (tolerating the failure of one disk)—but judges that RAID cannot rescue against logical data corruption or human error, so it is not a reason to forgo the backup regimen. For access control, since developers had a habit of running SELECT statements directly against the production DB for troubleshooting, the DBA enforces the principle of least privilege, using GRANT to allow the developer role only SELECT on the necessary tables, restricting update privileges to a minimal operational account. For sensitive customer data such as names and account information, the DBA encrypts data files and backup files with TDE so the content cannot be read even if a backup medium is stolen or lost, and additionally records who accessed customer data and when via audit logging, serving as an evidentiary trail for financial-regulatory compliance. Finally, having discovered that the customer-facing web app's login screen concatenated user input directly into SQL statements, the DBA judges there is a SQL injection risk and gives top priority to converting the code to parameterized queries using placeholders—since escaping alone is prone to missing new input patterns and is insufficient as a fundamental countermeasure. Working backward from business requirements (RPO/RTO) to the backup and redundancy scheme, and layering least privilege, encryption, auditing, and parameterized queries together, is the essence of this reliability and security design.

6.3.4Section summary

• Choose the backup scheme by working backward from business RPO/RTO requirements. Differential restores in two generations; incremental requires applying every increment in sequence
• RAID provides disk-failure resilience and is not a substitute for backups. Hot standby shortens RTO via immediate switchover
• Design GRANTs around the principle of least privilege, encrypt data at rest with TDE, and use parameterized queries as the fundamental countermeasure against SQL injection