If you wish to know which accounts have access to a MySQL or MariaDB database, you can use the command
select user from mysql.db where db='dbname';
where dbname is the name of the relevant database. E.g., if I want to
view access to the Abc database, I can use the command below while logged into
the MySQL/MariaDB DBMS as
root (you can log in using the MySQL/MariaDB root account with mysql
-u root -p):
MariaDB [(none)]> select user from mysql.db where db='Abc'; Empty set (0.00 sec) MariaDB [(none)]>
In the above example, no accounts have been granted access to the database. If I want to grant all types of access to the abcsales1 account to all tables in the database, I can use the command shown below:
MariaDB [(none)]> GRANT ALL on Abc.* to 'abcsales1'@'localhost'; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> select user from mysql.db where db='Abc'; +-----------+ | user | +-----------+ | abcsales1 | +-----------+ 1 row in set (0.00 sec) MariaDB [(none)]>
Bear in mind that the select user from mysql.db
where db='dbname'; will only show users who have
database-level access to the database named dbname. It is possible that
an account can have access at the table, column, or proc level rather being
granted access at the database level. To check for those access levels of
access as well as database-level acess, use all of the commands below
substituting the name of the database for dbname:
SELECT user,host FROM mysql.db WHERE db='dbname'; SELECT user,host FROM mysql.tables_priv WHERE db='dbname'; SELECT user,host FROM mysql.columns_priv WHERE db='dbname'; SELECT user,host FROM mysql.procs_priv WHERE db='dbname';
E.g.:
MariaDB [(none)]> SELECT user,host FROM mysql.db WHERE db='Abc'; +-----------+-----------+ | user | host | +-----------+-----------+ | abcsales1 | localhost | +-----------+-----------+ 1 row in set (0.00 sec) MariaDB [(none)]> SELECT user,host FROM mysql.tables_priv WHERE db='Abc'; Empty set (0.00 sec) MariaDB [(none)]> SELECT user,host FROM mysql.columns_priv WHERE db='Abc'; Empty set (0.00 sec) MariaDB [(none)]> SELECT user,host FROM mysql.procs_priv WHERE db='Abc'; Empty set (0.00 sec) MariaDB [(none)]>
References:
-
how to know all users that can access certain database (mysql)?
Asked: April 28, 2011
Server Fault -
Managing database access with MariaDB or MySQL
Date: July 17, 2015
MoonPoint Support
