mysql - 在 MySQL 中单独授予对所有列的权限

Question

我需要为我的数据库创建不同的用户，每个用户都可以访问特定表中的列子集。我意识到要做到这一点，我需要单独授予对列的访问权限，因为如果我授予对整个表的访问权限，我将无法撤销单个列的权限。所以我现在正在做的是按如下方式授予权限:

GRANT SELECT (`col_1`, `col_2`, ... , `col_100`), SHOW VIEW ON my_db.my_table TO 'my_user'@'%';

这很麻烦和困惑，因为我有大量的列，而且不太实用，因为将来可能会对列列表进行更改。有没有办法在不列出名称的情况下授予对每一列的权限？然后我可以撤销每个用户的个人。像这样的东西:

GRANT SELECT (SELECT `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA`='my_db' AND `TABLE_NAME`='my_table'), SHOW VIEW ON my_db.my_table TO 'my_user'@'%';

(但这给了我一个语法错误，所以这显然不是我需要的)。有办法做到这一点吗？

Answer 1

您可以使用 INFORMATION_SCHEMA，但不是您尝试过的方式。相反，您应该首先构建一个查询字符串。然后准备并执行它。

set @user   = 'testuser';
set @host   = 'localhost';
set @schema = 'test';
set @table  = 'test_table';

set @columns = (
    select group_concat('`', column_name, '`')
    from information_schema.columns
    where table_schema = @schema
      and table_name   = @table
);

set @sql = 'GRANT SELECT({columns}) ON TABLE `{schema}`.`{table}` TO {user}@{host}';
set @sql = replace(@sql, '{columns}', @columns);
set @sql = replace(@sql, '{schema}' , @schema);
set @sql = replace(@sql, '{table}'  , @table);
set @sql = replace(@sql, '{user}'   , quote(@user));
set @sql = replace(@sql, '{host}'   , quote(@host));

prepare stmt from @sql;
execute stmt;

根据需要更改四个变量和查询。