User Roles and Permissions

Every database system that stores sensitive or critical information must answer a fundamental question: who is allowed to do what? The answer lives in the system of user roles and permissions — a layered architecture of accounts, privileges, and access-control policies that determines which people, applications, and services can read, write, modify, or administer data. Getting this architecture right is one of the most consequential decisions in database security. A well-designed permission model limits the blast radius of a compromised credential, makes auditing straightforward, and enforces organizational policies automatically. A poorly designed one — built on shared passwords, over-broad privileges, and undocumented role assignments — can turn a single stolen credential into a catastrophic breach. This topic examines every layer of that architecture in depth.

Creating and Managing Database Users

A database user account is the foundational identity unit in any access-control system. At its most basic level, a user account is a named principal that the database engine recognizes and to which it can attach privileges. Most SQL-compliant databases create users with a variant of the CREATE USER statement:

-- PostgreSQL example
CREATE USER alice WITH PASSWORD 'Str0ng!Pass#2024';

-- SQL Server example
CREATE LOGIN alice WITH PASSWORD = 'Str0ng!Pass#2024';
CREATE USER alice FOR LOGIN alice;

-- Oracle example
CREATE USER alice IDENTIFIED BY "Str0ng!Pass#2024"
  DEFAULT TABLESPACE users
  QUOTA 100M ON users;

The syntax differs across platforms, but the intent is the same: establish a named identity that the engine can authenticate before granting any access at all.

A critical design principle is that each user account should correspond to exactly one person, application, or service. This one-to-one mapping makes it possible to answer questions like "who deleted those rows?" or "which application is executing these expensive queries?" with certainty. When multiple people share a single account — say, a generic admin or app_user account — the audit trail becomes useless because every action looks identical regardless of who actually performed it.

For application service accounts, the convention is to create a dedicated account per application, and often per environment (development, staging, production). An e-commerce platform might have accounts such as shop_app_prod, shop_reporting, and shop_etl, each with only the privileges its specific function requires.

User accounts are not static; they require active lifecycle management. Key practices include:

• Credential rotation: Passwords and authentication tokens should be changed on a regular schedule — typically every 90 days for human accounts and managed programmatically (often via secrets managers like HashiCorp Vault or AWS Secrets Manager) for service accounts. The SQL command for this is typically ALTER USER alice WITH PASSWORD 'NewPass!' in PostgreSQL or ALTER LOGIN alice WITH PASSWORD = 'NewPass!' in SQL Server.
• Account deactivation and removal: When an employee leaves or an application is decommissioned, the corresponding account must be disabled immediately. Leaving dormant accounts active is a well-known attack vector. Disabling rather than immediately deleting an account preserves the audit trail while preventing any further access: ALTER USER alice NOLOGIN; in PostgreSQL, or ALTER LOGIN alice DISABLE; in SQL Server.
• Periodic account review: A scheduled review — often quarterly — should confirm that every existing account still has a legitimate owner, the right level of access, and a current password. Accounts with no recent login activity are strong candidates for deactivation.

Separating individual accounts from shared or generic accounts also simplifies the work of security auditors. When an audit report shows that alice accessed the payments table at 2 a.m. on a Sunday, investigators know exactly whose workstation to examine. That clarity disappears entirely when the audit log shows only that app_user performed the action.

Privileges and Access Rights

Having a user account only proves identity — it does not automatically grant the ability to do anything useful inside the database. That ability comes from privileges, which are explicit permissions attached to an account (or to a role the account inherits). Privileges fall into two broad categories: object-level and system-level.

Object-level privileges control what a user can do with a specific database object — a table, view, sequence, stored procedure, or schema. The standard SQL object privileges include:

System-level privileges (called system privileges in Oracle, server roles or permissions in SQL Server, and various role attributes in PostgreSQL) govern capabilities that transcend individual objects. Examples include the ability to create new databases or schemas, create or drop user accounts, perform backups and restores, kill running sessions, or read the server error log. Because system-level privileges can affect the entire server, they should be granted extremely sparingly and only to dedicated administrative accounts.

The SQL commands for assigning and removing privileges are GRANT and REVOKE. Their basic syntax is consistent across most SQL-compliant databases:

-- Grant SELECT on a specific table to a user
GRANT SELECT ON TABLE orders TO alice;

-- Grant multiple privileges at once
GRANT SELECT, INSERT, UPDATE ON TABLE products TO bob;

-- Grant privileges on all tables in a schema (PostgreSQL)
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO reporting_user;

-- Grant a system-level privilege (SQL Server)
GRANT CREATE TABLE TO alice;

-- Revoke a previously granted privilege
REVOKE DELETE ON TABLE orders FROM alice;

-- Revoke all privileges on a table
REVOKE ALL PRIVILEGES ON TABLE orders FROM bob;

An important nuance is that REVOKE only removes privileges that were granted directly. If the same privilege was also conferred via a role, the role grant must be revoked separately. This is one of many reasons why managing privileges through roles rather than direct grants is strongly preferred — it keeps the permission model easier to reason about and audit.

Principle of Least Privilege

The principle of least privilege (PoLP) states that every user, application, or process should have access to exactly the resources it needs to perform its legitimate function — and nothing more. This is not merely a best practice; it is a foundational security control that appears in frameworks such as NIST SP 800-53, ISO 27001, and the CIS Controls.

The practical motivation is straightforward: if a credential is stolen or an account is compromised, the attacker inherits only the privileges of that account. An application service account that can only SELECT from one schema cannot be used to drop tables, exfiltrate an entire database, or escalate privileges further. An overly permissive account with broad UPDATE, DELETE, or administrative rights turns a credential theft into a catastrophic breach.

A concrete example illustrates the difference. Consider a web application that displays product listings. It only needs to read from the products and categories tables:

-- Overly permissive (dangerous)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO webapp_user;

-- Least-privilege (correct)
GRANT SELECT ON TABLE products TO webapp_user;
GRANT SELECT ON TABLE categories TO webapp_user;

With the first grant, if an attacker exploits an SQL injection vulnerability in the application, they can read every table including users and payment_methods, delete records, and potentially escalate further. With the second grant, the worst outcome is limited to reading product data — which is already publicly visible anyway.

Least privilege must be enforced at every layer of the stack:

• Database user accounts: Grant only the specific object privileges needed, on specific objects, within specific schemas.
• Application service accounts: The account used by an ORM or connection pool should never be a DBA or schema owner. It should have no DDL privileges (no CREATE, ALTER, DROP) in production.
• Administrative access: DBA accounts should be used only when administrative work is actively being performed, not for routine application operations. Some organizations implement "break-glass" procedures for superuser access, requiring approval and full audit logging before the credentials are released.
• Operating system and network layers: Least privilege at the database layer is undermined if the database server process itself runs as root, or if firewall rules allow any host to connect on the database port.

Privilege auditing — the regular, systematic review of what privileges exist and whether they are still justified — is necessary to maintain a least-privilege posture over time. Permissions accumulate. A developer granted temporary UPDATE access to fix a production incident may never have that access revoked. Quarterly privilege reviews, combined with automated queries against the system catalog, help surface these anomalies:

-- PostgreSQL: list all table-level privileges granted to non-superuser roles
SELECT grantee, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee NOT IN ('postgres', 'PUBLIC')
ORDER BY grantee, table_schema, table_name;

-- SQL Server: list explicit permissions granted to database principals
SELECT dp.name AS principal, o.name AS object_name,
       p.permission_name, p.state_desc
FROM sys.database_permissions p
JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
LEFT JOIN sys.objects o ON p.major_id = o.object_id
WHERE dp.type NOT IN ('R') -- exclude roles
ORDER BY dp.name, o.name;

Role-Based Access Control (RBAC)

Managing privileges one user at a time does not scale. In an organization with dozens of developers, a reporting team, a group of ETL engineers, and a small DBA team, granting and revoking privileges individually for each person is slow, error-prone, and produces an inconsistent permission landscape that is nearly impossible to audit. Role-Based Access Control solves this by introducing an abstraction layer: the role.

A role is a named collection of privileges. Instead of granting SELECT on twenty tables to every developer individually, you grant those privileges to a role called developer_readonly, and then grant that role to each developer. The privileges are defined once, applied consistently, and modified in one place when requirements change.

-- PostgreSQL RBAC example

-- Step 1: Create the role
CREATE ROLE developer_readonly;

-- Step 2: Grant object privileges to the role
GRANT USAGE ON SCHEMA app TO developer_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO developer_readonly;

-- Step 3: Grant the role to users
GRANT developer_readonly TO alice;
GRANT developer_readonly TO bob;
GRANT developer_readonly TO carol;

-- Now alice, bob, and carol all have identical, consistent SELECT access

The benefits of this approach are substantial:

• Consistency: Every member of a role has exactly the same permissions. There is no risk that one developer has UPDATE access that others do not because someone made a typographical error during a manual grant.
• Reduced administrative overhead: Adding a new developer requires only GRANT developer_readonly TO new_developer;. Removing one requires only REVOKE developer_readonly FROM departing_developer;. There is no need to enumerate individual object privileges.
• Easier auditing: Auditors can review the definition of each role rather than scanning thousands of individual privilege records.
• Faster onboarding and offboarding: Role grants and revocations take effect immediately, eliminating the risk that a new hire cannot work because their privileges were not fully provisioned, or that a departed employee retains access because revocation was incomplete.

Role hierarchies add further expressiveness. In most enterprise database systems, roles can be granted to other roles, creating an inheritance tree. A senior_developer role might inherit everything from developer_readonly and add write privileges on top:

-- Create base role
CREATE ROLE developer_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO developer_readonly;

-- Create elevated role that inherits the base role
CREATE ROLE senior_developer;
GRANT developer_readonly TO senior_developer;  -- inheritance
GRANT INSERT, UPDATE, DELETE ON TABLE app.feature_flags TO senior_developer;

-- Grant the senior role to a specific user
GRANT senior_developer TO dave;

Dave now has SELECT on all tables (inherited from developer_readonly) plus write access to feature_flags. If the base developer_readonly role later gains access to a new table, Dave's access automatically expands — no separate grant required.

All major enterprise database platforms support native RBAC:

Granting and Revoking Roles

Granting a role to a user is the mechanism by which all of the role's accumulated privileges become available to that user — without having to list each privilege individually. The grant takes effect immediately (within the same session in some databases; in others the user must reconnect or re-authenticate):

-- Grant a role to a user
GRANT reporting_analyst TO alice;

-- Grant multiple roles at once (PostgreSQL)
GRANT developer_readonly, etl_writer TO bob;

-- Confirm role membership (PostgreSQL)
SELECT rolname, member::regrole
FROM pg_auth_members
JOIN pg_roles ON pg_roles.oid = pg_auth_members.roleid
WHERE rolname = 'developer_readonly';

Revoking a role is equally immediate and comprehensive — when REVOKE developer_readonly FROM alice; executes, Alice loses every privilege that came from that role at once. This makes offboarding clean and reliable. There is no risk of forgetting to revoke one of twenty individual privileges because only one revocation statement is needed.

The WITH GRANT OPTION clause deserves special attention because it is frequently misused. When a role (or privilege) is granted with this option, the recipient can in turn grant the same role or privilege to others:

-- Alice can now grant developer_readonly to anyone she chooses
GRANT developer_readonly TO alice WITH GRANT OPTION;

-- Alice then grants the role to dave (without a DBA doing so)
-- As alice:
GRANT developer_readonly TO dave;

This creates a delegation chain that is difficult to track. If Alice later has her role revoked, what happens to Dave's grant? Behavior varies by platform. More importantly, it means privilege expansion can happen outside the DBA's awareness. WITH GRANT OPTION should be used only in specific, well-documented scenarios — for example, a team lead who is explicitly authorized to manage access for their team — and it should be monitored as part of the privilege audit process.

Separation of Duties and Administrative Roles

Separation of duties (SoD) is the principle that no single person or account should have unchecked end-to-end control over a sensitive process. In database administration, this means distinguishing between users who access data operationally and those who administer the database engine itself — and ensuring these are not the same people using the same accounts.

Highly privileged roles — database administrator, superuser, sysadmin in SQL Server, SYSDBA in Oracle — carry the power to create or destroy entire databases, read any data regardless of object-level privileges, modify audit logs, and bypass most security controls. For this reason:

• These roles should be assigned to as few accounts as possible — ideally one or two named individuals per system, plus a documented emergency break-glass account.
• DBA accounts should be distinct from the personal accounts those individuals use for non-administrative work. A DBA might have both alice (used for querying data as part of their job) and alice_dba (used only when performing administrative tasks). This ensures that routine work does not accidentally happen under an over-privileged context.
• Administrative actions must be logged separately and monitored. Every DDL statement, privilege change, and authentication event performed by a superuser account should be captured in an immutable audit log — ideally one that the superuser cannot modify. Database-native audit extensions (pgAudit for PostgreSQL, Oracle Unified Auditing, SQL Server's SQL Audit) provide this capability. Anomalies like a DBA account running SELECT queries against the payments table at 3 a.m. should trigger alerts.

Combining operational and administrative privileges in a single account is a common but dangerous pattern. A developer who is also a DBA on the same account might accidentally run a destructive DDL command in production when they intended to run it in development. Worse, if their credentials are phished, the attacker gets full administrative control. The separation not only limits human error; it limits attacker capability.

A well-structured role hierarchy for a medium-sized team might look like this:

This table illustrates a key property of well-designed RBAC: roles should be named for job functions, not for individual people, and the set of roles should cover every legitimate access pattern without overlap or unnecessary breadth. When a new person joins the team, they receive the role that matches their function — nothing more, nothing less.

Taken together, these practices — careful account creation, precise privilege assignment, least-privilege enforcement, role-based management, and separation of administrative duties — form a defense-in-depth model for database access control. Each layer independently reduces risk, and together they make unauthorized access, accidental data loss, and insider threats substantially harder to execute and easier to detect.