allowing any valid MySQL user access to a particular table

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.

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

allowing any valid MySQL user access to a particular table

Related

Recent Posts