Howto MySQL: Create a user and grant permission to a database

Here is something that as a system or database admin, you’ll do lots of – create a database, create a database user, and then assign the permission for that user to operate on that database. We can do the same thing to grant permissions to other databases for that user too.

Here’s what you want to know:

First, log in to your database server as a database admin user. Usually this will be root (note this is not the same root user as your Linux server, this is the database root user).

$mysql -u root -p

once logged in, you can create the database, user and assign the right privileges:

mysql> CREATE DATABASE somedatabase;
mysql> CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'their_password';

mysql> GRANT ALL PRIVILEGES ON somedatabase.* To 'new_user'@'localhost' IDENTIFIED BY 'their_password';
mysql> FLUSH PRIVILEGES;


Here’s what that all means:

CREATE – This command creates things like databases, users and tables. Note you can’t use usernames with dashes in them (underscores are OK).

GRANT – This command gives (grants) permission to databases, tables and so forth.

ALL PRIVILEGES – This tells it the user will have all standard privileges such as SELECT, INSERT, UPDATE, etc. The only privilege it does not provide is the use of the GRANT query, for obvious reasons!

ON somedatabase.* –  this means grant all the privileges to the named database. If you change the * after the dot for a table name, routine or view, then this will apply the GRANT to that specified table,etc only.

TO ‘new_user’@’localhost’ – ‘new_user’ is the username of the user account you are creating. It is very important to ensure you use single quotes (‘).  The hostname ‘localhost’ tells MySQL what hosts the user can connect from. In most cases, this will be localhost, because most MySQL servers are only configured to listen its own host. Opening it up to other hosts (especially on the Internet), is insecure.

IDENTIFIED BY ‘their_password’ – This sets the password for that user, replace the text their_password with a sensible password!

FLUSH PRIVILEGES – this makes sure that any privileges granted are updated in mysql so that they are ready to use.

Hope this helps. For more information on creating users, refer to the MySQL Reference Guide.