Create user and db in MySQL for various apps

Working on a portal which requires the integration of various opensource applications, I had to install quite a few systems that require mysql databases. In order to keep a safe separation between these systems, one should create different users that will have access only to the specific database. This is a simple way to prevent losing all your databases in case of  the mishap when an attack is successful on a system and sql commands pass directly to the database (sql injection etc). This is a how-to create the new user, the new database and set the requested permissions.

Open up a console (if you are not already on a terminal) and login to you mysql environment by posting the following command:

mysql -uROOT_USERNAME -p

where ROOT_USERNAME is a user that has create user and db permission (usually you will be using the ‘root’ user). This command will prompt you for the user password and after that you will get the mysql prompt:

mysql>

In this command prompt you will have to create a new user (with username ‘new_username’) that will be able to login using ‘new_user_password’ as password. Check out that this user is on the ‘localhost’ domain which is fine since it is a good practice to not allow connection directly to the database from outer sources. To create this user, you will have to post the following command:

create user ‘new_username’@'localhost' IDENTIFIED by 'new_user_password';

The next step is to create a new database named “new_database_name”:

create database new_database_name;

The final step is to set the required permissions in order to allow the user to operate on the database and create the schema. The most common set of permissions that is given to equivalent users (based on the drupal setup instructions) is the following:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON new_database_name.* to 'new_username'@'localhost' identified by 'new_user_password';

In order to enable all the permissions you will also have to flush the privileges on the MySQL server by posting:
 

flush privileges;

After that you may exit the mysql prompt by posting the “quit” command. Don’t forget the ending semi columns on all the above mentioned commands because otherwise you will be getting a prompt to continue your command when you press enter.

That’s all folks. From this point on you may continue with your system installation providing the newly created credentials.