MySQL provides ability to create and users from its shell. Security is important part of the database servers. In this tutorial we will examine how to create users and manage privileges in MySQL database server. We will specifically learn restrict user access to a database, database server, grant different type of privileges, print these privileges.

Login To MySQL Shell

In order to create user and manage privileges of this user we will login to the MySQL shell. MySQL shell provides ability to access databases, run SQL queries, administrative operations like create user, delete user, manage user privileges. We will use mysql command with the user name which is root in this case. We will provide the root user password too.

$ sudo mysql -u root -p

Create New Database User

We will use CREATE USER command by providing the user name, host name and password. Host name is used to set whether the user can connect from given host. In this example we specify the localhost which means given user  ismail can only connect from localhost to this database server. We also provide the password as SoS3cret..!

> CREATE USER 'ismail'@'localhost' IDENTIFIED BY 'SoS3cret..!';

As it is successfully created Query OK, 0 rows affected (0.01 sec)  is printed.

Remove Existing Database User

If we do not need given database user we have to remove user because it may create security problems and unintended database access. We can  remove existing database user with DROP USER command by providing the user name and access host name. In this example we will remove user ismail with localhost access.

> DROP USER ismail@localhost;

List Existing Database Users

After creating a database user we may want to check and list existing users in MySQL database. In this case we will use an SQL statement which will list content of the mysql database user table. user table in the mysql database holds database server users and related information like password etc. We will use SELECT User statement.

> SELECT User FROM mysql.user;

Create New User with To Access Specific Database

We can specify a user only access to the specified databases. This will prevent the user access to other databases. We will use GRANT ALL PRIVILEGES command in this case by providing the database name, user name and host name. In the following example we will grant all privileges of database poftut to the user ismail .

> GRANT ALL PRIVILEGES ON poftut . * TO 'ismail'@'localhost';

Write Changes To Database

Changes may be stored in cache which will not effect immediately. We can write these changes into the database server explicitly with the following command.

> FLUSH PRIVILEGES;

Create New User with Access Location

We can also specify the access location for the given user and database. Up to now we have used the localhost which is a secure way where only local users can access. We can also provide access rights for specific network or host. In this example we will provide access from IP Address 192.168.1.10 .

> GRANT ALL PRIVILEGES ON poftut . * TO 'ismail'@'192.168.1.10';

Show and List Given User Privileges

We may want to list granted privileges for the given user. We will use SHOW GRANTS FOR command for this operation. In this example we will list granted privileges for user ismail access from localhost.

> SHOW GRANTS FOR ismail@localhost;