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.

Categorised as: Linux