Databases and data security in applications

Almost every application exists to create, read, update and delete data, and most of the data that attackers want sits in databases behind applications. The CISSP body of knowledge has long treated database security as part of software development security because the protections are designed into the application and the database together: the data model, the transactions, the queries, the accounts the application uses and the views it exposes. This lesson covers the database concepts a security manager should know, the classic database-specific threats of aggregation and inference, and the techniques used to protect data inside applications.

CISSP-111 covers data classification, data states and protection methods at the asset level, and CISSP-112 Lesson 6 covers database servers as part of system vulnerability management. CMPUT-291 teaches database design and SQL in depth. Here we focus on where development decisions create or reduce risk.

Database models and terminology

A database management system (DBMS) stores data and controls access to it. Relational databases organize data into tables (relations) of rows (tuples) and columns (attributes). Each table has a primary key that uniquely identifies each row; a foreign key in one table refers to the primary key of another and enforces referential integrity, so that, for example, a refund cannot reference a payment that does not exist. Normalization organizes tables to reduce redundancy and update anomalies. A view is a stored query that presents a subset or transformation of data, and is a powerful security tool because users can be granted access to the view without access to the underlying tables. The data dictionary or catalog holds metadata describing every table, column, view and permission.

Other models appear on the exam and in practice. Hierarchical databases organize records in parent-child trees; network databases allow more general links; object-oriented databases store objects with their methods. NoSQL databases, which became popular for large-scale web applications, include key-value stores, document databases, wide-column stores and graph databases. They often trade some consistency guarantees for scale and flexibility, and many early deployments shipped with authentication disabled by default, leading to large data exposures from databases left open on the internet. The security principles are the same across models: authenticate every connection, grant least privilege, protect data in transit and at rest, validate queries and monitor access.

Transactions and the ACID properties

A transaction is a group of operations that must succeed or fail as a unit. Moving money from one account to another involves a debit and a credit; doing only one of them corrupts the books. Relational databases guarantee four properties for transactions, known by the acronym ACID.

Supporting mechanisms include commit (make the transaction permanent), rollback (undo it), savepoints, two-phase commit for transactions spanning several databases, locking to manage concurrent access, and transaction logs that allow recovery. Lesson 7 described a race condition in which two refund requests together exceed the original payment. A transaction that checks and updates in one atomic step, with constraints enforced by the database, closes that gap.

import sqlite3
# Atomic refund with a database constraint: over-refunding is impossible (defensive pattern)
db = sqlite3.connect(':memory:', isolation_level=None)
db.execute('''CREATE TABLE payments (id TEXT PRIMARY KEY, amount_cents INTEGER,
              refunded_cents INTEGER DEFAULT 0 CHECK (refunded_cents <= amount_cents))''')
db.execute("INSERT INTO payments (id, amount_cents) VALUES ('P-77', 12550)")
def refund(pid, cents):
    try:
        db.execute('BEGIN IMMEDIATE')
        cur = db.execute('UPDATE payments SET refunded_cents = refunded_cents + ? WHERE id = ?', (cents, pid))
        if cur.rowcount != 1:
            raise ValueError('unknown payment')
        db.execute('COMMIT')
        return 'committed'
    except (sqlite3.IntegrityError, ValueError) as e:
        db.execute('ROLLBACK')
        return 'rolled back (' + str(e).split(':')[0] + ')'
for cents in (10000, 2000, 1000):
    print(f'refund {cents / 100:7.2f}:', refund('P-77', cents))
print('refunded total:', db.execute("SELECT refunded_cents FROM payments WHERE id='P-77'").fetchone()[0] / 100)

refund  100.00: committed
refund   20.00: committed
refund   10.00: rolled back (CHECK constraint failed)
refunded total: 120.0

Database access from applications

Applications usually connect to databases through a service account. The security of that account is a design decision with large consequences. Each service should have its own account with only the privileges it needs: the reporting service can read selected views, the refunds service can update the refunds and payments tables but not drop them, and only the migration process can change schemas. Database administrators should use separate named accounts with strong authentication and logging, not the application account. Credentials come from a secrets manager or, better, short-lived identity-based authentication rather than a password in a configuration file.

Database interfaces and middleware, such as ODBC, JDBC and the drivers used by ORMs, all support parameterized queries, encrypted connections and certificate validation, but these protections must be configured. Stored procedures can enforce business rules and restrict what an application can do, provided they do not build dynamic SQL from their inputs. Database activity monitoring tools record queries and can alert on unusual access, such as a service account suddenly reading an entire customer table.

Aggregation and inference

Two threats are specific to collections of data. Aggregation occurs when individually non-sensitive items, combined, become sensitive. A list of employees' names is low risk, and so is a list of office locations, but combined with schedules and security clearances they may reveal far more. A single merchant's daily sales total may be harmless, while the full set of totals across all merchants in a city reveals competitive intelligence. Controls for aggregation include classifying the combined data at the higher level, restricting bulk queries and exports, rate limiting, and monitoring for unusually large result sets.

Inference occurs when a user deduces information they are not authorized to see from information they are authorized to see. Classic examples include deducing an individual's salary from department averages when the department has only one or two members, or deducing a confidential project from a sudden change in staff assignments. Controls include suppressing small cells in reports, adding noise or rounding to statistics, restricting queries that isolate small groups, polyinstantiation (described below), and careful design of what summary data is exposed. Differential privacy, a formal technique that adds calibrated random noise to query results, provides mathematical limits on what can be inferred about any individual and is used by statistical agencies and large technology companies.

from statistics import mean
# Small-cell suppression in a summary report to limit inference (illustrative data)
refunds = {   # city -> refund amounts per merchant this month
    'Northport': [1200, 950, 1430, 870, 1010],
    'Lakeview':  [640, 720, 910],
    'Ashford':   [2875],
}
MIN_CELL = 3
print(f"{'City':10s} {'merchants':>9s} {'average':>9s}")
for city, vals in refunds.items():
    n = len(vals)
    avg = f'{mean(vals):9.0f}' if n >= MIN_CELL else f"{'suppressed':>9s}"
    print(f'{city:10s} {n:9d} {avg}')
print(f'Rule: averages shown only where at least {MIN_CELL} merchants contribute.')

City       merchants   average
Northport          5      1092
Lakeview           3       757
Ashford            1 suppressed
Rule: averages shown only where at least 3 merchants contribute.

Polyinstantiation

Polyinstantiation means allowing several rows, or several versions of a data item, with the same primary key but different classification levels. It comes from multilevel secure databases used in defense settings. If a low-clearance user tries to create a record that already exists at a higher classification, refusing the insert would reveal that a secret record exists, which is itself an inference channel. Instead, the database stores a separate low-level version, and each user sees the version appropriate to their clearance. The exam standard example is a shipment recorded as routine supplies for uncleared users and as its real contents for cleared users. In commercial applications the same idea appears in more modest forms, such as presenting different levels of detail to different roles from the same record.

Protecting data within applications

Beyond access control, several techniques reduce the impact if data is exposed. Encryption at rest protects storage media and backups; transparent database encryption protects against theft of files but not against anyone who can query the database. Field-level or column-level encryption protects specific sensitive values, such as bank account numbers, so that only services holding the key can read them. Tokenization replaces a sensitive value with a random token and stores the real value in a separate, tightly controlled vault, which is why payment card environments use it to shrink the systems in scope for card industry assessments. Masking shows only part of a value, such as the last four digits. Hashing with a secret key allows matching without storing the original, for example to check whether an account number has been seen before.

APIs deserve particular attention because they are now the main route by which applications expose data. The OWASP API Security Top 10, last updated in 2023, is led by broken object-level authorization, the API equivalent of the insecure direct object reference. Other common API failures include excessive data exposure, in which an endpoint returns entire records and relies on the client to hide fields, and missing limits on resource consumption. A secure API returns only the fields the caller needs, enforces authorization for every object and property, and applies rate and size limits.