CODEWITHSUNDEEP
mysqlsql-grant
user1978237
user1978237

Reputation: 29

How to restrict queries from specific hosts

How do I make sure 'anil' cannot run commands from any machine called '%desk%'?

mysql> show grants for anil;

+------------------------------------------------------------------------------------+
| Grants for anil@%                                                                  |
+------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'anil'@'%'                                                   |
| GRANT SELECT, CREATE TEMPORARY TABLES, LOCK TABLES ON `production`.* TO 'anil'@'%' |
+------------------------------------------------------------------------------------+


mysql> revoke all privileges, grant option from 'anil'@'%desk%';
ERROR 1269 (HY000): Can't revoke all privileges for one or more of the requested users

mysql> revoke usage on *.* from 'anil'@'%desk%';
ERROR 1141 (42000): There is no such grant defined for user 'anil' on host '%desk%'

mysql> revoke SELECT, CREATE TEMPORARY TABLES, LOCK TABLES ON `production`.* from 'anil'@'%desk%';
ERROR 1141 (42000): There is no such grant defined for user 'anil' on host '%desk%'

Upvotes: 0

Views: 1147

Answers (1)

Sammitch
Sammitch

Reputation: 32272

MySQL only allows you to grant permissions, it does not have the capacity to grant to all except a certain host, or to deny permissions based on hostname in the way you are attempting. The only way to accomplish this is to only give the user permissions only for acceptable hostnames. The REVOKE command only removes previously GRANTed permissions.

If your network happens to be broken up into subdomains [ie: *.desk.company.tld and *.serv.company.tld] you should be able to grant to 'anil'@'%.serv.company.tld' or a similar subnet: 'anil'@'192.168.1.%'.

Upvotes: 1

Related Questions