MySQL - creating and editing users

Creating users for MySQL runs along the same lines as adding users on the Slice itself - you create a user with a password and assign permissions to different databases as needed.

As with the other MySQL commands, you will see it is actually quite easy to do this from the command line.


Login

First thing is to log into your Slice via the terminal or PuTTY, etc and then log into MySQL:

mysql -u root -p

You will be prompted for your MySQL root password (note this is not the same as the Slice root password).

New user

Let's jump straight in and create a new user. In this example the username will be 'pickled'. We'll also set a password for the new user:

CREATE USER 'pickled'@'localhost' IDENTIFIED BY 'newpassword';

Next we need to flush the privileges which reloads the 'user' table in MySQL - do this each time you add or edit users.

FLUSH PRIVILEGES;

Done.

Permissions - select

At this stage, our new user ('pickled') can't do anything as he has no permissions set.

We can start by assigning 'select' permissions on all the available databases. This will allow him to read them but not edit or delete.

Of course, this is just an example of how to set permissions - you may not want a user to have select permissions on all the databases. Please adjust for your needs.

GRANT SELECT ON * . * TO 'pickled'@'localhost';

Permissions - all

Let's create a new database and allow 'pickled' to have full access to it. When done he will be able to create, read, update and delete records as needed.

This is the type of permission set that could be used when setting a user and database for a web application. There would be no need to have the user access any other database.

CREATE DATABASE mytestdb;

Now we have the database and the user, we can assign the privileges:

GRANT ALL PRIVILEGES ON `mytestdb` . * TO 'pickled'@'localhost';

Note the backticks (`) surrounding the database name.

Flush the privileges:

FLUSH PRIVILEGES;

Log in as the new user

Logging into MySQL as the new user takes exactly the same format as when we logged in earlier:

mysql -u pickled -p

You will be prompted for the 'pickled' user password.

Once logged in, we can try to create a new database:

CREATE DATABASE mytestdb2;

You will get an error like this:

ERROR 1044 (42000): Access denied for user 'pickled'@'localhost' to database 'mytestdb2'

Which is good news as we granted 'select' privileges to everything and 'all' privileges on the 'mytestdb' database only.

Looks like everything is working very well.

Dropping a user

There may come a point where we have to part ways with 'pickled'. In a similar manner to dropping databases, we can simply 'drop' the user.

You will need to be logged into MySQL as the root user for this:

DROP USER 'pickled'@'localhost';

Gone.

The next article will look at the different levels of permissions we can assign - some are for data manipulation only (adding records, etc) and some are for tables manipulation (adding new tables to the database, etc).

Summary

Adding users to MySQL and assigning permissions is very simple from the command line. Restricting permission levels to a single database helps increase security.

PickledOnion

Article Comments:

satheesh commented Wed Jun 16 22:27:01 UTC 2010:

Hi sir, Plz explain to me,how to assign particular database to particular user by using PhpMyAdmin...

Thanking You sir

Jered commented Thu Jun 17 04:13:16 UTC 2010:

I haven't used PHPMyAdmin myself, but you can check their documentation page and see if you can find instructions on controlling user privileges in either the official docs or in the third-party tutorials they link there. Otherwise, the instructions in this article will let you control user privileges with regards to databases (it just means using the SQL commands instead of the nicer web interface).

Drop it like its Hawt commented Wed Aug 17 18:35:05 UTC 2011:

if i drop the user, will it also drop the entire database tables? Or Just the user? Can i assign a new user to an existing database?tia

Jered commented Sun Aug 21 18:19:11 UTC 2011:

Dropping a user will just remove the user, not the tables it's had permissions for. That means that yes, you can drop a user that has access to a database, then create a new user to take its place once you've granted the new one the proper permissions.

howtogetwebdesignbristol commented Fri Jan 11 13:11:42 UTC 2013:

If you are going for finest contents like myself, just pay a quick visit this website every day because it provides quality contents, thanks

http://www.eaglerocks.com commented Tue May 07 00:38:06 UTC 2013:

Hey there I am so glad I found your blog page, I really found you by accident, while I was researching on Digg for something else, Regardless I am here now and would just like to say thanks for a incredible post and a all round entertaining blog (I also love the theme/design), I don't have time to browse it all at the moment but I have book-marked it and also added your RSS feeds, so when I have time I will be back to read a great deal more, Please do keep up the excellent job.

frebet commented Tue May 07 05:14:44 UTC 2013:

A person essentially assist to make critically articles I might state. This is the first time I frequented your web page and thus far? I amazed with the research you made to make this particular submit incredible. Wonderful activity!

meuble salle de bain commented Wed May 15 21:58:43 UTC 2013:

Way cool! Some very valid points! I appreciate you writing this article and the rest of the site is extremely good.

pas cher commented Wed May 15 22:01:20 UTC 2013:

Hi would you mind stating which blog platform you're using? I'm planning to start my own blog in the near future but I'm having a difficult time making a decision between BlogEngine/Wordpress/B2evolution and Drupal. The reason I ask is because your design seems different then most blogs and I'm looking for something unique. P.S Sorry for being off-topic but I had to ask!

voyance commented Sat Jul 13 20:16:31 UTC 2013:

obviously like your web site however you need to check the spelling on several of your posts. Many of them are rife with spelling problems and I find it very troublesome to inform the truth nevertheless I'll definitely come again again.

solar chargers for iphone commented Fri Aug 02 17:28:22 UTC 2013:

Good post! It's also my biggest earner. Nevertheless, it's not as much.

Cheers!

Want to comment?


(not made public)

(optional)

(use plain text or Markdown syntax)