SQL Data Control Language (DCL)

DCL is the subset of SQL concerned with access control — who can do what within a database. It consists of two commands:

GRANT — gives a user or role permission to perform specific actions
REVOKE — removes those permissions

Good permission management is a core part of database security: the principle of least privilege says every user should have exactly the access they need, nothing more.

Users and Roles

Modern databases use roles rather than direct per-user permissions. A role is a named collection of privileges that can be assigned to users:

-- Create a role for read-only analysts
CREATE ROLE analyst;

-- Create a role for application users
CREATE ROLE app_user;

-- Create a user and assign a role
CREATE USER alice WITH PASSWORD 'secure_password_here';
GRANT analyst TO alice;

Using roles means you grant privileges to the role once, then assign the role to many users — much easier to manage than per-user permissions.

GRANT: Giving Permissions

Grant SELECT on a specific table:

GRANT SELECT ON products TO analyst;

Grant multiple privileges:

GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

Grant on all tables in a schema:

-- PostgreSQL: grant read access to all current tables in the public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Grant on future tables too (PostgreSQL default privileges)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO analyst;

Grant execute permission on a function:

GRANT EXECUTE ON FUNCTION calculate_tax(NUMERIC) TO app_user;

Grant with the ability to pass it on (WITH GRANT OPTION):

GRANT SELECT ON reports TO team_lead WITH GRANT OPTION;
-- team_lead can now grant SELECT on reports to others

Common Permission Types

SELECT    — read rows from a table
INSERT    — add rows to a table
UPDATE    — modify rows in a table
DELETE    — remove rows from a table
TRUNCATE  — clear all rows from a table
EXECUTE   — run a function or stored procedure
USAGE     — use a schema or sequence
ALL       — all permissions (use carefully)

REVOKE: Removing Permissions

-- Revoke SELECT permission
REVOKE SELECT ON products FROM analyst;

-- Revoke multiple privileges
REVOKE INSERT, UPDATE, DELETE ON orders FROM contractor;

-- Revoke all privileges on a table
REVOKE ALL ON customers FROM temp_user;

-- Revoke all privileges across all tables in a schema
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM former_employee;

Schema-Level Permissions

Permissions work at multiple levels: database, schema, table, column, row.

-- Allow a role to see the schema (required to access objects within it)
GRANT USAGE ON SCHEMA reporting TO analyst;

-- Allow access to tables within the schema
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst;

Without USAGE on the schema, a user can’t access any objects in it even if they have table-level grants.

Column-Level Permissions

Restrict access to specific columns — useful for sensitive data:

-- Allow reading all product columns EXCEPT cost_price
GRANT SELECT (product_id, name, price, category_id) ON products TO analyst;
-- analyst can NOT select cost_price

-- Grant UPDATE only on specific columns
GRANT UPDATE (status, updated_at) ON orders TO support_agent;

Row-Level Security (PostgreSQL)

For fine-grained control, PostgreSQL supports row-level security (RLS) — different users see different rows in the same table:

-- Enable RLS on the table
ALTER TABLE customer_data ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see rows belonging to their organization
CREATE POLICY org_isolation ON customer_data
    USING (org_id = current_setting('app.current_org_id')::INTEGER);

This is commonly used in multi-tenant applications where data from different organizations must be isolated.

Real-World Permission Structure

A typical permission hierarchy for a data platform:

-- Read-only analysts (BI dashboards, ad-hoc queries)
CREATE ROLE analyst;
GRANT USAGE ON SCHEMA public, reporting TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO analyst;

-- Application service accounts (only what the app needs)
CREATE ROLE app_readonly;
CREATE ROLE app_readwrite;

GRANT SELECT ON customers, products TO app_readonly;
GRANT SELECT, INSERT, UPDATE ON orders, sessions TO app_readwrite;

-- dbt / ETL service account
CREATE ROLE etl_service;
GRANT USAGE ON SCHEMA staging, public TO etl_service;
GRANT ALL ON ALL TABLES IN SCHEMA staging TO etl_service;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO etl_service;
GRANT INSERT ON ALL TABLES IN SCHEMA reporting TO etl_service;

-- Assign roles to users
GRANT analyst TO alice, bob;
GRANT app_readwrite TO api_service_account;
GRANT etl_service TO dbt_service_account;

Security Best Practices

Principle of least privilege. Grant only what’s needed. A BI analyst needs SELECT, not INSERT/DELETE. An ETL job writing to staging doesn’t need access to the production customer table.

Use roles, not user-level grants. Grant privileges to roles, assign roles to users. When someone’s role changes, update their role assignment — not dozens of individual grants.

Revoke ALL from PUBLIC. By default in PostgreSQL, the public role (which all users inherit) has broad permissions. Lock it down:

REVOKE ALL ON DATABASE mydb FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public TO app_user, analyst;

Rotate service account credentials. Application passwords and connection strings should rotate regularly. Use secrets managers (AWS Secrets Manager, HashiCorp Vault) rather than hardcoding credentials.

Audit privilege grants. Know who has access to what. In PostgreSQL, query the information_schema.role_table_grants view:

SELECT grantee, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
ORDER BY table_name, grantee;