MySQL provides the ability to create and users from its shell. Security is an 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 to restrict user access to a database, database server, grant different types of privileges, print these privileges.
Login To MySQL Shell
In order to create a user and manage the privileges of this user, we will log in to the MySQL shell. MySQL shell provides the 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, hostname, and password. The hostname is used to set whether the user can connect from a 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 a given database user we have to remove users because it may create security problems and unintended database access. We can remove the existing database user with the DROP USER command by providing the user name and access hostname. 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 the MySQL database. In this case, we will use an SQL statement that will list the 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 user access to other databases. We will use GRANT ALL PRIVILEGES command in this case by providing the database name, user name, and hostname. In the following example, we will grant all privileges of the database poftut
to the user ismail
.
> GRANT ALL PRIVILEGES ON poftut . * TO 'ismail'@'localhost';

Write Changes To Database
Changes may be stored in the cache which will not effective 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 networks or hosts. In this example, we will provide access from the 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 the user ismail
access from localhost
.
> SHOW GRANTS FOR ismail@localhost;

1 thought on “How To Create New User and Set, Grant Permissions To User In MySQL and MariaDB?”