How to Create User With Grant Privileges in MariaDB

In database management, effective user management and privilege assignment are important for secure and efficient operations. MariaDB, a popular open-source relational database management system, offers robust features for managing users and their access rights.

In this article, we will understand how to create users and grant privileges to them in MariaDB with the help of various examples.

How to Create User and Grant Privileges?

To manage users in MariaDB, we need to understand how to create users and grant them appropriate privileges. Let’s understand step-by-step instructions and examples.

• Connect to MariaDB
• Create a User
• Grant Permissions
• Reload Privileges
• Remove the MariaDB User Account

Connect to MariaDB

Use the MariaDB client or command–line interface to connect to the MariaDB server. Let’s Enter the password and access the MariaDB client.

mysql -u root -p

Output:

Connect to MariaDB

Create a User

Once we are connected to the MariaDB server, use the CREATE USER statement to create a new user. Replace the username and password with the desired username and password for the new user.

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Once we create user, check its status by entering:

SELECT User FROM mysql.user;

Example: Suppose we create a user named minal along with their password. Below are the syntax:

CREATE USER 'minal'@'localhost' IDENTIFIED BY '******';

SELECT User FROM mysql.user;

Output:

User Created

Explanation: As we can see in the output image the user named minal is created. Also Please use your password in place of ****.

Grant Permissions

Use the GRANT statement to grant privileges to the user. Specify the privileges and the database or tables they apply to. Replace username and database_name with appropriate values.

1. To grant privileges only for our database.

GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost';

Explanation:

• SELECT, INSERT, UPDATE, etc: Replace these with the privileges we want to grant.
• database_name: Replace this with the name of the database. we can also specify a table instead of * to grant privileges for specific tables.

Example: Suppose we are granting select and insert operations to the user minal.

GRANT SELECT, INSERT ON mydb.* TO 'minal'@'localhost';

2. To grant all privileges to user.

GRANT ALL PRIVILEGES ON *.* TO 'username'@localhost IDENTIFIED BY 'userpassword';

The database or table that the user has access to is denoted by the *.* in the sentence. This particular command gives access to every database on the server. we should change the symbol to the name of the database we are granting access to, as this might be a serious security risk.

Replace the username and userpassword with the desired username and password of the new user. By using this syntax we can provide all the access to all databases or table stores on the MariaDB server to the user identified by the password.

Reload Privileges

After granting privileges, we need to reload the privileges for the changes to take effect. we have to enter the following command each time we run a command on the MariaDB server.

FLUSH PRIVILEGES;

Once we have completed this step, we can verify the new user has the right permissions by using the following statement:

SHOW GRANTS FOR 'username'@localhost;

Example: Let’s check the grants for minal user.

SHOW GRANTS FOR 'minal'@localhost;

Output:

Permission Granted

Explanation: we can see below that the select and insert permission is only granted for user minal.

Remove MariaDB User Account

If we need to remove a user, we can use the DROP statement:

DROP USER 'username'@localhost;

The output confirms that user no longer has access nor privileges.

Example:

Drop the user minal.

DROP USER 'minal'@localhost;

Output:

Account Dropped

Best Practices

• Use Strong Passwords: Encourage users to use strong, unique passwords to additionally boost security.
• Grant Minimal Privileges: Attempt to limit the privileges for each user to the minimum. Do not grant excessive privileges to limit the amount of security risks.
• Regularly Review Privileges: Regularly revise user privileges according to the modifications of the requirements.
• Limit Remote Access: At the same time, filter the remote access to specific IP addresses or networks by the most effective security rules.

Conclusion

Overall, Assigning rights and privileges for your users also plays a big role in protecting the security and safety of your MariaDB databases. By implementing what you have read in this article and keeping in mind the right practices, you will offer users necessary access while also protecting the sensitive data from external users. Frequently monitoring and updating user privileges is a necessary step to keep the security environment healthy through changes in security requirements and consistent database environment.