如何限制用户为Schema中已创建的表新增列权限？

嘿，这个需求很常见，我来帮你梳理一下具体实现方式——核心是通过权限控制（必要时配合DDL触发器）来阻止用户执行ALTER TABLE ... ADD COLUMN操作，不同数据库的具体做法略有差异，咱们分情况说：

核心思路 #

大多数数据库中，ALTER TABLE权限默认包含修改表结构的所有操作（包括添加新列），所以首先要考虑回收用户对目标表的ALTER权限，但要注意：如果用户拥有更高级别的权限（比如数据库级/全局的ALTER权限，或者属于带该权限的角色），表级的回收可能不会生效，得先排查这些情况。

1. PostgreSQL #

PostgreSQL直接支持表级的ALTER权限回收，执行这条命令即可：

REVOKE ALTER ON TABLE schemaname.tablename FROM your_username;

• 注意：如果用户是从某个角色继承的ALTER权限，你需要同时回收角色的对应权限，或者将用户从该角色移除。
• 局限性：PostgreSQL目前没有更细粒度的权限（比如只禁止添加列但允许修改现有列），如果需要这种细分控制，只能通过DDL触发器拦截ADD COLUMN操作：

  CREATE OR REPLACE FUNCTION prevent_add_column()
  RETURNS event_trigger AS $$
  BEGIN
    IF TG_TAG = 'ALTER TABLE' AND EXISTS (
      SELECT 1 FROM pg_event_trigger_ddl_commands()
      WHERE command_tag = 'ALTER TABLE' AND object_type = 'table'
      AND command LIKE '%ADD COLUMN%'
      AND object_identity = 'schemaname.tablename'
    ) THEN
      RAISE EXCEPTION 'Adding columns to schemaname.tablename is prohibited';
    END IF;
  END;
  $$ LANGUAGE plpgsql;
  
  CREATE EVENT TRIGGER trigger_prevent_add_column
  ON ddl_command_end
  WHEN TAG IN ('ALTER TABLE')
  EXECUTE FUNCTION prevent_add_column();
  

2. MySQL #

MySQL同样支持表级回收ALTER权限：

REVOKE ALTER ON schemaname.tablename FROM 'your_username'@'your_host';

• 注意：如果用户有全局ALTER权限（比如GRANT ALTER ON *.* TO ...），表级的REVOKE不会覆盖全局权限，需要先回收全局权限。
• 细分控制方案：如果要允许用户执行其他ALTER操作（比如修改列类型）但禁止添加列，MySQL没有细粒度权限，只能用存储过程封装允许的操作，只给用户存储过程的EXECUTE权限，不给ALTER TABLE权限；或者用DDL触发器拦截：

  DELIMITER //
  CREATE TRIGGER trigger_prevent_add_column
  BEFORE ALTER ON schemaname.tablename
  FOR EACH ROW
  BEGIN
    DECLARE sql_text VARCHAR(255);
    SET sql_text = (SELECT EVENT_DATA FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'trigger_prevent_add_column');
    IF INSTR(sql_text, 'ADD COLUMN') > 0 THEN
      SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Adding columns to schemaname.tablename is prohibited';
    END IF;
  END //
  DELIMITER ;
  

3. SQL Server #

SQL Server通过回收表的ALTER权限实现：

REVOKE ALTER ON OBJECT::schemaname.tablename FROM your_username;

• 注意：如果用户属于db_ddladmin等自带DDL权限的数据库角色，需要先将用户从该角色移除，否则权限回收无效。
• 细分控制方案：用DDL触发器拦截ADD COLUMN操作：

  CREATE TRIGGER trigger_prevent_add_column
  ON DATABASE
  FOR ALTER_TABLE
  AS
  BEGIN
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @TableName NVARCHAR(128) = @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');
    DECLARE @SchemaName NVARCHAR(128) = @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(128)');
    DECLARE @CommandText NVARCHAR(MAX) = @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'NVARCHAR(MAX)');
  
    IF @SchemaName = 'schemaname' AND @TableName = 'tablename' AND CHARINDEX('ADD COLUMN', @CommandText) > 0
    BEGIN
      RAISERROR('Adding columns to schemaname.tablename is prohibited', 16, 1);
      ROLLBACK TRANSACTION;
    END
  END;
  

通用注意事项 #

• 测试验证：执行权限回收或创建触发器后，用目标用户登录，尝试执行ALTER TABLE schemaname.tablename ADD COLUMN new_col INT;，确认操作被拒绝。
• 权限继承：务必检查用户是否从角色继承了相关权限，这是很多权限回收不生效的常见原因。

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