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;




