A MySQL/MariaDB add user and grant example

MySQL “users” FAQ: How do I add a new user to a MySQL or MariaDB database (i.e., how do I create a MySQL user account, and then grant database permissions to that new user account)?

Solution: MySQL/MariaDB “add user” and grant syntax

The following MySQL/MariaDB commands show an example of what I did recently to:

• Create a new MySQL database, and then
• Add a new MySQL user account to work with that database

I have verified this approach with both MAMP and using MySQL on Linux servers.

Step 1: Log into the database

First, from your command-line prompt, log into my MySQL database server with the mysql command line client:

unix> mysql -u root -p
(here I enter 'my_root_password' to get through the mysql prompt)

Step 2: Create the database (if needed)

Next, create a new database with the MySQL create database command, specifying the name of the database (my_database) after that command:

mysql> create database my_database;

Step 3: Create the new MySQL user account

Then create a new MySQL user account, giving the user account all the privileges it needs to “own” this database with the MySQL grant command. With MySQL 5 I create a new user and then grant user options on the database in two steps:

# STEP 1:
CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';

# STEP 2:
GRANT ALL PRIVILEGES
ON my_database.*
TO 'my_user'@'localhost'
WITH GRANT OPTION;

I just did that in December, 2021, and it works fine.

With MySQL 4 I used to do that in one step, as follows, but this approach doesn’t work with MySQL 5:

GRANT ALL PRIVILEGES
ON my_database.*
TO 'my_user'@'localhost'
IDENTIFIED BY 'my_password' 
WITH GRANT OPTION;

You can put that entire MySQL grant command on one line; I just put it on multiple lines here so it would be easier to read. (It seems a little unusual, but using grant is how you create a MySQL user account.)

Note about this MySQL user account and “localhost”

It's important to note when I create this MySQL user, I'm giving the user access to this MySQL database from the computer system known as "localhost". This works fine when you're accessing this database with this new MySQL user account from the local computer system, but if you're going to access this database from another computer system, you'll need to specify the IP address of that remote system here instead of "localhost".

Altering a user password

In a slightly related note, in December, 2021, I needed to alter/update a MySQL 5 user password, and I was able to do that with this command:

ALTER USER my_user@localhost IDENTIFIED BY 'my_new_password';

More information

For more information on how to add/create a MySQL user, and also how to remove a MySQL user, see the following URLs on the MySQL website:

• How to create a MySQL user account
• How to drop a MySQL user