8

I'm not finding a user by show grants for 'username'@'localhost'' I'm thinking that perhaps the username is slightly different, so I want to do a wildcard search of all the database users. How can I do this?

update: for clarification, as I'm thinking maybe I've not been 100% clear. by wildcard I meant I wanted to be able to search for only part of the users name, or for all users who's name matches pattern. On the system I needed this on there were 5k+ users, and although that's not performance loss huge, I don't want to look through them one at a time either.

Improve this question
9

Thanks to @matthewh's answer I managed to compose a query that would work when searching through a large number of users: 

use mysql;
select  User, Host from  user where User like 'user%';

Note: <user> is whatever component of the username you need to search for, you may need a % on both sides if you don't know the start of the username, also <> are merely to note the variable, don't include them in an actual query, unless the username includes < or >.

Improve this answer
7
use mysql;

select Host,User from user;

will give you all the MySQL users on that server.

Improve this answer
1
  • I voted this down one, because it doesn't do what I meant with by wildcard, though I acknowledge that may have been my fault for not being clear enough up front. It might have been interpretted as everything when what I wanted was the equivalent of `find -name 'user*' for a filename on unix. – xenoterracide Jan 20 '11 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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