Security, Privacy, Ethics, and Governance

Introduction

Relational databases store some of an organisation’s most valuable assets: identities, financial transactions, health information, educational records, intellectual property, and operational history. Consequently, database security is not merely a technical concern. It is a strategic discipline involving architecture, law, organisational policy, professional ethics, and risk management.

A secure database must protect confidentiality, integrity, and availability. Confidentiality means that information is disclosed only to authorised parties. Integrity means that data remains accurate, complete, and protected from unauthorised alteration. Availability means that authorised users can access data when required. These objectives are often called the CIA triad. They must be balanced: a system that is perfectly confidential but unusable is not successful, nor is a highly available system whose records can be silently changed.

This chapter explains how authentication, authorisation, row-level controls, encryption, auditing, secret management, masking, retention, and secure SQL practices work together. It also examines SQL injection, privacy, bias, ownership, regulatory compliance, least privilege, and ethical data use.

Learning Objectives

By the end of this chapter, students should be able to:

• distinguish authentication, authorisation, accounting, privacy, and governance;
• design users, roles, privileges, and row-level controls in MySQL and PostgreSQL;
• apply encryption, masking, auditing, secret management, and retention controls;
• explain and prevent SQL injection and related application vulnerabilities;
• analyse privacy, ownership, bias, consent, and regulatory responsibilities;
• evaluate a database security design using least privilege and defence in depth;
• develop a practical governance approach for a production database.

1. Security as a Layered System

Database security is best understood as defence in depth. No single control is sufficient. A typical architecture contains several layers:

1. Physical and infrastructure security: protected data centres, operating-system hardening, network segmentation, backups, and patching.
2. Identity security: strong authentication, multifactor authentication, identity federation, and account lifecycle management.
3. Database authorisation: users, roles, privileges, views, stored procedures, and row-level security.
4. Application security: parameterised queries, input validation, secure sessions, and appropriate error handling.
5. Data protection: encryption in transit and at rest, tokenisation, masking, and controlled exports.
6. Monitoring and response: audit logs, anomaly detection, incident response, and recovery testing.
7. Governance: policies defining ownership, classification, retention, lawful use, and accountability.

A useful distinction is between authentication, which answers “Who are you?”, and authorisation, which answers “What may you do?” Accounting or auditing records what was done. Privacy addresses how personal information is collected, used, disclosed, retained, and deleted, including whether the activity is justified and expected by the affected person.

2. Authentication and Identity Management

Authentication verifies an identity. Passwords alone are weak because they can be guessed, reused, phished, or exposed in breaches. Passwords should be long, unique, stored only as slow salted password hashes, and protected by rate limiting. A database should not store plaintext passwords or reversible password encryption.

Multifactor authentication combines independent factors:

• something the user knows, such as a password;
• something the user possesses, such as a hardware security key;
• something the user is, such as a biometric characteristic.

A database application normally authenticates users through an identity provider rather than maintaining a separate database password for every person. Service accounts remain necessary for applications, but they should have narrowly defined permissions, managed credentials, and clear ownership.

Identity lifecycle controls include provisioning, periodic review, suspension, and timely removal. A departed employee whose account remains active represents an avoidable risk. Shared accounts reduce accountability and should generally be prohibited.

3. Roles, Privileges, and Least Privilege

A privilege is permission to perform an operation on an object, such as SELECT on a table or EXECUTE on a procedure. A role is a named collection of privileges. Role-based access control (RBAC) assigns users to roles rather than granting permissions individually.

The principle of least privilege states that each subject receives only the permissions required for an authorised task, for only as long as necessary. It reduces both the probability and impact of misuse. Separation of duties strengthens this principle by ensuring that sensitive activities require more than one person or role—for example, one employee prepares a refund while another approves it.

A practical role model might include:

In PostgreSQL, roles can be created and granted privileges as follows:

CREATE ROLE reporting NOLOGIN;
CREATE ROLE analyst LOGIN PASSWORD 'use-an-external-secret-manager';
GRANT reporting TO analyst;
GRANT SELECT ON customer_summary TO reporting;

In production, passwords should not be embedded in deployment scripts. PostgreSQL supports powerful role inheritance and default privileges; administrators must review inherited permissions carefully. MySQL also supports roles:

CREATE ROLE 'reporting'@'%';
GRANT SELECT ON sales.customer_summary TO 'reporting'@'%';
GRANT 'reporting'@'%' TO 'analyst'@'%';
SET DEFAULT ROLE 'reporting'@'%' TO 'analyst'@'%';

The exact syntax and behaviour vary by version, so implementation should be tested against the deployed database version.

4. Views, Stored Routines, and Row-Level Security

A view can expose only selected columns or filtered rows. For example, a reporting view might omit email addresses and authentication data:

CREATE VIEW customer_summary AS
SELECT customer_id, region, account_status, registration_date
FROM customer;

Views are useful but not automatically sufficient. Underlying table privileges, view security settings, ownership chaining, and query behaviour must be verified.

PostgreSQL provides built-in row-level security (RLS). It restricts which rows a role may select, insert, update, or delete. An illustrative tenant policy is:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_invoice_policy ON invoices
USING (tenant_id = current_setting('app.tenant_id')::integer)
WITH CHECK (tenant_id = current_setting('app.tenant_id')::integer);

USING controls rows visible or targetable by operations, while WITH CHECK controls rows that may be inserted or produced by an update. The application must set the tenant context safely, and privileged roles may bypass RLS depending on configuration. RLS is therefore a control that requires testing, monitoring, and careful administration.

MySQL does not provide PostgreSQL-style general-purpose RLS. Equivalent designs commonly use filtered views, stored procedures, application enforcement, separate schemas or databases, and carefully designed security predicates. Application-only controls are more vulnerable to omission or logic errors, so sensitive systems should use multiple independent controls where feasible.

5. Encryption, Masking, and Tokenisation

Encryption in transit protects data moving between clients, applications, and database servers. TLS certificates must be validated; disabling certificate verification defeats much of the protection.

Encryption at rest protects database files, disks, snapshots, and backups if storage is stolen. It does not necessarily protect data from an already authorised database administrator or a compromised application.

Column-level encryption can protect especially sensitive fields, but it complicates indexing, searching, sorting, key rotation, and recovery. Encryption is effective only when keys are protected separately from encrypted data. A key management service or hardware security module may provide controlled storage, rotation, access logging, and separation of duties.

Hashing is one-way transformation, appropriate for password verification when implemented with a password-specific algorithm such as Argon2id, scrypt, or bcrypt. It is not a substitute for encryption when the original value must later be recovered.

Tokenisation replaces a sensitive value with a surrogate token, while a protected vault retains the mapping. Masking displays only part of a value, such as 1234, and is useful in support interfaces and non-production environments. Masking is not encryption and must not be treated as a complete access-control mechanism.

6. Auditing, Monitoring, and Incident Response

An audit trail records security-relevant events such as logins, privilege changes, failed access attempts, exports, administrative actions, and modifications to critical records. A useful audit record identifies the actor, time, action, target, result, source, and relevant request or transaction identifier.

Audit logs must be protected against alteration. Common controls include append-only storage, restricted write and read roles, centralised log collection, time synchronisation, integrity checks, and retention policies. Logging everything without a review process can create cost and noise; logging too little prevents investigation.

A security monitoring programme should define alert thresholds and escalation procedures. Examples include an unusual bulk export, access from an unexpected region, repeated failed authentication, or a sudden grant of administrative privileges. Incident response normally includes preparation, detection, containment, eradication, recovery, and lessons learned. Evidence must be preserved in a manner appropriate to organisational and legal requirements.

7. Secret Management and Secure Configuration

Secrets include passwords, API keys, database connection strings, private keys, and encryption keys. They should not be committed to source control, placed in client-side code, or embedded in container images. A secret manager should provide controlled retrieval, access logging, rotation, expiry, and emergency revocation.

Configuration should separate development, testing, and production. Production data should not be copied into development environments unless there is a documented, lawful need and strong de-identification. Backups, replicas, exports, temporary files, and monitoring systems must be included in the security review; they frequently become overlooked copies of sensitive data.

Secure defaults include disabling unused accounts, restricting network exposure, applying security patches, using encrypted connections, limiting administrative interfaces, and testing restoration from backups.

8. Secure SQL and SQL Injection

SQL injection occurs when untrusted input changes the meaning of a SQL statement. A vulnerable pattern is:

"SELECT * FROM users WHERE name = '" + input + "'"

If input contains SQL syntax, the application may execute an unintended query. The primary defence is a parameterised query:

SELECT user_id, display_name
FROM users
WHERE name = ?;

The application supplies the value separately from the SQL structure. PostgreSQL drivers commonly use numbered parameters such as $1, while some MySQL libraries use ?; the driver documentation must be followed.

Parameterisation should also be used for inserts, updates, and deletes. Identifiers such as column names cannot normally be supplied as ordinary values. If an identifier must be dynamic, select it from a strict allow-list rather than concatenating arbitrary input. Input validation, least privilege, safe error messages, and web application firewalls are supplementary controls, not replacements for parameterisation.

Other secure SQL practices include:

• avoid granting applications unrestricted administrative privileges;
• validate transaction boundaries and concurrency assumptions;
• use explicit column lists instead of SELECT * for stable interfaces;
• protect dynamic reports and export functions from excessive data access;
• use safe migration processes and review schema changes;
• avoid exposing raw database errors to users;
• test authorization failure paths, not only successful queries.

9. Privacy, Ownership, and Ethical Data Use

Privacy is not simply secrecy. It includes justified collection, transparency, purpose limitation, data minimisation, accuracy, access, correction, deletion where applicable, and protection against inappropriate secondary use. Personal data may include direct identifiers, quasi-identifiers, device information, location, and inferred attributes.

A privacy impact assessment asks:

1. What data is collected and why?
2. Is the purpose necessary and proportionate?
3. What legal or organisational basis permits processing?
4. Who can access the data and under what conditions?
5. How long will it be retained?
6. What harms could result from disclosure, error, or misuse?
7. How can individuals exercise relevant rights?

Laws differ by jurisdiction. Frameworks such as the EU General Data Protection Regulation, the California Consumer Privacy Act and related state laws, HIPAA in relevant United States healthcare contexts, and sector-specific financial regulations impose different obligations. A database professional must not assume that compliance with one framework guarantees compliance with another. Legal counsel, privacy officers, and records-management specialists should be consulted.

Ownership is multidimensional. A data subject may have rights over personal information; an organisation may be the controller or custodian; a department may determine business meaning; and a database administrator may operate the technical system without having authority to use the content. Governance should define data owners, custodians, stewards, permitted purposes, and accountability.

Ethical data use requires more than legal permission. A practice may be technically lawful yet unfair, unexpected, discriminatory, or harmful. Examples include inferring sensitive characteristics, selling behavioural profiles without meaningful understanding, or using educational data to impose high-impact decisions without appeal.

10. Bias and Automated Decision-Making

Database systems often support analytics and artificial intelligence. Bias can enter through historical records, missing populations, inaccurate labels, proxy variables, unequal measurement, or feedback loops. Removing an explicit protected attribute does not necessarily remove bias: postal code, purchasing behaviour, or language may act as proxies.

Responsible practice includes documenting data provenance, measuring error across relevant groups, testing for disparate impact where appropriate, reviewing features and labels, providing human oversight for high-impact decisions, and enabling correction and appeal. Technical fairness metrics involve trade-offs and depend on context; no single metric proves that a system is fair. Organisations should record model purpose, limitations, training data, access controls, and change history.

11. Retention, Deletion, and Data Lifecycle Governance

Data should have a defined lifecycle: collection, active use, archival where justified, and deletion or irreversible anonymisation. Retention should be based on legal, contractual, operational, and risk requirements—not on the assumption that storage is unlimited.

A retention schedule should specify the data class, owner, retention period, legal hold procedure, archival format, deletion method, and evidence of completion. Deleting a row from a primary table may not remove copies in backups, replicas, exports, audit logs, or caches. Therefore, deletion policies must address the complete data landscape and distinguish ordinary deletion from preservation required by litigation or investigation.

Anonymisation attempts to prevent re-identification and must consider linkage with external datasets. Pseudonymisation replaces identifiers but generally remains personal data if re-identification is possible. These techniques reduce risk but do not automatically eliminate regulatory duties.

12. Governance Frameworks and Accountability

Security governance translates principles into repeatable decisions. A mature programme includes:

• data classification, such as public, internal, confidential, and restricted;
• named owners and stewards;
• access approval and periodic recertification;
• change management and secure development standards;
• vulnerability management and patching;
• backup, disaster recovery, and continuity testing;
• vendor and cloud-provider assessment;
• audit, metrics, training, and incident response.

Useful measures include the percentage of privileged accounts reviewed on schedule, time to revoke departed-user access, coverage of encrypted connections, unresolved critical vulnerabilities, backup restoration success, and the number and severity of unauthorised access events. Metrics should support decisions rather than encourage superficial compliance.

Common frameworks include ISO/IEC 27001, the NIST Cybersecurity Framework, CIS Controls, SOC 2 criteria, and sector-specific standards. Frameworks organise control activities but do not replace risk assessment or professional judgement.

Applied Case: University Research Data Platform

Background

A university creates a platform containing student records, research-participant information, and learning analytics. Analysts need aggregate trends, researchers need approved datasets, advisors need records for assigned students, and database administrators maintain the infrastructure.

Problem

Initially, all internal users connect through one shared database account. Analysts can query direct identifiers, development environments contain production copies, and no documented retention schedule exists. A research assistant exports a full table to a personal laptop, creating privacy and security risks.

Analysis

The shared account prevents reliable attribution and violates least privilege. Direct identifiers are unnecessary for most analytical tasks. The export demonstrates that database controls must include endpoints and data flows, not only server permissions. The absence of a retention schedule creates both unnecessary exposure and uncertainty about deletion obligations.

Approach and Solution

The university introduces institutional single sign-on with multifactor authentication and maps users to roles. Advisors receive access only to assigned students through PostgreSQL RLS. Analysts receive aggregate views with masked or removed identifiers. Researchers receive approved, purpose-limited datasets through a controlled request process. Production data is replaced with synthetic or de-identified data in development.

Connections require TLS. Sensitive columns and backups are encrypted, while keys are held separately. A secret manager supplies short-lived database credentials. Audit logs record exports, privilege changes, and access to restricted tables; a central monitoring team reviews alerts. A retention schedule defines deletion, archival, and legal-hold procedures. The data-governance committee reviews research purposes, consent requirements, access requests, and potential bias in learning analytics.

Outcome

The university can attribute actions to individuals, reduce unnecessary exposure, investigate unusual exports, and demonstrate accountable processing. Some convenience is lost: access requests take longer and analysts receive less raw data. These costs are justified by reduced risk and better alignment with privacy and ethical obligations.

Learning Points

Security is a socio-technical system. Role design, row-level controls, encryption, logging, endpoint restrictions, retention, and governance must reinforce one another. A technically sophisticated database cannot compensate for unclear purpose, excessive access, or weak organisational accountability.

Challenges and Limitations

No control is perfect. Encryption does not prevent an authorised user from misusing plaintext displayed by an application. RLS policies can be bypassed by highly privileged administrators or undermined by unsafe tenant context. Audit logs may contain sensitive information and therefore require their own protection. Masking may fail against inference attacks when many fields are combined. De-identification is difficult in high-dimensional datasets. Strict security can also reduce usability, encouraging users to create unsafe workarounds.

Security design is therefore an optimisation under uncertainty rather than a one-time configuration. Threat models, privacy assessments, penetration tests, code review, access recertification, and recovery exercises should be repeated as systems and purposes change.

Contemporary Developments

Cloud-managed databases increasingly provide integrated identity, encryption, monitoring, backup, and policy services, but responsibility is shared between provider and customer. Confidential computing and hardware-backed key protection may reduce exposure during processing, although deployment complexity remains significant. Data clean rooms and privacy-enhancing technologies support selected forms of collaboration without unrestricted sharing. Generative AI introduces additional concerns: prompts may contain confidential records, generated SQL may be insecure or overprivileged, and automated decisions may be difficult to explain. AI-generated queries must be reviewed, parameterised, tested, and executed under a constrained role.

Best-Practice Checklist

• Classify data before designing access.
• Use central identity and multifactor authentication where appropriate.
• Grant permissions through reviewed roles, not ad hoc individual grants.
• Apply least privilege, separation of duties, and periodic recertification.
• Use views, stored procedures, and row-level controls for restricted access.
• Encrypt connections, storage, and backups; protect keys separately.
• Use parameterised SQL and allow-lists for dynamic identifiers.
• Keep secrets out of source code and rotate them.
• Audit high-risk actions and protect audit records.
• Minimise collection, define retention, and control all copies.
• Test backups, incident response, access denial, and deletion procedures.
• Assess fairness, consent, transparency, ownership, and human impact.

Chapter Summary

Database security combines confidentiality, integrity, and availability with privacy, ethics, and accountable governance. Authentication establishes identity; authorisation determines permitted actions; auditing records activity. Roles and least privilege reduce unnecessary access, while views, stored routines, and row-level security restrict data by function or tenant. Encryption protects data in transit and at rest, but requires independent key protection. Masking, tokenisation, secret management, secure backups, and controlled exports address common exposure points.

Parameterised SQL is the primary defence against injection. Secure systems also require monitoring, incident response, retention schedules, data classification, ownership definitions, and privacy impact assessment. Legal compliance is necessary but not sufficient: ethical practice considers fairness, consent, proportionality, bias, transparency, and potential harm. Effective governance treats the database as part of a larger socio-technical system and continuously reviews its controls as technology, threats, and purposes change.

Key Terms

Authentication: Verification of a user or system identity.

Authorisation: Determination of which actions an authenticated subject may perform.

Availability: The property that authorised users can access systems and data when needed.

Confidentiality: Protection against unauthorised disclosure.

Data steward: Person responsible for managing data quality, meaning, and appropriate use.

Encryption: Transformation of readable data into protected ciphertext using a key.

Least privilege: Granting only the access necessary for an authorised task.

Masking: Hiding all or part of a value when displayed or used.

Pseudonymisation: Replacing identifiers while retaining a method that may permit re-identification.

RLS: Row-level security that applies access rules to individual records.

Role: A named collection of database privileges.

Separation of duties: Division of sensitive responsibilities among different people or roles.

SQL injection: Manipulation of SQL meaning through unsafe incorporation of untrusted input.

Tokenisation: Replacement of sensitive values with controlled surrogate tokens.

Audit trail: Protected record of relevant actions and events.

Data minimisation: Collecting and using only data necessary for a stated purpose.

Retention schedule: Documented rule specifying how long data is kept and how it is disposed of.

Threat model: Structured analysis of assets, adversaries, vulnerabilities, and possible controls.

Review Questions

Short Answer Questions

1. Distinguish authentication from authorisation and auditing.
2. What is the principle of least privilege?
3. Why is parameterised SQL safer than string concatenation?
4. Differentiate encryption, hashing, masking, and tokenisation.
5. What purposes do USING and WITH CHECK serve in PostgreSQL row-level security?

Long Answer Questions

1. Design a role and privilege model for an online banking database, explaining separation of duties and periodic access review.
2. Explain how encryption, key management, backups, and secret management should be integrated in a production database.
3. Discuss the privacy and ethical risks of using student learning analytics for automated intervention decisions.
4. Compare PostgreSQL RLS with common MySQL approaches to row-restricted access.
5. Develop a database audit and incident-response plan for unauthorised bulk data export.

Analytical / Application Questions

1. An application uses a highly privileged account and constructs SQL by concatenating search terms. Identify the risks and propose a staged remediation plan.
2. A company wants to retain every customer record indefinitely because storage is inexpensive. Evaluate this decision using privacy, legal, operational, and ethical criteria.
3. A predictive model denies loans more often to one demographic group. Explain how database provenance, feature selection, measurement, human review, and governance could be used to investigate and address the issue.

Multiple Choice Questions with Answers

Question 1

Which statement best distinguishes authentication from authorisation?

A. Authentication determines identity; authorisation determines permitted actions.

B. Authentication encrypts data; authorisation backs it up.

C. Authentication records events; authorisation deletes logs.

D. Authentication defines retention; authorisation defines ownership.

Correct Answer: A. Authentication determines identity; authorisation determines permitted actions.

Question 2

What is the primary purpose of least privilege?

A. To give every employee identical access

B. To reduce unnecessary access and limit potential damage

C. To eliminate the need for authentication

D. To make all database queries public

Correct Answer: B. To reduce unnecessary access and limit potential damage

Question 3

Which technique is the primary defence against SQL injection?

A. Displaying detailed database errors

B. Using longer table names

C. Using parameterised queries

D. Granting the application administrator privileges

Correct Answer: C. Using parameterised queries

Question 4

What does PostgreSQL row-level security restrict?

A. Only the physical location of database servers

B. Which rows a role may access or modify

C. The length of database passwords

D. The encryption algorithm used for backups

Correct Answer: B. Which rows a role may access or modify

Question 5

Which statement about password storage is correct?

A. Passwords should be stored in plaintext for easy recovery

B. Passwords should normally be stored using a salted, password-specific hash

C. Passwords should be stored in reversible base64 encoding

D. Passwords should be stored in audit logs

Correct Answer: B. Passwords should normally be stored using a salted, password-specific hash

Question 6

Which control most directly protects database traffic between an application and server?

A. TLS encryption in transit

B. Column masking

C. A retention schedule

D. A database view

Correct Answer: A. TLS encryption in transit

Question 7

Why should encryption keys be protected separately from encrypted data?

A. To make SQL syntax shorter

B. To prevent compromise of the data store from automatically revealing the keys

C. To eliminate the need for backups

D. To permit unauthorised administrators to decrypt records

Correct Answer: B. To prevent compromise of the data store from automatically revealing the keys

Question 8

Which practice is most appropriate for a reporting analyst who needs trends but not identities?

A. Grant unrestricted access to all base tables

B. Provide an approved aggregate or masked view

C. Give the analyst the database administrator role

D. Copy the production database to a personal laptop

Correct Answer: B. Provide an approved aggregate or masked view

Question 9

What is a key limitation of masking?

A. It is identical to multifactor authentication

B. It necessarily destroys every original value

C. It is not the same as encryption and may not prevent inference

D. It prevents all privileged users from viewing data

Correct Answer: C. It is not the same as encryption and may not prevent inference

Question 10

Which statement best describes pseudonymisation?

A. It guarantees that data can never be re-identified

B. It replaces identifiers but may allow re-identification through additional information

C. It is the same as deleting data

D. It prevents the need for access control

Correct Answer: B. It replaces identifiers but may allow re-identification through additional information