Loading...
X

How to create a new MySQL (MariaDB) user and configure its status and privileges

This note will show how to create a new MySQL (MariaDB) user, as well as configure its privileges (permissions) at the DBMS level as a whole, at the level of a particular database, and even at the level of individual tables and columns.

Why create a new MySQL or MariaDB user

Using root to perform Data Management Language (DML) operations on a database is a bad idea.

Having dedicated, separate users for each database will prevent harm to all databases from compromising one account. This means that if, for example, one of the websites is hacked (let’s say, when an SQL injection vulnerability is found in it), then when using one account on all websites, the attacker will have access to all databases.

If each website uses a different user to work with the database, then in the event of a vulnerability in one website, other databases (and the websites that use them) will be safe.

In a situation where different users work with databases, using the same account, or incorrectly setting access rights (permissions), will result in users being able to read or even modify other users' databases.

To avoid this, we can create a new MySQL (MariaDB) user account.

At the same time, setting privileges and maintaining them after deleting and recreating databases and tables in MySQL is sometimes counter-intuitive. Given this and the importance of access rights issues, it is recommended to study this topic in more depth – this is what this article is devoted to.

All subsequent queries are made in the MySQL (MariaDB) console. To connect to the MySQL (MariaDB) server, use the appropriate clients:

mysql -u root -p

Or:

mariadb -u root -p

How to create a new MySQL or MariaDB user

To create a new user, use the following command:

CREATE USER USERNAME@localhost IDENTIFIED BY 'USERS-PASSWORD';

For example, the following command will create a user named “mial” with password “HackWare.ru”.

CREATE USER mial@localhost IDENTIFIED BY 'HackWare.ru';

Note: MySQL and MariaDB support several password hashing algorithms, as well as two- and three-factor authentication. You can set the password hashing algorithm and multi-factor authentication when creating a user. See the link for details: https://dev.mysql.com/doc/refman/8.1/en/create-user.html

Why is MySQL (MariaDB) user unable to create and view databases

By default, a newly created user does not have any privileges to create or even view databases.

Note: The exceptions are the “information_schema” and “test” databases, which are allowed to be accessed by any user.

When creating a user, you can select one of the predefined user roles. In this article, we will not touch on this issue, just remember: if you create users in bulk and configure them with the same type of privileges (access rights), then you can improve performance using user roles.

In order for a user to perform any operations, including creating databases and viewing the contents of databases and tables, you must grant him the appropriate privileges.

See also: ERROR 1044 (42000): Access denied for user ‘mial’@’localhost’ to database ‘TestDB’. Can’t create MySQL database (SOLVED)

Important information about user privileges in MySQL and MariaDB.

This section briefly lists what you need to know about user privileges in MySQL (MariaDB).

1. Access rights (permissions) in MySQL (MariaDB) are called “privileges”.

2. For each action (for example, create, delete, update, etc.) there are separate privileges, each of which can be granted to the user (and also later revoked).

3. Privileges can be configured at different levels: global, database level, table level, and table column level.

4. If a user has been granted the privilege to create a certain (or any) database, then after creating this database, the user will not have rights, for example, to view, insert, update, and other actions with records within this database. This is counter-intuitive, but the fact that a user has created a database does not mean that he has full access to it. The user has only those privileges on the database that have been explicitly granted by the administrator.

5. When deleting a database (or another unit for which a privilege is granted: tables or columns), the privileges granted to the user do not disappear! This is not only counter-intuitive, but also different from some other SQL DBMS. So, the privileges granted to the user are in no way tied to the existence of databases and tables (as well as columns) to which they are issued. It is possible to issue privileges on non-existent databases and tables – in this case, the user will be able to exercise his privileges after the creation of these units.

See also: How to list MySQL/MariaDB users and their privileges (using RDBMS client and PHP)

How to allow a user to create databases

The query to allow the user _USERNAME_ to create any databases looks like this:

GRANT CREATE ON *.* TO '_USERNAME_'@'localhost';

But as you already learned a little higher, if the user creates databases and tables inside them, then he will not be able to insert data there, or even view their contents.

Therefore, it may be an idea to grant the user all the privileges at once. This is done in the following way:

GRANT ALL PRIVILEGES ON *.* TO '_USERNAME_'@'localhost';

But granting all privileges to a user on all databases is not always a good idea. For example, if you use different MySQL / MariaDB accounts for different sites in order to prevent all databases from being compromised (in case one site is hacked), then this will not work when giving users all privileges.

You can grant all privileges on one particular database. If you want to give a user all possible database privileges, then use a query like this (replace _TestDB_ with the name of your database and replace _USERNAME_ with the username):

GRANT ALL PRIVILEGES ON _TestDB_.* TO '_USERNAME_'@'localhost';

For fine-grained configuration of user privileges, see the following articles:


Leave Your Observation

Your email address will not be published. Required fields are marked *