Secure Database and Application Design

Introduction

Database security is most effective when it is designed into a system rather than added after deployment. A secure database is not simply a database protected by a firewall or restricted to a few administrators. Security depends on decisions made throughout the system: what data is collected, how tables and relationships are structured, how applications authenticate users, how queries are constructed, how errors are handled, and how changes are tested and monitored.

This chapter examines secure database and application design for practical cybersecurity work. It focuses on secure defaults, data minimisation, validation, parameterised queries, stored procedures, and secure development lifecycle practices. These controls are mutually reinforcing. Data minimisation reduces the consequences of compromise; validation prevents malformed or dangerous input; parameterised queries separate data from SQL instructions; stored procedures can centralise controlled database operations; and secure development practices ensure that these measures are applied consistently.

Security must also be balanced with usability, performance, maintainability, legal obligations, and business requirements. A control that is theoretically strong but impossible for developers or users to apply consistently may produce little real protection. The design objective is therefore risk reduction through practical, verifiable controls.

Learning Objectives

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

1. Explain the principles of secure database and application design.
2. Apply secure-by-default and least-privilege decisions to database systems.
3. Design schemas that minimise unnecessary collection and exposure of data.
4. Distinguish validation, encoding, sanitisation, and authorisation.
5. Explain how parameterised queries prevent SQL injection.
6. Evaluate appropriate and inappropriate uses of stored procedures.
7. Apply secure development lifecycle activities to database-backed applications.
8. Analyse a practical database security design and identify improvements.

1. Security as a Design Property

A database application usually consists of several layers: the user interface, application services, data-access components, the database management system, and infrastructure such as networks, operating systems, backups, and monitoring. A weakness in one layer can undermine protections in another. For example, a well-designed schema cannot prevent an application account with excessive privileges from deleting every table.

A useful design principle is defence in depth. Security should not depend on a single control. An application should validate input, use parameterised queries, restrict database privileges, protect credentials, log important events, and test its implementation. If one control fails, other controls should limit the impact.

The principal security properties are:

• Confidentiality: only authorised subjects can access data.
• Integrity: data and database operations remain accurate and trustworthy.
• Availability: authorised users can access required services when needed.
• Accountability: actions can be attributed to users, services, or administrators.
• Privacy: personal data is collected and used fairly, lawfully, and proportionately.

Security requirements should be written as testable statements. “The system must be secure” is not testable. “A reporting account may read approved views but may not insert, update, or delete records” is testable through configuration review and controlled access tests.

Threat modelling during design

Threat modelling identifies what must be protected, from whom, and by which attack paths. A simple process is:

1. Identify assets, such as customer records, credentials, payment information, and audit logs.
2. Identify trust boundaries, such as the boundary between a browser and an application server or between an application and a database.
3. Identify threats, including injection, unauthorised access, data leakage, tampering, and denial of service.
4. Select controls and record their owners.
5. Test whether the controls work as intended.

Threat modelling is not a one-time document. New data fields, integrations, database extensions, and changes in legal requirements can change the risk profile.

2. Secure Defaults and Least Privilege

A secure default is the safer behaviour provided automatically when no special configuration is supplied. Examples include denying database network access from unapproved hosts, disabling unused accounts, requiring encrypted connections, and refusing access unless an explicit permission is granted. Secure defaults reduce dependence on individual administrators remembering every security setting.

The principle of least privilege states that a user, process, or service should receive only the permissions required for its legitimate task, for only as long as those permissions are needed. Least privilege applies to database users, application roles, operating-system accounts, backup operators, developers, and automated jobs.

A practical role model might include:

Applications should not normally connect to a database using the database owner or administrator account. If an application is compromised, administrative credentials could allow an attacker to create accounts, alter data, disable logging, or destroy the database.

Secure configuration principles

Important configuration practices include:

• Disable default accounts, sample databases, and unused extensions.
• Change vendor default passwords before production use.
• Permit database connections only from approved application hosts.
• Use encrypted connections and verify certificates where supported.
• Store credentials in a managed secret store rather than source code.
• Separate development, testing, staging, and production environments.
• Restrict administrative interfaces to controlled networks.
• Apply security patches through a tested change process.
• Configure backups with access controls and encryption.
• Set conservative timeouts, connection limits, and resource quotas.

Security and availability must be considered together. For instance, excessively restrictive connection limits might cause legitimate service failure, while unlimited connections could permit resource exhaustion. Configuration should therefore be tested under expected and abnormal workloads.

3. Data Minimisation and Secure Schema Design

Data minimisation means collecting, retaining, exposing, and sharing only the data necessary for a defined purpose. It is both a privacy principle and a security control. Data that is never collected cannot be stolen from the system; data that is deleted when no longer required cannot be exposed by a later breach.

Before adding a field, designers should ask:

• What business purpose requires it?
• Is the purpose sufficiently specific?
• Is the field mandatory or optional?
• Who needs to access it?
• How long must it be retained?
• Can a less sensitive attribute meet the same need?
• What happens when the purpose ends?

For example, a service may need to know whether a customer is over a specified age, but may not need to store the customer’s complete date of birth. A system may need the last four digits of a payment instrument for display, but should not store full payment data unless a properly governed payment architecture requires it.

Schema controls

A secure schema commonly uses:

• Primary keys to identify records consistently.
• Foreign keys to preserve relationships and prevent orphaned records.
• NOT NULL constraints when a value is essential.
• CHECK constraints for permitted ranges or states.
• UNIQUE constraints where duplication would be unsafe or misleading.
• Appropriate data types to prevent unintended values.
• Views to expose only required columns and rows.
• Separate tables or domains for especially sensitive information.
• Retention and deletion fields or processes governed by policy.

Constraints improve integrity but do not replace application controls. A database constraint may reject an invalid value, but it does not establish whether the requesting user is authorised to make the change.

Sensitive values require special treatment. Passwords must not be reversibly encrypted for ordinary authentication; they should be processed using a password-hashing algorithm designed for password storage, with a unique salt and an appropriate work factor. Encryption may be required for data at rest, but encryption keys must be managed separately from the encrypted data. Hashing, encryption, tokenisation, and masking are different techniques and should not be treated as interchangeable.

Retention and deletion

Retention should be explicit. A record should not remain indefinitely merely because storage is inexpensive. Deletion procedures must address primary records, replicas, exports, temporary files, caches, and backups according to the organisation’s retention and legal policies. In some cases, deletion is constrained by audit, taxation, safety, or litigation requirements. Such exceptions should be documented rather than silently ignored.

4. Input Validation, Encoding, and Authorisation

Input handling is frequently misunderstood because several different activities are given the single label “sanitisation.” They solve different problems.

Validation checks whether input conforms to an expected rule. For example, a quantity may be required to be an integer between 1 and 20, and a country code may be required to match an approved list. Prefer allow-list validation, which accepts known valid forms, over deny-list validation, which attempts to list every dangerous form.

Canonicalisation converts equivalent representations into a consistent form before validation. This matters because attackers may use alternate encodings, case variations, or Unicode representations to evade checks.

Output encoding transforms data so that it is treated as data in its output context. HTML, JavaScript, SQL, URLs, and operating-system commands have different encoding rules. Data safe for HTML is not automatically safe for SQL or a shell command.

Authorisation determines whether an authenticated identity may perform a particular action on a particular object. Validation asks, “Is this input structurally acceptable?” Authorisation asks, “Is this user allowed to perform this operation?” Both are necessary.

Validation should occur at appropriate boundaries, especially at the application boundary and again at critical database boundaries. However, validation must not be used as the main defence against SQL injection. Parameterised queries provide the separation needed between SQL code and data.

Error messages also require design. An application should give users useful but non-sensitive information. A public error should not reveal SQL statements, table names, connection strings, stack traces, or internal file paths. Detailed diagnostic information should be recorded securely for authorised administrators and developers.

5. Parameterised Queries and SQL Injection Prevention

SQL injection occurs when untrusted input is incorporated into SQL as executable syntax. A vulnerable pattern constructs a query by concatenating strings. If an input changes the intended structure of the statement, an attacker may bypass authentication, read unauthorised rows, modify data, or invoke database functions.

A parameterised query defines the SQL statement separately from its values. The database driver transmits the statement structure and parameter values distinctly, so a value containing SQL metacharacters is treated as data rather than as new SQL instructions.

Conceptually, an unsafe query might be represented as:

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

A safer design is:

SELECT id FROM users WHERE name = :name

The application then binds userInput to the parameter named :name. The exact syntax varies among languages and database drivers; the security property is the separation of code and data.

Parameterisation should be used for values in SELECT, INSERT, UPDATE, and DELETE statements. It should also be used in stored procedure calls. Developers sometimes incorrectly parameterise ordinary values but concatenate table names, column names, or sort directions. Identifiers cannot usually be bound as ordinary values. Where dynamic identifiers are necessary, use a strict allow-list mapping supplied names to known internal identifiers. Never accept arbitrary table or column names from users.

Parameterised queries do not solve every security problem. They do not automatically provide authorisation, prevent excessive data access, protect secrets, or make unsafe stored procedures safe. They also do not eliminate the need for input validation, secure database permissions, logging, and testing.

6. Stored Procedures and Controlled Data Access

A stored procedure is a named program executed by the database server. It can centralise repeated operations, enforce transaction boundaries, reduce application-to-database round trips, and expose a controlled interface to data.

Stored procedures can support security when they:

• Accept typed parameters rather than concatenated SQL.
• Perform explicit validation and authorisation checks where appropriate.
• Return only required columns.
• Execute with carefully controlled privileges.
• Use transactions consistently.
• Record security-relevant actions through approved auditing mechanisms.

For example, an application might call TransferFunds(sourceAccount, destinationAccount, amount) rather than directly updating several account tables. The procedure can verify that the amount is positive, that the source account belongs to the requesting user, and that both updates succeed or fail together.

Stored procedures are not inherently secure. Dynamic SQL inside a procedure can still be injectable if it concatenates untrusted values. Procedures can also become difficult to review if they contain excessive business logic, undocumented privilege escalation, or broad permissions. A procedure that runs with powerful definer rights requires particular scrutiny because an ordinary application user may indirectly perform privileged operations.

A balanced design uses stored procedures where they provide a clear control or consistency benefit, while retaining readable, testable application logic elsewhere. Procedures should be version-controlled, reviewed, tested, and deployed through the same change process as application code.

7. Secure Development Lifecycle for Database Applications

A secure development lifecycle (SDL) integrates security activities into planning, design, implementation, testing, release, and maintenance. Security is not a single testing phase at the end of a project.

Requirements

Security requirements should identify assets, roles, trust boundaries, regulatory obligations, retention rules, availability needs, and audit requirements. Requirements should be measurable, such as “the reporting role can access aggregated sales data but not customer contact details.”

Design

Design activities include threat modelling, data-flow analysis, schema review, privilege modelling, abuse-case development, and selection of authentication and encryption controls. The team should decide how failures behave. For example, an access-control check should fail closed rather than grant access when a policy service is unavailable.

Implementation

Developers should use approved database libraries, parameterised queries, secure secret management, code review, and standard error handling. Security-sensitive functions should avoid copying credentials into logs or exposing full records in exceptions.

Verification

Verification may include:

• Static analysis for dangerous query construction and secret leakage.
• Dependency and container scanning.
• Unit tests for validation and authorisation rules.
• Integration tests against a real or representative database.
• Negative tests that attempt unauthorised access and malformed input.
• Dynamic application testing for injection and access-control failures.
• Manual review of high-risk functions and stored procedures.
• Configuration review of database accounts, network access, and logging.

A useful test is to create two users with different permissions and verify that every protected operation behaves correctly for both. Tests should cover direct object references, such as changing an identifier in a URL or API request to access another customer’s record.

Deployment and operations

Production deployment should use controlled migrations, peer approval, backups, rollback planning, and environment-specific secrets. Database schema changes should be tracked and repeatable. Emergency changes should be documented and reviewed afterward.

Operations include patching, monitoring, access recertification, backup restoration tests, vulnerability management, incident response, and periodic review of retained data. Logs should support investigation without becoming an uncontrolled copy of sensitive information. Access to logs must itself be restricted.

8. Applied Case: Illustrative Online Clinic Portal

Background

The following is an illustrative case. A community clinic develops an online portal where patients book appointments, view selected medical information, and receive reminders. The initial design stores full patient profiles, appointment notes, identity documents, and payment details in one database. The web application connects as a database administrator because this simplifies development.

Problem

A security review identifies several weaknesses: SQL statements are built through string concatenation; the appointment search endpoint accepts a patient identifier without verifying ownership; unused test accounts remain active; error messages expose database details; and every support employee can read the complete patient table.

The portal also retains identity documents indefinitely, although staff rarely need them after registration. Backups are copied to a shared storage location without a documented access review.

Analysis

The principal risks are unauthorised disclosure, injection, excessive privilege, poor retention, and weak accountability. The risks are connected. If SQL injection is discovered, the administrator connection could allow complete database compromise. If an identifier is changed in a request, the missing object-level authorisation check could disclose another patient’s information even without injection.

Approach and solution

The team first creates a data inventory and classifies fields by sensitivity. Identity documents are moved to a separately controlled service with a defined retention period. The portal stores only the reference and status needed for routine operation. Payment processing is delegated to a specialised provider, so the clinic does not store full payment card data.

The schema is redesigned with separate patient, appointment, clinical-note, and audit domains. Support staff receive access to a view containing the minimum fields needed for their duties. Sensitive clinical notes are unavailable to ordinary support roles. Foreign keys, nullability rules, and check constraints improve integrity.

The application uses a restricted runtime account. It executes parameterised queries and calls a small set of reviewed procedures for appointment booking and cancellation. Each request performs authentication and object-level authorisation: the patient must be associated with the requested appointment, while authorised clinicians receive access based on assigned clinical responsibility.

The team removes test accounts, stores secrets in a managed secret store, suppresses database details from public errors, and encrypts approved backups. The deployment pipeline runs schema migration tests, static analysis, dependency scanning, and negative authorisation tests. Security logs record administrative actions, failed access attempts, and changes to clinical information, while avoiding unnecessary duplication of medical content.

Outcome

The revised design reduces the quantity of sensitive data, limits the impact of application compromise, and improves the ability to investigate misuse. It does not guarantee that the portal can never be attacked. Instead, it creates multiple barriers: reduced data exposure, restricted permissions, safe query construction, explicit authorisation, controlled retention, and continuous review.

Learning points

1. Application security and database security must be designed together.
2. A restricted database account limits the consequences of an application flaw.
3. A valid identifier does not prove that the requester is authorised to use it.
4. Minimising data reduces both privacy obligations and breach impact.
5. Security controls must be tested in deployment and operation, not merely documented.

9. Challenges, Ethics, and Governance

Secure design involves trade-offs. More detailed auditing can improve accountability but may increase privacy risk and storage costs. Encryption can protect data but creates key-management responsibilities. Strong validation may reject legitimate international names or addresses if designers assume narrow formats. Least privilege can slow development when access requests are poorly managed. These trade-offs should be recorded and reviewed rather than hidden.

Organisations must comply with applicable privacy and cybersecurity requirements. Depending on jurisdiction and sector, obligations may include lawful processing, purpose limitation, data-subject rights, breach notification, retention controls, auditability, and protection of health or financial information. Compliance is not identical to security: a system may satisfy a checklist while remaining vulnerable, and a strong technical control may still violate a legal purpose limitation if data is collected without justification.

Ethical design requires respect for individuals affected by database decisions. Developers should avoid collecting sensitive attributes merely because they might be useful later. Automated decisions should be explainable to the extent required by policy or law, and access logs should not be used for unjustified employee surveillance. AI-based coding tools may suggest database queries or validation logic, but generated code must be reviewed, tested, and checked for insecure patterns, invented library behaviour, and accidental disclosure of confidential source code or data.

10. Contemporary Practices and Best Practices

Modern applications increasingly use APIs, cloud-managed databases, microservices, containers, and infrastructure as code. These technologies do not remove foundational database risks. They introduce additional identities, network paths, secrets, configuration files, and service-to-service permissions.

Current practice increasingly emphasises:

• Zero-trust access: verify each request rather than trusting network location alone.
• Identity-aware database access: use managed identities or short-lived credentials where practical.
• Policy as code: express security and configuration rules in version-controlled, testable form.
• Continuous verification: integrate security checks into automated delivery pipelines.
• Centralised secrets management: avoid long-lived credentials in repositories and images.
• Database activity monitoring: detect unusual queries, access volumes, and privilege changes.
• Privacy-enhancing techniques: use masking, tokenisation, aggregation, or controlled synthetic data for testing.
• Resilient recovery: test restoration, not merely backup creation.

A concise professional checklist is:

1. Define the data purpose, classification, owner, and retention period.
2. Model threats and trust boundaries before implementation.
3. Start from deny-by-default permissions.
4. Use a separate, restricted runtime identity.
5. Apply schema constraints and expose minimum necessary data.
6. Validate input and perform explicit authorisation checks.
7. Use parameterised queries and safe handling of dynamic identifiers.
8. Review stored procedures for injection and privilege risks.
9. Protect secrets, connections, backups, and logs.
10. Test negative cases and monitor the system after release.

Chapter Summary

Secure database and application design treats security as a property of the whole system. Secure defaults and least privilege reduce the authority available to attackers and limit accidental misuse. Data minimisation reduces exposure by restricting collection, access, and retention. Strong schemas use appropriate types, keys, constraints, views, and controlled domains to protect integrity and privacy.

Validation checks whether input meets an expected rule, while authorisation determines whether an identity may perform an operation. Output encoding is context-dependent and should not be confused with SQL injection prevention. Parameterised queries separate SQL instructions from values and are the primary defence against unsafe query construction, although they do not replace authorisation or privilege management. Stored procedures can provide controlled interfaces and transaction consistency, but they must themselves be parameterised, reviewed, and governed.

A secure development lifecycle incorporates requirements analysis, threat modelling, secure implementation, automated and manual testing, controlled deployment, monitoring, patching, access review, and recovery testing. The illustrative clinic case demonstrates how schema design, restricted accounts, data minimisation, explicit authorisation, safe queries, and governance operate together. Effective security is therefore not a single product or technique; it is a continuous design and management discipline.

Key Terms

Authorisation: The decision about whether an authenticated identity may perform a specific action on a resource.

Canonicalisation: Converting equivalent representations into a consistent form before processing or validation.

Data minimisation: Collecting, retaining, and exposing only data necessary for a defined purpose.

Defence in depth: Using multiple complementary controls so that failure of one control does not cause total compromise.

Least privilege: Granting only the permissions required for a legitimate task and limiting their duration where possible.

Parameterised query: A query whose structure is separated from values supplied by the application.

Primary key: A column or set of columns that uniquely identifies a row.

Secure default: The safer behaviour applied automatically when no explicit configuration is provided.

Secure development lifecycle: A development process that integrates security activities from requirements through operation.

Stored procedure: A named program executed by the database server.

Threat modelling: A structured process for identifying assets, threats, attack paths, and appropriate controls.

Tokenisation: Replacing a sensitive value with a controlled substitute or token.

Validation: Checking whether data conforms to specified structural, semantic, or business rules.

View: A database-defined representation that can expose selected rows or columns without granting direct access to base tables.

Object-level authorisation: Checking whether the requester may access the particular record or object identified in a request.

Data retention: The period and conditions under which information is kept before deletion or archival.

Review Questions

Short Answer Questions

1. What is meant by a secure default in a database system?
2. Why does data minimisation improve security as well as privacy?
3. What is the difference between validation and authorisation?
4. How do parameterised queries reduce SQL injection risk?
5. Why should an application avoid using a database administrator account?

Long Answer Questions

1. Explain how least privilege can be applied to application, reporting, support, and migration accounts.
2. Discuss the security benefits and limitations of stored procedures.
3. Describe the major stages of a secure development lifecycle for a database-backed application.
4. Explain how schema constraints, views, retention controls, and encryption contribute to secure database design.
5. Discuss why parameterised queries alone cannot provide complete application security.

Analytical / Application Questions

1. An online retailer allows users to change an order identifier in an API request. The application uses parameterised queries but does not check ownership of the order. Analyse the vulnerability and propose controls.
2. A university stores student identity documents, full medical information, and contact details in a single table accessible to all administrative staff. Design a more secure data and access model, including minimisation and role separation.
3. A developer proposes accepting a user-supplied table name in a report query and escaping special characters. Evaluate this approach and recommend a safer design.

Multiple Choice Questions with Answers

Question 1

Which principle requires a database account to receive only the permissions necessary for its task?

A. Least privilege

B. Data duplication

C. Open trust

D. Maximum availability

Correct Answer: A. Least privilege

Question 2

What is the primary security benefit of a parameterised query?

A. It automatically encrypts the database backup.

B. It separates SQL structure from supplied values.

C. It grants users access to all required tables.

D. It permanently deletes invalid input.

Correct Answer: B. It separates SQL structure from supplied values.

Question 3

Which control determines whether a user may access a particular appointment record?

A. Canonicalisation

B. Input formatting

C. Object-level authorisation

D. Database indexing

Correct Answer: C. Object-level authorisation

Question 4

Which practice best demonstrates data minimisation?

A. Keeping every field indefinitely for possible future use

B. Collecting only information necessary for a defined purpose

C. Giving all staff access to the complete customer table

D. Copying production data into every test environment

Correct Answer: B. Collecting only information necessary for a defined purpose

Question 5

Why is using a database administrator account for normal application operations dangerous?

A. It prevents all SQL queries from running.

B. It makes backups impossible.

C. A compromised application may gain extensive database control.

D. It automatically disables encryption.

Correct Answer: C. A compromised application may gain extensive database control.

Question 6

Which statement about stored procedures is correct?

A. Stored procedures are automatically immune to injection.

B. Stored procedures can centralise controlled operations but must still be reviewed.

C. Stored procedures eliminate the need for authorisation.

D. Stored procedures should always run with administrator privileges.

Correct Answer: B. Stored procedures can centralise controlled operations but must still be reviewed.

Question 7

Which activity belongs early in a secure development lifecycle?

A. Deleting all audit logs

B. Threat modelling and identification of security requirements

C. Disabling code review

D. Publishing database credentials

Correct Answer: B. Threat modelling and identification of security requirements

Question 8

What should normally be used when a user selects one of several allowed sort fields?

A. Unrestricted string concatenation

B. A strict allow-list mapping to known identifiers

C. A password hash

D. A database administrator account

Correct Answer: B. A strict allow-list mapping to known identifiers

Question 9

Which statement correctly distinguishes validation from output encoding?

A. Validation checks expected input rules, while encoding protects data in a particular output context.

B. Validation is the same as encryption, while encoding is the same as hashing.

C. Validation grants permissions, while encoding creates database accounts.

D. Validation is performed only by firewalls, while encoding is performed only by users.

Correct Answer: A. Validation checks expected input rules, while encoding protects data in a particular output context.

Question 10

Which design most directly reduces the impact of a stolen application credential?

A. Granting the credential unrestricted schema privileges

B. Using a restricted runtime account with only required permissions

C. Storing the credential in source code

D. Removing all database logging

Correct Answer: B. Using a restricted runtime account with only required permissions