PostgreSQL临时禁止普通用户编辑表及恢复权限的实现方法

一、临时禁止修改权限（分主流数据库实现） #

PostgreSQL #

1. 备份当前权限：先导出table1的所有非超级用户权限记录，用于后续恢复：

   SELECT 'GRANT ' || array_to_string(array_agg(privilege_type), ', ') || ' ON table1 TO "' || grantee || '";'
   FROM information_schema.role_table_grants
   WHERE table_name = 'table1' AND grantee NOT IN ('postgres', 'pg_superuser');
   

   执行后将生成的所有GRANT语句复制保存。

2. 批量撤销修改权限：通过脚本自动撤销所有非超级用户的INSERT/UPDATE/DELETE/TRUNCATE权限：

   DO $$
   DECLARE
       rec RECORD;
   BEGIN
       FOR rec IN SELECT grantee FROM information_schema.role_table_grants WHERE table_name = 'table1' AND grantee NOT IN ('postgres', 'pg_superuser') LOOP
           EXECUTE 'REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON table1 FROM "' || rec.grantee || '";';
       END LOOP;
   END $$;
   

   同时撤销PUBLIC角色的默认修改权限：

   REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON table1 FROM PUBLIC;
   

MySQL #

1. 备份当前权限：导出table1的非超级用户权限语句：

   SELECT CONCAT('GRANT ', privilege_type, ' ON `', table_schema, '`.`', table_name, '` TO ''', grantee, ''';')
   FROM information_schema.table_privileges
   WHERE table_name = 'table1' AND grantee NOT LIKE '%root@%';
   

   保存生成的结果。

2. 批量撤销修改权限：创建存储过程批量处理非超级用户：

   DELIMITER //
   CREATE PROCEDURE RevokeTableModifications()
   BEGIN
       DECLARE done INT DEFAULT FALSE;
       DECLARE grantee_str VARCHAR(255);
       DECLARE cur CURSOR FOR SELECT grantee FROM information_schema.table_privileges WHERE table_name = 'table1' AND grantee NOT LIKE '%root@%';
       DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   
       OPEN cur;
       read_loop: LOOP
           FETCH cur INTO grantee_str;
           IF done THEN
               LEAVE read_loop;
           END IF;
           SET @sql = CONCAT('REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON `', DATABASE(), '`.`table1` FROM ', grantee_str, ';');
           PREPARE stmt FROM @sql;
           EXECUTE stmt;
           DEALLOCATE PREPARE stmt;
       END LOOP;
       CLOSE cur;
   END //
   DELIMITER ;
   
   CALL RevokeTableModifications();
   

   最后撤销全局默认用户的修改权限：

   REVOKE INSERT, UPDATE, DELETE, TRUNCATE ON `your_database`.`table1` FROM ''@'%';
   

SQL Server #

1. 备份当前权限：导出table1的非超级用户权限记录：

   SELECT 'GRANT ' + permission_name + ' ON table1 TO [' + grantee_principal_name + '];'
   FROM sys.database_permissions dp
   JOIN sys.objects o ON dp.major_id = o.object_id
   JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
   WHERE o.name = 'table1' AND dp.class = 1 AND dpri.name != 'sa';
   

   复制保存生成的语句。

2. 批量拒绝修改权限：用脚本批量拒绝所有非超级用户的修改权限（DENY会覆盖任何已有的GRANT）：

   DECLARE @sql NVARCHAR(MAX) = '';
   SELECT @sql += 'DENY INSERT, UPDATE, DELETE, TRUNCATE ON table1 TO [' + grantee_principal_name + '];' + CHAR(13)
   FROM sys.database_permissions dp
   JOIN sys.objects o ON dp.major_id = o.object_id
   JOIN sys.database_principals dpri ON dp.grantee_principal_id = dpri.principal_id
   WHERE o.name = 'table1' AND dp.class = 1 AND dpri.name != 'sa';
   
   EXEC sp_executesql @sql;
   

   同时拒绝PUBLIC角色的权限：

   DENY INSERT, UPDATE, DELETE, TRUNCATE ON table1 TO PUBLIC;
   

二、恢复原有权限 #

直接执行备份阶段保存的所有GRANT语句，即可精确恢复到禁止操作前的权限状态。

注意事项 #

• 所有操作需以超级用户/数据库管理员身份执行
• 备份权限时需确保覆盖所有相关用户、角色的权限记录
• 不同数据库的系统视图名称可能存在差异，需根据实际环境调整查询语句

内容的提问来源于stack exchange，提问作者bugmenot123