Creating Users and Setting Permissions in MySQL - Reducing User Privileges

(Page 3 of 4 )

In both of the previous examples, we created users that were granted all privileges on their respective database(s). It may be wise to consider reducing these privileges, especially if the user is connecting from a remote workstation. This is accomplished with the following statement:

mysql> grant select,insert,update,delete,create,drop
-> on somedb.* to someusr@"%" identified by 'passwrd';

With this statement, the user someusr is allowed limited access to the database somedb from any host ("%") using the password passwrd.

The GRANT statement specifies that this user is only allowed to run a limited amount of statements on the MySQL server. This user will be allowed to: select records, insert records, update records, delete records, create databases, and DROP DATABASEs. More importantly, this user is not permitted to create users and set privileges.

You could further reduce a user's privileges by removing other items from the GRANT statement. The best policy here is that the user should only be given permission to access the functions that are necessary to perform their tasks.

Refresh the MySQL grant tables
After going through and setting up users and permissions with grant statements, there is one final step to making the whole thing work.

At this point it is important for you to understand where all of this permission information is kept. When MySQL is installed, a database called mysql is automatically created. This database contains several tables that hold all of the information pertaining to each user, the databases they have access to, the hosts they can connect from, and the privileges allowed for each.

MySQL only loads these user tables and the permissions held within when it first boots. It does not take another look at those tables unless it is told to do so. So, without this step, none of your newly created users will work at all.

There are several ways to reload the privilege tables, however since we are already logged in to the MySQL console, we can do so by running the following command:

mysql> flush privileges;

This tells MySQL to take another look at the user tables and hence puts all of your new users and privileges into operation.

Next: Conclusion >>
 

More MySQL Articles
More By Ryan Schwiebert

Comments On: Creating Users and Setting Permissions in MySQL

>>> Be the FIRST to comment on this article!