1

I have 2 users, one with ALL grants, another with SELECT only. User 1 can see the database, not user 2, why?

Read-only user:

MariaDB [(none)]> SHOW GRANTS FOR 'readonly'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for readonly@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `readonly`@`%` IDENTIFIED BY PASSWORD '*854D73C4F9D2019568DEA092DA192405FAD90F77' |
| GRANT SELECT ON `database`.* TO `readonly`@`%`                                                           |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

# mysql -u readonly -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.001 sec)

Full-grants user:

MariaDB [(none)]> SHOW GRANTS FOR 'fullgrants'@'%';
+--------------------------------------------------------------------------------------------------------+
| Grants for fullgrants@%                                                                                   |
+--------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `fullgrants`@`%` IDENTIFIED BY PASSWORD '*72A473061F48961B5B0EB890CFEA4B9698A569E9' |
| GRANT ALL PRIVILEGES ON `database`.* TO `fullgrants`@`%`                                                   |
+--------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

# mysql -u fullgrants -e "SHOW DATABASES;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| database           |
+--------------------+
2 rows in set (0.002 sec)
3
  • Had you FLUSH PRIVILEGES; before logging in?
    – Kondybas
    Feb 22, 2022 at 17:37
  • Yes i did. I just did it now too (just in case), same result. Feb 22, 2022 at 18:01
  • @Kondybas you'll notice the GRANT manual doesn't say anything about using FLUSH PRIVILEGES which is when started with --skip-grant-table, or the dodgy practices of manually manipulating the underlying MariaDB tables occur. Its not needed for SQL user manipulation activities.
    – danblack
    Feb 25, 2022 at 0:11

1 Answer 1

0

You aren't actually connected as the readonly user. If you select current_user(), you'll see an anonymous user that hasn't got any privileges on the database.

3
  • # mysql -u readonly -p -e "select current_user()" Enter password: +----------------+ | current_user() | +----------------+ | readonly@% | +----------------+ Feb 26, 2022 at 8:38
  • Apparently I was wrong. Try 'show grants` as this user?
    – danblack
    Feb 26, 2022 at 21:19
  • Same output as on the initial post: GRANT SELECT ON database.* TO readonly@% Feb 28, 2022 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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