SQL User Roles & Permissions

Database security relies on managing access to sensitive information and operations. Relational Database Management Systems (RDBMS) enforce security using an access control model based on Users, Roles, and Privileges (Permissions).

This framework allows database administrators (DBAs) to enforce the Principle of Least Privilege: giving users and applications only the exact access required to perform their tasks—and nothing more.

Core Security Concepts

   [ Privileges ]  ---> Assigned to --->  [ Roles ]  ---> Granted to --->  [ Users ]
(SELECT, INSERT, etc.)                (e.g., app_developer)             (e.g., alice_db)

• Privileges: Specific rights to execute SQL statements or access database objects (e.g., reading a table, executing a stored procedure).
• Users: Accounts created within the database engine used for authenticating individual developers, administrators, or external applications.
• Roles: Named collections of privileges. Grouping permissions into roles simplifies administrative overhead—instead of granting privileges to every user individually, you grant privileges to a role and assign users to that role.

Privileges Overview

Database privileges are generally split into two levels:

1. Object-Level Privileges

Control access to specific database objects like tables, views, sequences, or stored procedures:

• SELECT: Ability to read data from a table or view.
• INSERT: Ability to add new rows to a table.
• UPDATE: Ability to modify existing rows (can be scoped to specific columns).
• DELETE: Ability to remove rows from a table.
• EXECUTE: Ability to run stored procedures or functions.
• REFERENCES: Ability to create a foreign key referencing a table.

2. System-Level Privileges

Control administrative rights across the database instance:

• **CREATE TABLE / CREATE VIEW**: Right to create new schema objects.
• **CREATE USER / DROP USER**: Right to manage database user accounts.
• ALTER ANY TABLE: Right to modify table definitions created by any user.

Managing User Roles

Creating roles allows you to manage permissions centrally.

1. Creating a Role

CREATE ROLE analyst_role;

2. Assigning Privileges to a Role

-- Grant read-only access to specific tables
GRANT SELECT ON Customers TO analyst_role;
GRANT SELECT ON Orders TO analyst_role;

-- Grant access to all tables in a schema (PostgreSQL)
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst_role;

3. Assigning a Role to Users

-- Create database users
CREATE USER alice WITH PASSWORD 'SecurePass123!';
CREATE USER bob WITH PASSWORD 'SecurePass456!';

-- Assign the role to users
GRANT analyst_role TO alice;
GRANT analyst_role TO bob;

The GRANT Statement

The GRANT command assigns privileges or roles to users or other roles.

Basic Syntax

GRANT privilege_type [, privilege_type]
ON object_name
TO user_or_role [, user_or_role]
[WITH GRANT OPTION];

Common GRANT Examples

1. Granting Specific Table Privileges

GRANT SELECT, INSERT, UPDATE ON Products TO app_user;

2. Granting Column-Level Permissions

Restrict write access to sensitive columns (e.g., allow updating contact info, but not financial data):

GRANT UPDATE (Phone, Email, Address) ON Customers TO support_agent;

3. Granting Execution on Stored Procedures

Instead of giving direct table permissions, give users access to execute encapsulated stored procedures:

GRANT EXECUTE ON PROCEDURE ProcessPayment TO payment_service;

4. The WITH GRANT OPTION Clause

Allows the receiving user or role to pass the granted permission along to other users.

GRANT SELECT ON Orders TO team_lead WITH GRANT OPTION;

Warning: Use WITH GRANT OPTION sparingly, as it delegates administrative authority and can lead to permission sprawl.

The REVOKE Statement

The REVOKE command removes previously granted privileges or roles from users or roles.

Basic Syntax

REVOKE privilege_type [, privilege_type]
ON object_name
FROM user_or_role
[CASCADE | RESTRICT];

Common REVOKE Examples

1. Revoking Specific Table Privileges

REVOKE DELETE, UPDATE ON Orders FROM app_user;

2. Revoking All Privileges

REVOKE ALL PRIVILEGES ON Customers FROM former_employee;

3. Revoking a Role from a User

REVOKE analyst_role FROM bob;

4. Handling Cascading Revokes (CASCADE)

If User A granted a privilege to User B using WITH GRANT OPTION, revoking that privilege from User A requires specifying CASCADE in engines like PostgreSQL to revoke it from User B as well:

REVOKE SELECT ON Orders FROM team_lead CASCADE;

Best Practices for Database Security

1. Follow Least Privilege: Never connect production web applications to a database using administrator accounts (sa, root, or postgres). Create application-specific roles with permissions limited to required operations.
2. Prefer Roles over Individual Grants: Assign permissions to roles based on job functions (e.g., read_only_reporter, app_writer), then assign users to those roles.
3. Restrict Schema Modification Rights: Prevent application users from possessing ALTER, DROP, or TRUNCATE privileges in production environments.
4. Audit Permissions Periodically: Regularly inspect active database users, role memberships, and explicitly granted object permissions to eliminate orphan privileges.