Select Page

Metal storage cubes
[toc]This tutorial explains how to create new MySQL database users using SQL commands. As well as creating the users you’ll also learn how to restrict what they can do in specific databases.

Please note:All the commands shown in this tutorial are SQL commands. They will only work if you enter them into the MySQL command line client or the SQL command panel of a tool such as PHPMyAdmin or MySQL Workbench.

How mySQL controls access to its databases

A mySQL database is made up of tables, each of which can store rows of data. mySQL enables you to control which users can create tables, insert new data into tables and update the data that already exists in the tables on a table by table basis. In other words, you could create a user that can only view the data in one particular table of a database and another user that can do anything to all the tables in the database. The level of access that a user has is defined by their privileges.

The root user

Every mySQL database has a master user called the “root” user. This user will usually be able to do anything to every database in the mySQL installation. This means that you need to be really careful about who you give the root user’s details to. It is much better to create specific users to work with each database rather than using the root user – this ‘compartmentalises’ the risk to each database. If a user is compromised, the other databases won’t be at risk. If the user root is compromised – all the databases in the system will be at risk!

How to create a new MySQL user

Okay, you’re going to need to decide on a username and password for the new database user.
I’m going to call mine haddock and the password will be fishnet.

CREATE USER 'haddock'@'localhost' IDENTIFIED BY 'fishnet';

This will have created a new user called haddock on the local computer system (localhost) but it won’t have associated it with the new database. We need to grant permissions to the new user to let it access the new database.

How to allow the user to work with a database

There’s a whole bunch of permissions called privileges that you could grant to the user that allow it to do everything from selecting data from specific tables in the database to creating or deleting data. How much you grant depends on how much you trust the user. 🙂

In this case, for simplicity, I’m going to let my new user do everything with the new database. I’m going to GRANT ALL permissions to the new user within the database.

GRANT ALL PRIVILEGES ON fish.* TO 'haddock'@'localhost';
FLUSH PRIVILEGES;

In the example above, I’ve granted all privileges on every table (fish.*) in the fish database to the user called haddock.

The second line, FLUSH PRIVILEGES tells mySQL to make your changes permanent by reloading the privileges available to each user in the database.

How to find out what privileges a user has

Okay – we’ve created a user called Bob and given him access to tables in two different databases. So, how do you check what privileges Bob actually has? Fortunately, that’s also really easy to with the SHOW GRANTS command.

SHOW GRANTS FOR bob@localhost.com;

Here’s what the output looks like:

SHOW GRANTS

A simple example – Bob’s Databases

database_privileges

Bob has Select, Insert and Delete privileges for the marine table in the fish database but only has Select privileges for the cakes table in the Bakery database.

In the image here you can see that Bob can access two databases; Fish and Bakery. Each database only has one table.

He can insert new rows into the marine table of the fish database, select data from the marine table and delete rows in the marine table. He can only select data from the cake table in the bakery database however.

So how do we go about setting up a situation like this?

If you want to create the databases, you can use my code sample. Just copy and paste it from the expanding box below. I’ll be explaining more about creating tables in my next article so don’t worry too much about what the code means. If you can’t wait, there’s always Google. 😉

[toggle title=”Sample database SQL script”]

CREATE DATABASE `bakery` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `bakery`;
CREATE TABLE IF NOT EXISTS `cakes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name_of_cake` varchar(100) NOT NULL,
  `ingredients` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_of_cake` (`name_of_cake`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

CREATE DATABASE `fish` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `fish`;
CREATE TABLE IF NOT EXISTS `marine` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name_of_fish` varchar(100) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_of_fish` (`name_of_fish`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

[/toggle]

 

Now that we have the databases and tables set up, we need to create a user for Bob to use to access the databases. Let’s create that now. Bob’s password is going to be blueshirt. Not a very secure password, I know.

CREATE USER 'bob'@'localhost' IDENTIFIED BY 'blueshirt';

Right now, Bob can’t connect to any databases. He can log in but he won’t be able to do much.
Let’s give him the ability to access the marine table in the fish database.

GRANT SELECT,INSERT,DELETE ON fish.marine TO 'bob'@'localhost';
FLUSH PRIVILEGES;

Ok, Bob should now be able to select data from the marine table in the fish database, insert new data into it and delete existing data from it too. Don’t forget fish.marine means the marine table in the fish database. If we wanted to give Bob access to all the tables we could have used fish.* instead.

Now, let’s add the ability to just select data from the cakes table in the bakery database.

GRANT SELECT ON bakery.cakes TO 'bob'@'localhost';
FLUSH PRIVILEGES;

And that’s it. We’ve allowed Bob to have two different types of access to two completely different databases using the mySQL command line interface. How cool is that?

[note class=”warn”]Never use your root user for web applications such as WordPress or Joomla.
Create specific users for each database. That way, if someone gets hold of your database user details, they can’t harm the rest of the database.[/note]