0

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%'
LSerni
  • 55,617
  • 10
  • 65
  • 107
user1978237
  • 29
  • 3

1 Answers1

1

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.%'.

Sammitch
  • 30,782
  • 7
  • 50
  • 77
  • Maybe there's an entirely different way of achieving this - I want to make sure someone doesn't accidentally run scripts from their desktops against the production database. They have to specifically log on to a production machine to do so. The production machines have less of a standard naming convention. The desktops are all named '%desk%'. – user1978237 Jan 14 '13 at 19:31
  • Can you not just create a user at the production machine’s IP address? – Martin Bean Jan 14 '13 at 19:39
  • We have a number of production machines and we distribute jobs across them. Just seemed least intrusive restricting from '%desk%' rather than granting for a series of production machine names. – user1978237 Jan 14 '13 at 19:43