Create a user

You should not do all your work in MySQL with the root user; instead, it is recommended to create users with custom permissions. To create a user, the CREATE USER ‘user’@’hostname’ IDENTIFIED BY ‘password’ command is used. Here is an example:

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'verysecret';
Query OK, 0 rows affected (0.00 sec)

The command above creates a new user called testuser with the password of verysecret (localhost refers to the local computer MySQL is running on).

When the user is created, it has no permissions to do anything with the databases. The GRANT command is used to grant privileges. Here is the syntax:

GRANT PRIVILEGES ON database.object TO 'username'@'hostname' IDENTIFIED BY 'password'

The database.object keywords refer to the database itself and the objects it contains (e.g. tables). Here are some values of these keywords and their meaning:

*.* – all databases and all their objects.
database.* – the database specified and all its objects.
database.object – the database specified and its specified objects.

For example, to grant the full access to the user testuser@localhost on the testdb database, the following command can be used:

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)

You can grant only privileges that you already have, and you must have the privilege to issue the GRANT command.

We can rerun MySQL and log in as testuser to test whether the command above worked:

C:>mysql -u testuser -p
 Enter password: **********
 Welcome to the MySQL monitor. Commands end with ; or g.
 Your MySQL connection id is 22
 Server version: 5.7.11-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> SHOW databases;
 +--------------------+
 | Database |
 +--------------------+
 | information_schema |
 | testdb |
 +--------------------+
 2 rows in set (0.00 sec)
mysql>

As you can see from the output above, we’ve successfully logged in as testuser and displayed the testdb database.

Create a user

MySQL Course