MariaDB 教程 / 创建用户并验证最小权限与撤销结果

MariaDB 使用“用户名 + 来源主机”识别账户,通过 GRANT 向指定范围授予权限。应把管理员连接与应用连接分开,并用真正的受限账户同时验证允许和拒绝的操作,而不是只查看授权语句。

区分账户身份、来源主机与权限范围

'name'@'localhost' 与 'name'@'127.0.0.1' 可以代表不同的账户定义。实际匹配还受连接方式和名称解析影响;CURRENT_USER() 返回权限检查使用的账户,不能仅凭连接时输入的用户名判断身份。日常应用若一直使用 root,便难以落实只授予所需权限的原则。

权限范围 写法示例 适用目的
全局 *.* 确实需要服务器范围能力的管理工作
数据库 shopcourse.* 在指定数据库内操作多个对象
表 shopcourse.c43_products 本篇仅允许读取一张商品表
列 UPDATE(stock) 仅允许修改指定列

本篇目标是单表只读,不需要全局权限、账户管理权限或转授权能力。账户与授权语法可查阅 MariaDB 的 CREATE USER 文档和 GRANT 文档。

示例密码只是占位值,执行前必须替换为唯一的练习密码。连接时使用密码提示或限制访问的选项文件,不要把真实密码直接写入命令参数、截图、公开 SQL 文件或检验记录。授权输出也可能包含认证信息,应按敏感内容处理。

先准备数据,再切换到管理员处理账户

使用 course_user 连接学习服务器并选择 shopcourse。该账户需要有本数据库建表与插入数据的权限。按命令行与 UTF-8 SQL 文件执行方法保存 SQL 后,可在 MariaDB 客户端执行 SOURCE C:/mariadb-lab/lesson.sql;,路径换成实际文件位置。SQL 不应直接输入操作系统终端。

先运行 SELECT VERSION(), DATABASE(); 与 SHOW TABLES;,确认下列四张 c43_ 练习表没有与现有对象冲突。若已有同名表,应检查结构与数据,不能为重做练习直接覆盖。以下完整样本保留韩文字符串,以保证各语言版本的字面量相同。

USE shopcourse;
CREATE TABLE c43_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c43_order_customer FOREIGN KEY (customer_id)
  REFERENCES c43_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c43_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c43_item_order FOREIGN KEY (order_id)
  REFERENCES c43_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c43_item_product FOREIGN KEY (product_id)
  REFERENCES c43_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c43_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c43_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c43_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c43_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

应有四位客户、四件商品、四张订单和六条明细。商品编号 1~4 依次代表笔记本、圆珠笔、马克杯、台灯;客户 1、3 的城市值为首尔,2、4 分别为釜山、大田。理解说明时按编号对应,复制执行时保留 UTF-8 原文。

建好表后,另开具有账户创建与相应授权能力的管理员连接。普通学习账户不因能建表就自动能执行 CREATE USER 或 GRANT。管理员应先确认 'shop_reader43'@'127.0.0.1' 不存在;如已存在,另选新的练习名并同步修改后续引用,不要覆盖或删除原账户。

只授予一张商品表的 SELECT 权限

在管理员连接中执行以下代码,并先替换示例密码。SHOW GRANTS 用于检查授权范围;检查后不要把含认证信息的原始结果发布出去。

CREATE USER 'shop_reader43'@'127.0.0.1' IDENTIFIED BY 'Replace_With_Unique_Lab_Password!';
GRANT SELECT ON shopcourse.c43_products TO 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

这里授予的是 shopcourse.c43_products 上的 SELECT。没有授予客户表或订单表的读取权限,也没有授予 INSERT、UPDATE 或修改结构的 DDL 权限。若业务只需要本表查询,这个范围比数据库级全部权限更符合实际需求。

通过 CREATE USER、GRANT 等受支持的账户管理语句完成操作后,通常不需要再执行 FLUSH PRIVILEGES。不要直接改系统权限表来替代这些语句;授权发生错误时,检查执行者权限、账户主机部分和对象名称,而不是盲目刷新。

用新连接验证读取成功与越权失败

以下命令在操作系统终端中运行,随后在提示处输入刚设置的密码。它明确使用 TCP 与 127.0.0.1;如果学习服务器端口不是 3306,应改为实际端口。不要继续在 course_user 或管理员会话里测试只读账户。

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=shop_reader43 --password --default-character-set=utf8mb4 shopcourse

连接后执行:

SELECT USER(),CURRENT_USER();
SELECT product_id,product_name FROM c43_products ORDER BY product_id;

检查 CURRENT_USER() 是否为预期的 shop_reader43@127.0.0.1。商品查询应返回编号 1~4 的四条记录。USER() 用于观察客户端提供的连接身份,CURRENT_USER() 用于确认服务器实际匹配并检查权限的账户,两者的用途不同。

再在同一个受限会话中分别执行下列两条语句。请一条一条运行以看到各自错误,不要把第一条的失败当作第二条也已测试。

SELECT * FROM c43_customers;
UPDATE c43_products SET stock=stock WHERE product_id=1;

客户表 SELECT 应被拒绝;把库存写回原值的 UPDATE 也应被拒绝,因为即使数据最终不变,操作仍需要 UPDATE 权限。权限拒绝是本练习的预期结果,不应立即用 ALL PRIVILEGES 消除它。若业务确实增加写入需求,应确认最小对象与操作范围,再授予对应权限。

若部署中使用角色,还需检查当前激活角色所提供的权限,不能只看直接授权。本篇使用新建的单用途账户,避免既有角色或其他授予范围混淆判断。

撤销权限后,用全新连接复查

回到管理员连接,撤销商品表的读取权限并再次检查授权:

REVOKE SELECT ON shopcourse.c43_products FROM 'shop_reader43'@'127.0.0.1';
SHOW GRANTS FOR 'shop_reader43'@'127.0.0.1';

关闭旧的只读连接,使用相同身份重新连接后再查询商品表。权限变更对已有会话的生效细节与权限范围有关,因此应以新连接验证,不能只依赖一条一直开着的连接。

当账户失去唯一的数据库权限时,连接命令末尾指定 shopcourse 本身就可能导致拒绝访问。此时去掉默认数据库参数完成身份验证,再执行 SELECT * FROM shopcourse.c43_products;;表级查询也应被拒绝。这样可以区分“账户仍能通过认证”与“账户已经没有读表权限”。

SHOW GRANTS 中的 USAGE 可以表示账户仍存在,但不代表在该范围具有实际的数据操作权限。停用账户时还要审查应用连接,以及引用该账户作为 DEFINER 的视图和存储例程;DROP USER 并不会代替这些依赖对象和数据的生命周期管理。

仅清理本次新建的账户与练习表

确认只读账户确由本篇新建且不再被使用,关闭其连接后,由管理员删除它。其他应用账户不在本练习清理范围内。

DROP USER 'shop_reader43'@'127.0.0.1';

删除账户不会自动删除商品等业务数据。清理表时回到具有所需表权限的连接,先核对数据库与四张表的内容,备份需要保留的数据,仅删除确认可丢弃的本篇对象。按照子表到父表的顺序执行;DROP TABLE 不能通过普通 ROLLBACK 撤销,应与事务练习分开处理。

DROP TABLE shopcourse.c43_items;
DROP TABLE shopcourse.c43_orders;
DROP TABLE shopcourse.c43_products;
DROP TABLE shopcourse.c43_customers;