1

I'm trying to grant all privileges to a specific IP but when I try to get the list of privileged IPs it always shows only localhost, I followed the instructions in this question but it doesn't do any changes, what am I doing wrong?

MariaDB [(none)]> GRANT ALL ON database.* TO 'root'@'192.168.3.1' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show slave status;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation
mysql> show grants;
+------------------------------------------------------------------+
| Grants for root@192.168.1.5                                      |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'192.168.1.5'                       |
| GRANT ALL PRIVILEGES ON `western_star`.* TO 'root'@'192.168.1.5' |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)

Note:

I still get denied even though I logged in with my user remotely and I have the permissions.

mysql> show slave status;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation
mysql> show grants;
+------------------------------------------------------------------+
| Grants for root@192.168.1.5                                      |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'192.168.1.5'                       |
| GRANT ALL PRIVILEGES ON `western_star`.* TO 'root'@'192.168.1.5' |
+------------------------------------------------------------------+
2 rows in set (0.00 sec)
Community
  • 1
  • 1
VaTo
  • 2,936
  • 7
  • 38
  • 77
  • 1
    NOTE: The FLUSH PRIVILEGES statement is redundant. That operation is only needed (I believe) when we make DML changes to the privilege tables in the mysql database. GRANT and REVOKE operations automatically do the required operations to make the privilege changes effective. – spencer7593 Apr 19 '16 at 01:31

1 Answers1

5

Try running this statement:

SHOW GRANTS FOR 'root'@'192.168.3.1' ;

And compare to the return from this:

SHOW GRANTS FOR 'root'@'localhost' ;

SHOW GRANTS shows the grants for the current user.

Note that "root@localhost" is not the same user as "root@192.168.3.1". MySQL identifies a user by both user AND host. (Those are two different users.)

FOLLOWUP

The SUPER and REPLICATION CLIENT privileges are global privileges, not database privileges. Syntax for granting those privileges is ON *.*. For example:

GRANT REPLICATION CLIENT ON *.* TO 'root'@'192.168.1.5' ;
spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • I think the confusion was that I thought `show grants` would list all grants for all users. – VaTo Apr 19 '16 at 01:58