Database Management SystemsDatabase Languages

Database Languages: The Complete Language Toolkit

LevelBeginner

Duration75 mins

TopicDatabase Languages

3 / 5

Data Control Language (DCL)

The Language That Guards Your Data

A database without security is a database waiting to be breached. As data has become the most valuable asset of modern organizations, the systems that protect access to that data have become critical infrastructure. Every unauthorized data access, every privilege escalation, every insider threat—these are the adversaries that Data Control Language (DCL) is designed to defend against.

DCL is the security layer of SQL. While DDL defines what objects exist and DML manipulates their contents, DCL determines who can do what to those objects. Every database user, every application connection, every administrative action is governed by permissions defined through DCL statements.

In an era of data breaches, regulatory compliance (GDPR, HIPAA, SOX, PCI-DSS), and zero-trust security models, DCL is not optional—it is fundamental. A database administrator who cannot properly configure access control is a liability; a developer who ignores security principles invites catastrophe.

What You Will Learn

By the end of this page, you will deeply understand DCL's two primary statements—GRANT and REVOKE—their precise semantics, the types of privileges they control, and how they interact with database users, roles, and objects. You'll learn role-based access control patterns, the principle of least privilege, and real-world security implementation strategies.

Understanding Data Control Language

Data Control Language (DCL) is the subset of SQL that provides commands for controlling access to database objects. DCL manages the security model of the database—determining which users can perform which operations on which objects.

The Two Fundamental DCL Commands:

DCL consists of just two primary statements:

DCL Statements
Statement	Purpose	Security Effect
GRANT	Give permissions to users or roles	Expands what a user can do
REVOKE	Remove permissions from users or roles	Restricts what a user can do

The DCL Security Model:

DCL implements a discretionary access control (DAC) model. In DAC:

Every database object has an owner (typically the user who created it)
The owner has full privileges on the object
The owner can grant privileges to other users (discretion)
Privileges can be granted with or without the ability to further grant to others
Privileges can be revoked to restrict access

This contrasts with mandatory access control (MAC) where a central authority strictly controls all access. Most relational databases use DAC, giving object owners significant control over their objects' security.

The Principle of Least Privilege

The foundational security principle for DCL is 'least privilege': every user should have exactly the permissions required to perform their job function—no more, no less. Start with zero permissions and grant only what's necessary. This limits the damage from compromised accounts and reduces the attack surface of your database.

Types of Database Privileges

Privileges in database systems are categorized into multiple types, each controlling different aspects of database access. Understanding this hierarchy is essential for effective security design.

Privilege Categories and Their Scope
Category	Scope	Examples	Typical Recipients
System Privileges	Database-wide operations	CREATE TABLE, CREATE USER, BACKUP DATABASE	DBAs, privileged roles
Object Privileges	Specific database objects	SELECT on table X, EXECUTE on procedure Y	Application users, developers
Column Privileges	Specific columns within tables	SELECT(salary) on employees	Limited access roles
Schema Privileges	All objects within a schema	ALL PRIVILEGES ON SCHEMA sales	Schema owners, team leads

Common Object Privileges:

Different database objects support different privilege types:

Object Privileges by Object Type
Object Type	Available Privileges	Description
TABLE	SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRUNCATE	Full CRUD and referential access
VIEW	SELECT, INSERT, UPDATE, DELETE	Access through the view (if updateable)
SEQUENCE	USAGE, SELECT	Generate next value, query current value
FUNCTION/PROCEDURE	EXECUTE	Call the function or procedure
SCHEMA	CREATE, USAGE	Create objects in schema, access schema objects
DATABASE	CONNECT, CREATE, TEMPORARY	Connect to database, create schemas, create temp tables

privilege_hierarchy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- =====================================================
-- Privilege Hierarchy Examples
-- =====================================================
 
-- SYSTEM PRIVILEGES (database-wide)
-- These control the ability to perform administrative operations
GRANT CREATE TABLE TO developer_role;
GRANT CREATE USER TO admin_role;
GRANT BACKUP DATABASE TO backup_operator;
 
-- OBJECT PRIVILEGES (specific table)
-- These control access to individual database objects
GRANT SELECT ON employees TO reporting_user;
GRANT INSERT, UPDATE ON orders TO order_entry_app;
GRANT ALL PRIVILEGES ON products TO inventory_manager;
 
-- COLUMN-LEVEL PRIVILEGES (specific columns)
-- Fine-grained control within a table
GRANT SELECT (employee_id, first_name, last_name, department_id) 
ON employees TO directory_app;
-- Notice: salary, SSN, and other sensitive columns NOT granted
 
GRANT UPDATE (email, phone) ON employees TO self_service_app;
-- Users can update their own contact info, but not salary
 
-- SCHEMA PRIVILEGES (all objects in schema)
-- Useful for team-based access
GRANT USAGE ON SCHEMA sales TO sales_team;
GRANT CREATE ON SCHEMA sales TO sales_developers;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales TO sales_admin;

ALL PRIVILEGES: Use With Caution

GRANT ALL PRIVILEGES gives complete access to an object. While convenient, it violates the principle of least privilege. If a user only needs SELECT and INSERT, granting ALL also gives them DELETE and UPDATE—permissions they shouldn't have. Always enumerate specific privileges unless ALL is genuinely required.

GRANT: Bestowing Privileges

The GRANT statement assigns privileges to database principals (users, roles, or groups). It is the primary mechanism for enabling access to database resources.

GRANT Syntax and Semantics:

The general form of GRANT is:

GRANT <privileges> ON <object> TO <principal> [WITH GRANT OPTION];

The GRANT executes immediately and takes effect for the principal's next database action. Existing sessions may not immediately see new privileges (depending on caching).

grant_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- =====================================================
-- GRANT Statement: Complete Examples
-- =====================================================
 
-- 1. Grant single privilege to single user
GRANT SELECT ON customers TO analyst_user;
 
-- 2. Grant multiple privileges to single user
GRANT SELECT, INSERT, UPDATE ON orders TO order_service;
 
-- 3. Grant to multiple users at once
GRANT SELECT ON reports TO user1, user2, user3;
 
-- 4. Grant to PUBLIC (all users - use sparingly!)
GRANT SELECT ON public_announcements TO PUBLIC;
 
-- 5. Grant all privileges (owner-level access)
GRANT ALL PRIVILEGES ON inventory TO warehouse_manager;
 
-- 6. Grant with ability to further grant (delegation)
GRANT SELECT ON products TO team_lead WITH GRANT OPTION;
-- team_lead can now: GRANT SELECT ON products TO team_member;
 
-- 7. Grant on all tables in schema (PostgreSQL)
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO report_readers;
 
-- 8. Grant on future tables (PostgreSQL default privileges)
ALTER DEFAULT PRIVILEGES IN SCHEMA sales 
GRANT SELECT ON TABLES TO sales_analysts;
-- New tables created in 'sales' schema will auto-grant SELECT
 
-- 9. Grant column-level privileges
GRANT SELECT (id, name, department) ON employees TO directory_service;
GRANT UPDATE (email, phone) ON employees TO self_service_portal;
 
-- 10. Grant EXECUTE on functions/procedures
GRANT EXECUTE ON FUNCTION calculate_tax(numeric) TO billing_app;
GRANT EXECUTE ON PROCEDURE process_order(integer) TO order_processor;
 
-- 11. Grant schema privileges
GRANT USAGE ON SCHEMA confidential TO cleared_users;
GRANT CREATE ON SCHEMA development TO dev_team;
 
-- 12. Grant database-level privileges (PostgreSQL)
GRANT CONNECT ON DATABASE production TO app_user;
GRANT CREATE ON DATABASE staging TO developer;
 
-- 13. Grant sequence privileges
GRANT USAGE ON SEQUENCE order_id_seq TO order_service;
 
-- 14. System privileges (Oracle-style)
-- GRANT CREATE TABLE TO developer_role;
-- GRANT CREATE SESSION TO app_user;
-- GRANT DBA TO admin_user;

WITH GRANT OPTION: Privilege Delegation

The WITH GRANT OPTION clause is powerful and dangerous. It allows the recipient to grant the same privilege to other users. This creates a chain of privilege delegation:

Owner → User A (WITH GRANT OPTION) → User B (WITH GRANT OPTION) → User C

Each user in the chain can grant to others. If User A's privilege is later revoked, what happens to Users B and C depends on the REVOKE syntax (CASCADE vs RESTRICT).

Use Roles, Not Direct User Grants

Instead of granting privileges directly to users, grant to roles, then assign users to roles. This simplifies administration: when an employee changes teams, change their role membership rather than modifying dozens of individual grants. It also enables consistent policy enforcement across user populations.

REVOKE: Withdrawing Privileges

The REVOKE statement removes privileges from database principals. It is the mechanism for restricting access when users change roles, leave the organization, or when permissions need to be tightened.

REVOKE Syntax and Semantics:

The general form of REVOKE is:

REVOKE <privileges> ON <object> FROM <principal> [CASCADE | RESTRICT];

REVOKE takes effect immediately for new operations. Existing sessions may continue using cached privileges until they reconnect or refresh their permission cache.

revoke_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- =====================================================
-- REVOKE Statement: Complete Examples
-- =====================================================
 
-- 1. Revoke single privilege from single user
REVOKE SELECT ON customers FROM former_employee;
 
-- 2. Revoke multiple privileges
REVOKE INSERT, UPDATE, DELETE ON orders FROM demoted_user;
 
-- 3. Revoke all privileges
REVOKE ALL PRIVILEGES ON sensitive_data FROM compromised_account;
 
-- 4. Revoke from multiple users
REVOKE SELECT ON reports FROM user1, user2, user3;
 
-- 5. Revoke from PUBLIC -- 6. Revoke GRANT OPTION (but keep the privilege)
REVOKE GRANT OPTION FOR SELECT ON products FROM team_lead;
-- team_lead can still SELECT, but cannot grant to others
 
-- 7. CASCADE revoke: Remove from grantees of the revokee
REVOKE SELECT ON data FROM manager CASCADE;
-- If manager granted to subordinates, their access is also revoked
 
-- 8. RESTRICT revoke: Fail if others have dependent grants
REVOKE SELECT ON data FROM manager RESTRICT;
-- Fails if manager granted to others (must revoke them first)
 
-- 9. Revoke schema privileges
REVOKE USAGE ON SCHEMA confidential FROM former_cleared_user;
REVOKE CREATE ON SCHEMA development FROM former_developer;
 
-- 10. Revoke column privileges
REVOKE UPDATE (salary) ON employees FROM basic_hr_role;
 
-- 11. Revoke on all tables in schema (PostgreSQL)
REVOKE SELECT ON ALL TABLES IN SCHEMA finance FROM departing_employee;
 
-- 12. Revoke EXECUTE on functions
REVOKE EXECUTE ON FUNCTION sensitive_calculation() FROM untrusted_user;
 
-- 13. Complete access removal workflow
-- Step 1: Revoke object privileges
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM terminated_user;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM terminated_user;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM terminated_user;
 
-- Step 2: Revoke schema access
REVOKE USAGE ON SCHEMA public FROM terminated_user;
 
-- Step 3: Revoke database connection
REVOKE CONNECT ON DATABASE production FROM terminated_user;
 
-- Step 4: Disable/drop the user account
-- ALTER USER terminated_user WITH NOLOGIN;
-- or: DROP USER terminated_user;

CASCADE vs RESTRICT Semantics:

When a user with GRANT OPTION has granted privileges to others, REVOKE behavior depends on the CASCADE/RESTRICT keyword:

CASCADE: The privilege is revoked from the specified user AND from everyone they granted it to. This can cause a chain of revocations.
RESTRICT: The REVOKE fails if the user has granted the privilege to anyone else. You must explicitly revoke from the downstream users first.

CASCADE is powerful but can have unintended consequences. In complex grant chains, a single CASCADE REVOKE can disable access for many users.

The Orphaned Permission Problem

When users are terminated, it's critical to revoke all their privileges before removing their account. Otherwise, if the username is reused, the new user may inherit the old user's permissions. Always follow a complete privilege removal workflow: revoke object privileges, schema access, and database connection before disabling or dropping the user account.

Roles and Role-Based Access Control

Role-Based Access Control (RBAC) is the industry-standard approach to managing database permissions at scale. Instead of granting privileges directly to users, privileges are granted to roles, and users are assigned to roles.

The RBAC Model:

Privileges → Roles → Users

[SELECT on orders]     →  [OrderReader]  →  [analyst1, analyst2, analyst3]
[INSERT on orders]     →  [OrderWriter]  →  [app_service, order_entry_user]
[ALL on orders]        →  [OrderAdmin]   →  [order_manager]

This indirection provides enormous administrative benefits:

Benefits of Role-Based Access Control

•Simplified Administration — Change a role once, affect all members. When permissions change, update the role, not individual users.
•Consistent Enforcement — All users in a role have identical permissions. No accidental permission drift between similar users.
•Easy Onboarding/Offboarding — Add new employees to roles; remove departing employees from roles. No individual privilege management.
•Auditability — Role membership is easier to review than scattered individual grants. Compliance audits check 'who is in what role'.
•Role Hierarchy — Roles can inherit from other roles. A 'DBA' role can include all privileges of 'Developer' plus additional admin rights.
•Separation of Duties — Create roles that, combined, provide complete access, but individually are limited. Fraud prevention through division.

role_management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- =====================================================
-- Role-Based Access Control Implementation
-- =====================================================
 
-- 1. CREATE ROLES for different access levels
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;
 
-- 2. GRANT privileges TO roles (not directly to users)
-- Read-only role: SELECT on all relevant tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
 
-- Read-write role: SELECT + INSERT + UPDATE + DELETE
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
GRANT USAGE ON SCHEMA public TO readwrite;
 
-- Admin role: All privileges + DDL
GRANT ALL PRIVILEGES ON SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;
 
-- 3. ROLE INHERITANCE: Admin includes readwrite includes readonly
GRANT readonly TO readwrite;
GRANT readwrite TO admin;
-- Now: admin has readonly + readwrite + admin privileges
 
-- 4. CREATE users and ASSIGN to roles
CREATE USER analyst1 WITH PASSWORD 'secure_password_1';
CREATE USER analyst2 WITH PASSWORD 'secure_password_2';
CREATE USER app_service WITH PASSWORD 'app_service_key';
CREATE USER dba_user WITH PASSWORD 'admin_secure_pass';
 
GRANT readonly TO analyst1;
GRANT readonly TO analyst2;
GRANT readwrite TO app_service;
GRANT admin TO dba_user;
 
-- 5. REVOKE role membership when user changes role
REVOKE readonly FROM analyst1;
GRANT readwrite TO analyst1;  -- Promoted!
 
-- 6. Application-specific roles
CREATE ROLE order_processing;
GRANT SELECT, INSERT ON orders TO order_processing;
GRANT SELECT, INSERT ON order_items TO order_processing;
GRANT USAGE ON SEQUENCE order_id_seq TO order_processing;
GRANT EXECUTE ON FUNCTION calculate_order_total(integer) TO order_processing;
 
CREATE ROLE payment_processing;
GRANT SELECT, INSERT, UPDATE ON payments TO payment_processing;
GRANT SELECT ON orders TO payment_processing;
GRANT EXECUTE ON FUNCTION process_payment(integer, numeric) TO payment_processing;
 
-- 7. Default privileges for future objects (PostgreSQL)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly;
 
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
 
-- 8. View role memberships
-- PostgreSQL: SELECT * FROM pg_roles;
-- PostgreSQL: \du  (in psql)

The Standard Role Hierarchy

Most organizations implement a tiered role structure: (1) readonly — SELECT only for reporting/analytics. (2) readwrite — Full DML for application services. (3) developer — readwrite + DDL on development schemas. (4) dba_operator — System monitoring, backup/restore. (5) dba_admin — Full administrative access. Start with this foundation and customize for your specific needs.

Database Security Best Practices

Effective database security extends beyond DCL syntax to encompass design patterns, operational practices, and security architecture. The following best practices represent industry standards for securing database access.

Essential Security Practices

•1. Principle of Least Privilege — Grant only the minimum permissions required. If SELECT suffices, don't grant UPDATE. If one table is needed, don't grant schema-wide access.
•2. Use Application-Specific Accounts — Each application should have its own database user with only the permissions that application needs. Never share credentials between applications.
•3. Separate Read and Write Accounts — Use different credentials for read operations (reporting, analytics) and write operations. A compromised read-only account cannot corrupt data.
•4. Never Use Default Passwords — Default postgres/root/sa passwords are the first thing attackers try. Always set strong, unique passwords during installation.
•5. Disable/Rename Default Accounts — Default 'sa' or 'postgres' accounts are attack targets. Create named admin accounts and disable defaults.
•6. Implement Password Policies — Enforce password complexity, rotation, and history. Consider certificate-based or external authentication (LDAP/Active Directory).
•7. Encrypt Connections — Always use TLS/SSL for database connections. Network-sniffed credentials enable full database access.
•8. Regular Access Reviews — Periodically audit who has access to what. Remove access for terminated employees; reduce excessive privileges.
•9. Audit Privilege Changes — Log all GRANT and REVOKE operations. Security incidents require understanding how access changed over time.
•10. Use Rows-Level Security Where Needed — When different users should see different data in the same table, implement row-level security (RLS) policies.

security_implementation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- =====================================================
-- Security Implementation Patterns
-- =====================================================
 
-- 1. Application-specific user with minimal privileges
CREATE USER order_app WITH PASSWORD 'randomly_generated_secret_key';
GRANT CONNECT ON DATABASE production TO order_app;
GRANT USAGE ON SCHEMA orders TO order_app;
GRANT SELECT, INSERT ON orders.orders TO order_app;
GRANT SELECT, INSERT ON orders.order_items TO order_app;
GRANT USAGE ON SEQUENCE orders.order_id_seq TO order_app;
-- Nothing else! No DELETE, no other tables, no other schemas.
 
-- 2. Read replica user (strictly read-only)
CREATE USER analytics_readonly WITH PASSWORD 'analytics_key';
GRANT CONNECT ON DATABASE production TO analytics_readonly;
GRANT USAGE ON SCHEMA public TO analytics_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_readonly;
-- No INSERT, UPDATE, DELETE possible
 
-- 3. Row-Level Security (PostgreSQL example)
-- Employees can only see their own department's data
ALTER TABLE employee_data ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY department_isolation ON employee_data
    USING (department_id = current_setting('app.department_id')::integer);
 
-- Application sets the context before queries:
-- SET app.department_id = '100';
-- SELECT * FROM employee_data; -- Only sees department 100
 
-- 4. Column-level selective access
-- HR sees all columns, others don't see salary/SSN
CREATE ROLE hr_full_access;
GRANT SELECT ON employees TO hr_full_access;
 
CREATE ROLE basic_employee_access;
GRANT SELECT (employee_id, first_name, last_name, email, department_id) 
ON employees TO basic_employee_access;
-- salary, ssn, birth_date, etc. NOT accessible
 
-- 5. View-based security (alternative to column grants)
CREATE VIEW employee_directory AS
SELECT employee_id, first_name, last_name, email, department_id
FROM employees;
 
GRANT SELECT ON employee_directory TO directory_viewers;
-- Users see the view; underlying table is inaccessible
 
-- 6. Function-based security (PostgreSQL SECURITY DEFINER)
CREATE FUNCTION get_my_salary() 
RETURNS DECIMAL 
SECURITY DEFINER  -- Runs with function owner's privileges
AS $$
    SELECT salary FROM employees WHERE employee_id = current_user_id();
$$ LANGUAGE SQL;
 
GRANT EXECUTE ON FUNCTION get_my_salary() TO all_employees;
-- Users can see their own salary but not query the table directly

The Danger of GRANT ... TO PUBLIC

GRANT ... TO PUBLIC gives the privilege to all database users, including future users. This is almost never appropriate for production data. Use explicit user/role grants instead. If you must use PUBLIC, limit it to truly public data (reference tables, public announcements) and document the business justification.

Privilege Auditing and Compliance

Regulatory compliance (HIPAA, PCI-DSS, SOX, GDPR) requires not only proper access control but also the ability to demonstrate that access is properly controlled. This requires auditing both privilege configuration and privilege usage.

What Must Be Auditable:

Current Privileges: Who has access to what right now?
Privilege Changes: When were privileges granted/revoked, and by whom?
Privilege Usage: Who accessed sensitive data, and when?
Authentication Events: Login attempts, failures, and session management

audit_queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
-- =====================================================
-- Privilege Auditing Queries (PostgreSQL examples)
-- =====================================================
 
-- 1. List all users and their roles
SELECT 
    r.rolname AS role_name,
    r.rolsuper AS is_superuser,
    r.rolinherit AS inherits_privileges,
    r.rolcanlogin AS can_login,
    r.rolreplication AS is_replication_role,
    ARRAY_AGG(m.rolname) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
GROUP BY r.rolname, r.rolsuper, r.rolinherit, r.rolcanlogin, r.rolreplication
ORDER BY r.rolname;
 
-- 2. List all privileges on a specific table
SELECT 
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_name = 'employees'
  AND table_schema = 'public'
ORDER BY grantee, privilege_type;
 
-- 3. List all object privileges for a specific user
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'analyst_user'
ORDER BY table_schema, table_name;
 
-- 4. Find users with superuser access (high risk)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb
FROM pg_roles
WHERE rolsuper = true OR rolcreaterole = true OR rolcreatedb = true;
 
-- 5. List column-level privileges
SELECT 
    grantee,
    table_schema,
    table_name,
    column_name,
    privilege_type
FROM information_schema.column_privileges
WHERE grantee != 'PUBLIC'
ORDER BY grantee, table_schema, table_name, column_name;
 
-- 6. Check who can access a specific schema
SELECT 
    nspname AS schema_name,
    pg_catalog.pg_get_userbyid(nspowner) AS owner,
    nspacl AS access_privileges
FROM pg_catalog.pg_namespace
WHERE nspname = 'confidential';
 
-- 7. Comprehensive privilege report
WITH role_hierarchy AS (
    SELECT 
        r.rolname,
        ARRAY_AGG(m.rolname) FILTER (WHERE m.rolname IS NOT NULL) AS member_of_roles
    FROM pg_roles r
    LEFT JOIN pg_auth_members am ON r.oid = am.member
    LEFT JOIN pg_roles m ON am.roleid = m.oid
    GROUP BY r.rolname
)
SELECT 
    rh.rolname,
    rh.member_of_roles,
    tp.table_schema,
    tp.table_name,
    ARRAY_AGG(tp.privilege_type) AS privileges
FROM role_hierarchy rh
LEFT JOIN information_schema.table_privileges tp ON rh.rolname = tp.grantee
WHERE tp.table_schema NOT IN ('information_schema', 'pg_catalog')
GROUP BY rh.rolname, rh.member_of_roles, tp.table_schema, tp.table_name
ORDER BY rh.rolname, tp.table_schema, tp.table_name;

Automated Compliance Monitoring

Modern organizations use automated tools to continuously monitor database privileges against security policies. Solutions like AWS Config Rules, pgAudit, SQL Server Audit, or Oracle Audit Vault can alert when privilege configurations deviate from baselines. Integrate privilege auditing into your security monitoring infrastructure.

Summary: Mastering DCL

Data Control Language is the security foundation of database systems. Through GRANT and REVOKE, DCL implements the access control policies that protect data from unauthorized access, modification, and deletion.

Let's consolidate the key concepts from this page:

Key Takeaways

•DCL consists of GRANT and REVOKE — GRANT enables access; REVOKE restricts it. Together they implement complete access control.
•Privileges exist at multiple levels — System privileges, object privileges, column privileges, and schema privileges provide granular control.
•WITH GRANT OPTION enables delegation — Users can pass privileges to others, creating grant chains that CASCADE or RESTRICT behavior affects.
•Role-Based Access Control is essential at scale — Grant to roles, assign users to roles. This simplifies administration and enforces consistency.
•Principle of least privilege is fundamental — Grant only what's needed. Excessive privileges increase risk from compromised accounts.
•Security extends beyond DCL syntax — Separate accounts, password policies, encryption, and auditing are all part of database security.
•Privilege auditing supports compliance — Regulations require demonstrating proper access control. Query system catalogs and enable audit logging.
•Never underestimate insider threats — Most data breaches involve authorized users. DCL protects against internal misuse as much as external attacks.

What's Next:

With security mastered, we turn to the language that controls transaction behavior—Transaction Control Language (TCL). The next page explores COMMIT, ROLLBACK, and SAVEPOINT: the statements that implement the atomicity and durability guarantees essential for reliable data management.

Page Complete

You now understand DCL: the language that controls database access. From GRANT's permission assignment to REVOKE's access restriction, DCL provides complete security control. Role-based access, least privilege principles, and audit capabilities ensure databases remain protected. Next, we'll explore TCL—the language that manages transaction boundaries.

3 / 5

Loading learning content...

Database Management SystemsDatabase Languages

Database Languages: The Complete Language Toolkit

LevelBeginner

Duration75 mins

TopicDatabase Languages

3 / 5

Data Control Language (DCL)

The Language That Guards Your Data

What You Will Learn

Understanding Data Control Language

The Two Fundamental DCL Commands:

DCL consists of just two primary statements:

DCL Statements
Statement	Purpose	Security Effect
GRANT	Give permissions to users or roles	Expands what a user can do
REVOKE	Remove permissions from users or roles	Restricts what a user can do

The DCL Security Model:

DCL implements a discretionary access control (DAC) model. In DAC:

Every database object has an owner (typically the user who created it)
The owner has full privileges on the object
The owner can grant privileges to other users (discretion)
Privileges can be granted with or without the ability to further grant to others
Privileges can be revoked to restrict access

The Principle of Least Privilege

Types of Database Privileges

Privileges in database systems are categorized into multiple types, each controlling different aspects of database access. Understanding this hierarchy is essential for effective security design.

Privilege Categories and Their Scope
Category	Scope	Examples	Typical Recipients
System Privileges	Database-wide operations	CREATE TABLE, CREATE USER, BACKUP DATABASE	DBAs, privileged roles
Object Privileges	Specific database objects	SELECT on table X, EXECUTE on procedure Y	Application users, developers
Column Privileges	Specific columns within tables	SELECT(salary) on employees	Limited access roles
Schema Privileges	All objects within a schema	ALL PRIVILEGES ON SCHEMA sales	Schema owners, team leads

Common Object Privileges:

Different database objects support different privilege types:

Object Privileges by Object Type
Object Type	Available Privileges	Description
TABLE	SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRUNCATE	Full CRUD and referential access
VIEW	SELECT, INSERT, UPDATE, DELETE	Access through the view (if updateable)
SEQUENCE	USAGE, SELECT	Generate next value, query current value
FUNCTION/PROCEDURE	EXECUTE	Call the function or procedure
SCHEMA	CREATE, USAGE	Create objects in schema, access schema objects
DATABASE	CONNECT, CREATE, TEMPORARY	Connect to database, create schemas, create temp tables

privilege_hierarchy.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- =====================================================
-- Privilege Hierarchy Examples
-- =====================================================
 
-- SYSTEM PRIVILEGES (database-wide)
-- These control the ability to perform administrative operations
GRANT CREATE TABLE TO developer_role;
GRANT CREATE USER TO admin_role;
GRANT BACKUP DATABASE TO backup_operator;
 
-- OBJECT PRIVILEGES (specific table)
-- These control access to individual database objects
GRANT SELECT ON employees TO reporting_user;
GRANT INSERT, UPDATE ON orders TO order_entry_app;
GRANT ALL PRIVILEGES ON products TO inventory_manager;
 
-- COLUMN-LEVEL PRIVILEGES (specific columns)
-- Fine-grained control within a table
GRANT SELECT (employee_id, first_name, last_name, department_id) 
ON employees TO directory_app;
-- Notice: salary, SSN, and other sensitive columns NOT granted
 
GRANT UPDATE (email, phone) ON employees TO self_service_app;
-- Users can update their own contact info, but not salary
 
-- SCHEMA PRIVILEGES (all objects in schema)
-- Useful for team-based access
GRANT USAGE ON SCHEMA sales TO sales_team;
GRANT CREATE ON SCHEMA sales TO sales_developers;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA sales TO sales_admin;

ALL PRIVILEGES: Use With Caution

GRANT: Bestowing Privileges

The GRANT statement assigns privileges to database principals (users, roles, or groups). It is the primary mechanism for enabling access to database resources.

GRANT Syntax and Semantics:

The general form of GRANT is:

GRANT <privileges> ON <object> TO <principal> [WITH GRANT OPTION];

The GRANT executes immediately and takes effect for the principal's next database action. Existing sessions may not immediately see new privileges (depending on caching).

grant_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- =====================================================
-- GRANT Statement: Complete Examples
-- =====================================================
 
-- 1. Grant single privilege to single user
GRANT SELECT ON customers TO analyst_user;
 
-- 2. Grant multiple privileges to single user
GRANT SELECT, INSERT, UPDATE ON orders TO order_service;
 
-- 3. Grant to multiple users at once
GRANT SELECT ON reports TO user1, user2, user3;
 
-- 4. Grant to PUBLIC (all users - use sparingly!)
GRANT SELECT ON public_announcements TO PUBLIC;
 
-- 5. Grant all privileges (owner-level access)
GRANT ALL PRIVILEGES ON inventory TO warehouse_manager;
 
-- 6. Grant with ability to further grant (delegation)
GRANT SELECT ON products TO team_lead WITH GRANT OPTION;
-- team_lead can now: GRANT SELECT ON products TO team_member;
 
-- 7. Grant on all tables in schema (PostgreSQL)
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO report_readers;
 
-- 8. Grant on future tables (PostgreSQL default privileges)
ALTER DEFAULT PRIVILEGES IN SCHEMA sales 
GRANT SELECT ON TABLES TO sales_analysts;
-- New tables created in 'sales' schema will auto-grant SELECT
 
-- 9. Grant column-level privileges
GRANT SELECT (id, name, department) ON employees TO directory_service;
GRANT UPDATE (email, phone) ON employees TO self_service_portal;
 
-- 10. Grant EXECUTE on functions/procedures
GRANT EXECUTE ON FUNCTION calculate_tax(numeric) TO billing_app;
GRANT EXECUTE ON PROCEDURE process_order(integer) TO order_processor;
 
-- 11. Grant schema privileges
GRANT USAGE ON SCHEMA confidential TO cleared_users;
GRANT CREATE ON SCHEMA development TO dev_team;
 
-- 12. Grant database-level privileges (PostgreSQL)
GRANT CONNECT ON DATABASE production TO app_user;
GRANT CREATE ON DATABASE staging TO developer;
 
-- 13. Grant sequence privileges
GRANT USAGE ON SEQUENCE order_id_seq TO order_service;
 
-- 14. System privileges (Oracle-style)
-- GRANT CREATE TABLE TO developer_role;
-- GRANT CREATE SESSION TO app_user;
-- GRANT DBA TO admin_user;

WITH GRANT OPTION: Privilege Delegation

The WITH GRANT OPTION clause is powerful and dangerous. It allows the recipient to grant the same privilege to other users. This creates a chain of privilege delegation:

Owner → User A (WITH GRANT OPTION) → User B (WITH GRANT OPTION) → User C

Each user in the chain can grant to others. If User A's privilege is later revoked, what happens to Users B and C depends on the REVOKE syntax (CASCADE vs RESTRICT).

Use Roles, Not Direct User Grants

REVOKE: Withdrawing Privileges

The REVOKE statement removes privileges from database principals. It is the mechanism for restricting access when users change roles, leave the organization, or when permissions need to be tightened.

REVOKE Syntax and Semantics:

The general form of REVOKE is:

REVOKE <privileges> ON <object> FROM <principal> [CASCADE | RESTRICT];

REVOKE takes effect immediately for new operations. Existing sessions may continue using cached privileges until they reconnect or refresh their permission cache.

revoke_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- =====================================================
-- REVOKE Statement: Complete Examples
-- =====================================================
 
-- 1. Revoke single privilege from single user
REVOKE SELECT ON customers FROM former_employee;
 
-- 2. Revoke multiple privileges
REVOKE INSERT, UPDATE, DELETE ON orders FROM demoted_user;
 
-- 3. Revoke all privileges
REVOKE ALL PRIVILEGES ON sensitive_data FROM compromised_account;
 
-- 4. Revoke from multiple users
REVOKE SELECT ON reports FROM user1, user2, user3;
 
-- 5. Revoke from PUBLIC -- 6. Revoke GRANT OPTION (but keep the privilege)
REVOKE GRANT OPTION FOR SELECT ON products FROM team_lead;
-- team_lead can still SELECT, but cannot grant to others
 
-- 7. CASCADE revoke: Remove from grantees of the revokee
REVOKE SELECT ON data FROM manager CASCADE;
-- If manager granted to subordinates, their access is also revoked
 
-- 8. RESTRICT revoke: Fail if others have dependent grants
REVOKE SELECT ON data FROM manager RESTRICT;
-- Fails if manager granted to others (must revoke them first)
 
-- 9. Revoke schema privileges
REVOKE USAGE ON SCHEMA confidential FROM former_cleared_user;
REVOKE CREATE ON SCHEMA development FROM former_developer;
 
-- 10. Revoke column privileges
REVOKE UPDATE (salary) ON employees FROM basic_hr_role;
 
-- 11. Revoke on all tables in schema (PostgreSQL)
REVOKE SELECT ON ALL TABLES IN SCHEMA finance FROM departing_employee;
 
-- 12. Revoke EXECUTE on functions
REVOKE EXECUTE ON FUNCTION sensitive_calculation() FROM untrusted_user;
 
-- 13. Complete access removal workflow
-- Step 1: Revoke object privileges
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM terminated_user;
REVOKE ALL ON ALL SEQUENCES IN SCHEMA public FROM terminated_user;
REVOKE ALL ON ALL FUNCTIONS IN SCHEMA public FROM terminated_user;
 
-- Step 2: Revoke schema access
REVOKE USAGE ON SCHEMA public FROM terminated_user;
 
-- Step 3: Revoke database connection
REVOKE CONNECT ON DATABASE production FROM terminated_user;
 
-- Step 4: Disable/drop the user account
-- ALTER USER terminated_user WITH NOLOGIN;
-- or: DROP USER terminated_user;

CASCADE vs RESTRICT Semantics:

When a user with GRANT OPTION has granted privileges to others, REVOKE behavior depends on the CASCADE/RESTRICT keyword:

CASCADE: The privilege is revoked from the specified user AND from everyone they granted it to. This can cause a chain of revocations.
RESTRICT: The REVOKE fails if the user has granted the privilege to anyone else. You must explicitly revoke from the downstream users first.

CASCADE is powerful but can have unintended consequences. In complex grant chains, a single CASCADE REVOKE can disable access for many users.

The Orphaned Permission Problem

Roles and Role-Based Access Control

The RBAC Model:

Privileges → Roles → Users

[SELECT on orders]     →  [OrderReader]  →  [analyst1, analyst2, analyst3]
[INSERT on orders]     →  [OrderWriter]  →  [app_service, order_entry_user]
[ALL on orders]        →  [OrderAdmin]   →  [order_manager]

This indirection provides enormous administrative benefits:

Benefits of Role-Based Access Control

•Simplified Administration — Change a role once, affect all members. When permissions change, update the role, not individual users.
•Consistent Enforcement — All users in a role have identical permissions. No accidental permission drift between similar users.
•Easy Onboarding/Offboarding — Add new employees to roles; remove departing employees from roles. No individual privilege management.
•Auditability — Role membership is easier to review than scattered individual grants. Compliance audits check 'who is in what role'.
•Role Hierarchy — Roles can inherit from other roles. A 'DBA' role can include all privileges of 'Developer' plus additional admin rights.
•Separation of Duties — Create roles that, combined, provide complete access, but individually are limited. Fraud prevention through division.

role_management.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- =====================================================
-- Role-Based Access Control Implementation
-- =====================================================
 
-- 1. CREATE ROLES for different access levels
CREATE ROLE readonly;
CREATE ROLE readwrite;
CREATE ROLE admin;
 
-- 2. GRANT privileges TO roles (not directly to users)
-- Read-only role: SELECT on all relevant tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
 
-- Read-write role: SELECT + INSERT + UPDATE + DELETE
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
GRANT USAGE ON SCHEMA public TO readwrite;
 
-- Admin role: All privileges + DDL
GRANT ALL PRIVILEGES ON SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;
 
-- 3. ROLE INHERITANCE: Admin includes readwrite includes readonly
GRANT readonly TO readwrite;
GRANT readwrite TO admin;
-- Now: admin has readonly + readwrite + admin privileges
 
-- 4. CREATE users and ASSIGN to roles
CREATE USER analyst1 WITH PASSWORD 'secure_password_1';
CREATE USER analyst2 WITH PASSWORD 'secure_password_2';
CREATE USER app_service WITH PASSWORD 'app_service_key';
CREATE USER dba_user WITH PASSWORD 'admin_secure_pass';
 
GRANT readonly TO analyst1;
GRANT readonly TO analyst2;
GRANT readwrite TO app_service;
GRANT admin TO dba_user;
 
-- 5. REVOKE role membership when user changes role
REVOKE readonly FROM analyst1;
GRANT readwrite TO analyst1;  -- Promoted!
 
-- 6. Application-specific roles
CREATE ROLE order_processing;
GRANT SELECT, INSERT ON orders TO order_processing;
GRANT SELECT, INSERT ON order_items TO order_processing;
GRANT USAGE ON SEQUENCE order_id_seq TO order_processing;
GRANT EXECUTE ON FUNCTION calculate_order_total(integer) TO order_processing;
 
CREATE ROLE payment_processing;
GRANT SELECT, INSERT, UPDATE ON payments TO payment_processing;
GRANT SELECT ON orders TO payment_processing;
GRANT EXECUTE ON FUNCTION process_payment(integer, numeric) TO payment_processing;
 
-- 7. Default privileges for future objects (PostgreSQL)
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO readonly;
 
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
 
-- 8. View role memberships
-- PostgreSQL: SELECT * FROM pg_roles;
-- PostgreSQL: \du  (in psql)

The Standard Role Hierarchy

Database Security Best Practices

Essential Security Practices

•1. Principle of Least Privilege — Grant only the minimum permissions required. If SELECT suffices, don't grant UPDATE. If one table is needed, don't grant schema-wide access.
•2. Use Application-Specific Accounts — Each application should have its own database user with only the permissions that application needs. Never share credentials between applications.
•3. Separate Read and Write Accounts — Use different credentials for read operations (reporting, analytics) and write operations. A compromised read-only account cannot corrupt data.
•4. Never Use Default Passwords — Default postgres/root/sa passwords are the first thing attackers try. Always set strong, unique passwords during installation.
•5. Disable/Rename Default Accounts — Default 'sa' or 'postgres' accounts are attack targets. Create named admin accounts and disable defaults.
•6. Implement Password Policies — Enforce password complexity, rotation, and history. Consider certificate-based or external authentication (LDAP/Active Directory).
•7. Encrypt Connections — Always use TLS/SSL for database connections. Network-sniffed credentials enable full database access.
•8. Regular Access Reviews — Periodically audit who has access to what. Remove access for terminated employees; reduce excessive privileges.
•9. Audit Privilege Changes — Log all GRANT and REVOKE operations. Security incidents require understanding how access changed over time.
•10. Use Rows-Level Security Where Needed — When different users should see different data in the same table, implement row-level security (RLS) policies.

security_implementation.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
-- =====================================================
-- Security Implementation Patterns
-- =====================================================
 
-- 1. Application-specific user with minimal privileges
CREATE USER order_app WITH PASSWORD 'randomly_generated_secret_key';
GRANT CONNECT ON DATABASE production TO order_app;
GRANT USAGE ON SCHEMA orders TO order_app;
GRANT SELECT, INSERT ON orders.orders TO order_app;
GRANT SELECT, INSERT ON orders.order_items TO order_app;
GRANT USAGE ON SEQUENCE orders.order_id_seq TO order_app;
-- Nothing else! No DELETE, no other tables, no other schemas.
 
-- 2. Read replica user (strictly read-only)
CREATE USER analytics_readonly WITH PASSWORD 'analytics_key';
GRANT CONNECT ON DATABASE production TO analytics_readonly;
GRANT USAGE ON SCHEMA public TO analytics_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analytics_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analytics_readonly;
-- No INSERT, UPDATE, DELETE possible
 
-- 3. Row-Level Security (PostgreSQL example)
-- Employees can only see their own department's data
ALTER TABLE employee_data ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY department_isolation ON employee_data
    USING (department_id = current_setting('app.department_id')::integer);
 
-- Application sets the context before queries:
-- SET app.department_id = '100';
-- SELECT * FROM employee_data; -- Only sees department 100
 
-- 4. Column-level selective access
-- HR sees all columns, others don't see salary/SSN
CREATE ROLE hr_full_access;
GRANT SELECT ON employees TO hr_full_access;
 
CREATE ROLE basic_employee_access;
GRANT SELECT (employee_id, first_name, last_name, email, department_id) 
ON employees TO basic_employee_access;
-- salary, ssn, birth_date, etc. NOT accessible
 
-- 5. View-based security (alternative to column grants)
CREATE VIEW employee_directory AS
SELECT employee_id, first_name, last_name, email, department_id
FROM employees;
 
GRANT SELECT ON employee_directory TO directory_viewers;
-- Users see the view; underlying table is inaccessible
 
-- 6. Function-based security (PostgreSQL SECURITY DEFINER)
CREATE FUNCTION get_my_salary() 
RETURNS DECIMAL 
SECURITY DEFINER  -- Runs with function owner's privileges
AS $$
    SELECT salary FROM employees WHERE employee_id = current_user_id();
$$ LANGUAGE SQL;
 
GRANT EXECUTE ON FUNCTION get_my_salary() TO all_employees;
-- Users can see their own salary but not query the table directly

The Danger of GRANT ... TO PUBLIC

Privilege Auditing and Compliance

What Must Be Auditable:

Current Privileges: Who has access to what right now?
Privilege Changes: When were privileges granted/revoked, and by whom?
Privilege Usage: Who accessed sensitive data, and when?
Authentication Events: Login attempts, failures, and session management

audit_queries.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
-- =====================================================
-- Privilege Auditing Queries (PostgreSQL examples)
-- =====================================================
 
-- 1. List all users and their roles
SELECT 
    r.rolname AS role_name,
    r.rolsuper AS is_superuser,
    r.rolinherit AS inherits_privileges,
    r.rolcanlogin AS can_login,
    r.rolreplication AS is_replication_role,
    ARRAY_AGG(m.rolname) AS member_of
FROM pg_roles r
LEFT JOIN pg_auth_members am ON r.oid = am.member
LEFT JOIN pg_roles m ON am.roleid = m.oid
GROUP BY r.rolname, r.rolsuper, r.rolinherit, r.rolcanlogin, r.rolreplication
ORDER BY r.rolname;
 
-- 2. List all privileges on a specific table
SELECT 
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges
WHERE table_name = 'employees'
  AND table_schema = 'public'
ORDER BY grantee, privilege_type;
 
-- 3. List all object privileges for a specific user
SELECT 
    table_schema,
    table_name,
    privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'analyst_user'
ORDER BY table_schema, table_name;
 
-- 4. Find users with superuser access (high risk)
SELECT rolname, rolsuper, rolcreaterole, rolcreatedb
FROM pg_roles
WHERE rolsuper = true OR rolcreaterole = true OR rolcreatedb = true;
 
-- 5. List column-level privileges
SELECT 
    grantee,
    table_schema,
    table_name,
    column_name,
    privilege_type
FROM information_schema.column_privileges
WHERE grantee != 'PUBLIC'
ORDER BY grantee, table_schema, table_name, column_name;
 
-- 6. Check who can access a specific schema
SELECT 
    nspname AS schema_name,
    pg_catalog.pg_get_userbyid(nspowner) AS owner,
    nspacl AS access_privileges
FROM pg_catalog.pg_namespace
WHERE nspname = 'confidential';
 
-- 7. Comprehensive privilege report
WITH role_hierarchy AS (
    SELECT 
        r.rolname,
        ARRAY_AGG(m.rolname) FILTER (WHERE m.rolname IS NOT NULL) AS member_of_roles
    FROM pg_roles r
    LEFT JOIN pg_auth_members am ON r.oid = am.member
    LEFT JOIN pg_roles m ON am.roleid = m.oid
    GROUP BY r.rolname
)
SELECT 
    rh.rolname,
    rh.member_of_roles,
    tp.table_schema,
    tp.table_name,
    ARRAY_AGG(tp.privilege_type) AS privileges
FROM role_hierarchy rh
LEFT JOIN information_schema.table_privileges tp ON rh.rolname = tp.grantee
WHERE tp.table_schema NOT IN ('information_schema', 'pg_catalog')
GROUP BY rh.rolname, rh.member_of_roles, tp.table_schema, tp.table_name
ORDER BY rh.rolname, tp.table_schema, tp.table_name;

Automated Compliance Monitoring

Summary: Mastering DCL

Let's consolidate the key concepts from this page:

Key Takeaways

•DCL consists of GRANT and REVOKE — GRANT enables access; REVOKE restricts it. Together they implement complete access control.
•Privileges exist at multiple levels — System privileges, object privileges, column privileges, and schema privileges provide granular control.
•WITH GRANT OPTION enables delegation — Users can pass privileges to others, creating grant chains that CASCADE or RESTRICT behavior affects.
•Role-Based Access Control is essential at scale — Grant to roles, assign users to roles. This simplifies administration and enforces consistency.
•Principle of least privilege is fundamental — Grant only what's needed. Excessive privileges increase risk from compromised accounts.
•Security extends beyond DCL syntax — Separate accounts, password policies, encryption, and auditing are all part of database security.
•Privilege auditing supports compliance — Regulations require demonstrating proper access control. Query system catalogs and enable audit logging.
•Never underestimate insider threats — Most data breaches involve authorized users. DCL protects against internal misuse as much as external attacks.

What's Next:

Page Complete

3 / 5