Home / server / Questions / 309849
Accepted
ceejayoz
Asked: 2011-09-10 10:59:19 +0800 CST

allowing any valid MySQL user access to a particular table

  • 772

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  ''@'%';
mysql

2 Answers

  1. 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

    • 3
  2. Best Answer

    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.

    • 1