MySQL grant all privileges to database except one table

by

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.

More Related Contents:

  1. grant remote access of MySQL database from any IP address
  2. How to grant remote access permissions to mysql server for user?
  3. How can I restore the MySQL root user’s full privileges?
  4. How to start MySQL with –skip-grant-tables?
  5. I can not find my.cnf on my windows computer [duplicate]
  6. How to grant remote access to MySQL for a whole subnet?
  7. #1130 – Host ‘localhost’ is not allowed to connect to this MySQL server
  8. GRANT with database name wildcard in MySQL?
  9. “INSERT IGNORE” vs “INSERT … ON DUPLICATE KEY UPDATE”
  10. How can I make SQL case sensitive string comparison on MySQL?
  11. Import data in MySQL from a CSV file using LOAD DATA INFILE
  12. Library not loaded: libmysqlclient.16.dylib error when trying to run ‘rails server’ on OS X 10.6 with mysql2 gem
  13. Count the occurrences of DISTINCT values
  14. INSERT INTO … SELECT FROM … ON DUPLICATE KEY UPDATE
  15. How do I modify a MySQL column to allow NULL?
  16. MySQL WHERE IN ()
  17. MySQL error 1241: Operand should contain 1 column(s)
  18. MySQL – Supertype/Subtype design
  19. How can write queries in MySQL that can parse JSON data in a column?
  20. Mysql: Order by like?
  21. MySQL Time Zones
  22. How to use django models with foreign keys in different DBs?
  23. How to change MySQL column definition?
  24. How can I undo a mysql statement that I just executed?
  25. Continue SQL query even on errors in MySQL workbench
  26. How to generate a UUIDv4 in MySQL?
  27. is php sort better than mysql “order by”?
  28. How can “set timestamp” be a slow query?
  29. Detecting utf8 broken characters in MySQL
  30. django cannot connect mysql in docker-compose

Leave a Comment