<< Home

Chapter 17: Users, Roles, and Permissions

Security in SQL databases begins with users, roles, and privileges. Every statement runs under a specific identity, and permissions determine which objects and operations are allowed. This chapter covers privilege management with GRANT and REVOKE, shows how to organize permissions with roles, applies the principle of least privilege, and explains how views can act as protective layers around data.

GRANT and REVOKE

Permissions control access to database objects such as tables, views, and routines. You can assign or remove them with GRANT and REVOKE. Each database defines its own privilege types, but core privileges are widely supported.

Privilege Purpose
SELECT Read data from tables or views.
INSERT Add new rows.
UPDATE Modify existing rows.
DELETE Remove rows.
EXECUTE Run stored procedures or functions.
USAGE Access objects like sequences or schemas (varies by engine).
ALL PRIVILEGES Grant every available permission for the object.

Granting privileges

-- Give SELECT and UPDATE rights on a table
GRANT SELECT, UPDATE ON employees TO analyst;

-- Allow a role to run a procedure
GRANT EXECUTE ON PROCEDURE payroll_run TO payroll_role;

-- Grant all privileges to an admin
GRANT ALL PRIVILEGES ON database company TO admin;

Privileges can be granted to individual users or to roles. Roles then group privileges for easier management.

💡 Use roles to group related permissions (for example, sales_reader, data_entry). Then assign roles to users instead of repeating the same GRANT statements.

Revoking privileges

REVOKE removes privileges previously granted. Without specific privileges, users lose access to the affected objects.

REVOKE UPDATE ON employees FROM analyst;

REVOKE ALL PRIVILEGES ON database company FROM temp_user;

Some databases also support REVOKE GRANT OPTION FOR, which prevents a user from passing privileges to others without removing their own access.

⚠️ Revoking a privilege from a role removes it from all users who hold that role. Be careful when roles are shared across departments.

Ownership and inheritance

Object owners automatically have full rights to their objects. In some systems (like PostgreSQL), ownership can be transferred with ALTER ... OWNER TO. In others (like MySQL), privileges depend on grant tables and not strict ownership.

Roles may inherit privileges from other roles, forming a hierarchy. Use this to model layered access (for example, junior_analyst inherits reader privileges but adds limited write access).

Managing privileges

Privilege management involves creating users, assigning roles, and auditing access. Each system provides its own syntax for creating accounts and linking authentication credentials.

Creating users and roles

-- MySQL
CREATE USER 'jane'@'%' IDENTIFIED BY 'password123';
GRANT SELECT ON company.* TO 'jane'@'%';

-- PostgreSQL
CREATE ROLE analyst LOGIN PASSWORD 'strongpass';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- SQL Server
CREATE LOGIN jane WITH PASSWORD = 'password123';
CREATE USER jane FOR LOGIN jane;
EXEC sp_addrolemember 'db_datareader', 'jane';

Use CREATE ROLE to define reusable sets of permissions. In many databases, users and roles are both represented as roles with or without the LOGIN privilege.

💡 Prefer roles over direct user grants. This simplifies revocation and auditing when staff change or projects end.

Viewing existing privileges

-- MySQL
SHOW GRANTS FOR 'jane'@'%';

-- PostgreSQL
\du jane   -- psql shortcut to show roles and privileges

-- SQL Server
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('jane');

Regularly auditing privileges helps maintain a secure environment and ensures compliance with organizational policies.

Grant chains and cascading effects

When a user is granted privileges with WITH GRANT OPTION, they can grant the same privileges to others. Revoking the original grant also removes privileges cascaded from it.

GRANT SELECT ON employees TO alice WITH GRANT OPTION;
GRANT SELECT ON employees TO bob;  -- by alice
REVOKE SELECT ON employees FROM alice CASCADE;  -- removes bob’s access too
⚠️ Avoid unnecessary WITH GRANT OPTION privileges. They can spread access beyond intended boundaries.

Least-privilege design

The principle of least privilege means giving users only the minimum rights required to perform their tasks. This limits the impact of mistakes and intrusions.

Implementing least privilege

Least privilege applies not only to human users but also to application connections. Each application should connect with a role that has access only to the schema objects it needs.

💡 Create a dedicated database user for each application component (web frontend, analytics job, backup tool). Restrict their access independently.

Temporary or time-limited privileges

Some databases support time-limited grants or scripts to revoke privileges after audits. When not supported natively, manage expiry manually via scheduled jobs or automation tools.

-- Example manual revocation
GRANT SELECT ON reports TO contractor;
-- Later
REVOKE SELECT ON reports FROM contractor;

Views as security boundaries

Views provide a controlled interface to data. They can hide sensitive columns, filter rows, or present derived information. By granting access to the view instead of the base table, you restrict what users can see or modify.

Creating secure views

-- Create a view that hides salary details
CREATE VIEW staff_public AS
SELECT employee_id, name, department
FROM employees;

GRANT SELECT ON staff_public TO hr_assistant;
REVOKE SELECT ON employees FROM hr_assistant;

This allows HR assistants to read staff information without seeing salaries or private data.

Updatable views

In many systems, simple views are updatable. More complex views (with joins, aggregates, or groupings) are read-only unless triggers or INSTEAD OF rules are defined.

-- SQL Server example
CREATE VIEW v_customers AS
SELECT customer_id, name, email FROM customers
WHERE active = 1
WITH CHECK OPTION;  -- prevents updates that violate the filter
⚠️ Use WITH CHECK OPTION to ensure updates through a view cannot modify rows outside its defined filter.

Definer and invoker security

Views can run with the privileges of either the view’s creator (definer rights) or the calling user (invoker rights). Definer rights let users access underlying data indirectly, while invoker rights enforce their own privileges.

-- MySQL example
CREATE DEFINER = 'admin'@'%' VIEW confidential_summary AS
SELECT COUNT(*) AS employee_count FROM employees;

When a normal user queries this view, it executes under the admin privileges defined at creation time, not the caller’s privileges.

💡 Use definer rights only for controlled reporting or shared dashboards. Avoid them when the logic performs updates or could expose private data indirectly.

Auditing access via views

Views can also log or trace access if combined with triggers or audit tables. For example, you can record every read of a sensitive view by inserting a log entry into an audit table through a trigger or wrapper function.

By combining roles, privileges, and carefully designed views, you can enforce fine-grained access control and maintain both usability and security within your database systems.