1

We grant access to users with the following

grant select, insert, update, delete on product_events to 'product_admin'@'%' ;

Now, like this there are a lot of tables, and a lot of users.

I want to find all users having any grants on a particular table

That is, given product_events table, I would like to get 'product_admin'@'%'

2
  • What is the name of database product_events resides in ??? Mar 16, 2021 at 16:21
  • @RolandoMySQLDBA You can assume product-db Mar 16, 2021 at 16:22

1 Answer 1

1

This messy query should give you every user that can access product_db.product_events

SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' OR insert_priv='Y' OR update_priv='Y' OR delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

If you are looking for users with all four(4) permissions (SELECT,INSERT,UPDATE and DELETE grants), then use this (basically using AND instead of OR)

SELECT user,host,'Global' Level FROM mysql.user WHERE
select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y'
UNION
SELECT user,host,'DB' FROM mysql.db
WHERE db='product_db' AND
(select_priv='Y' AND insert_priv='Y' AND update_priv='Y' AND delete_priv='Y')
UNION
SELECT user,host,'Table' FROM mysql.tables_priv
WHERE db='product_db' AND table_name='product_events';

NOTE : Table level privileges are even more messier to check since the grants are listed as a set rather than as individual columns

mysql> desc mysql.tables_priv;
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                                                                                                              | Null | Key | Default           | Extra                       |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char(60)                                                                                                                          | NO   | PRI |                   |                             |
| Db          | char(64)                                                                                                                          | NO   | PRI |                   |                             |
| User        | char(16)                                                                                                                          | NO   | PRI |                   |                             |
| Table_name  | char(64)                                                                                                                          | NO   | PRI |                   |                             |
| Grantor     | char(77)                                                                                                                          | NO   | MUL |                   |                             |
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set('Select','Insert','Update','Delete','Create','Drop','Grant','References','Index','Alter','Create View','Show view','Trigger') | NO   |     |                   |                             |
| Column_priv | set('Select','Insert','Update','References')                                                                                      | NO   |     |                   |                             |
+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)

mysql>
2
  • Thanks a lot.. This works.. But could this somehow be improved to include all privileges as I had to look up all privileges in mysql.db table and mysql.user table and mention each one with an OR. Mar 16, 2021 at 17:01
  • 1
    Each table has a different level with a different set of columns per table. You specified the grants, so my answer has them. If you need more grants to check, simply add those grants to the WHERE clauses. Don't worry about searching. Grant tables are usually small. Mar 16, 2021 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.