Create a MySQL database and assign user permissions

Most of the time to keep the integrity of your MySQL server you may not wish you have phpmyadmin running locally or allow remote login to your MySQL server. This is generally the case when you have a MySQL server driving web based tools like forums or a CMS and you are connecting to it via SSH.

There are a lot of nice GUI tools that are available for user and database management when you have remote or local access to it (tools like Sparx Systems Enterprise Architect or Navicat) however some times you need to get down to the command line.


In Ubuntu Server access MySQL:

mysql -u root -p

Enter in the password

Remember now that you are in MySQL land so ever command must end with ;

Create the database:

create database smfau;

If successful you will receive messages like: Query OK, 1 row affected (0.00 sec)

Crate a new user for each database as we don't want to give root access to our applications:

create user 'newuser'@'localhost' identified by 'newuserpassword';

Be sure to swap in your own newuser and newuserpassword.

By default the new user as no permissions to do anything, so we want to give it some limited permissions to the new database. We also probably don't want to grant all permissions either as we have security to consider so we can give it a list of available permissions like this:

grant select, insert, update, delete, create, alter, drop, index on smfau.* to 'newuser'@'localhost';

As we are working directly with the database commandline we don't need to "flush permissions;" to make them active. Your user is now ready to go. For each different web application that needs to use a database I would suggest creating a new user. This means that it is less likely any compromised application to be able to affect the next.

Reference Short List of Commonly Used Permissions:

  • ALL PRIVILEGES- this would allow a MySQL user full access to a database
  • CREATE -  create new tables or databases
  • DROP -  them to delete tables or databases
  • DELETE -  delete rows from tables
  • INSERT - insert rows into tables
  • SELECT - use the Select command to read through databases
  • UPDATE - update table rows
  • GRANT OPTION - grant or remove other users' privileges



If you are looking for a guide to setup MySQL on Ubuntu then you might want to click here

Copyright 2020 OReillyIT. All rights reserved.