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.