Home arrow MySQL arrow Page 3 - Creating Users and Setting Permissions in MySQL

Creating Users and Setting Permissions in MySQL

MySQL is an extremely powerful database, but in order to properly maximize its potential you may need to learn how to set up users and their respective permissions. In this article Ryan takes a look at using the MySQL console application to create users and grant permissions to them. He also shows us how to grant permissions from remote hosts, how to reduce user privledges and more.

Author Info:
By: Ryan Schwiebert
Rating: 4 stars4 stars4 stars4 stars4 stars / 126
May 21, 2002
  1. · Creating Users and Setting Permissions in MySQL
  2. · Opening the MySQL console application
  3. · Reducing User Privileges
  4. · Conclusion

print this article

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.
blog comments powered by Disqus

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2019 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials