CODEWITHSUNDEEP
mysqlsql-grant
VaTo
VaTo

Reputation: 3098

Grant ALL on Database it's not granting any privileges to my specific IP

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 [email protected]                                      |
+------------------------------------------------------------------+
| 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 [email protected]                                      |
+------------------------------------------------------------------+
| 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)

Upvotes: 1

Views: 4255

Answers (1)

spencer7593
spencer7593

Reputation: 108510

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 "[email protected]". 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' ;

Upvotes: 5

Related Questions