0

I have two servers both running on Version(): 10.3.21-MariaDB

On server1 when I log into mysql as root and execute the command: show grants for someuser; It shows me all the grants for that user.

On server2 when I log into mysql as root and execute the command: show grants for someuser; I get this error:

ERROR 1141 (42000): There is no such grant defined for user 'someuser' on host '%'

On both servers when I execute the command: 

select user from mysql.user;

I get a list of the users. So the users that I am trying to see their grants are there ...

What is would be different with server2 that root can not see the grants ?

on all the servers is_role = N

but I did notice another difference among the 3 servers - the root user has different host entries Also I have added the vm type that the server is running also have results for a 3rd server

server3 (virtuozzo) show grant must have the @somehost using all of the different host entries give a result with the only difference being the @somehost is whatever was used in the for someuser@somehost

GRANT USAGE ON . TO 'someuser'@'somehost' indentified by .... GRANT USAGE ON . TO 'someuser'@'ip address' indentified by ... etc.

root user entries: | root | localhost | N | | root | server.example.com | N | | root | 127.0.0.1 | N | | root | ::1 | N |

server2 (vmware) show grant must have the @host only the @ip address gives a result

root user entries: | root | localhost | N | | mysql.session | localhost | N | | mysql.sys | localhost | N |

server1 (kwm) show grant does not have to have the @host using all of the different host entries give a result with the only difference being the @somehost is whatever was used in the for someuser@somehost

GRANT USAGE ON . TO 'someuser'@'somehost' indentified by .... GRANT USAGE ON . TO 'someuser'@'ip address' indentified by ... etc.

root user entries: | root | localhost | N | | root | cent6base-cpanel.pxe.bluehost.com | N | | root | 127.0.0.1 | N

Improve this question
1

1 Answer 1

Reset to default
1

I'm only guessing here, but could it be that you have defined different host values for someuser on server1 and server2?

When you execute SHOW GRANTS FOR someuser; it really executes SHOW GRANTS FOR someuser@'%'; - that is, if you don't specify the host part, the it will default to using the wildcard (%) for the host part.

So if you have someuser@'%' on server1 and e.g. someuser@'localhost' on server2, then you will get the result you have observed.

You can see the host part of the users by executing SELECT user, host FROM mysql.user;

And then you can view the grants for the different users by executing SHOW GRANTS FOR someuser@'localhost'; or similar for users with other host values in their mysql.user rows. Note that each row in the table is a different user - it doesn't matter that some of them might have the same user names as long as they have different host values.

Improve this answer
5
  • So I did the select user, host from mysql.user; and it listed the users for several hosts: someuser | xxx.xxx.xxx.xxx, someuser | localhost, someuser | server.mydomain.org, - on server 2 only the command: show hosts for [email protected] showed me the grants for someuser .... whereas on server1 they all show the grants. I set up the users on both servers exactly the same way (via cpanel) - and only server2 requires the someuser@host to show the grants ... and only when I referenced the server ip
    – pineyscripter
    Dec 31, 2019 at 4:57
  • @pineyscripter Does the SELECT user, host FROM mysql.user; query give you the same result on server1 and server2?
    – dbdemon
    Dec 31, 2019 at 5:32
  • Also, you might want to try SELECT user, host, is_role FROM mysql.user; on both servers and make sure you get the same result.
    – dbdemon
    Dec 31, 2019 at 5:50
  • on all the servers is_role = N but I did notice another difference among the 3 servers - the root user has different host entries Also I have added the vm type that the server is running also have results for a 3rd server
    – pineyscripter
    Dec 31, 2019 at 14:45
  • @pineyscripter Do you have a user someuser@'%' on server1? If so, that would explain why you are able to do SHOW GRANTS FOR someuser; on that server. When you do SHOW GRANTS FOR ... you really should include the host. The user + host are the two components that together uniquely identify a DB user. If you leave out the host, then MariaDB will assume you actually mean '%'. I hope that explains it.
    – dbdemon
    Dec 31, 2019 at 20:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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