FreeKB - mySQL / MariaDB Create a new user account using the CREATE USER statement
mySQL / MariaDB - Create a new user account using the CREATE USER statement

This assume you are able to log into your MariaDB or mySQL server or you have configured passwordless authentication and wil be using the -e command line option and the user must have the Create_priv. You may want to first SELECT USER to display the list of current users.

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 'John'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant select, insert, update on *.* to 'John'@'%' 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 86fa7 in the box below so that we can be sure you are a human.




Comments

Web design by yours truely - me, myself, and I   |   jeremy.canfield@freekb.net   |