MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
November
Sun Mon Tue Wed Thu Fri Sat
22
         
2015
Months
Nov


Sun, Nov 22, 2015 8:43 pm

Viewing accounts that have access to a MySQL or MariaDB database

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:

  1. how to know all users that can access certain database (mysql)?
    Asked: April 28, 2011
    Server Fault
  2. Managing database access with MariaDB or MySQL
    Date: July 17, 2015
    MoonPoint Support

[/software/database/mysql] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo