MySQL show who has access to database

Databases
1

I have my website on a shared webhost who allows me to have shell access to do mysql queries among other things. when granting access for users to a database, is there a way to list the users i’ve added and their privlages?

Right now i can find out what privlages i have for myself

mysql> show grants for geek@"%";
+-----------------------------------------------------------------------------+
| Grants for geek@%                                                          |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'geek'@'%' IDENTIFIED BY PASSWORD '<encrypted>' |
| GRANT ALL PRIVILEGES ON Forum.* TO 'geek'@'%' WITH GRANT OPTION       |
| GRANT ALL PRIVILEGES ON newsletter.* TO 'geek'@'%' WITH GRANT OPTION       |
| GRANT ALL PRIVILEGES ON testdb.* TO 'geek'@'%' WITH GRANT OPTION           |
| GRANT ALL PRIVILEGES ON survey.* TO 'geek'@'%' WITH GRANT OPTION           |
+-----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

but trying to do

mysql> show grants for “%”; and
mysql> show grants for geek2@“%”; (i created the geek2 user)
yields
ERROR 1044: Access denied for user: ‘geek@localhost’ to database ‘mysql’

How do i find out which users have access to my database and the privlages granted (for example “survey”)?

2

I guess one way to do it would be to log in as the root user, then select the mysql database, then:

mysql> select * from db;

This will show all the hosts, users, databases, and privileges contained in your installation.

3

would you happen to know of another method because my mysql dB is on a webhost (i have no root privileges)

4

phpMyAdmin

Sean :slight_smile:

5

too bad phpmyadmin doesnt say which users has grants to the database. please correct me if it can list the users and their grants.

6

This is what it gives me (just running locally)

Sean :slight_smile:

7

Where’s that sean?
I’m usin phpMyAdmin 2.2.2 and I can’t find a screen like that anywhere.

8

on the main phpMyAdmin screen, click on Users. if you’re not able to view it (like on my host), it won’t be there.

9

Ahh i c.
figured it was something restricted.

10

seanf, any way to see that but as a normal user? I dont have root access (mysql is on my webhost)

11

How are you adding users?

Sean :slight_smile:

12

with grant

13