show grants for someuser not working for root

Question

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

Answer 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.