MySQL授予数据库除一张表之外的所有权限

Question

我一直无法找到合理的解决方案来实现以下目标：

我希望拥有一个对数据库（或具有相同架构的一系列数据库）拥有所有权限的用户，除了一个表，他们只有 SELECT 权限。

本质上，我希望用户可以自由控制数据库，但不能更新特定的表。

到目前为止，我已经尝试过，但没有成功：

• 授予该数据库（db_name.*）的所有权限，然后专门授予对该所需表的选择权限（希望它会覆盖“全部”，我知道很愚蠢）。 授予该数据库（db_name.*）的所有权限

• 授予该数据库 (db_name.*) 的所有权限，然后撤销插入、更新和删除。但这会产生一个错误，指出 db_name.table_name 没有授予规则。

根据我收集到的信息，我必须单独授予数据库每个表的所有权限（只读表除外）。

请有人告诉我有一个更简单的方法

注意：我正在运行 MySQL 5.1。 Ubuntu 10.04 上提供了最新版本。

Answer 1

我知道这是一篇旧帖子，但我想我应该添加到 @tdammers 问题以供其他人查看。您还可以对 information_schema.tables 执行 SELECT CONCAT 来创建授予命令，而不必编写单独的脚本。

首先撤销该数据库的所有权限：

REVOKE ALL PRIVILEGES ON db.* FROM user@localhost;  

然后创建 GRANT 语句：

SELECT CONCAT("GRANT UPDATE ON db.", table_name, " TO user@localhost;")
FROM information_schema.TABLES
WHERE table_schema = "YourDB" AND table_name <> "table_to_skip";

将结果复制并粘贴到 MySQL 客户端并全部运行。

Answer 2

AFAIK，是的，您需要为每个表单独授予。但是嘿，你那里有一台电脑。计算机非常擅长为您自动执行重复性任务，所以为什么不编写一个脚本来执行以下操作：

1. 获取数据库中所有表的列表 (SHOW TABLES;)
2. 对于数据库中的每个项目列表，授予所有权限
3. 撤销对特殊表的权限

或者，或者：
2. 对于列表中的每一项，检查是否是特殊表；如果不是，则授予所有权限

我不提供代码的原因是它可以用任何具有 MySQL 功能的脚本语言（甚至是 shell 脚本）来完成；使用您最舒服的方式。

Answer 3

这是我用来在 MariaDB 中授予角色的草稿。
也许设置一个事件会让事情变得更酷:-)

DELIMITER $

DROP PROCEDURE IF EXISTS refreshRoles $
CREATE PROCEDURE refreshRoles ()
  COMMENT 'Grant SELECT on new databases/tables, revoke on deleted'
BEGIN
  DECLARE done BOOL;
  DECLARE db VARCHAR(128);
  DECLARE tb VARCHAR(128);
  DECLARE rl VARCHAR(128);
  DECLARE tables CURSOR FOR
    SELECT table_schema, table_name, '_bob_live_sg' FROM information_schema.tables
    WHERE table_schema LIKE '%bob\_live\_sg' AND
      (  false
      OR table_name LIKE 'bundle%'
      OR table_name LIKE 'cart%'
      OR table_name LIKE 'catalog%'
      OR table_name LIKE 'url%'
      );

  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=true;

  CREATE ROLE IF NOT EXISTS '_bob_live_sg';
  REVOKE ALL, GRANT OPTION FROM '_bob_live_sg';

  OPEN tables;
  SET done = false;
  grant_loop: LOOP
    FETCH tables INTO db, tb, rl;
    IF done THEN
      LEAVE grant_loop;
    END IF;
    SET @g = CONCAT('GRANT SELECT ON `', db, '`.`', tb, '` TO ', rl);
    PREPARE g FROM @g;
    EXECUTE g;
    DEALLOCATE PREPARE g;
  END LOOP;
  CLOSE tables;
END $

DELIMITER ;

CALL refreshRoles;

Answer 4

不幸的是，MySQL 中有内置的自然方法来执行选择性/特殊任务。

您可以使用下面的脚本（Linux 控制台 bash 脚本）

#!/bin/bash

# Define the database and root authorization details
db_host='localhost'
db_name='adhoctuts'
db_user='root'
db_pass='Adhoctuts2018#'

# Define the query to get the needed tables
table_list=$(mysql -h $db_host -u $db_user -p"$db_pass" -se "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema='$db_name' and table_name not like 'tbl1' AND table_name not like '\_\_%';" $db_name | cut -f1)

# Convert the query result into the array
table_arr=(${table_list//,/ })

# Declare the associative array of the users as username=>password pair
# e.g: declare -A user_list=(["'user1'"]="pass1" ["'user2'"]="pass2")
# In our case there is a single user
declare -A user_list=(["'aht_r'@'localhost'"]="Adhoctuts2018#")
for user in "${!user_list[@]}"
do
    pass=${user_list[$user]}
    # Recreate user
    mysql -h $db_host -u $db_user -p"$db_pass" -se "drop user if exists $user; create user $user identified by '$pass';"

    # Provide SELECT privilege
    mysql -h $db_host -u $db_user -p"$db_pass" -se "revoke all privileges, grant option from $user;" $db_name
    mysql -h $db_host -u $db_user -p"$db_pass" -se "grant usage on $db_name.* TO $user;" $db_name
    for tbl in "${table_arr[@]}"; do
        echo "grant select on $tbl TO $user"
        mysql -h $db_host -u $db_user -p"$db_pass" -se "grant select on $tbl TO $user;" $db_name    
    done
done

如果您有 Windows 控制台，您可以使用以下 .bat 文件：

@ECHO OFF
%= Define the database and root authorization details =% 
set db_host=192.168.70.138
set db_name=adhoctuts
set db_user=adhoctuts
set db_pass=Adhoctuts2018#

mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "select concat(table_schema,'.',table_name) from information_schema.tables where table_schema='%db_name%' and table_name not like 'tbl1' AND table_name not like '\_\_%%';" %db_name% > tbls

setlocal EnableDelayedExpansion
set user_cnt=2
set user[1]='Adhoctuts1'@'192.168.%%.%%'
set pass[1]=Adhoctuts1_2018#
set user[2]='Adhoctuts2'@'192.168.%%.%%'
set pass[2]=Adhoctuts2_2018#

set i=1
:loop
    set user=!user[%i%]!
    set pass=!pass[%i%]!
    mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "drop user if exists %user% ; create user %user%  identified by '%pass%';"
    mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "revoke all privileges, grant option from %user%;" %db_name%      
    for /F "usebackq delims=" %%a in ("tbls") do (
        mysql -h %db_host% -u %db_user% -p"%db_pass%" -se "grant select on %%a TO %user%;" %db_name%
    )
    if %i% equ %user_cnt% goto :end_loop
    set /a i=%i%+1
goto loop

:end_loop
del /f tbls

首先编写查询以获取所需表的列表，接下来定义您想要的用户列表授予访问权限。每次数据库结构发生变化时，您都需要执行该脚本。我为 MySQL 选择性/特殊任务创建了单独的简短教程。

https://adhoctuts.com/mysql-selective-exceptional-permissions -and-backup-restore/

https://youtu.be/8fWQbtIISdc