MySQL - creating and editing users

Adding MySQL users follows the same principles as adding system users. Each user can be assigned different privileges to different databases.

Privileges can range from read only for a single database to full MySQL wide administrator rights.

You need administrator rights to create a new MySQL user so log into MySQL as the root user:

mysql -u root -p

Create

Let's start off by creating a new MySQL user called 'paul':

CREATE USER 'paul'@'localhost' IDENTIFIED BY 'newpassword';
FLUSH PRIVILEGES;

As before, the syntax is straightforward, we created the user 'paul' who can access MySQL locally and assigned a password.

We 'flushed' the privileges. Cool. This simply reloaded the 'user' table in the mysql database so MySQL can use the new user details. Do this each time you change users and privilege levels.

Grant

We can now assign some privileges to paul. I'd like him to be able to read all the databases but not be able to add or drop data:

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

So now paul can select data from all the databases but we also want paul to have admin privileges over the pickled1 database:

GRANT ALL PRIVILEGES ON `pickled1` . * TO 'paul'@'localhost';

Paul now has full privileges on the pickled1 database and select only on the others.

Log in

OK. Lets test this new user. Log out of MySQL:

quit

And then log in using the user 'paul':

mysql -u paul -p

Enter the new password and then enter the command:

SHOW databases;

As we gave paul 'select' privileges on all databases, you'll see a list of available ones.

Drop

We know that of the databases listed, the one called 'mysql' holds the users details and is, in fact, the most important database there. Without it things would be very messy indeed.

Let's go ahead and drop it.....

What?

You are now logged in as the user 'paul'. We gave paul all privileges on the pickled1 database but select only privileges on the other databases.

Firstly, make sure you are logged in as paul and have assigned privileges as above.

Secondly this does a couple of things: It puts some trust in the programme and, more importantly, it builds confidence in what you are doing. You know you have assigned select only privileges on the other databases so now is the time to put it to the test:

DROP database mysql;

This is what happened when I did it:

ERROR 1044 (42000): Access denied for user 'paul'@'localhost' to database 'mysql'

Different privileges give different permissions.

Levels

Now we've got that out of the way, we can briefly look at the different levels of privileges available. I'm not going to go through them all but the basics you need are:

For data manipulation:

SELECT........read only
INSERT........insert rows/data
UPDATE.......change inserted rows/data
DELETE.......delete drop rows of data

For table manipulation:

CREATE......create new tables
ALTER........change table/column names
DROP.........drop columns/tables

Examples

Remember that privileges can only be assigned by the root user (well, at this point anyway) so make sure you are logged in as the root user when granting privileges.

1: You have a user (paul) who you want to be able to access all data. He will enter data, correct errors and so on. But you do not want paul to be able to alter the database structure:

GRANT SELECT , INSERT , UPDATE , DELETE ON `pickled1` . * TO 'paul'@'localhost';

2: You have another user (called, err, paul) who you want to be able to alter the structure of the table:

GRANT CREATE , DROP , INDEX , ALTER ON `pickled1` . * TO 'paul'@'localhost';

And so on. Assign privileges as needed.

Remove

Lastly, we want to be able to remove a user when they become redundant (in all senses of the word):

DROP USER 'paul'@'localhost';

Quite a lot going on in this article, but I hope you can see how easy it is to fine tune your MySQL users so they can't cause havoc within your MySQL database.

PickledOnion.

Digg it | del.icio.us | reddit | StumbleUpon