SQL 数据安全与权限管理

mysql权限分配后不生效需检查flush privileges是否执行及’user’@’host’作用域是否匹配；postgresql revoke无效可能因权限继承或default privileges覆盖；sql注入防护中表名列名须白名单校验；dump默认不导出权限，需单独备份系统库。

MySQL 中 GRANT 权限分配后不生效？检查 FLUSH PRIVILEGES 和作用域

权限改了但用户还是连不上或报 access denied，大概率是权限没刷进内存，或者 GRANT 时写错了主机名或数据库名。

• GRANT 命令本身只写入系统表（如 mysql.user），不会自动重载到运行时权限缓存；生产环境务必跟一句 FLUSH PRIVILEGES（MySQL 5.7+ 在某些场景下可省略，但不建议赌）
• 权限作用域由 'user'@'host' 完全决定：'app'@'192.168.1.%' 和 'app'@'localhost' 是两个独立账号，后者在 unix socket 连接时才匹配
• 用 select User, Host FROM mysql.user; 确认账号是否存在、Host 字段是否和实际连接来源一致（比如 docker 容器内连宿主机 MySQL，host 很可能是 172.x.x.x 而非 %）

PostgreSQL 的 REVOKE 为什么删不掉某个表的 INSERT 权限？

PostgreSQL 权限继承机制比 MySQL 复杂，直接 REVOKE 可能被更上层的权限覆盖，比如角色成员关系或 DEFAULT PRIVILEGES。

• 先查清权限来源：z table_name（psql 命令）或查 pg_class + pg_namespace 关联权限字段，确认 INSERT 是谁给的
• 如果该表属于某个 schema，且之前执行过 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT INSERT ON TABLES TO role_x;，那新创建的表会自动带权限，REVOKE 单表无效，得同步改 default 规则
• 注意角色继承：若 role_a 是 role_b 的 member，而 role_b 有 INSERT，那么撤掉 role_a 的权限没用，得从 role_b 层级处理

SQL 注入防护不能只靠 PreparedStatement？还得管动态拼接的表名和列名

PreparedStatement 能防参数注入，但对 SQL 结构部分（如 ORDER BY column_name、FROM table_name）完全无效——这些地方一旦拼接用户输入，就是高危漏洞。

• 表名/列名必须白名单校验：比如排序字段只允许 ['id', 'created_at', 'status']，用 in_array($input, $whitelist) 或等价逻辑判断，别用正则“过滤”
• 避免任何字符串拼接构造 DDL（CREATE TABLE、ALTER COLUMN），这类操作应严格限定为运维脚本或配置驱动，不走用户请求流
• PostgreSQL 中 quote_ident()、MySQL 中 QUOTE() 或反引号包裹仅解决语法错误，不防恶意标识符（比如合法但指向敏感视图的列名），本质仍是信任输入

用 pg_dump 或 mysqldump 备份时，权限信息会不会一起导出？

默认不会。dump 工具专注数据与结构，权限定义存在系统库（mysql.user / pg_authid），不在常规导出范围内。

• MySQL：加 --all-databases 也不含权限；要导用户和权限，得单独跑 mysqldump mysql user db --skip-extended-insert，再手动处理 CREATE USER 和 GRANT 语句
• PostgreSQL：用 pg_dumpall --roles 导出角色定义，pg_dumpall --globals-only 包含 CREATE ROLE 和 GRANT，但不含数据库级权限（需配合 pg_dump -g）
• 备份策略里漏掉权限导出，恢复后服务起不来是常见事故点——尤其 CI/CD 自动部署环境，账号密码可能对得上，但权限不对就直接 403

事情说清了就结束。权限管理最麻烦的不是设错，而是设对了但没生效、或者生效了但被更高层规则覆盖，查的时候得一层层翻元数据，别只盯着 GRANT 那一行。