mysql中的数据库与表级权限控制

mysql中GRANT语句通过指定数据库名（如mydb.）授予数据库级权限，不可用代替库名；表级权限需写全database.table格式；权限在连接建立时加载，已存在连接需重连才生效；可用SHOW GRANTS验证。

MySQL 中 GRANT 语句怎么给数据库级权限

数据库级权限作用于整个数据库（schema），比如 select、INSERT、DROP 整个库的表。用 GRANT 时，必须明确指定数据库名，不能用通配符 * 代替库名（除非是全局权限）。

常见错误：执行 GRANT SELECT ON *.* TO 'u1'@'%' 是给所有库的权限，不是“当前库”；而 GRANT SELECT ON mydb.* TO 'u1'@'%' 才是只对 mydb 这个库生效。

• GRANT SELECT, INSERT ON mydb.* TO 'u1'@'localhost' —— 允许用户查、插 mydb 下所有表
• GRANT CREATE, DROP ON mydb.* TO 'u1'@'localhost' —— 允许建表、删表，但不能删库（DROP DATABASE 是单独权限）
• 执行后必须跟 FLUSH PRIVILEGES（仅在直接改 mysql.db 表时才需要；用 GRANT 通常自动刷新）

如何精确控制某张表的读写权限

表级权限比数据库级更细，适用于敏感表隔离场景，例如只让运营人员查 orders 表但不能碰 users 表。

语法上必须写成 database_name.table_name 格式，且该表必须已存在（MySQL 8.0+ 支持对不存在的表预授权，但权限不会生效直到表被创建）。

• GRANT SELECT, UPDATE(price, status) ON shop.orders TO 'ops'@'%' —— 只允许查和更新 orders 表的 price 和 status 字段
• GRANT INSERT ON shop.log_archive TO 'app'@'10.20.%' —— 应用服务器只能往日志归档表写，不能读
• 注意：REVOKE 必须完全匹配之前 GRANT 的范围，比如用 ON shop.* 授的权，就不能用 ON shop.orders 来回收

权限生效时机与常见失效原因

MySQL 权限不是实时广播的，而是在线程连接建立时加载一次。这意味着已存在的连接不会感知新授/回收的权限，直到重连。

• 新用户连接立即生效；已有连接需断开重连，或执行 mysqladmin flush-privileges
• 如果用 root 授权后应用仍报 access denied for user，先确认是否用了错误的 host（如 'user'@'localhost' ≠ 'user'@'127.0.0.1'）
• MySQL 8.0 默认启用 caching_sha2_password 插件，旧客户端可能因认证失败误判为权限问题；可用 ALTER USER 'u1'@'%' IDENTIFIED WITH mysql_native_password BY 'pwd' 临时切换

查看用户实际拥有的权限有哪些

别只信自己记的 GRANT 命令，用内置命令验证最可靠。权限是叠加的（全局 + 库 + 表 + 列），最终效果取并集。

SHOW GRANTS FOR 'u1'@'%';

如果想看某个用户在特定库下的有效权限（含继承关系），可查系统表：

SELECT * FROM mysql.db WHERE User='u1' AND Host='%' AND Db='mydb'G

或更直观地模拟检查：

SELECT COUNT(*) FROM information_schema.role_table_grants  WHERE grantee = "'u1'@'%'" AND table_schema = 'mydb';

权限层级越深，排查路径越长——尤其是当用户同时有 mydb.* 和 mydb.orders 两级授权时，字段级限制可能被库级权限覆盖，得逐层核对。