/ Tutorials / By

MySQL – Creating a User

MySQL and MariaDB are probably the most popular OpenSource database management systems. With these databases, you can dynamically store and manage large amounts of data. A very fine-grained access rights system helps you controlling the access of different users.

This short article is about creating a new user and giving him rights to a database. Even if only MySQL is mentioned in this article, all commands can be transferred to MariaDB one-to-one.

Creating a user in MySQL

First start the MySQL console. Open a terminal (e.g. PuTTY or XTerm) and connect to the server with your MySQL installation. We use the user ‘root’ on your MySQL installation. If you provide the user with a password, use the following command:

$ mysql -u root -p

You will then be prompted for a password. After the input you should see the MySQL prompt. If you have not set a password, do so without the parameter ‘-p’ and you will not be asked for a password.

root@mysql:~# mysql -u root -p
Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection ID is 98
Server version: 5.5.47-0+deb8u1 (Debian)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Creating a new user is comparatively easy. Just replace the values “user” and “password” in the following command:

CREATE USER 'user'@'localhost' IDENTIFIED BY 'password';

You have created a user who does not have any permissions on any database or table yet. For test purposes create a new database, which the user may access later.

CREATE DATABASE 'myDB';

And now grant your new user all rights to this database. Replace the value “user” with your own user name:

GRANT ALL PRIVILEGES ON myDB . * TO 'user'@'localhost';

A short explanation of the commands above:

– GRANT ALL PRIVILEGES : All available privileges (rights) are assigned. There are also limited privileges, more about that later.
– ON ‘myDB’ . * : This specifies the areas to which the rights are to be assigned. Also possible ‘*. * ‘To grant a user, for example, all rights, or the restriction to individual tables of a database, e.g. ” MyDB ”. ‘My table’ ‘.
– TO ‘user’@’localhost’ : This is the user who is going to receive these rights.

MySQL usually holds the user access rights in memory, hence slow I / O operations do not have to happen every time a user logs on to the database. To update the copy of the privileges in the memory, you should run the command:

FLUSH PRIVILEGES;

once after each change. If you forget the command, it is possible that you cannot register with the new (or changed) access data to the database server yet.

More privileges in MySQL

In addition to the ‘ALL PRIVILEGES’ used above, you can also assign other sets of privileges to a user in your MySQL server. The following are, for example:

  • ALL PRIVILEGES: A wildcard for all rights to the selected database object or – with a *. * – to all databases
    CREATE: Allows a user to create new databases
    DROP: Allows a user to delete databases
    DELETE: Allows a user to delete individual rows in a table
    INSERT: Allows a user to create new rows in a table
    SELECT: Read permissions for a database or table
    FIX: Permission to update a row
    GRANT OPTION: Allows a user to set or revoke the rights of other users

The exact syntax is very well described in the documentation of MySQL. You will find it here.

Deleting a user in MySQL

You can delete a user just as easily. Use the following command:

DROP USER 'user'@'localhost';

Make sure you have reloaded the privileges after every change to the revision tables.