A MySQL add user and GRANT example

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

MySQL add user and grant syntax - solution

Here's an example of what I did recently to (a) create a new MySQL database and then (b) add a new MySQL user account to work with that database.

First, from my Unix prompt, I logged 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)

Next, I created my new database with the MySQL "create database" command:

mysql> create database my_database;

Then I created a MySQL user account, giving the user account all the priviliges it needs to "own" this database with the MySQL GRANT command. Note that I assign both the username and password when I add this MySQL user:

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

This might be a little easier to copy and paste from:

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

Of course 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 this GRANT command is how you create a MySQL user account.

Update: Please see the Comments section below for an additional "MySQL add user" example that uses the "MySQL grant" command.

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".

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:

Thank you

I am a noob sysadmin, and this was very helpful.

Bugzilla MySQL user commands

I was just working on a new Bugzilla installation, and noticed their grant command example, and thought I'd share it here as another example:

mysql> GRANT SELECT, INSERT,
       UPDATE, DELETE, INDEX, ALTER, CREATE, LOCK TABLES,
       CREATE TEMPORARY TABLES, DROP, REFERENCES ON bugs.*
       TO bugs@localhost IDENTIFIED BY '$db_pass';

mysql> FLUSH PRIVILEGES;

They also show this syntax for changing the MySQL root user password, which is a little different than the syntax I normally use, so I thought I'd share that here as well:

mysql> UPDATE user 
       SET password = password('new_password') 
       WHERE user = 'root';

mysql> FLUSH PRIVILEGES;

I am also a noob sysadmin and

I am also a noob sysadmin and this is very helpfull

Post new comment

The content of this field is kept private and will not be shown publicly.