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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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 Install MySQL Cluster on a single server for testing purposes
  2. Can MySQL Cluster Run Multiple Nodes On a Single Server
  3. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  4. How To Purge Audit Logs in MySQL
  5. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  6. How to gather information on the MySQL 8 Roles and Privileges assigned to a user without using SHOW GRANTS
  7. How To Install MySQL RPM packages in a different location to allow multiple versions (versions < 5.6.10)
  8. MySQL ‘show processlist’ statement
  9. How to Generate Unique IDs For MySQL Cluster Backups
  10. How To Configure Separate Override.conf For Multiple MySQL Instances Using Systemd

You May Also Like

Primary Sidebar

Recent Posts

  • JavaFX ComboBox: Set a value to the combo box
  • Nginx load balancing
  • nginx 504 gateway time-out
  • Images preview with ngx_http_image_filter_module

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright