Security, Ethics and Data Governance

Introduction

A database is not merely a technical store of records. It is an institutional asset that may contain identity information, financial details, health records, intellectual property, behavioural profiles and evidence of organizational decisions. A failure to protect such data can cause financial loss, legal penalties, discrimination, reputational damage and harm to individuals. Database security and governance therefore form a strategic responsibility shared by developers, database administrators, security professionals, managers and data subjects.

This chapter examines how SQL systems support authentication, authorization, roles, least privilege, encryption, auditing and SQL-injection prevention. It then extends the technical discussion to privacy, retention, data quality, regulatory compliance and ethical data use. The central principle is that secure data management requires more than adding a password to an application: it requires coordinated controls throughout the data life cycle.

Learning Objectives

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

1. Distinguish authentication, authorization, accountability and auditing.
2. Design database roles using least privilege and separation of duties.
3. Explain encryption in transit, encryption at rest and application-level protection.
4. Identify and prevent common forms of SQL injection.
5. Design practical audit, retention, privacy and data-quality controls.
6. Relate database practices to ethical responsibilities and regulatory principles.
7. Evaluate security and governance decisions in a realistic organizational case.

1. The Security and Governance Landscape

Database security protects confidentiality, integrity and availability. These three objectives are often called the CIA triad:

• Confidentiality means that information is disclosed only to authorized parties.
• Integrity means that information remains accurate, complete and protected from unauthorized alteration.
• Availability means that authorized users can access information when required.

Security controls may address one or more of these objectives. Encryption primarily supports confidentiality, constraints support integrity, and backups and disaster recovery support availability. No single control is sufficient. A database with strong encryption but unrestricted administrator accounts remains vulnerable, while a well-authorized database without reliable backups may be unavailable after an incident.

Governance is the system by which an organization directs, controls and evaluates its use of data. It assigns ownership, defines acceptable use, establishes policies and provides evidence that obligations are being met. Governance asks questions such as:

• What data do we collect, and why?
• Who owns each data set?
• Who may access it, for what purpose and for how long?
• How do we correct inaccurate information?
• When must data be deleted or archived?
• How can the organization demonstrate compliance?

A useful distinction is that security focuses strongly on protecting systems and information, whereas governance also addresses decision rights, accountability, quality, value and responsible use.

2. Identity, Authentication and Authorization

2.1 Authentication

Authentication verifies an identity claim. A user may authenticate with:

• Something they know, such as a password.
• Something they have, such as a hardware security key or authenticator device.
• Something they are, such as a fingerprint.
• A combination of these factors in multifactor authentication (MFA).

Passwords should never be stored as plain text. A password database should store a slow, salted, one-way password hash produced by an approved password-hashing algorithm. A salt is a unique random value stored with the hash; it prevents identical passwords from producing identical stored values and makes precomputed attacks more difficult. Encryption is reversible with a key, whereas password hashing is designed for verification rather than recovery.

Authentication should also include rate limiting, protection against credential stuffing, secure account recovery, session expiration and monitoring of unusual login behaviour. A database account used by an application should normally be distinct from human administrator accounts.

2.2 Authorization

Authorization determines what an authenticated identity may do. SQL privileges commonly include SELECT, INSERT, UPDATE, DELETE, EXECUTE, and privileges on schemas, views, sequences or stored procedures. Authorization should be based on business purpose rather than convenience.

For example, a customer-service employee might need to view order status but should not see complete payment-card data or modify accounting records. A reporting analyst might require read access to a carefully designed view but not to the underlying tables.

2.3 Roles and separation of duties

A role is a named collection of permissions. Users receive roles, and roles receive privileges. This role-based access control (RBAC) approach is easier to review than assigning individual permissions directly to many users.

A simple design may define:

CREATE ROLE support_agent;
CREATE ROLE reporting_analyst;
CREATE ROLE finance_manager;

GRANT SELECT ON orders TO support_agent;
GRANT SELECT ON monthly_sales_view TO reporting_analyst;
GRANT SELECT, UPDATE ON invoices TO finance_manager;

The exact syntax varies across PostgreSQL, MySQL, SQL Server, Oracle and other systems. The design principle is portable, but implementation details must be checked against the chosen database engine.

Separation of duties prevents one person from completing a high-risk process alone. For example, one employee may prepare a supplier payment while another approves it. In database terms, this can involve different roles, approval tables, workflow states and audit records.

3. Least Privilege and Secure Database Design

The principle of least privilege states that a subject should receive only the permissions necessary for an authorized task, for only as long as necessary. Least privilege reduces the damage caused by compromised credentials, software defects and insider misuse.

A practical implementation process is:

1. Identify business tasks and data flows.
2. Map each task to required tables, columns and operations.
3. Create roles around responsibilities rather than job titles alone.
4. Deny access by default where the platform supports that model.
5. Grant access to views or stored procedures instead of exposing base tables unnecessarily.
6. Review privileges periodically and remove obsolete access.
7. Use separate accounts for development, testing, deployment and production.
8. Use temporary or just-in-time elevated privileges for exceptional work.

Column-level protection is particularly important. A view may expose the last four digits of an account number while excluding the full value:

CREATE VIEW support_customer_view AS
SELECT customer_id,
       full_name,
       email,
       RIGHT(account_number, 4) AS account_suffix
FROM customers;

GRANT SELECT ON support_customer_view TO support_agent;

This example is illustrative. Masking is not a substitute for proper authorization, and the account_number column must not be indirectly recoverable through other queries or poorly protected exports.

Row-level security can restrict records according to a user, department, tenant or region. It is valuable in multi-tenant systems, but it must be tested carefully. Errors in policy conditions can expose one tenant’s records to another.

4. Encryption and Secrets Management

4.1 Encryption in transit and at rest

Encryption in transit protects data moving between clients, application servers and database servers. Transport Layer Security (TLS) should be configured with current protocols, certificate validation and secure cipher suites.

Encryption at rest protects database files, backups, snapshots and storage media if they are stolen or accessed outside the database service. It may be implemented at the disk, volume, database, table or column level.

Encryption is not a complete security solution. An authorized application can usually see decrypted data, and an attacker who obtains both encrypted data and its key may defeat the protection. Therefore, key management is central: keys should be generated securely, access-controlled, rotated according to policy and separated from the data they protect. Keys and database passwords should not be placed in source code or committed to version control; a secrets-management service is preferable.

4.2 Hashing, tokenization and masking

Different protections serve different purposes:

A system should minimize the collection of highly sensitive data. If a payment provider can retain card details, an organization may store a provider token rather than the card number itself.

5. SQL Injection and Secure Query Construction

SQL injection occurs when untrusted input changes the structure or meaning of a SQL statement. A vulnerable pattern concatenates input into a query:

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

If the input contains SQL syntax, it may alter filtering, disclose records or perform unauthorized modifications. Injection can affect web forms, APIs, reporting tools, administrative scripts and stored procedures.

The primary defense is parameterized queries, in which SQL structure is defined separately from values:

SELECT user_id, display_name
FROM users
WHERE email = :email_parameter;

The application binds the supplied email as a value, not as executable SQL. Prepared statements should be used consistently, including for INSERT, UPDATE and DELETE operations.

Additional defenses include:

• Allow-list validation for identifiers such as sortable column names.
• Avoiding dynamic SQL when a static query can perform the task.
• Escaping only as a secondary measure, because escaping rules are database- and context-specific.
• Restricting the application account’s privileges.
• Logging suspicious input and failed queries without recording secrets.
• Testing with security-focused unit and integration tests.
• Keeping database drivers and frameworks patched.

Parameterized values cannot normally replace SQL identifiers. If a user may choose a sort column, the application should map an approved external value to a fixed identifier:

allowedSorts = {
  "date": "created_at",
  "name": "display_name"
}

The application should reject values not in the allow-list. It should never insert arbitrary user input as a table or column name.

6. Auditing, Monitoring and Incident Response

An audit trail records significant actions and supports accountability, investigation and compliance. Useful audit fields include:

• Event identifier and timestamp, preferably in a consistent time standard.
• Authenticated identity and service account.
• Source application, device or network context where appropriate.
• Action performed and affected object.
• Before-and-after values for high-risk changes, subject to privacy limits.
• Success or failure result.
• Correlation or transaction identifier.

An audit table might have the following conceptual structure:

CREATE TABLE audit_event (
    event_id       BIGINT PRIMARY KEY,
    occurred_at    TIMESTAMP NOT NULL,
    actor_id       VARCHAR(100) NOT NULL,
    action_name    VARCHAR(100) NOT NULL,
    object_type    VARCHAR(100),
    object_id      VARCHAR(100),
    result         VARCHAR(20) NOT NULL,
    correlation_id VARCHAR(100)
);

Audit data itself is sensitive. If users can modify or delete their own audit records, the audit system loses credibility. Controls may include append-only storage, restricted write paths, separation of audit administration and security administration, centralized log storage and alerting.

Monitoring is not equivalent to collecting everything. Excessive logging can create another privacy risk, increase cost and make important events difficult to detect. A useful logging policy identifies events that require immediate alerts, events requiring periodic review and events retained only for troubleshooting.

An incident-response process commonly includes preparation, detection, containment, eradication, recovery and lessons learned. Database teams should know how to revoke credentials, preserve evidence, isolate affected systems, restore from trusted backups and communicate with legal and regulatory stakeholders.

7. Privacy and the Data Life Cycle

Privacy concerns the appropriate collection, use, disclosure and control of information about identifiable people. A data life cycle usually includes collection, storage, use, sharing, archival and deletion.

Important privacy principles include:

• Purpose limitation: collect and use data for specified legitimate purposes.
• Data minimization: collect no more personal data than necessary.
• Accuracy: provide mechanisms to correct inaccurate information.
• Storage limitation: retain identifiable data only as long as justified.
• Transparency: explain what is collected and how it is used.
• Security: protect data with proportionate technical and organizational controls.
• Accountability: demonstrate that policies are implemented, not merely written.

Privacy is not achieved simply by removing names. Quasi-identifiers such as date of birth, postcode and occupation may permit re-identification when combined with another data set. Anonymization seeks to prevent re-identification under a reasonable threat model; pseudonymization replaces direct identifiers but generally remains personal data if re-identification is possible.

A retention schedule should specify the data category, business purpose, retention period, legal hold conditions, archival method and destruction method. Deletion must cover production data, replicas, exports, temporary files and backups where feasible. Legal holds may suspend ordinary deletion when records are relevant to litigation or investigation.

8. Data Quality and Integrity

Security cannot compensate for inaccurate data. Poor-quality records can lead to incorrect payments, unfair decisions, failed services and misleading analysis.

Important dimensions of data quality include:

• Accuracy: values represent the real-world entity correctly.
• Completeness: required values are present.
• Consistency: related records do not conflict.
• Timeliness: data is current enough for its purpose.
• Validity: values conform to permitted formats and rules.
• Uniqueness: duplicate records are controlled.

SQL supports quality through primary keys, foreign keys, NOT NULL, UNIQUE, CHECK constraints and suitable data types:

CREATE TABLE product (
    product_id   INTEGER PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    price        DECIMAL(12,2) NOT NULL CHECK (price >= 0),
    category_id  INTEGER NOT NULL,
    FOREIGN KEY (category_id) REFERENCES category(category_id)
);

Constraints provide immediate protection, but they cannot establish whether a name is true, whether a customer consented or whether two records represent the same person. Those concerns require validation processes, stewardship, reconciliation and human review.

Data owners define acceptable use and quality expectations. Data stewards operationalize definitions, monitor quality and coordinate correction. Developers implement controls, while system owners ensure that controls remain effective as systems change.

9. Regulatory Compliance and Ethical Data Use

Regulatory obligations vary by jurisdiction and sector. Common themes in modern privacy regimes include lawful or legitimate processing, transparency, rights of access and correction, restrictions on sensitive data, breach response, processor oversight and accountability. Sector-specific requirements may apply to health, finance, education, employment or children’s data.

Compliance is not the same as ethics. A practice may be technically legal yet ethically questionable. For example, an application might infer a person’s vulnerability from browsing behaviour and use that inference to manipulate a purchase. Ethical analysis asks whether the practice respects autonomy, fairness, dignity, proportionality and reasonable expectations.

A responsible data-use assessment should consider:

1. Who may benefit and who may be harmed?
2. Are affected people aware of the use?
3. Is consent meaningful, or is refusal practically impossible?
4. Could the data or model produce disparate effects?
5. Are there less intrusive ways to achieve the purpose?
6. Can the organization explain and challenge the decision?
7. How will errors be corrected?

Artificial intelligence increases these concerns because data may be reused for profiling, prediction and automated decisions. Database professionals should document data provenance, access training data appropriately, test for bias, limit sensitive attributes, protect model outputs and retain human oversight where decisions affect rights or significant opportunities.

10. A Governance Framework for SQL Projects

A practical governance framework can be organized into five connected activities:

1. Discover: inventory data assets, classifications, owners, flows and dependencies.
2. Classify: label data according to sensitivity, legal obligations and business impact.
3. Protect: apply authentication, authorization, encryption, minimization and secure development.
4. Monitor: review access, quality, retention, incidents and control effectiveness.
5. Improve: remediate weaknesses, update policies and reassess risks after changes.

Data classification should be understandable and actionable. For example:

A risk-based approach is preferable to applying identical controls to every column. Risk can be discussed qualitatively as a function of likelihood and impact:

Here, likelihood represents the chance of an adverse event and impact represents its consequence. This is a conceptual prioritization model, not a universal measurement law. Organizations may use numerical scales, but the scales must be defined consistently. A low-probability event with catastrophic impact may deserve stronger controls than a frequent event with minor impact.

11. Illustrative Case: MedixCare Appointment Platform

Background

MedixCare is an illustrative outpatient clinic that operates an online appointment platform. Its SQL database contains patient identities, appointment history, clinician notes, billing references and communication preferences. Support staff need appointment information, clinicians need clinical information for their patients, and finance staff need billing records.

Problem

An internal review finds that the web application uses one shared database account with broad read and write privileges. Search queries are built by string concatenation. Backups are encrypted, but encryption keys are stored in the same cloud account. Audit logs record administrator logins but not patient-record access. The organization retains inactive patient exports indefinitely.

Analysis

The shared account prevents meaningful attribution and violates least privilege. A successful injection attack could access or alter all tables. Co-locating keys with encrypted backups weakens the separation between data and key material. Incomplete auditing makes it impossible to determine which records were viewed. Indefinite exports increase privacy exposure and retention risk.

Approach and solution

MedixCare implements separate service and human accounts, MFA for privileged users and roles for support, clinicians, finance, reporting and database administration. Clinicians receive row-level restrictions based on assigned patients. Support users receive a view that excludes clinical notes and full billing identifiers. The application adopts parameterized queries and allow-lists dynamic sort fields.

The organization moves secrets and encryption keys to a managed secrets and key-management service, with restricted administration and rotation procedures. It enables database activity monitoring for sensitive tables and stores append-only audit events in a separate security account. A retention schedule deletes obsolete exports and places legally required records under documented holds. Data stewards establish quality checks for duplicate patient identities and invalid appointment references.

Before deployment, the team performs threat modeling, privilege testing, injection testing, backup restoration tests and a privacy impact assessment. It also creates an incident procedure for credential revocation and notification assessment.

Outcome

The new design reduces the scope of a compromised application account, improves attribution and makes inappropriate access more detectable. It does not eliminate risk: authorized users may still misuse access, configuration errors may still occur and clinical data remains highly sensitive. Therefore, MedixCare schedules recurring access reviews, control testing and staff training.

Learning points

The case demonstrates that security is layered. Parameterized queries address injection, but they do not replace authorization. Encryption protects stored data, but it does not decide who should access it. Auditing provides evidence, but only if records are complete and protected. Governance connects these controls to retention, quality, accountability and ethical purpose.

12. Challenges and Limitations

Security controls introduce cost, complexity and operational friction. Strong access restrictions can disrupt legitimate work if roles are poorly designed. Encryption can complicate searching, indexing, backup recovery and key rotation. Detailed auditing can affect performance and produce sensitive logs. Data minimization may reduce future analytical flexibility. Deletion can be difficult when data is replicated across caches, warehouses and third-party services.

Controls can also fail through configuration drift. A secure design at deployment may become insecure after a new role, integration or emergency privilege is added. Consequently, security must be treated as a continuing process involving change management, testing, independent review and measurable accountability.

13. Best Practices for Professional SQL Development

• Use individual accounts for people and narrowly scoped service accounts for applications.
• Require MFA for administrators and other high-risk access.
• Prefer roles, views, stored procedures and row-level controls over unrestricted table access.
• Apply least privilege and review privileges regularly.
• Use parameterized queries and allow-lists for dynamic SQL elements.
• Encrypt network connections, backups and sensitive storage; manage keys separately.
• Never store passwords or secrets in source code or plain-text configuration.
• Record security-relevant events while minimizing unnecessary personal data in logs.
• Enforce integrity with constraints and monitor data-quality indicators.
• Maintain documented retention, deletion, legal-hold and backup policies.
• Conduct privacy and threat assessments before collecting or repurposing sensitive data.
• Test restoration, incident response, authorization boundaries and injection defenses.
• Document data ownership, provenance, permitted use and accountability.
• Reassess controls whenever architecture, vendors, regulations or data uses change.

Chapter Summary

Database security protects confidentiality, integrity and availability, while data governance establishes accountability, acceptable use, quality and life-cycle control. Authentication verifies identity; authorization determines permitted actions; roles and least privilege reduce unnecessary access; and separation of duties limits concentrated power. Encryption in transit and at rest protects data, but effective key management is essential.

SQL injection is primarily prevented through parameterized queries, supported by input allow-lists, restricted privileges and secure testing. Auditing and monitoring create accountability and support incident response, but audit data must itself be protected. Privacy requires purpose limitation, minimization, transparency, accuracy and appropriate retention. Data quality depends on both SQL constraints and organizational stewardship. Compliance should be treated as an accountability process rather than a checklist, and ethical practice requires attention to fairness, autonomy, proportionality and potential harm. A mature SQL professional therefore designs not only for functionality, but also for responsible and demonstrable data use.

Key Terms

Authentication: Verification of a claimed identity.

Authorization: Determination of what an authenticated identity may do.

Accountability: Ability to associate actions with responsible identities or systems.

Audit trail: Protected record of significant actions and events.

Confidentiality: Protection against unauthorized disclosure.

Data governance: Direction, control and accountability for organizational data.

Data minimization: Collection and use of only the data necessary for a purpose.

Data steward: Person responsible for operational data definitions, quality and appropriate use.

Encryption: Transformation of data so it can be read only with an appropriate key.

Integrity: Accuracy, completeness and protection from unauthorized alteration.

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

Multifactor authentication: Authentication using two or more independent factor types.

Parameterized query: SQL statement whose values are bound separately from SQL structure.

Pseudonymization: Replacement of identifiers while retaining a possible re-identification path.

Role-based access control: Assigning permissions through named roles.

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

Separation of duties: Distribution of a sensitive process among multiple authorized parties.

Tokenization: Replacement of sensitive data with a protected substitute token.

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

Review Questions

Short Answer Questions

1. Distinguish authentication from authorization.
2. What is the principle of least privilege?
3. Why should passwords be hashed rather than encrypted for storage?
4. What is the main purpose of a parameterized query?
5. Name three dimensions of data quality.

Long Answer Questions

1. Explain how roles, views, row-level security and separation of duties can be combined in a healthcare database.
2. Compare encryption, hashing, masking and tokenization, including an appropriate use for each.
3. Describe the main components of a database auditing and monitoring strategy.
4. Explain how privacy principles influence collection, retention and deletion decisions.
5. Discuss why regulatory compliance does not by itself guarantee ethical data use.

Analytical / Application Questions

1. An application account can select and update every table in a retail database, although it only needs to create orders. Analyze the risks and propose a revised privilege design.
2. A reporting team wants to combine postcode, age, occupation and purchase history to identify “high-value” customers. Evaluate the proposal using privacy, fairness, re-identification and governance considerations.
3. During an incident, an organization discovers that audit logs contain administrator logins but no record of which customer rows were viewed. Assess the limitations of the evidence and design an improved audit approach.

Multiple Choice Questions with Answers

Question 1

Which control primarily determines what an authenticated user is permitted to do?

A. Authentication

B. Authorization

C. Encryption

D. Data retention

Correct Answer: B. Authorization

Question 2

What is the best primary defense against SQL injection in application queries?

A. Longer table names

B. Parameterized queries

C. More database indexes

D. Deleting old audit logs

Correct Answer: B. Parameterized queries

Question 3

Which principle gives a database account only the permissions necessary for its task?

A. Data duplication

B. Least privilege

C. Open access

D. Availability maximization

Correct Answer: B. Least privilege

Question 4

Which technique is specifically designed to verify passwords without storing recoverable passwords?

A. Password hashing

B. TLS encryption

C. Column masking

D. Database replication

Correct Answer: A. Password hashing

Question 5

What is the main purpose of encryption in transit?

A. To enforce foreign keys

B. To protect data moving between systems

C. To remove duplicate records

D. To define user roles

Correct Answer: B. To protect data moving between systems

Question 6

Which SQL feature most directly supports entity uniqueness?

A. UNIQUE constraint

B. ORDER BY clause

C. VIEW name

D. Audit timestamp

Correct Answer: A. UNIQUE constraint

Question 7

Why should encryption keys generally be managed separately from encrypted backups?

A. To improve query sorting

B. To preserve separation if the backup is exposed

C. To remove the need for authentication

D. To make data public

Correct Answer: B. To preserve separation if the backup is exposed

Question 8

Which privacy principle limits collection to information needed for a stated purpose?

A. Data minimization

B. Separation of duties

C. Availability

D. Referential integrity

Correct Answer: A. Data minimization

Question 9

Which statement about pseudonymization is correct?

A. It always makes data anonymous

B. It replaces identifiers but may allow re-identification

C. It is identical to password hashing

D. It prevents all insider misuse

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

Question 10

Why is an audit trail valuable?

A. It makes authorization unnecessary

B. It supports accountability and investigation

C. It guarantees data accuracy

D. It eliminates the need for backups

Correct Answer: B. It supports accountability and investigation