Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How do I list out all users accessing a list of on the mysql server.

Thanks Jean

share|improve this question

migrated from superuser.com Oct 11 '10 at 15:12

This question came from our site for computer enthusiasts and power users.

    
This is a bit unclear. Do you want a list of all users connected to the MySQL daemon? A list of system users on the system the MySQL daemon is running on? –  Ignacio Vazquez-Abrams Oct 11 '10 at 9:43
    
I want to list all users that can connect to dbs –  Jean Oct 11 '10 at 9:48

2 Answers 2

Any user that can connect to the database can be found in the user table.

When a user tries to connect to the database, MySQL checks that that particular username/host/password combination has permission to connect.

It is important to understand that the host and user together determine an individual permission for connecting. User Nosipho may have access from host A, and not from host B. In fact, user Nosipho on host B may be an entirely different user.

So to find out who can connect and from where run this sql SELECT host,user FROM user; This is a good introduction to MySQL permissions


share|improve this answer
    
running that shows me no db selected –  Jean Oct 11 '10 at 10:14
1  
not sure, but I think there's a database called "mysql" ? Try USE mysql or SELECT host,user FROM mysql.user –  nickf Oct 11 '10 at 10:16
    
@nickf - Yup global privileges are stored in the mysql.user table. But you can have privileges per database too so you can use SELECT host,user FROM my_very_own_db.user –  Nifle Oct 11 '10 at 10:23
    
there is a user and host table. host is empty –  Jean Oct 11 '10 at 10:28
    
@Jean - I think that means they can only connect locally –  Nifle Oct 11 '10 at 10:30

The query you want is this:

SELECT user,
       host
  FROM mysql.user

And you probably need to be logged in as root in order to see the mysql table.

If you want to run an ssh command on one machine and have it return the MySQL users on a different machine, that's a little more complicated, but not terribly hard.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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