MySQL grant all privileges to database except one

Question

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), except for one table, to which they will only have SELECT privileges.

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.

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.

Answer 1

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.

First revoke all privileges from that db:

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

Then create your GRANT statements:

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.

Answer 2

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:

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

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

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.

Answer 3

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

You could use below script(linux console bash script)

#!/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:

@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.

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

https://youtu.be/8fWQbtIISdc

Answer 4

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

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;