Database Access-Control Models

Introduction

Database security is concerned not only with protecting a database server from attack, but also with ensuring that legitimate users can perform only the actions justified by their responsibilities. A database may be encrypted, patched and monitored, yet remain insecure if a reporting user can alter payroll records, an application account can read every customer record, or a former employee retains access through an inherited role.

Access control is the systematic process of deciding who or what may access a resource, which operation may be performed, under what conditions, and how the decision is enforced. In database systems, resources include databases, schemas, tables, views, columns, rows, stored procedures and administrative functions. The principal security objective is least privilege: every subject receives only the minimum permissions needed to perform an authorised task, for only as long as those permissions are needed.

This chapter examines four important access-control models—discretionary access control, mandatory access control, role-based access control and attribute-based access control. It then applies these models to database roles, permissions, views, row-level security and policy enforcement.

Learning Objectives

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

• explain the purpose and components of database access control;
• distinguish discretionary, mandatory, role-based and attribute-based models;
• apply least privilege and separation of duties to database design;
• configure users, roles, privileges and permission boundaries;
• use views and stored procedures to expose restricted data safely;
• explain row-level security and policy enforcement;
• identify common access-control failures and recommend controls;
• evaluate an access-control design in a realistic organisational scenario.

1. Foundations of Database Access Control

An access-control decision involves several elements:

• Subject: a user, service account, application, process or device requesting access.
• Object: a protected resource such as a table, row, column, view or procedure.
• Action: an operation such as SELECT, INSERT, UPDATE, DELETE or EXECUTE.
• Policy: rules defining which actions are permitted.
• Context: relevant conditions, such as time, location, device status, transaction value or data classification.
• Reference monitor: the mechanism that evaluates requests and mediates access.

A useful abstraction is:

Access\ decision = f(subject,\ object,\ action,\ context,\ policy)

This is a conceptual relationship rather than a numerical formula. The decision function returns a result such as permit, deny or require additional authentication. A secure reference monitor should be complete, tamper-resistant and consistently invoked. In database systems, the database engine, privilege system, policy engine and auditing mechanisms collectively perform this function.

Authentication and authorisation

Authentication establishes identity: for example, a user proves possession of a password, token or cryptographic credential. Authorisation determines what that authenticated identity may do. Authentication without authorisation is insufficient. A successfully authenticated user may still have no permission to read a particular table.

Accounting or auditing records what occurred. These three activities are often summarised as authentication, authorisation and accountability. They should not be confused: an audit log does not prevent unauthorised access, and a permission does not prove that a user’s identity was genuine.

Least privilege

Least privilege requires that a subject receive only the permissions necessary for its current duties. It has several dimensions:

• Functional least privilege: permit only required operations, such as SELECT but not UPDATE.
• Data least privilege: expose only required records or columns.
• Temporal least privilege: grant access only for the required period.
• Administrative least privilege: separate ordinary use from system administration.
• Execution least privilege: restrict the privileges available to an application process.

Least privilege reduces the impact of compromised accounts, programming errors and insider misuse. It also improves accountability because actions can be associated with narrowly defined duties.

Separation of duties

Separation of duties prevents one person or account from completing a high-risk process alone. For example, an employee who creates a supplier should not be the only person able to approve payments to that supplier. In a database, this may be implemented through separate roles, approval tables, workflow procedures and independent audit review.

2. The Principal Access-Control Models

2.1 Discretionary Access Control

Under discretionary access control (DAC), the owner of an object, or an administrator acting on the owner’s behalf, can grant and revoke access. Traditional database privileges and SQL GRANT statements commonly exhibit DAC characteristics.

For example:

CREATE ROLE reporting_user;
GRANT SELECT ON sales.monthly_summary TO reporting_user;
REVOKE INSERT, UPDATE, DELETE ON sales.monthly_summary FROM reporting_user;

The role receives a defined privilege set. In systems that permit users to grant privileges onward, the grantor may also control delegation through a mechanism such as WITH GRANT OPTION.

Strengths of DAC include flexibility, ease of use and suitability for collaborative data sharing. Weaknesses include privilege proliferation, accidental sharing and difficulty maintaining a consistent organisation-wide policy. If users can pass permissions to others, access may spread beyond the original design.

DAC is appropriate where data owners need controlled autonomy, but it should be combined with role governance, periodic review and restrictions on privilege delegation.

2.2 Mandatory Access Control

Mandatory access control (MAC) assigns security labels to subjects and objects. Access is determined by a central policy based on those labels, rather than by the discretion of an individual data owner. Labels may represent classifications such as Public, Internal, Confidential and Restricted, together with compartments or categories.

A subject may have a clearance and an object may have a classification. The policy engine compares them before allowing access. A simplified example is:

• a user cleared for Confidential may read Confidential and lower-classified information;
• a user cleared only for Internal may not read Confidential information;
• a user may be prevented from writing information to a lower classification if that would create an information-flow risk.

The exact rules depend on the organisation’s security policy. A classification label alone does not make a database secure; labels must be accurate, enforced and protected from unauthorised modification.

MAC is useful in government, defence, research and regulated environments where information-flow rules must apply consistently. Its limitations include administrative complexity, reduced flexibility and the difficulty of assigning meaningful classifications to rapidly changing business data.

2.3 Role-Based Access Control

Role-based access control (RBAC) assigns permissions to roles and assigns users to roles. A role represents a job function or responsibility, such as claims_adjuster, auditor or warehouse_manager.

The basic relationship is:

Users → Roles → Permissions → Database objects and actions

RBAC separates the management of people from the management of permissions. If a staff member changes department, administrators can change role membership rather than individually editing every privilege.

Important RBAC concepts include:

• Role hierarchy: a senior role may inherit permissions from a junior role.
• Permission assignment: privileges are attached to roles.
• User assignment: users are attached to roles.
• Constraint: a rule limiting combinations or use of roles.
• Separation of duties: mutually exclusive roles cannot be activated by the same user or used in the same transaction.
• Session activation: a user may possess several roles but activate only those needed for a session.

Example:

CREATE ROLE claims_reader;
CREATE ROLE claims_supervisor;

GRANT SELECT ON insurance.claims TO claims_reader;
GRANT claims_reader TO claims_supervisor;
GRANT UPDATE ON insurance.claims TO claims_supervisor;

GRANT claims_reader TO analyst_07;

The precise syntax varies among database products. The security principle is portable: permissions should be granted to managed roles, not directly to large numbers of individual users.

RBAC is often the most practical foundation for business databases. However, role explosion can occur when organisations create a separate role for every department, location, project and exception. RBAC also does not naturally express dynamic conditions such as “allow access only during a scheduled shift” or “allow a clinician to see records only for assigned patients.” Such conditions motivate ABAC and row-level policies.

2.4 Attribute-Based Access Control

Attribute-based access control (ABAC) evaluates attributes of the subject, object, action and environment. A policy might state:

Permit a clinician to read a patient record when the clinician is assigned to the patient, the purpose is treatment, the request originates from an approved device, and the patient has not restricted that department.

Typical attributes include:

ABAC can express fine-grained and context-sensitive rules. It reduces the need to create a role for every possible combination of conditions. Its challenges include attribute quality, policy complexity, performance overhead and the need to secure the attribute sources themselves.

ABAC is commonly implemented together with RBAC. A role may establish a broad permission, while attributes and policies narrow the permitted records or circumstances. For example, the role regional_manager may permit access to sales data, while an attribute policy limits access to the manager’s assigned region.

3. Comparing the Models

These models are not always mutually exclusive. A database may use RBAC for normal administration, MAC-like classifications for sensitive data, DAC-style ownership for controlled collaboration and ABAC or row-level policies for contextual restrictions.

4. Configuring Permissions and Roles

Direct privileges and inherited privileges

A privilege may be assigned directly to a user or inherited through a role. Direct grants can be useful for exceptional, temporary access, but they are difficult to discover and review. A preferred pattern is:

1. define a business or technical role;
2. grant the minimum required privileges to the role;
3. assign approved users to the role;
4. record the owner, purpose and review date;
5. remove the assignment when the need ends.

Administrators must understand the difference between object privileges and system privileges. Object privileges apply to resources such as tables and procedures. System privileges permit broad actions such as creating users, altering schemas or bypassing ordinary controls. System privileges should be rare, separated from ordinary application roles and protected with stronger authentication.

Grant and revoke governance

A secure permission lifecycle includes request, approval, implementation, verification, review and revocation. A permission should have an identifiable business owner. Periodic access reviews should examine actual use, not merely intended use.

Revocation must account for inherited permissions. Removing a direct grant may not remove access if the user still receives the same permission through another role. Effective access analysis should therefore evaluate the complete privilege graph, including nested roles and public or default permissions.

Application accounts

Applications should not connect using a personal administrator account. Each application or service should have a dedicated account with a narrowly scoped role. Development, testing and production accounts should be separate. Secrets should be stored in an approved secret-management system, rotated periodically and prevented from appearing in source code or logs.

5. Views, Procedures and Column Protection

A view is a virtual table defined by a query. Views can reduce exposure by presenting only selected columns or rows.

CREATE VIEW support_customer_contact AS
SELECT customer_id, name, email
FROM crm.customer;

GRANT SELECT ON support_customer_contact TO support_agent;

If the support agent does not need payment-card data, the base table should not be granted directly. A view is especially useful for column-level minimisation, but its security depends on ownership, view evaluation rules and the privileges available to the view owner. Administrators must test whether updates through a view are possible and whether underlying objects can be reached through other paths.

Stored procedures can expose approved operations without granting direct modification rights. For example, an application may execute a procedure that changes an order status after validating business rules, while the application account has no direct UPDATE privilege on the table.

CREATE PROCEDURE sales.approve_order(order_number INTEGER)
AS
BEGIN
  -- Validate order state, approver authority and audit requirements.
  -- Perform only the permitted status change.
END;

The syntax is illustrative because procedure languages differ. A secure procedure should validate inputs, enforce authorisation, use safe transaction handling, write an audit record where appropriate and avoid dynamic SQL injection.

Views and procedures are not substitutes for a complete authorisation design. They must be combined with base-object permissions, ownership controls, auditing and testing.

6. Row-Level Security and Policy Enforcement

Table-level permission answers “may this subject access the table?” Row-level security (RLS) answers “which rows may this subject access?” RLS attaches a policy predicate to a table. The database automatically applies the predicate to relevant queries, reducing reliance on every application developer remembering to add a filter.

A conceptual policy for regional access is:

CREATE POLICY regional_sales_policy
ON sales.orders
FOR SELECT
USING (region_code = current_user_region());

The exact syntax differs among database management systems. The policy function must obtain the user’s trusted region from a secure identity or session context, not from an editable request parameter.

RLS may apply separately to:

• visibility policies: which rows may be selected;
• modification policies: which rows may be inserted, updated or deleted;
• check conditions: whether new or changed rows satisfy the policy.

For example, a tenant application may be permitted to read rows where tenant_id equals the authenticated tenant, and may be permitted to insert only rows carrying that same tenant identifier. Both read and write paths must be protected; otherwise a user may insert data into another tenant’s partition or infer protected information through error messages.

Policy enforcement principles

Effective enforcement should be:

• centralised where practical: common rules should not be duplicated across applications;
• fail-closed: inability to determine authorisation should result in denial rather than broad access;
• tamper-resistant: users should not control the attributes or functions used by the policy;
• auditable: policy decisions and administrative changes should be traceable;
• testable: positive and negative cases should be automated;
• consistent across interfaces: direct SQL, APIs, reporting tools and background jobs should not create bypasses.

Administrators must pay particular attention to privileged accounts, table owners and maintenance processes. Some systems allow an owner or administrator to bypass ordinary row policies. Such bypasses should be restricted, monitored and used only for justified operational purposes.

7. Worked Design Example: A Multi-Department Clinic

Consider a fictional clinic database containing patients, appointments, diagnoses, prescriptions and invoices. The clinic has physicians, nurses, reception staff, billing staff, external auditors and database administrators.

Requirements

• Physicians may read and update clinical records for assigned patients.
• Nurses may read relevant clinical information but may not change diagnoses.
• Reception staff may manage appointments and basic contact details.
• Billing staff may access invoices but not diagnoses.
• Auditors may read selected records for approved audits and may not alter data.
• Administrators maintain the system but should not routinely view clinical content.

Analysis

A single broad role such as clinic_user would violate least privilege. Direct grants to individuals would create high administrative overhead. The design should therefore combine RBAC and contextual controls:

• RBAC establishes roles such as physician, nurse, receptionist, billing_clerk and auditor.
• Views hide unnecessary columns.
• RLS limits clinical access to assigned patients.
• Procedures enforce controlled updates to diagnoses and prescriptions.
• An audit policy records access to sensitive clinical data.
• Temporary auditor access is approved for a defined engagement and revoked afterwards.

Illustrative implementation

CREATE ROLE physician;
CREATE ROLE nurse;
CREATE ROLE receptionist;
CREATE ROLE billing_clerk;
CREATE ROLE auditor;

GRANT SELECT ON clinic.patient_contact_view TO receptionist;
GRANT SELECT, UPDATE ON clinic.appointment TO receptionist;
GRANT SELECT ON clinic.billing_view TO billing_clerk;
GRANT SELECT ON clinic.clinical_summary_view TO nurse;
GRANT EXECUTE ON clinic.update_diagnosis TO physician;
GRANT SELECT ON clinic.audit_extract TO auditor;

The physician role should not automatically expose every patient. A row policy can require that the physician is recorded as part of the patient’s care team. The nurse view may omit sensitive fields not needed for nursing duties. The billing view may contain invoice identifiers and payment status but exclude diagnoses and clinical notes.

Outcome

The design limits unnecessary exposure, supports department changes through role assignment, and places high-risk changes behind controlled procedures. It also creates a clearer audit trail. The system remains dependent on accurate staff-role assignments, current care-team data and secure administration of policy functions.

Learning points

1. A role is a useful administrative abstraction, but it is not always sufficient for record-level decisions.
2. Views minimise columns; RLS minimises rows; procedures control approved operations.
3. Privileged administrators require separate governance because technical maintenance access may otherwise become unrestricted data access.
4. Access control must cover reporting tools, exports, backups and support interfaces, not only the main application.

8. Testing and Reviewing Access Control

Access-control testing should include both permitted and prohibited actions. A test plan should verify that:

• each role can perform required tasks;
• each role is denied unnecessary actions;
• users cannot obtain access through nested roles or public privileges;
• RLS applies to SELECT, INSERT, UPDATE and DELETE as intended;
• views do not expose hidden columns through joins or metadata;
• procedures validate authorisation and input;
• administrative bypasses are limited and logged;
• revoked users lose effective access;
• failed policy evaluations deny access;
• audit records identify the real human or service identity.

A useful review compares three sources: approved job responsibilities, configured privileges and observed access logs. Differences indicate over-privilege, unused permissions or unexpected application behaviour. Access reviews should occur after organisational changes and at a defined periodic interval.

Security testing should also consider inference. Even if a user cannot read a protected column, counts, error messages, sorting behaviour or aggregate results may reveal sensitive facts. Controls should limit query capabilities where inference risk is significant.

9. Challenges, Ethics and Governance

Role explosion and stale access

As organisations add exceptions, roles become difficult to understand. A role catalogue should specify purpose, owner, included privileges and review frequency. Unused roles and dormant accounts should be removed.

Emergency access

Healthcare and critical infrastructure may require emergency access. Break-glass access should be time-limited, justified, strongly authenticated and subject to retrospective review. Emergency capability must not become a routine alternative to proper provisioning.

Privacy and data minimisation

Access control supports privacy principles such as purpose limitation and data minimisation. A person may be legally entitled to access a system for work but not to inspect every record in that system. Organisations should define legitimate purposes, retention periods and rules for exports and secondary use.

Accountability and fairness

Automated policies based on attributes can produce unfair or incorrect outcomes if attributes are incomplete, outdated or biased. Policy decisions should be explainable to administrators, and individuals should have a process for correcting inaccurate identity or organisational data. AI-assisted access decisions should not be treated as authoritative without governance, testing and human accountability.

Regulatory considerations

Requirements vary by jurisdiction and sector, but common expectations include least privilege, access review, segregation of duties, auditability, protection of sensitive data and timely revocation. Compliance is not achieved merely by buying an access-control product; the organisation must demonstrate that controls are designed, implemented, monitored and improved.

10. Contemporary Practice and Best Practices

Modern database security increasingly combines database-native controls with identity and access-management platforms. Single sign-on, multifactor authentication, privileged-access management, central policy administration and zero-trust principles can strengthen database access, but they do not eliminate the need for precise database permissions.

Recommended practices include:

1. Define data owners, classifications and legitimate business purposes.
2. Prefer roles over large numbers of direct grants.
3. Use separate accounts for people, applications, administration and automation.
4. Grant only the required action, object, rows and columns.
5. Use views and procedures to minimise exposure and constrain operations.
6. Apply row-level policies for tenant, department, region or assignment boundaries.
7. Protect policy attributes and identity context from user modification.
8. Deny by default and fail closed when policy data is unavailable.
9. Restrict and monitor powerful accounts, including owners and service accounts.
10. Review effective permissions, not merely documented permissions.
11. Test negative cases and bypass paths after every significant policy change.
12. Log privilege changes, sensitive access, failed attempts and emergency use.
13. Revoke access promptly when duties change or employment ends.
14. Treat exports, backups, replicas and analytics platforms as protected copies.
15. Document exceptions with an owner, justification, expiry date and review record.

Chapter Summary

Database access control determines which subjects may perform which actions on which objects under specified conditions. Least privilege, separation of duties and accountability are central principles. DAC provides owner-controlled flexibility; MAC applies central classification and information-flow rules; RBAC organises permissions around job functions; and ABAC evaluates subject, object, action and environmental attributes.

In practical database design, RBAC commonly provides the administrative foundation, while views restrict columns, stored procedures constrain approved operations and row-level security restricts records. Effective policy enforcement must be centralised where possible, fail closed, resist tampering, apply consistently across interfaces and remain auditable. A secure design also includes permission lifecycle management, privileged-account governance, testing, review and prompt revocation. Access control is therefore both a technical mechanism and an organisational governance process.

Key Terms

Access control: The process of deciding and enforcing whether a subject may perform an action on a resource.

Authentication: Verification of a subject’s claimed identity.

Authorisation: Determination of actions permitted to an authenticated subject.

Attribute-Based Access Control (ABAC): Access control based on attributes of subjects, objects, actions and environments.

Discretionary Access Control (DAC): A model in which an owner or authorised grantor controls access to an object.

Least privilege: Granting only the minimum access required for an approved task.

Mandatory Access Control (MAC): A centrally enforced model using classifications, labels or information-flow rules.

Role-Based Access Control (RBAC): A model that assigns users to roles and permissions to roles.

Role hierarchy: An arrangement in which one role inherits permissions from another.

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

Privilege: An authorised ability to perform an operation on a database resource.

View: A virtual table defined by a query, often used to limit exposed rows or columns.

Stored procedure: A database program that performs controlled operations.

Row-level security: Policy enforcement that restricts access to individual table rows.

Policy predicate: A condition used to determine whether a row or operation is permitted.

Reference monitor: A mechanism that mediates access requests according to policy.

Privilege escalation: Obtaining permissions beyond those legitimately assigned.

Break-glass access: Exceptional emergency access subject to enhanced logging and review.

Effective permissions: The complete access a subject receives after direct, inherited and default privileges are considered.

Review Questions

Short Answer Questions

1. Distinguish authentication from authorisation.
2. State the principle of least privilege and give one database example.
3. What is the main difference between RBAC and ABAC?
4. How can a view reduce data exposure?
5. What problem does row-level security address?

Long Answer Questions

1. Explain DAC, MAC, RBAC and ABAC, comparing their strengths and limitations.
2. Describe how roles, views and stored procedures can be combined to protect a business database.
3. Explain the importance of separation of duties and privileged-account governance.
4. Discuss the lifecycle of a database permission from request through revocation.
5. Explain how an organisation should test and review effective database permissions.

Analytical / Application Questions

1. A regional manager can read all sales records because the database grants SELECT on the entire table. Design an RBAC and row-level policy solution that restricts access to the manager’s region.
2. A hospital gives nurses direct access to the full patient table, including payment and administrative fields. Propose a least-privilege redesign using roles, views and policies.
3. A tenant application filters records by tenant identifier in application code, but users can also connect through a reporting tool. Analyse the risk and recommend database-level enforcement.

Multiple Choice Questions with Answers

Question 1

Which access-control model assigns permissions to job functions and then assigns users to those roles?

A. Discretionary access control

B. Mandatory access control

C. Role-based access control

D. Attribute-based access control

Correct Answer: C. Role-based access control

Question 2

What is the primary purpose of least privilege?

A. To give every user administrative access

B. To grant only the access needed for an approved task

C. To eliminate authentication requirements

D. To allow users to delegate all permissions

Correct Answer: B. To grant only the access needed for an approved task

Question 3

Which control is most directly suited to restricting a user to selected records within a table?

A. Row-level security

B. Database backup

C. Password hashing

D. Schema naming

Correct Answer: A. Row-level security

Question 4

Which model bases decisions on subject, object, action and environmental attributes?

A. RBAC

B. DAC

C. MAC

D. ABAC

Correct Answer: D. ABAC

Question 5

Why are views commonly used in database security?

A. They automatically encrypt every database file

B. They can expose only selected rows or columns

C. They replace the need for authentication

D. They prevent all administrative access

Correct Answer: B. They can expose only selected rows or columns

Question 6

Which practice best reduces permission sprawl?

A. Granting privileges individually without records

B. Using shared administrator accounts

C. Managing permissions through well-defined roles

D. Allowing all users to grant privileges onward

Correct Answer: C. Managing permissions through well-defined roles

Question 7

What should normally happen when a policy engine cannot determine whether access is authorised?

A. Access should be allowed automatically

B. Access should be denied or fail closed

C. The user should receive administrator privileges

D. The database should disable auditing

Correct Answer: B. Access should be denied or fail closed

Question 8

In the clinic example, which control is most appropriate for limiting physicians to assigned patients?

A. Row-level security based on care-team assignment

B. Granting physicians unrestricted table ownership

C. Removing all audit logs

D. Using only a database password

Correct Answer: A. Row-level security based on care-team assignment

Question 9

What is separation of duties intended to prevent?

A. Use of database indexes

B. One person completing a sensitive process alone

C. Use of role hierarchies

D. Auditing of privileged activity

Correct Answer: B. One person completing a sensitive process alone

Question 10

Which statement about effective permissions is correct?

A. Only direct grants matter

B. Inherited and default privileges may also provide access

C. Revocation is unnecessary when a role remains

D. Views always prevent every possible bypass

Correct Answer: B. Inherited and default privileges may also provide access