#SQL Detailed DCL - Complete Guide to Data Control Language

#Introduction

There are usually multiple applications and multiple users accessing the database at the same time. If a temporary account can delete the database and check the password table, the consequences will be disastrous. Among the three core languages ​​of the database, DDL is responsible for building structures, DML is responsible for processing data, and DCL (data control language) specifically solves the problem of "who can touch what data and how much" and can be called the "security gatekeeper" of the database.

This article will start from the basic concepts of DCL, use MySQL and PostgreSQL to demonstrate how to manage users, how to authorize and withdraw rights, and finally provide a set of directly usable security specifications to help you build a secure database access system from scratch.

#1. DCL Basic Literacy

#1.1 What is DCL?

DCL is like the access control + room card system of the database:

• GRANT → Issue room cards to others (grant object permissions)
• REVOKE → Take away the room card (revoke the given permission)
• DENY → Block people (supported by some databases, covering all authorizations)

As long as you make good use of these three tools, you can accurately control the security boundaries of each database operation.

#1.2 Common permission table

Regardless of MySQL or PostgreSQL, the meaning of core permissions is similar. Just remember this table:

#2. Build “people” first: user/role management

#2.1 MySQL user management (5.7+ general, 8.0+ enhanced)

Before MySQL 8.0, "users" and "roles" were separated. After 8.0, they were unified into a PostgreSQL-like model. Here we first demonstrate it in the most common way.

#Core operations

-- 1. 创建用户（Host 很重要！用来限制连接来源）
CREATE USER 'webapp'@'localhost' IDENTIFIED BY 'P@ssw0rd!202x';    -- 只能本地连
CREATE USER 'analyst'@'192.168.1.%' IDENTIFIED BY 'S3cur3Rep0rt'; -- 仅允许指定 IP 段

-- 2. 查看现有用户
SELECT User, Host FROM mysql.user;

-- 3. 修改密码（8.0 推荐用 ALTER，老版本兼容好）
ALTER USER 'webapp'@'localhost' IDENTIFIED BY 'N3wP@ss!202x';

-- 4. 删除用户
DROP USER 'analyst'@'192.168.1.%';

#2.2 PostgreSQL user/role management (native unified!)

There is no separate "user" command in PostgreSQL. All accounts are "roles" with different attributes:

• Those who can log in are calledLOGINRole (actually the user)
• Can't log in?NOLOGINRole (for grouping)

#Core operations

-- 1. 创建能登录的用户
CREATE ROLE webapp LOGIN PASSWORD 'P@ssw0rd!202x';
-- 也可以直接使用 CREATE USER（内部等价）
CREATE USER analyst PASSWORD 'S3cur3Rep0rt';

-- 2. 创建不能登录的组（用来批量授权）
CREATE ROLE app_developer NOLOGIN;
CREATE ROLE report_reader NOLOGIN;

-- 3. 查看现有角色（psql 快捷命令 \du，或标准 SQL）
SELECT rolname, rolsuper, rolcreatedb FROM pg_roles;

-- 4. 删除角色（必须先回收其所有权限）
DROP ROLE report_reader;

#3. Manage "permissions" again: GRANT / REVOKE

#3.1 MySQL permission grant (Don’t miss the Host!)

MySQL permissions are bound toUser@HostOmitting to write Host can easily cause the authorization to be invalid.

#Common scenarios

-- 1. 给应用表级权限（最安全！不要直接给库级 ALL）
GRANT SELECT, INSERT, UPDATE ON company_db.employees TO 'webapp'@'localhost';
GRANT SELECT ON company_db.departments TO 'webapp'@'localhost';

-- 2. 临时管理员权限（用完记得回收）
GRANT ALL PRIVILEGES ON company_db.* TO 'dba_temp'@'localhost';

-- 3. 刷新权限（ALTER 或 GRANT 后通常自动刷新，但 CREATE/DROP 后可能需手动执行）
FLUSH PRIVILEGES;

-- 4. 查看已授予的权限
SHOW GRANTS FOR 'webapp'@'localhost';

#3.2 MySQL permission revocation

-- 1. 收回单个权限
REVOKE UPDATE ON company_db.employees FROM 'webapp'@'localhost';

-- 2. 收回所有权限
REVOKE ALL PRIVILEGES ON company_db.* FROM 'dba_temp'@'localhost';

-- 3. 一定要刷新！
FLUSH PRIVILEGES;

#3.3 PostgreSQL permission grant (don’t forget to give the USAGE of the schema first!)

PostgreSQL has an extremely easy pitfall: **The schema must be explicitly grantedUSAGEPermissions **, otherwise even if you have table permissions, you will not be able to see the table.

#Common scenarios

-- 0. 先给 public 模式的使用权限（必做！）
GRANT USAGE ON SCHEMA public TO webapp;

-- 1. 给应用表级权限
GRANT SELECT, INSERT, UPDATE ON TABLE employees TO webapp;
GRANT SELECT ON TABLE departments TO webapp;

-- 2. 批量授权当前已有表
GRANT SELECT ON ALL TABLES IN SCHEMA public TO report_reader;

-- 3. 批量授权将来创建的表（MySQL 8.0 才开始有限支持！）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT ON TABLES TO webapp;

-- 4. 查看表权限（psql 快捷命令 \dp，或标准 SQL）
\dp employees

#3.4 PostgreSQL permission revocation

-- 1. 收回单个表权限
REVOKE UPDATE ON TABLE employees FROM webapp;

-- 2. 收回未来表的默认授权
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
REVOKE SELECT, INSERT ON TABLES FROM webapp;

#4. Security best practices (must read! Don’t step into pitfalls)

#4.1 Always follow the "principle of least privilege"

Don’t touch the two red lines:

• Does not give application account library levelALL PRIVILEGES
• Don't let application accounts becomeSUPERUSER

#MySQL Example: Standard e-commerce application account

CREATE USER 'ecommerce'@'localhost' IDENTIFIED BY 'Ec0mm3rc3!202x';
GRANT SELECT, INSERT, UPDATE ON ecommerce_db.orders TO 'ecommerce'@'localhost';
GRANT SELECT, INSERT ON ecommerce_db.order_items TO 'ecommerce'@'localhost';
GRANT SELECT ON ecommerce_db.products TO 'ecommerce'@'localhost';
FLUSH PRIVILEGES;

#4.2 Use "role (group)" to authorize batches

Don’t delegate authority to employees individually! Create a group first (NOLOGINrole), grant all permissions to the group, and finally add the user to the group.

#PostgreSQL Example: Standard Company Permission System

-- 1. 建组
CREATE ROLE app_developer NOLOGIN;
CREATE ROLE sales_reader NOLOGIN;
CREATE ROLE finance_writer NOLOGIN;

-- 2. 给组批量授权
GRANT USAGE ON SCHEMA public TO app_developer, sales_reader, finance_writer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_developer;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO sales_reader;
GRANT SELECT, UPDATE ON finance_salaries TO finance_writer;

-- 3. 未来表的默认授权（新加入的组员自动继承）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL PRIVILEGES ON TABLES TO app_developer;

-- 4. 把人加到组里（三个新用户，对应不同组）
CREATE USER lily LOGIN IN ROLE app_developer PASSWORD 'L1lyDev!202x';
CREATE USER tom LOGIN IN ROLE sales_reader PASSWORD 'T0mS3ll!202x';

#4.3 Restrict login sources

• MySQL strict settingsHostFields, do not provide unless necessary@'%'
• PostgreSQL Passedpg_hba.confConfiguration files control IP ranges, and don’t open them all

#5. MySQL vs PostgreSQL DCL core differences (one minute quick check)

#Related tutorials

• MySQL与PostgreSQL新特性对比
• 视图、函数和过程
• 索引优化策略
• Python接入MySQL与PostgreSQL数据库

#Summarize

DCL is the "security shield" of the database. It is enough to remember three key points:

1. Create roles (groups) first and authorize in batches. Do not grant permissions to users one by one.
2. Always follow the principle of least privilege, and only provide necessary table-level operations to the application account
3. Restrict login sources and review the permission list regularly

MySQL's DCL is simple and direct, suitable for quick online deployment; PostgreSQL's DCL is more sophisticated and powerful, suitable for scenarios with higher security requirements. Choose the right tools and use DCL well, and your database will be stable and secure.