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

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  ''@'%';
share|improve this question
add comment

2 Answers

up vote 1 down vote accepted
+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.

share|improve this answer
    
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 '11 at 12:46
2  
You can use incron to monitor /var/lib/mysql/mysql/user.MYI and execute the above script. –  quanta Sep 13 '11 at 6:31
    
Just add quanta's script to the script you use for account creation. No need to remember anything then. –  Iain Sep 13 '11 at 6:40
    
Accepted for the info about incron. Thanks! –  ceejayoz Sep 16 '11 at 15:48
add comment

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

share|improve this answer
    
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 '11 at 4:12
add comment

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.