Shell way: Adding new user(s) to MySQL Databases.

This small tip shows how to add new users to a MySQL system and keep their databases seperate from each other.

If you have a server of your own it makes a lot of sense to replicate this setup – for each database application you wish to use create a specific database to hold its data and create a dedicated user to access it.

This means that if the application is vulnerable to a security problem only the single database is compromised.

Other reasons to create new users are to allow other users to share your database – if you have a virtual server for example.

Creating a new database and associated user involves using the mysql client command.

When you install the mysql-server package on Debian you will by default end up with a superuser account setup for the database server root with an empty password.

If you want to create a new user USERNAME with a database that they have full control over we will run the following commands:

1.
# Connect to the local database server as user root
# You will be prompted for a password.

user@localhost:~$ mysql -h localhost  -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.5.24-9 (Debian)
...
mysql> 

2.
# Now we see the 'mysql>' prompt and we can run
# the following to create a new database for user.
mysql> create database DATABASENAME character set utf8;
Query OK, 1 row affected (0.00 sec)

3.
# Now we create the user USERNAME and give him full 
# permissions on the new database

mysql> grant CREATE,INSERT,DELETE,UPDATE,SELECT on DATABASENAME.* to USERNAME@localhost;
Query OK, 0 rows affected (0.00 sec)

# Next we set a password for this new user
mysql> set password for USERNAME = password('USERPASSWORD');
Query OK, 0 rows affected (0.00 sec)

OR

create user 'USERNAME'@'localhost' identified by 'USERPASSWORD';
grant all privileges on DATABASENAME.* to 'USERNAME'@'localhost';


4.
# Cleanup and exit
mysql> flush privileges;
mysql> exit;

Once all this has done you have created a new user with complete control over the contents of the database DATABASENAME – the user can create new tables, delete them and generally use the database.

Note that this new user will have no other access to the server, only the dabase that you gave them permissions on.