Granting rights to multiple databases on MySQL


To grant a user rights to perform actions on several databases with similar names we can use a pattern and with a single command perform all the needed operations. Suppose you have a DBA server with multiple databases and you want to grant rights to a single user on databases ending with _openfire. To achieve this result you would use a command similar to:
grant select on `%_openfire`.* to ‘user1’@’%’ identified by ‘”12wqasxzedc3;
 
select – the permissions granted to the user
% – is used to specify for any value that precedes _openfire
‘user1’@’%’ – specifies that for user1, the permissions for %_openfire will be propagated to all tables within those databases (specified with @)
identified by – set the password for that username
To visualize the databases, use the following command:
show databases;
Now we can view the newly added permissions, note that mysql automatically creates hashes from passwords so you will not be able to see them:
mysql> show grants for ‘user1‘@’%’;
+——————————————————————————————————-+
| Grants for user1@%                                                                                   |
+——————————————————————————————————-+
| GRANT USAGE ON *.* TO ‘user1‘@’%’ IDENTIFIED BY PASSWORD ‘*F4A2073C8F87C6D1CB45E206228335EE95D6E7A2’ |
| GRANT SELECT ON `%_openfire`.* TO ‘user1‘@’%’                                                        |
+——————————————————————————————————-+
MySQL stores the the results of GRANT and CREATE USER statements in memory. To release the cached memory you need to use the flush privileges; command after using the grant statement. If you don’t run this command after you’ve set the permissions, they will be flushed once the server is rebooted.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s