Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Suppose I have a MySQL server, and I would like to grant a set of (non-trivial) permissions to 10 users (user1, user2, ... user10), each of which may come from any of 10 different hosts that can't be specifically selected by a wildcard or netmask; for example:

  • abc.foo.example.com
  • def.bar.example.com
  • ghi.baz.example.com
  • etc.

Is there an easy way to manage this without having to maintain 10x10=100 sets of GRANTs for each distinct user@host combination?

I can think of a few compromises:

  1. Use a broader wildcard like: 'user1'@'%.example.com'. This includes all necessary hosts, but also permits access from other hosts within example.com, some of which may be malicious. (Imagine a university with thousands of students inside the network.) Strong passwords ameliorate the problem, but it is still nice to have the extra layer of host-specific security.

  2. Do #1, but also use a firewall to restrict the MySQL port to the 10 specific hosts we want to permit. The downside is if we later want to add an 11th user with very restricted GRANTs that can access from anywhere: 'user11'@'%'.

share|improve this question
    
What is the problem with maintaining the user/host combination? Do they change frequently? –  Nils Mar 6 '12 at 21:41
    
Can you force the users to connect to VPN before they access the DB? –  ckliborn Mar 6 '12 at 21:50

3 Answers 3

Here is a conceptual example

Suppose you want to create a list of users

  • user1
  • user2
  • ...
  • user10

where each user would access example.com with these prefixes

  • abc.foo
  • def.bar
  • ghi.baz

and each user is to be granted

INSERT, UPDATE, DELETE, SELECT

and the password for the users is 'whateveriwant'

Here is a pure query MySQL Query

select CONCAT('GRANT ',Grants,' TO ''',User,'''@''',Host,'.example.com'' IDENTIFIED BY PASSWORD ''',pwd,''';') GrantCommand
from
    (select "INSERT,UPDATE,DELETE,SELECT ON *.*" Grants) G,
    (SELECT PASSWORD('whateveriwant') pwd) P,
    (
        select 'user1' User
        union select 'user2'
        union select 'user3'
        union select 'user4'
        union select 'user5'
        union select 'user6'
        union select 'user7'
        union select 'user8'
        union select 'user9'
        union select 'user10'
    ) U,
    (
        select 'abc.foo' Host
        union  select 'def.bar'
        union  select 'ghi.baz'
    ) H
;

Here it is that pure MySQL Query executed in MySQL Client

mysql> select CONCAT('GRANT ',Grants,' TO ''',User,'''@''',Host,'.example.com'' IDENTIFIED BY PASSWORD ''',pwd,''';') GrantCommand
    -> from
    ->     (select "INSERT,UPDATE,DELETE,SELECT ON *.*" Grants) G,
    ->     (SELECT PASSWORD('whateveriwant') pwd) P,
    ->     (
    ->         select 'user1' User
    ->         union select 'user2'
    ->         union select 'user3'
    ->         union select 'user4'
    ->         union select 'user5'
    ->         union select 'user6'
    ->         union select 'user7'
    ->         union select 'user8'
    ->         union select 'user9'
    ->         union select 'user10'
    ->     ) U,
    ->     (
    ->         select 'abc.foo' Host
    ->         union  select 'def.bar'
    ->         union  select 'ghi.baz'
    ->     ) H
    -> ;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GrantCommand                                                                                                                                   |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user1'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user1'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user1'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user2'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user2'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user2'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user3'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user3'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user3'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user4'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user4'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user4'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user5'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user5'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user5'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user6'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user6'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user6'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user7'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user7'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user7'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user8'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user8'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user8'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user9'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user9'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user9'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA';  |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user10'@'abc.foo.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user10'@'def.bar.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; |
| GRANT INSERT,UPDATE,DELETE,SELECT ON *.* TO 'user10'@'ghi.baz.example.com' IDENTIFIED BY PASSWORD '*2BE1C8DCB5977386CFE99E3BE97EEFA7510786BA'; |
+------------------------------------------------------------------------------------------------------------------------------------------------+
30 rows in set (0.00 sec)

mysql>

Just place that query in a text file, such as /root/MakeBulkUserGrants.sql

Then just run

mysql -uroot -hlocalhost -p < /root/MakeBulkUserGrants.sql > /root/BulkUserGrants.sql

Give it a Try !!!

share|improve this answer
    
So the general approach you recommend is: create scripts to do the bulk account/privilege management. I suspected this is where I would wind up, but was hoping I'd simply missed some corner of MySQL that let you do this more cleanly. Creating the initial set of privileges and hosts is pretty straightforward, but once you get into long-term maintenance (adding a new allowed host for all users; modifying privileges for a single user from all permitted hosts; etc.) it looks like you'll end up with quite a few different scripts. (Interesting to see SQL used to generate SQL, by the way!) –  Kevin Ivarsen Mar 7 '12 at 19:39

The closest thing I'm aware of to what you are asking is the Securich project. It creates a set of stored procedures and helpers that you can use to create pseudo-roles and manage a lot of privileges in MySQL. Note that I haven't really used it myself; I've just watched the blog posts with interest. You can read more at http://mysqlpreacher.com/wordpress/tag/securich/. It was also presented last year at the MySQL conference: http://en.oreilly.com/mysql2011/public/schedule/detail/17372 although I can't find the slide deck, and Sheeri K. Cabral has mentioned it in her talk at IOUG Collaborate: http://technocation.org/files/doc/MySQLSecurity2011_06.pdf

share|improve this answer
    
Looks like a useful set of tools. I don't see anything that specifically addresses the problem of specifying a subset of hosts common to many users, but clone_user(), along with some external scripting, could make it easy to maintain similar permissions across the many user@host combos. –  Kevin Ivarsen Mar 7 '12 at 19:46

First of all, I would recommend to disable DNS resolution in your mysql configuration. (skip-name-resolve option) If you setup grants via FQDN (abc.bcd.example.com) instead of IP you may have troubles when a DNS query cannot run on time and the user will not be able to benefit from the privilege you gave him (or it, if it is an app).

Also, connection time will be much faster when using skip-name-resolve, on mysql load you cannot live without this option.

For 100 grants, I would use a shell script to add the privileges and if you want to query them, use pt-show-grants, part of the Percona Toolkit.

Hope this helps

share|improve this answer
    
Good general advice - there are certainly reliability concerns in adding DNS to the dependency chain. For my purposes most clients use long-running connections or connection pools and the small connection overhead is negligible, so I wanted to keep hostnames as an option in case there was a clever way to group the hosts I wanted (that couldn't be accomplished through netmasks / IP wildcards). –  Kevin Ivarsen Mar 7 '12 at 19:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.