How to create a new MariaDB user account

Home > Search > How-to
  by

Type mysql -u root -p and press enter to connect to MariaDB. Type the password entered during the mysql_secure_installation.

[root@server1 ~]# mysql -u root -p
Enter password:

 

Use the following command to view the current users. In this example, there are 3 user accounts for root.

MariaDB [(none)]> select user,host from mysql.user;
+------------------+
| user | host      |
+------------------+
| root | 127.0.0.1 |
| root | ::1       |
| root | localhost |
+------------------+
3 rows in set (0.00 sec)

 

Type create user 'username'@'hostname' identified by 'password'; command to create a new MariaDB user account.  In this example, the username is John and the password is Password123.

MariaDB [(none)]> create user 'John'@'localhost' identified by 'Password123';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> create user 'John'@'%' identified by 'Password123';
Query OK, 0 rows affected (0.00 sec)

 

The newly added accounts will be displayed. However, John does not have the priviledge to select, insert, or update records.

MariaDB [(none)]> select user,host,Select_priv,Insert_priv,Update_priv from mysql.user;
+------------------------------------------------------------+
| user | host      | Select_priv | Insert_priv | Update_priv |
+------------------------------------------------------------+
| root | 127.0.0.1 | Y           | Y           | Y           |
| root | ::1       | Y           | Y           | Y           |
| root | localhost | Y           | Y           | Y           |
| John | localhost | N           | N           | N           |
| John | %         | N           | N           | N           |
+------------------------------------------------------------+
5 rows in set (0.00 sec)

 

Give John select, insert and update privileges.

Using the *, we can grant an account access to any of our MySQL databases.  On the other hand, we can specify the databases an account is allowed to access.  As an example, instead of using *.* in the grant command, if we used mydb.*, then the public account would only be able to access the mydb database. 

MariaDB [(none)]> grant select, insert, update on *.* to 'public'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select, insert, update on *.* to 'public'@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

 

John will now have priviledge to select, insert, and update.

MariaDB [(none)]> select user,host,Select_priv,Insert_priv,Update_priv from mysql.user;
+------------------------------------------------------------+
| user | host      | Select_priv | Insert_priv | Update_priv |
+------------------------------------------------------------+
| root | 127.0.0.1 | Y           | Y           | Y           |
| root | ::1       | Y           | Y           | Y           |
| root | localhost | Y           | Y           | Y           |
| John | localhost | Y           | Y           | Y           |
| John | %         | Y           | Y           | Y           |
+------------------------------------------------------------+
5 rows in set (0.00 sec)

 



Add a Comment




We will never share your name or email with anyone. Enter your email if you would like to be notified when we respond to your comment.




Please enter in the box below so that we can be sure you are a human.




Comments