Loading...
X

How to allow a MySQL (MariaDB) user to create new users and grant privileges to manage databases and their contents to other users

By default, the right (privilege) to create new users, as well as grant them rights to databases, is granted only to root.

The root user can do all kinds of operations on databases and users. If you want to grant some administrative privileges to another user, then you can do so.

How to create a MySQL (MariaDB) administrative user

You need to know the following:

1. The user can transfer to other users only those privileges that are granted to him.

2. Privileges and rights for their transfer can be configured in great detail. For example, you can create users who will have all possible rights to the specified database (for example, create, delete, insert data, etc.), but can only transfer privileges to view and insert new data into the table.

3. You can give a normal user the privilege to create other users.

4. The privilege to create users also includes the privilege to delete users, rename them, and revoke all privileges at once, regardless of who created those users.

5. The right to create users does not include the right to give new users any privileges – these permissions must be configured separately.

6. All user privileges are lost (removed) when the user is deleted.

How to allow a user to create other MySQL (MariaDB) users

Create a new user (replace “_USERNAME_” with the username and “_PASSWORD_” with its password):

CREATE USER _USERNAME_@localhost IDENTIFIED BY '_PASSWORD_';

Use the following query to grant the user _USERNAME_ the privilege to create other users.

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

In this query, “*.*” means “global privilege level”.

Note that, given the privilege to create users, _USERNAME_ also receives the following privileges on all users, regardless of whether they were created by him or root:

CREATE USER

DROP USER

RENAME USER

REVOKE ALL PRIVILEGES

Also note that if you want _USERNAME_ to have the ability to assign privileges to databases after creating a new user, then _USERNAME_ must first receive these privileges, and when receiving them, the transfer right “WITH GRANT OPTION” must be indicated.

By the way, a user who has received the “CREATE USER” privilege can delete not only any other user, but also himself.

For the granted global privilege to create a new user to take effect, the user must log out and reconnect to the MySQL (MariaDB) server.

How to allow a user to transfer privileges to manage databases

Now configure in detail the rights of the new user to access and manage databases, the articles will help you with this:

To allow a user to grant a privilege that you assign to them, append “WITH GRANT OPTION” to the request. For example, this query gives all possible privileges on the _TestDB_ database to the user _USERNAME_ with the right to transfer to any other user:

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

Leave Your Observation

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