如何预防SQL脚本误操作删除数据_配置只读用户与备份机制-SQL-PHP中文网

MySQL只读用户需显式授SELECT权限并回收所有DML权限；PostgreSQL应避免pg_read_all_data，改用自定义角色+schema级授权；备份须自动触发、带时间戳与实例标识；SQL执行前强制类型标记、干跑验证及安全参数防护。

如何预防sql脚本误操作删除数据_配置只读用户与备份机制

只读权限不是加个 SELECT 就完事——MySQL 的 READ ONLY 模式、用户权限粒度、甚至存储引擎都可能让“只读”失效。

实操建议：

用 CREATE USER 'reporter'@'%' IDENTIFIED BY 'xxx'; 创建用户后，**只显式授予 SELECT 权限**，别用 GRANT ALL 再回收，容易漏项
执行 GRANT SELECT ON `sales_db`.* TO 'reporter'@'%';，注意数据库名要用反引号包裹，避免含短横线的库名报错
禁用该用户的全局 DML 权限：运行 REVOKE INSERT, UPDATE, DELETE, DROP, CREATE, ALTER ON *.* FROM 'reporter'@'%';
检查是否生效：SHOW GRANTS FOR 'reporter'@'%';，确认输出里只有 SELECT 相关语句

常见错误现象：用户仍能执行 DELETE FROM t1; —— 很可能是之前授过 ALL PRIVILEGES 且没 FLUSH PRIVILEGES;，或者用了通配符库名（如 myapp_%）但权限没覆盖到新表

PostgreSQL 的 pg_read_all_data 角色看似只读，但它隐式包含对 pg_catalog 的读写能力，某些扩展或函数（比如 pg_stat_reset()）会间接触发写操作；更关键的是，它不阻止对临时表的操作。

实操建议：

不要直接给用户 pg_read_all_data，而是新建角色：CREATE ROLE reporter WITH NOLOGIN;
只赋予具体 schema 的 USAGE 和表级 SELECT：GRANT USAGE ON SCHEMA public TO reporter; GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter;
对已存在表补授权后，记得运行 ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reporter;，否则新表默认无权限
禁止创建临时表：ALTER ROLE reporter SET temp_tablespaces = '';（空字符串会让 CREATE TEMP TABLE 报错）

性能影响：过度使用 DEFAULT PRIVILEGES 不影响查询性能，但若 schema 下表极多，首次授权时元数据扫描略慢，属一次性开销

靠人定时跑 mysqldump 或 pg_dump 不可靠，关键是要在 DDL/DML 执行前自动触发快照，且保留足够时间窗口供回滚。

实操建议：

MySQL：用 mysqlbinlog + 定时 FLUSH LOGS 组合，每 15 分钟切一个 binlog 文件，配合 expire_logs_days = 7，确保至少有 7 天可精确恢复
PostgreSQL：开启 archive_mode = on，配合 pg_archivecleanup 管理 WAL 归档，比单纯 pg_dump 全量备份恢复更快、更细粒度
所有备份脚本开头加校验：if [ "$(hostname)" != "db-prod-01" ]; then echo "NOT ON PRODUCTION"; exit 1; fi，防止本地测试脚本误跑线上
备份文件名必须含时间戳和实例标识，例如 pg_dump_salesdb_20240522T142300Z_db01.sql.gz，避免覆盖或混淆

容易踩的坑：用 date +%s 当文件名会导致不同服务器时区不一致；务必统一用 UTC 时间（date -u +%Y%m%dT%H%M%SZ）

再严格的权限和备份也挡不住带 WHERE 1=1 的删除脚本直接执行。真正的防护得嵌进开发和运维流程里。

实操建议：

所有 .sql 文件开头加注释标记类型：-- TYPE: DML_SAFE / DML_DANGEROUS / DDL，CI 流程中用 grep "^-- TYPE:" 提取并拦截 DML_DANGEROUS 类型未加 --dry-run 参数的执行
MySQL 脚本执行前加 SET SQL_SAFE_UPDATES = 1;，它会拒绝没有 WHERE 或主键条件的 UPDATE/DELETE，但注意这仅对当前 session 有效
PostgreSQL 可用 BEGIN; DELETE ...; ROLLBACK; 包裹脚本做干跑，但需确认应用连接未设 autocommit=on，否则 ROLLBACK 无效
DBA 工具链里集成 pt-query-digest 或 pgbadger 日志分析，对高频 DELETE 语句自动告警，而不是等出事才查