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

MySQL grant all privileges to database except one table

提问by xzyfer

I've been unable to find a reasonable solution to achieve the following:

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

I wish to have a user that has ALL privileges on a database (or series of databases with the same schema), exceptfor one table, to which they will only have SELECT privileges.

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

Essentially I want the user to have free reign over a database but not to be able to update a specific table.

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

So far I have tried, to no avail:

到目前为止，我已经尝试过，但无济于事：

• Granting all privileges on that database (db_name.*) and then specifically granting only select privileges on that desired table (hoping it would overwrite the "all", stupid I know).

• Granting all privileges on that database (db_name.*) then revoking insert, update, and delete. But this produced an error saying there was no grant rule for db_name.table_name.

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

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

From what I've been able to gather I'll have to individually grant all privileges on each table of the database except the read only table.

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

Please someone tell me there is a easier way

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

Note: I'm running MySQL 5.1. The latest available on Ubuntu 10.04.

注意：我正在运行 MySQL 5.1。Ubuntu 10.04 上可用的最新版本。

回答by Carlos

I know this is an old post, but I thought I'd add on to @tdammers question for others to see. You can also perform a SELECT CONCAT on information_schema.tables to create your grant commands, and not have to write a separate script.

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

First revoke all privileges from that db:

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

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

Then create your GRANT statements:

然后创建您的 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";

Copy and paste the results into your MySQL client and run them all.

将结果复制并粘贴到您的 MySQL 客户端并运行它们。

回答by tdammers

AFAIK, yes, you need to grant individually per table. But hey, you have a computer there. Computers are great at automating repetitive tasks for you, so why don't you make a script that does the following:

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

1. Get a list of all tables in the database (SHOW TABLES;)
2. For each item on the list, grant all permissions
3. Revoke permissions on the special table

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

Or, alternatively: 2. For each item on the list, check if it is the special table; if it's not, grant all permissions

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

The reason I'm not giving code is that it can be done in any scripting language with MySQL facilities, even shell script; use what you're most comfortable using.

我没有给出代码的原因是它可以用任何带有 MySQL 工具的脚本语言来完成，甚至是 shell 脚本；使用你最舒服的使用方式。

回答by tdammers

Here is a draft of what I use to grant roles in MariaDB. Maybe setting an EVENT would make it more cool :-)

这是我用来在 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;

回答by user628176

Unfortunately, there is built-in natural ways in MySQL to perform selective/exceptional tasks.

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

You could use below script(linux console bash script)

您可以使用以下脚本（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

If you have a windows console you could use the following .bat file:

如果您有 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

First you write the query to get the list of the needed tables, next you define the list of users you want to grant access for. You need to execute the script every time the database structure changes. I have created separate short tutorial for MySQL selective/exceptional tasks.

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

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

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

https://youtu.be/8fWQbtIISdc

https://youtu.be/8fWQbtIISdc