Mariadb – show grants for someuser not working for root

mariadb

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

Best Answer

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.

Related Question