How To Create a New User and Grant Permissions in MariaDB

new user permissions mariadb

Learn how to create a new user and grant permissions in MariaDB through this article! MariaDB is a popular open-source relational database management system.

Summary

Creating A New User

  1. To create a new user in MariaDB, you will first need to login to MariaDB using the following command:
    mysql -u root -p
  2. Upon successfully logging in with your password, the following screen greets you:
    Welcome to the MariaDB monitor. Commands end with ; or \g.
    Your MariaDB connection id is 24
    Server version: 10.3.28-MariaDB MariaDB Server
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MariaDB [(none)]>
  3. Execute the following command to see the list of databases within MariaDB:
    show databases;
  4. Next, use the following command to list all users currently in MariaDB:
    SELECT User FROM mysql.user;
  5. Now, use the following command, replace “user” with the desired user, to see the permissions that have been granted:
    show grants for 'user'@'localhost';
  6. To create a new user “newuser” with password “password”, execute the following command:
    CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
  7. Finally, if you wish to remove this “newuser” from MariaDB, use the following command:
    DROP USER 'newuser'@'localhost';

Granting User Permissions

Here is a list of common permissions that can be granted to any users:

  • ALL PRIVILEGES- this would allow a MySQL user full access to a designated database (if no database is selected, global access across the system)
  • CREATE- allows them to create new tables or databases
  • DROP- allows them to them to delete tables or databases
  • DELETE- allows them to delete rows from tables
  • INSERT- allows them to insert rows into tables
  • SELECT- allows them to use the SELECT command to read through databases
  • UPDATE- allow them to update table rows
  • GRANT OPTION- allows them to grant or remove other users’ privileges

You may use the following command to grant the type of permission above (replace with “permission” below) on any particular database and table, to your desired database user (replace “username” below):

GRANT permission ON database_name.table_name TO 'username'@'localhost';

To review a user’s permission already granted, use the following command:

SHOW GRANTS FOR 'username'@'localhost';

If you wish to revoke the user’s permission, use the following command:

REVOKE permission ON database_name.table_name FROM 'username'@'localhost';

Whenever you are done, log out of MariaDB using:

quit

Conclusions

Since MariaDB is a fork of MySQL, you can create new user and grant permissions using the same commands above. Finally, if your MariaDB or MySQL database deals with large transactions, be sure to check out NVMe based VPS for better speed and performance!

Related Post