For many web applications, a database is needed to store and organise the data being generated. As a MySQL database system was installed as part of the LAMP (Linux, MySQL, Apache, PHP) server installation, you just need to create the individual databases which your web app will need.
Adminer is a very simple and useful single file application which you can use to work with your MySQL database. We covered downloading and setting it up in another article, and we are going to use it now to create a new database and database user.
When setting up a database for a web application, it is important that you also set up a new user for that database also. If you add another web application to your server, set up another unique user and database. This way, if an attacker ever hacked your web application and gained access to your database, the scope of the damage they can do is limited to the database associated with that web app alone.
One of the most common methods for attacking online web applications is via a method known as SQL Injection. In this, an attacker sends specially crafted commands to the web app which allows them gain access to the database behind the application. SQL injection is possible because of bugs in the way the application has been coded, and unfortunately truly secure code is difficult and often time consuming.
Using SQL Injection, a hacker can read parts of the database they are not meant to, like password fields or credit card numbers, or even delete data and bring the application down. If you hear of a company suffering a data breach or losing control of their passwords, often it was through suffering an SQL injection attack.
Note, this is very different to an attack on the server itself which runs the web app. Web application attacks, such as SQL injection or cross site scripting, are attacks on the web site and application and, while not ‘impossible’, would not typically impact the underlying server itself.
To protect against the possibility of SQL injection, separate and distinct user accounts should be used for separate databases. Ensure the usernames and passwords vary also, and are not predictable or follow a pattern. This provides containment of the problem if it should occur, and limits the damage you are exposed to. Never use the root account for a web application; this account should only be used to administer other user accounts.
The principle of least privilege should also be applied too, by restricting just what the user account can do and removing any permissions it will not need.
The four most basic operations within a databases are Create, Read, Update and Delete (CRUD). These are going to be the most common operations being carried out in your database. In addition, most web apps will need permissions to create the table structures within the database for when it is first being installed. Any higher permissions should be considered carefully as to whether they could pose a security risk.
A final security measure for database data is to encrypt it. The data most commonly encrypted in databases is password data, and this is done using a form of encryption called hashing. Hashing is a special encryption in that it is one way, and cannot be reversed, i.e. decrypted. Thus if an attacker breaches a database and gets the password list, they cannot decrypt them to get the users passwords back. For legitimate users, when they log in, they enter their password, the web page hashes it and the hash is compared to that which is saved in the database. If they match, the user is granted access. Unfortunately not all applications properly hash their passwords and thus they are unprotected in the event of an attack.
In the rest of this section we are going to create a new user and database, and provide limited permissions to the user.
Creating a New Database
Browse to the adminer web directory. Enter your database root username and password to log on. Leave the Database field blank.
You will see all existing databases on the server, including system databases. Do not alter or delete any existing system databases. Click Create database.
Enter the name of the database you want to create. For the purposes of tracking which database is being used for a web app, it helps to give the database a similar name to the app which will be using it
Once the database has been created you can create a user to associate with that database. You can confirm that you are in the new database from the breadcrumbs along the top, which show you are in MySQL > Server > Database: freshrss. Click Privileges to view the users currently associated with this database.
There are none, so click Create User
As the database is this server, i.e. the same server as the web server, leave ‘Server’ as ‘localhost’.
Enter a Username.
Enter a password. Note, the server may enforce complexity requirements, i.e. upper & lower case letters, numbers & special characters.
The password field is hidden by dots, but clicking the ‘Hashed’ check box will allow you to see it in plain text. Be sure to untick it again before clicked Save to continue. This ‘Hashed’ option is for if you wanted to use a pre-hashed password. Rather than hashing it again, it would enter it directly into the database. For our purposes though, this will be left unticked.
Tick the boxes below to assign the necessary privileges to your user. As you are creating this user within the confines of a database, the Server level permissions are not available, and that’s a good thing. Also, though the first option, All Privileges, suggests that this will provide all the privileges below, I have found it not to work. For setting up your web app for the first time, I would recommend ticking all Database, Table and Column permissions. Your web app user is not going to need the Grant Option privilege, which permits it to grant privileges to other user accounts.
Click Save when done
Your database and user are now created. Enter the database name, username and password into your web application setup page and you should be good to go!