Database Security: Authorization, Views and Auditing in DBMS

18
0

Introduction

Different database users need different access. A reporting user may need employee names and departments but should not see salaries or change employee records. Database security must enforce these limits even when someone submits SQL directly.

Authorization controls permitted actions, views provide selected data, and auditing records relevant activity. Together, they help limit access and make important actions easier to review.

Authentication, Authorization, and Auditing

These terms describe different responsibilities.

Concept

Main question

Example

Authentication

Who is connecting?

Verify the credentials of report_user.

Authorization

What may this identity do?

Allow reading an employee directory but not salaries.

Auditing

What activity was recorded?

Record an attempted read of the salary table and its outcome.

Successful authentication does not automatically grant access to every table.

Auditing also does not replace authorization. Recording an action does not, by itself, prevent it.

Example Table and Access Requirement

Consider an Employee table that stores employee identifiers, names, departments, and salaries.

Employee: initial data

EmployeeID

EmployeeName

Department

Salary

101

Asha

Support

60000.00

102

Ravi

Finance

75000.00

103

Meera

Support

65000.00

EmployeeID is the primary key and uniquely identifies each employee.

The reporting requirement is:

  • Show only employees in the Support department.

  • Show identifiers, names, and departments.

  • Hide the Salary column.

  • Allow reading, but not inserting, updating, or deleting records.

A view named EmployeeDirectory will expose the permitted data.

Privileges: Permissions on Database Objects

A privilege permits a particular action on a database object.

Privilege

Typical purpose

SELECT

Read data.

INSERT

Add rows.

UPDATE

Change rows or permitted columns.

DELETE

Remove rows.

EXECUTE

Run a permitted routine.

GRANT gives a privilege or role membership. REVOKE removes a grant.

Some privileges can be granted at column level. For example, a user could receive permission to update an employee’s department without permission to update salary.

WITH GRANT OPTION allows a recipient to pass an object privilege to others. It should be given only when that delegation is required.

Roles and Least Privilege

A role groups related permissions.

Instead of granting the same privileges separately to several reporting users:

  1. Create a role such as report_reader.

  2. Grant the required privileges to that role.

  3. Assign the role to the relevant users.

This follows least privilege: give only the access needed for a task.

Permissions can come from several places, including direct grants, role memberships, broad database-level grants, and ownership. Removing one grant does not guarantee that every other route to the same access has disappeared.

Authorization through roles

Authorization through roles

Using Views to Restrict Visible Data

A view is a named query that can be accessed like a table. An ordinary view stores its definition rather than an independent copy of its result rows.

A view can restrict:

  • Columns: Exclude sensitive fields such as salary.

  • Rows: Include only records that meet a condition.

For this example, EmployeeDirectory selects Support employees and excludes salary.

However, creating the view alone does not protect the base table. The reporting user must receive access to the view without another permission that exposes Employee directly.

Whose Privileges Does a View Use?

View security behaviour depends on the database and its settings.

  • MySQL: SQL SECURITY DEFINER checks underlying-object access using the view’s definer. SQL SECURITY INVOKER uses the caller’s privileges.

  • PostgreSQL: Ordinary views normally use the view owner’s privileges for underlying relations. A security_invoker view instead requires the caller’s underlying permissions.

In the example, the trusted view creator can read Employee, while the reporting user can read only the view.

PostgreSQL also provides security_barrier for views used to filter sensitive rows. It limits certain query rearrangements that could otherwise expose information through caller-supplied expressions. It does not replace correct grants.

Views Are Not Automatically Read-Only

Some simple views are updatable.

The example becomes read-only for the reporting user because that user receives only SELECT, not modification privileges.

For an updatable filtered view, WITH CHECK OPTION can prevent inserts or updates through that view from producing rows outside its filter. It does not grant write permission or prevent direct base-table access through other privileges.

A Compact SQL Example

Objective: Allow report_user to read Support employee details without exposing salary or granting direct table access.

The setup assumes:

  • MySQL already has a database named company.

  • PostgreSQL already has a schema named company in the connected database.

  • MySQL account 'report_user'@'localhost' and PostgreSQL login role report_user already exist and can connect.

  • The reporting identity has no other object privileges, inherited access, ownership, or administrative powers.

  • A trusted administrator performs the setup and owns or controls the created objects.

Account creation and authentication configuration are separate from the object permissions demonstrated here.

1. Create the Base Table

Consider the following queries to create and populate the employee table.

CREATE TABLE company.Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50) NOT NULL,
Department VARCHAR(30) NOT NULL,
Salary DECIMAL(10, 2) NOT NULL
);
INSERT INTO company.Employee
(EmployeeID, EmployeeName, Department, Salary)
VALUES
(101, 'Asha', 'Support', 60000.00),
(102, 'Ravi', 'Finance', 75000.00),
(103, 'Meera', 'Support', 65000.00);

Expected outcome: The table contains the three rows shown in the initial dataset.

2. Create the Restricted View and Role

Consider the following queries to expose the permitted rows and columns through a reporting role.

MySQL:

CREATE SQL SECURITY DEFINER VIEW company.EmployeeDirectory AS
SELECT EmployeeID, EmployeeName, Department
FROM company.Employee
WHERE Department = 'Support';
CREATE ROLE 'report_reader';
GRANT SELECT
ON company.EmployeeDirectory
TO 'report_reader';
GRANT 'report_reader'
TO 'report_user'@'localhost';
SET DEFAULT ROLE 'report_reader'
TO 'report_user'@'localhost';

The trusted creator becomes the view’s definer because no separate DEFINER account is specified.

SET DEFAULT ROLE makes the reporting role active by default in new connections for this account.

PostgreSQL:

CREATE VIEW company.EmployeeDirectory
WITH (security_barrier = true) AS
SELECT EmployeeID, EmployeeName, Department
FROM company.Employee
WHERE Department = 'Support';
CREATE ROLE report_reader NOLOGIN;
GRANT USAGE ON SCHEMA company
TO report_reader;
GRANT SELECT ON company.EmployeeDirectory
TO report_reader;
GRANT report_reader
TO report_user;

NOLOGIN makes report_reader a permission-group role rather than a login account.

USAGE ON SCHEMA allows access to objects within that schema when the required object privileges also exist. It does not grant permission to read every table.

Expected permissions

Object

Permission through report_reader

company.EmployeeDirectory

SELECT

company.Employee

No direct access

Employee modifications

Not granted

3. Read the View as the Reporting User

Open a connection as the reporting identity.

Consider the following queries to activate the reporting role and read the permitted data.

SET ROLE 'report_reader';
SELECT EmployeeID, EmployeeName, Department
FROM company.EmployeeDirectory
ORDER BY EmployeeID;

SET ROLE activates or switches to the assigned role for the session. The PostgreSQL role membership created above permits this switch.

Expected result

EmployeeID

EmployeeName

Department

101

Asha

Support

103

Meera

Support

Ravi’s row is excluded by the department filter. Salary is absent because the view does not select that column.

A view limits visible data

A view limits visible data

4. Verify That Direct Table Access Is Denied

Consider the following queries to test direct salary access using the same reporting connection.

SELECT EmployeeID, Salary
FROM company.Employee;

Expected outcome: A permission error occurs, and no salary result table is returned.

The user can read the view because its underlying access is evaluated through the trusted definer or owner. That does not give the user direct permission on the base table.

5. Remove the Role’s View Permission

Return to the administrator connection.

Consider the following queries to remove the reporting role’s read permission.

REVOKE SELECT
ON company.EmployeeDirectory
FROM 'report_reader';

Expected outcome: Subsequent view queries relying only on this role’s grant are denied.

This removes the privilege from the role, affecting all users who depended on that grant. Other independent grants must be reviewed separately.

What Is Database Auditing?

Database auditing records selected activity so that authorised reviewers can investigate access and changes.

Useful audit information includes:

  • The database identity and, where available, application identity.

  • The time and connection details.

  • The action and target object.

  • Whether the action succeeded or was denied.

  • Relevant statement or change details, according to the audit policy.

For the example, configured auditing might produce records like these:

Time

Database identity

Action

Object

Outcome

10:00:00

report_user

SELECT

EmployeeDirectory

Success

10:01:00

report_user

SELECT

Employee

Permission denied

10:05:00

security_admin

REVOKE SELECT

EmployeeDirectory

Success

These are illustrative audit records, not output automatically produced by the earlier queries. The audit facility must be configured to capture these event types.

Authorization and auditing

Authorization and auditing

Audit Logs, Change Triggers, and Recovery Logs

These mechanisms serve different purposes.

Mechanism

Main purpose

Important limit

Security audit logging

Record selected access, administrative actions, and outcomes.

Coverage depends on configuration and available features.

Row-change trigger

Record details such as old and new values for modifications.

Does not capture ordinary SELECT queries or denied access attempts.

Recovery log

Support transaction recovery and durability.

Is not a substitute for a complete security audit trail.

A trigger-based audit row usually participates in the same transaction as the change. If that transaction rolls back, its audit-table insert normally rolls back too.

General statement logging also needs careful interpretation. A statement appearing in a log does not necessarily prove that its transaction committed.

In MySQL, dedicated audit facilities depend on the installed edition or component. PostgreSQL logging can capture selected activity, while more structured auditing may require an audit extension. Neither system automatically provides every event in the illustrative audit table merely because GRANT was used.

Keeping Audit Records Useful

Audit records should be protected from unauthorised reading, modification, and deletion. Ordinary application accounts should not be able to erase the evidence of their own actions.

A useful audit policy also:

  • Selects important events instead of logging unnecessary sensitive detail.

  • Uses consistent timestamps and a defined retention period.

  • Reviews records and alerts on relevant patterns.

  • Tests whether both successful and denied actions are captured as intended.

If an application shares one database login across many people, the database may identify only that shared account. Identifying the actual person requires trusted application context or separate identities.

Views are access-control tools, not encryption or a guarantee that no information can ever be inferred from permitted results.

Summary

Authorization determines which actions an identity may perform. Roles simplify permission management, and least privilege limits unnecessary access. Views can expose selected rows and columns when their ownership settings and grants are designed correctly.

Auditing records selected activity for review, but it must be configured and protected. Restricted views, carefully managed privileges, and reliable audit records work together to control access and make important database actions traceable.

CS Core

Read Similar Blogs

Comments0