2

I have a database table that I want all valid MySQL users to have access to (it's for tracking a particular set of statistics across multiple databases). Is there a way to do this?

I know you can wildcard hosts, but you don't appear to be able to wildcard usernames. Essentially, I want to do this:

REVOKE ALL PRIVILEGES ON  `database`.`table` FROM  ''@'%';
GRANT INSERT, UPDATE ON  `database`.`table` TO  ''@'%';

2 Answers 2

1
+200

Loop through all MySQL users in mysql.user table and grant privileges which you want:

mysql --skip-column-names -e "select user,host from mysql.user where host='%';" \
| grep -v root | \
while read account; do
    mysql -e "revoke all privileges on db.table from \
        '`echo $account | awk '{ print $1 }'`'@'%';"
    mysql -e "grant insert, update on db.table to \
        '`echo $account | awk '{ print $1 }'`'@'%';"
done
mysql -e "flush privileges;"

UPDATE

As I said in below comment, you can use incron to monitor /var/lib/mysql/mysql/user.MYI and run the above script whenever an user is created.

Install incron and start it:

# /etc/init.d/incrond start
Starting Filesystem event daemon (incrond):                [  OK  ]

Create incron table for mysql user:

# incrontab -l -u mysql
/var/lib/mysql/mysql/user.MYI IN_MODIFY,IN_CLOSE_WRITE /var/lib/mysql/update_privileges.sh

When you create a new MySQL user, you will see the below in /var/log/cron:

incrond[13084]: (mysql) CMD (/var/lib/mysql/update_privileges.sh)

and the new user will be automatically granted INSERT, UPDATE privileges on db.table:

+-----------------------------------------------------------------------------------------------------------+
| Grants for testuser@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'testuser'@'%' IDENTIFIED BY PASSWORD '*' |
| GRANT INSERT, UPDATE ON `db`.`table` TO 'testuser'@'%'                                                |
+-----------------------------------------------------------------------------------------------------------+

I wonder whether there is a way to get the latest MySQL user and grant privileges for this one instead of doing for all users.

3
  • I know I can do that, but I'll have to remember to do it every time we create an account. I'd prefer something automatic.
    – ceejayoz
    Sep 10, 2011 at 12:46
  • 2
    You can use incron to monitor /var/lib/mysql/mysql/user.MYI and execute the above script.
    – quanta
    Sep 13, 2011 at 6:31
  • Just add quanta's script to the script you use for account creation. No need to remember anything then.
    – user9517
    Sep 13, 2011 at 6:40
3

MySQL does not support wildcards in user names as per mysql docs and there is no way other than using any script to add additional user privileges each time you add a new user.

Actually the grant statement without any user specified will grant access to the anonymous user account and you will need to set and use the password for the anonymous user account to access the table.

If you are trying to use this in a script I would rather suggest using a separate mysql user to gather information from that table rather than granting privilege & using individual mysql user credentials.

References:

http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-table-privileges

http://dev.mysql.com/doc/refman/5.5/en/default-privileges.html

1
  • I've got a separate account for gathering, it's the inserting I'd rather be doing via wildcard permissions (as I'd prefer each app not need to use two separate db connections). Oh well. An odd omission! Thanks for your answer.
    – ceejayoz
    Sep 13, 2011 at 4:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .