MySQL create user account and grant privileges

MySQL can be said to be one of the leading and widely used open source database management systems. It is the first choice of relational database management system in most web hosting companies and other cloud providers.

In this tutorial, we will study how to create a MySQL user account and grant permissions to allow them to access and manage the created database.

prerequisites

Before starting, this tutorial assumes that you have installed a MySQL database or MariaDB instance, which is a fork of MySQL on your system. We already know how to install MySQL 8 on Ubuntu, so take a look.

Access MySQL Shell

Once MySQL or MariaDB is installed and secured, you can access the shell by running the following command

$ mysql -u root -p

After that, you will be prompted for the root password, and after providing the password, press Enter to access the shell.

    Login to mysql

How to create a user in MySQL

To add a user in MYSQL, use the mysql create user command as shown below

CREATE USER 'linoxide_user'@'localhost' IDENTIFIED BY '[email protected]';

Create MySQL user account

MySQL users include 2 parts: username with hostname. In the above command, the username is linoxide_user And the hostname is localhost

The host name part indicates the user linoxide_user You can only connect to MySQL locally (ie from the server hosting MySQL).

To grant access from an external host system, replace localhost With the IP address of the remote host.

For example, to grant access from a host system with IP ‘172.16.10.10’, run:

CREATE USER 'linoxide_user'@'172.16.10.10' IDENTIFIED BY '[email protected]';

Create MySQL user account

To grant access from any host system, use ‘%’ as a wildcard.

CREATE USER 'linoxide_user'@'%' IDENTIFIED BY '[email protected]';

Granting permissions to MySQL users

When creating a user, you may want to assign certain privileges to the database engine. These privileges grant users access to the database and enable them to make some modifications to the database.

In MySQL, users can be assigned many privileges. However, in this guide, we clarify the most commonly used privileges:

select: This allows users to read the entire database

Update: This allows users to update rows in a table

insert: Allow users to insert rows in a given table

delete: Grant usage rights to delete rows in a given table

create: Allow users to create databases and tables

decline: Grant users permission to delete entire databases and tables

To grant users some privileges to access the database, use the following syntax:

GRANT privilege1, privilege2 ON database_name.* TO 'database_user'@'localhost';

E.g:

GRANT SELECT, INSERT, ON database_name.* TO [email protected]'localhost';

Grant partial privileges

To grant all privileges to a specific user on a specific table in the database, issue the following command:

GRANT ALL PRIVILEGES ON database_name.table_name TO 'database_user'@'localhost';

Create new MySQL user

To grant the user all privileges for all databases, run the following command:

GRANT ALL PRIVILEGES ON database_name.* TO 'database_user'@'localhost

Grant all privileges to all users

Display user account permissions

After a user is created and assigned privileges in the database, you can easily check and confirm the privileges granted to the user using the following command: SHOW GRANTS The command is shown below:

SHOW GRANTS FOR 'database_user'@'localhost';

Show user permissions

Revoke privileges for MySQL users

In addition, you can choose to revoke one or more privileges of the MySQL user. The syntax is almost similar to that when granting user privileges.

REVOKE ALL PRIVILEGES ON database_name.* FROM 'database_user'@'localhost';

Revoke user permissions

Delete existing MySQL user

To delete or delete a MySQL user, use DROP The command looks like this:

DROP USER 'database_user'@'localhost';

E.g:

Delete a MySQL user

in conclusion

This tutorial introduces the basic concepts of creating and managing MySQL users by assigning and revoking privileges. For starters, this should be a good starting point. We hope you can easily create MySQL user accounts and manage privileges and users.

Sidebar