# 10 Rules for Secure Database Schema Design (Before Writing Any Code)
My study on secure architecture awareness continues! This time, I examined the most fundamental layer: **Database Schema Design**.
Are you a **Back-End Developer**? What security considerations do you make when modeling your tables? Or are you a **Security Expert**? How familiar are you with secure database schema architecture?
Secure schema design is a set of principles and policies that protect data at the architectural layer—long before any migration code is written. In this article, we’ll focus on **Securing Database Schema Design**, *not* Database Hardening!
> Designing a secure schema before coding is the foundation of data security throughout the software lifecycle.
Here are the core principles every architect should follow:
### **01.** [**Managing Identifiers & Preventing Enumeration Attacks**](https://cheatsheetseries.owasp.org/cheatsheets/Authorization_Cheat_Sheet.html)
· **Non-disclosure of Sequential Primary Keys (Sequential IDs):** Using predictable, automatically numbered (like BigInt) for public-facing primary keys (PK) introduces **IDOR** (Insecure Direct Object Reference) vulnerabilities and allows attackers to guess user or resource IDs via API requests to access unauthorized data.
· **Use UUIDv4 or ULID for Public IDs:** Ensure that keys exposed in APIs and URLs are unguessable (UUID/ULID). If you rely on bigint for internal join performance, implement a **Dual-ID Strategy**: keep bigint internally and expose a **separate public\_id** column of type UUID to the outside world.
### **Real-World Example**
**Scenario (IDOR & Business Logic):**
An application exposes an endpoint `GET /api/v1/orders/1005`. An attacker increments the integer to `1006` to attempt viewing another user's invoice. Also, a competitor creates an account today (ID: 50) and another tomorrow (ID: 70), only to discover that the platform gained only 20 new users that day. Easy peasy lemon squeezy!
**Solution:**
Keep internal IDs fast with integers; keep public IDs safe with UUIDs.
### **02.** [**Data Classification & PII Isolation**](https://csrc.nist.gov/pubs/sp/800/122/final)
· **Isolate Sensitive Data:** Storing Personally Identifiable Information (PII)—such as full names, national IDs, phone numbers, and email addresses—in the same table as non-sensitive operational data expands the risk of data leaks. Isolate PII into dedicated, access-controlled tables.
· **Column-Level Encryption & Hashing:** Clearly designate at the schema level which columns require **Application-Level Encryption** or **Database-Level Encryption at rest**. Ensure authentication credentials (e.g., passwords or tokens) are strictly stored as salted cryptographic hashes (e.g., Argon2id or bcrypt).
### **Real-World Example**
**Scenario(Over-privileged Access & Unencrypted PII):**
A developer runs a simple `SELECT * FROM users` query to display public usernames on a leaderboard. Because sensitive PII—such as phone numbers, national IDs, and email addresses—is stored in the same table without encryption, this private data is accidentally exposed to analytics tools, application logs, or internal staff.
**Solution:**
Separate public account data from sensitive PII into dedicated tables, and explicitly define encryption rules for sensitive fields in your data model.
### **03.** [**Data Validation & Strict Schema Constraints**](https://cheatsheetseries.owasp.org/cheatsheets/Input_Validation_Cheat_Sheet.html)
· **Enforce Strict String Lengths:** Avoid using unbounded text types (e.g., unlimited `TEXT` or `VARCHAR`) for standard user input fields. Defining strict max lengths reduces ReDoS (Regular Expression Denial of Service), Buffer Overflow vectors, and excessive storage consumption.
· **Database-Level CHECK Constraints & Nullability:** Never rely only on application-level validation. Enforce `NOT NULL` constraints by default and use database `CHECK` constraints to validate formats (e.g., regex patterns for URLs/emails or value ranges) directly at the database engine level.
### **Real-World Example**
**Scenario (Payload Injection & Application Bypass):**
If you rely only on frontend or backend code to validate input, an attacker can bypass those checks—or a developer might forget them in a new API. This allows harmful data, like malicious scripts or oversized text, to be injected directly into your database.
**Solution:**
Set strict data types and check constraints directly inside the database table definition as a final safety step.
### **04.** [**Referential Integrity & Delete Behaviors**](https://www.postgresql.org/docs/current/ddl-constraints.html)
· Always explicitly define Foreign Key rules (`ON DELETE` / `ON UPDATE`). Automatically deleting related records using [`CASCADE`](https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html) can accidentally wipe historical data (for example, invoices), while missing rules can create orphaned data. Choose safe constraints, like `SET NULL` or `RESTRICT`, to preserve data integrity.
### **Real-World Example**
**Scenario (Orphaned Data & Clear Financial History):**
An application allows users to delete their accounts. If the foreign key on the orders table uses `CASCADE`, deleting a user instantly clears all their order history, destroying financial records and audit logs. On the other hand, without a foreign key rules definition, deleting the user leaves orphaned order records attached to a non-existent user\_id.
**Solution:**
Explicitly set foreign key constraints (such as `ON DELETE RESTRICT` or `ON DELETE SET NULL`) to protect critical business records while maintaining referential integrity across related tables.
### **05. System Auditability & Soft Delete Strategy**
· [**System Auditability Columns**](https://utmstack.com/nist-800-53-controls/#:~:text=AU%2D12%20Audit%20Record%20Generation.%20AU%2D12%20focuses%20on,or%20inconsistent.%20System%20and%20Information%20Integrity%20family.)**:** All critical tables must explicitly include `created_at` and `updated_at` metadata columns, automatically populated by the system using `CURRENT_TIMESTAMP`. This ensures **reliable** event tracking and prevents developers or users from manually tampering with timestamp history.
· [**Soft Delete Policy (deleted\_at)**](https://gdpr-info.eu/art-17-gdpr/#:~:text=Where%20the%20controller%20has%20made%20the%20personal,to%20inform%20controllers%20which%20are%20processing%20the)**:** Implement a soft delete strategy using a `deleted_at` column to safeguard data against **accidental human errors** or **SQL Injection attacks**. Hard deletes should never happen immediately. Instead, automated background tasks should remove the data only after the required legal retention period ends.
### **Note:**
> **Soft Delete:** Marks records as deleted by setting a deleted\_at timestamp while keeping the data intact for recovery and auditing.
>
> **Hard Delete:** Permanently purges records from the storage disk, making data recovery impossible without backups.
### **Real-World Example**
**Scenario (Irreversible Data Loss & Missing Audit Trail):**
A system administrator accidentally runs an unconstrained `DELETE FROM users` query, or a compromised backend account executes a destructive SQL Injection. Without a **soft delete** mechanism, critical business records are immediately **lost**. Furthermore, without **system-managed timestamp** **metadata**, security teams cannot determine **when** records were created or modified during an incident investigation.
**Solution:**
Implement immutable `created_at/updated_at` timestamps using database-level defaults (`CURRENT_TIMESTAMP`), and apply a soft delete pattern (`deleted_at IS NULL`) across all application queries. This preserves data for auditability and allows instant **recovery** from unauthorized or accidental deletion.
### **06.** [**Schema-Level Access Control & Secure Views**](https://cheatsheetseries.owasp.org/cheatsheets/Authorization_Cheat_Sheet.html)
· **Row/Column Level Security (RLS/CLS) Architecture:** Design database schemas to natively support Row-Level Security (RLS) and Column-Level Security (CLS). For instance, including a mandatory `tenant_id` column across multi-tenant tables enforces strict data isolation at the database level, ensuring users can **only access** their **authorized records**.
· **Secure Views Abstraction:** Avoid giving application users or external integrations direct read access to underlying base tables containing sensitive attributes. Instead, expose restricted Secure Views that mask or exclude sensitive columns (such as PII, hashed passwords, or financial data).
### **Real-World Example**
**Scenario (Cross-Tenant Data Exposure & Excessive Privilege):**
In a SaaS application, a developer forgets to add `WHERE tenant_id = ?` in a complex search query, or an attacker manipulates API parameters. As a result, User `A` gains unauthorized access to Customer `B`'s confidential data. Similarly, granting a reporting dashboard direct table access exposes raw password hashes or credit card metadata.
**Solution:**
Enforce Row-Level Security (RLS) directly in the database engine using policies tied to session variables (e.g., `tenant_id`). Regardless of application-level bugs, the database automatically filters out unauthorized rows. Additionally, grant the reporting service access only to a Secure View that exposes sanitized, un-sensitive metrics.
### 07\. [Least Privilege Schema Design](https://csrc.nist.gov/pubs/sp/800/53/r5/upd1/final) (Avoiding Sensitive Data Co-location)
· **Decoupling Sensitive and Non-Sensitive Attributes:** Avoid placing sensitive data (e.g., credit card numbers, IBANs) in the same table as non-sensitive operational attributes (e.g., user preferences or UI themes) just for denormalization or performance optimization. Grouping these attributes within a single row forces access control systems to grant read permissions over sensitive columns even when a service only requires non-sensitive data.
· Schema Normalization for partial access: Normalize database schemas to physically isolate high-risk attributes into dedicated, restricted tables. This separation ensures that application modules operate strictly under the Principle of Least Privilege (PoLP), limiting exposure whether from an over-privileged internal query or a cyber attack.
### **Real-World Example**
**Scenario (Over-privileged Access via Denormalization):**
Imagine a single user's table that stores non-sensitive data (like `username` and `theme_color`) right next to sensitive financial data (like c`redit_card_hash`).
When the application's UI service queries the table just to get the user's preferred theme, it reads the entire row—which includes the credit card information. If an attacker exploits a flaw in the UI service or executes a **SQL injection** through the theme settings, they instantly gain access to the sensitive financial data sitting in that same row.
**Solution:**
Separate the schema into **two distinct tables**: a **user\_preferences** table (for UI settings) and a restricted **user\_payment\_methods** table (for financial data).
By giving the UI service access only to the user\_preferences table, even a successful attack on the UI module cannot reach the payment data—effectively stopping the breach at the database level.
### **08. Data Lifecycle Management (TTL, Automated Expiration & Purging)**
· [**Data Expiration & Retention Architecture:**](https://www.iso.org/standard/27001) Database schemas must natively support automated data lifecycle management to comply with privacy regulations and minimize breach impact. Temporary and short-lived records—such as authentication tokens, user sessions, temporary audit logs, and One-Time Passwords (OTPs)—should explicitly incorporate timestamp columns (e.g., `expires_at`) or Time-To-Live (TTL) mechanisms.
· [**Automated Purging for Storage Limitation:**](https://gdpr-info.eu/art-5-gdpr/) Storing stale or expired sensitive data creates unnecessary liability and increases the attack surface. Schemas must be architected so that background cleanup processes, database TTL indexes, or automated partitioning policies can efficiently identify and hard-delete expired records without degrading database performance.
### **Real-World Example**
**Scenario (Indefinite Storage of Temporary Credentials):**
A system stores password reset tokens and active session IDs in a `user_sessions` table without defined expiration timestamps or background cleanup jobs. Over time, millions of expired tokens accumulate in the database. If an attacker breaches the database via an unpatched vulnerability, they can execute offline analysis or session hijacking using valid-looking residual tokens that were never properly invalidated or erased.
**Solution:**
Design the `user_sessions` schema with an indexed `expires_at` column (or leverage native TTL mechanisms like Redis TTL or MongoDB TTL indexes). A scheduled background process or automated database policy continuously purges rows where `expires_at < CURRENT_TIMESTAMP`. This ensures that even in a worst-case database leak, no usable or expired authentication credentials remain on disk.
### **09.** [**Index Security & Information Leakage Prevention**](https://cheatsheetseries.owasp.org/cheatsheets/Cryptographic_Storage_Cheat_Sheet.html)
· **Information Leakage via Database Indexes:** Creating database indexes (e.g., `CREATE INDEX`) directly on encrypted or hashed sensitive attributes can unintentionally expose data patterns or raw values. Standard, unsalted hashes always produce the same output for the same input. If an attacker gains access to these database indexes, they can easily use precomputed dictionary lists (Rainbow Tables) or pattern analysis to reverse the hashes and reveal the original sensitive information.
· **Secure Indexing Architecture:** To enable efficient searching over sensitive records without compromising confidentiality, build search indexes using strong, keyed cryptographic constructions—such as Keyed-Hash Message Authentication Code (HMAC) with a secret key or blind indexing techniques. This ensures the index remains cryptographically secure and unsearchable to unauthorized parties even if database files are leaked.
### **Real-World Example**
**Scenario (Standard Hashes are Easily Guessed):**
To protect users' National IDs while still allowing search, a developer saves a "hashed" (scrambled) version of the IDs in the database index. The problem is that standard hashes always produce the same output for the same ID. If hackers steal the database, they can use a "Rainbow Table"—a massive, pre-calculated cheat sheet of all possible IDs and their hashes—to easily translate the scrambled text back into real National IDs.
**Solution:**
Instead of a standard hash, use a Keyed-Hash (like HMAC). This method mixes the National ID with a unique "Secret Key" before hashing it, and only stores this final result in the index. The secret key is kept safely outside the database. Now, even if hackers steal the database, their cheat sheets (Rainbow Tables) are completely useless because they don't have your secret key to reverse the data.
### 10\. Database Migration Security & Schema Versioning
· **Automated & Version-Controlled Schema Migrations:** All database schema modifications must be executed through version-controlled migration scripts tracked in source control (e.g., Git) rather than making manual and direct changes in production environments. Changing database structures manually or without code review greatly increases the risk of security mistakes—such as accidentally exposing private columns, removing access restrictions, or breaking compliance rules.
· **Traceability & Migration Safety Controls:** Applying infrastructure-as-code principles to database schemas ensures complete auditability and repeatability across environments. Automated CI/CD deployment pipelines must validate, test, and apply migration scripts, eliminating human error and ensuring that schema changes adhere to defined security standards before reaching production.
### **Real-World Example**
**Scenario (Production Misconfiguration via Manual Changes):**
During an urgent system update, a database administrator manually alters a production table using a raw SQL statement to add a new column for temporary debugging. In the process, they forget to apply the necessary Row-Level Security (RLS) policies or column permissions. Because the change was made manually outside of source control, it bypasses code review, goes unrecorded, and leaves a persistent security hole in the live environment.
**Solution:**
Enforce a strict policy where production databases accept schema changes only via automated migration tools (like Flyway, Liquibase, or ORM migrations) built into an automated CI/CD pipeline.
Under this approach, every schema update is saved as a version-controlled code file in Git. It must pass a peer code review and be tested in a staging environment before going live. This process creates a clear audit trail and guarantees that no manual changes ever happen in production.
Following these rules won't solve all your life problems, but at least your database won't end up on the front page of the news tomorrow!