SQL TL;DR: Security & Permissions (DCL)

Security & Permissions (DCL)

/tldr: Controlling who can do what to which objects in the database.

Data Control Language (DCL) Access Management Least Privilege

THE CORE IDEA: The Access Control Matrix

Database security is built on three pillars: **Principals** (who requests access), **Securables** (what is being accessed), and **Permissions** (what action can be performed). DCL commands manage the relationship between these three.

The Principle of Least Privilege

Users should only be granted the minimum permissions necessary to perform their work. This minimizes the surface area for errors, unauthorized access, and security breaches.

1. Data Control Language (DCL)

These two core commands are used to assign and remove rights to and from users or roles.

GRANT

Gives a user or role specific privileges on a database object.

-- Grant ability to read data from the 'products' table
GRANT SELECT ON products TO 'analyst_user';

-- Grant ability to run a stored procedure
GRANT EXECUTE ON stored_procedure_name TO 'app_role';

-- Grant ability to add and change data
GRANT INSERT, UPDATE ON orders TO 'web_service_role';

REVOKE

Removes specific privileges that were previously granted.

-- Revoke the ability to read all products data
REVOKE SELECT ON products FROM 'analyst_user';

-- Revoke all update permissions on all tables in the 'sales' schema
REVOKE UPDATE ON SCHEMA :: sales FROM 'sales_manager';

2. The Actors and Objects

Principals (The Who)

Entities that can be granted access.

**Users:** Specific accounts ('john_doe').
**Roles/Groups:** Collections of permissions that can be assigned to multiple users (e.g., 'DBA_Role', 'ReadOnly_Group'). **Best Practice:** Always grant permissions to roles/groups, not individual users.
**Public:** A special pseudo-role that represents all users/connections (avoid granting access to public unless absolutely necessary).

Securables (The What)

The objects that can have permissions assigned to them.

**Server/Database Level:** Permissions like CREATE DATABASE or BACKUP DATABASE.
**Schema Level:** A logical container for tables, views, and procedures. Granting SELECT on a schema applies to all current and future objects within it.
**Object Level:** Individual tables, views, stored procedures, or functions. This is the most granular level (e.g., GRANT SELECT ON TABLE :: customers).

3. Common Object Permissions

These are the typical permissions granted on tables and other data objects.

SELECT

Read data from the object.

INSERT

Add new rows to a table.

UPDATE

Modify existing data in rows/columns.

DELETE

Remove existing rows from a table.

EXECUTE / REFERENCES

Run procedures/functions, or create foreign keys referencing the object.

ALL / CONTROL

All permissions on the object (use sparingly).

Grant to the Role, not the User.