Dec16

Written by:David Aldridge
12/16/2009 4:53 PM 

One of the most basic MySQL admin tasks is creating users and assigning them rights to databases.

Users can be created with the 'create user' command:

create user 'myuser'@'localhost' identified by 'mypass';
create user 'myuser'@'host.mysite.com' identified by 'mypass';
create user 'myuser'@'%' identifed by 'mypass';


In these you replace 'myuser' and 'mypass' with appropriare values.  The first form creates a local only user, the second form (with the @'%') creates a user who can login from anywhere and the third form creates a user who can login from a specific host (you replace 'host.mysite.com' with the correct address).

Once the user is created, you need to assign (grant) rights to databases.  This is done through the grant command:

grant all privileges on mydb.* to 'myuser'@'localhost' with grant option;
grant select,insert,update,delete on mydb.* to 'myuser'@'%';
grant select on mydb.* to 'myuser'@'host.mysite.com';


The first form grants all rights on the mydb database to the myuser user on the local machine.  It also allows myuser to grant rights to the database ('with grant option').  The second form grants a more reduced set of rights - select, insert, update and delete only on mydb database to the myuser user from any machine.  The final form grants just the select right on the mydb database to the myuser user from the host 'host.mysite.com'.

Once you have granted rights you need to flush them in order for them to take effect.  You can do this using the flush command:

flush privileges;

Tags:

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment  Cancel 
You must be logged in and have permission to create or edit a blog.