MySQL grant all privileges to database except one table

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.TABLESWHERE table_schema = "YourDB" AND table_name <> "table_to_skip";

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

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.

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;