• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Granting All Privileges On All databases Except One Specific Table in MySQL

By admin

The post discusses a useful method to grant all privileges on all databases except one specific table to a user.

MySQL does not support granting all privileges on all databases to a user then revoking all privileges on a specific table from that user. This produces an error saying there was no grant rule for the specific table. For example:

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.80 sec)
mysql> CREATE TABLE testdb.testtable (i int);
Query OK, 0 rows affected (0.99 sec)

mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'testpass';
Query OK, 0 rows affected (0.12 sec)
mysql> GRANT ALL ON *.* TO 'test'@'%';
Query OK, 0 rows affected (0.18 sec)
mysql> SHOW GRANTS FOR 'test'@'%'\G
*************************** 1. row ***************************
Grants for test@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO 'test'@'%'
*************************** 2. row ***************************
Grants for test@%: GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO 'test'@'%'
2 rows in set (0.00 sec)
mysql> REVOKE ALL ON testdb.testtable FROM 'test'@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test' on host '%' on table 'testtable'

To achieve this goal, you need to grant individually per database/table. There is a handy way to perform a SELECT CONCAT on information_schema.tables to create your grant commands SQL script, and then execute the script via mysql client. For example:

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'test'@'%';
mysql> SELECT CONCAT("GRANT ALL PRIVILEGES ON ", table_schema, ".*", " TO 'test'@'%';") INTO OUTFILE "/var/lib/mysql-files/mytest1.sql"
FROM information_schema.TABLES
WHERE table_schema <> "testdb"
GROUP BY table_schema;
mysql> SOURCE /var/lib/mysql-files/mytest1.sql;
mysql> SELECT CONCAT("GRANT ALL PRIVILEGES ON ", table_schema, ".", table_name, " TO 'test'@'%';") INTO OUTFILE "/var/lib/mysql-files/mytest2.sql"
FROM information_schema.TABLES
WHERE table_schema = "testdb" AND table_name <> "testtable";
mysql> SOURCE /var/lib/mysql-files/mytest2.sql;

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to use a Wrapper Script to set Custom Per-Process Attributes for MySQL Server
  2. How To Reset MySQL 8.0 Root Password On Windows
  3. Multi-Versioning in MySQL Database
  4. Understanding the Different Configuration files used for MySQL Server
  5. How To Shut Down a Node in MySQL Cluster
  6. What is the purpose of “system user” in MySQL Replication
  7. System Tuning for MySQL Server
  8. MySQL 8.0 : Persisted Variables
  9. MySQL: How to Set Account Resource Limits
  10. “Another MySQL daemon already running with the same unix socket” – error while starting MYSQL

You May Also Like

Primary Sidebar

Recent Posts

  • What are different Oracle Database Vault Roles
  • Unable to export realm protected table using data pump
  • Beginners Guide to Oracle Database Vault
  • How to Disable IPv6 on Ubuntu 18.04 Bionic Beaver Linux
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary