[SOLVED] How to show existing permissions granted to MYSQL databases/tables?

[deerly]

Help!

 

I am using command line mysql and cannot for the life of me find a way to show existing database permissions exist!

 

For instance, if I did: GRANT SELECT ON database.table TO someone@"%" IDENTIFIED BY 'password';

 

How can I then FIND the results of that command?

 

I want to see:

USERPASSWORDSELECTINSERTUPDATEDELETE

someonepasswordX[/td][td]X

 

Obviously not in that format but what I want to do is audit my permissions and also make sure that I am typing things correctly.

 

I am currently having a connection error that I don't understand. Using a user name and pw I granted permissions to several times, it says it cannot connect. Want to verify that the permissions exist and that I didn't make a typo of some kind.

 

Thank you, I hope that makes sense!

[deerly]

Is this even possible?  ??? Google doesn't seem to offer any help!

[Ken2k7]

There is SHOW GRANTS.

 

http://dev.mysql.com/doc/refman/5.0/en/show-grants.html

[deerly]

Thank you, that is not something I knew about.

 

Unfortunately it only seems to show granted privileges for the user you are currently logged in as?  ???

 

The problem is that I cannot connect as this user. I don't know what the problem is with granting permissions and I'd like to see ALL permissions granted to various users to make sure there isn't some kind of typo?

 

I would imagine this would also be very useful to audit who can and cannot access different actions/databases and tables. I can't believe something so basic is so hard to find, hehe!

[Ken2k7]

Can you do - SHOW GRANTS FOR user?

[deerly]

Hmm, well that does work (and is very helpful to know, thank you!) but doesn't help the problem I am having like I was hoping it would. It looks like I granted permissions correctly but it still won't let me connect. Weird!

 

Also, it would be helpful to be able to at least see the different user names that have ANY permissions so we could use SHOW GRANTS FOR on them.

 

Seems like it should be a basic security issue so that you can make sure that no one has access to something they shouldn't!

 

 

[Mchl]

You can do

SELECT * FROM `information_schema`.`USER_PRIVILEGES`;

 

and

 

SELECT * FROM `information_schema`.`SCHEMA_PRIVILEGES`;

[deerly]

Oh, I wish that worked! I get an error 1064 with both of those, error with SQL Syntax  :-\

[Mchl]

Which MySQL version do you have?

[fenway]

SHOW GRANTS should work in the older version, too.

[deerly]

Got it all figured out a while ago, just updating here in case others have the same problem.

 

I was right, it WAS exceptionally simple!

 

You can view all your database users with:

 

SELECT user, host, password FROM mysql.user;

 

You can also view specific permissions for a user with:

 

SHOW GRANTS FOR userName@"%";

 

Easy as pie, I was just missing the right syntax. Weird that it was so hard to find online though!

 

I hope this helps!