packetcode logo
Basic Database security

Basic Database security

3911 views 1 year ago Tutorials PHP

Databases are the heart of the data on any system and definitely its worth to keep them secure. Since PHP and MySQL work hand-in-hand so often, some understanding of these concepts for protecting databases is important for every programmer.

Run database server as an ordinary, unprivileged user

Running server as root grants privileges to all accounts under it and therefore clients may take advantage and modify the system which could be a potential threat. Running it as ordinary account minimises the risk factor.

Securing data directory

It always advisable to secure the data directory (i.e. where the server creates databases and writes its log files). This prevents unprivileged users from monitoring the log files to see what queries are being run by legitimate users.Following are methods to secure data directory

1. Within the data directory itself (as we mentioned previously, typically /var/db/mysql), you will normally find only directories, each one corresponding to a database. After changing to this directory, set the owners and the groups of all these subdirectories to be the same user ID as the one the MySQL server process runs as (typically mysql), with the following command:

chown -R mysql:mysql ./

In this command, the -R flag means to recurse through all subdirectories. We next specify the colon-separated owner and group we want to set. Finally, the ./ (dot-slash) tells the operating system to begin the recursion in the current directory.

2. Set the mode of the data directory to be readable only by the MySQL user, with a command something like this:

chmod -R 700 ./

Again, the -R flag means to recurse through all subdirectories, and the ./ (dot- slash) means to start in the current directory. The octal value of 700 assigns rwx privileges to the owner (which is the user known as mysql), and turns off all privileges for the group and the world.

Securing Option Files

Option files contain information that can be used to control a server’s operation and can store information like client usernames and passwords, and they are therefore a potential point of compromise. Option files can be global, server-specific, or user-specific. The precautions you take to secure a given file depend on its scope and purpose.

Global Option Files

/etc/my.cnf is a global option file used both by the MySQL server and by client programs. Protect it against unauthorized modification (to prevent someone from changing it to tell the server to start up as root, for example), but also make it publicly readable so that client programs can access it. To make this file owned by and modifiable only by root but readable to anyone, use these commands:

# chown root /etc/my.cnf
# chmod 644 /etc/my.cnf

Because this file is world-readable, it should not be used to specify password values.

Server-Specific Option Files

A MySQL server can also have its own private option file, named my.cnf, located in the server's data directory. This file should be owned by and accessible only to the account used to run the server. If you've followed the procedure outlined earlier for securing your data directory, these access constraints should be satisfied already. To set the ownership and mode explicitly (assuming the data directory is /usr/local/mysql/data), use these commands:

# chown mysqlusr /usr/local/mysql/data/my.cnf
# chmod 600 /usr/local/mysql/data/my.cnf

User-Specific Option Files

On multiple-user systems, each login account may have its own option file. The file is named .my.cnf and is typically located in the account's home directory. Personal option files are commonly used to store MySQL user names and passwords, so each one should be accessible only to its owner to prevent other users from reading its contents. A user who has a .my.cnf file can (and should) make it private by executing this command:

% chmod 600 .my.cnf

Although you might think this precaution can be left up to the discretion of individual MySQL users, a more proactive administrator might want to run a daily cron job that looks for .my.cnf files and sets proper ownership and restrictions.

Securing MySQL Accounts

The MySQL installation procedure creates a database named mysql, which in turn contains the grant tables that the server uses to determine which MySQL accounts can perform what actions. Specifically, the user table in the mysql database lists all valid accounts and indicates which global privileges they have, if any. This section provides some guidelines that you can use to evaluate existing accounts, and that you should keep in mind when creating new accounts. These guidelines apply to servers running on any platform.

A general set of principles for securing the MySQL accounts listed in your grant tables is as follows:
1.Remove anonymous accounts.
2.Make sure that each account has a password.
3.Don't grant global privileges unnecessarily.

If you have never examined the grant tables that are set up during the MySQL installation procedure, you should do so now, using the instructions that follow. The default grant tables created during MySQL installation include accounts for the MySQL root user as well as some "anonymous" accounts that can be used without specifying a user name. These anonymous accounts are convenient for initial testing, but should be removed when you're satisfied that the server is running properly. In addition, none of the default accounts have a password initially—not even the root accounts! This is a security hole that should be fixed by assigning passwords.

Delete Anonymous MySQL Accounts

Anonymous MySQL accounts allow clients to connect to the server without specifying a user name. Under Windows, the default accounts may even have full access to any database managed by the server. To remove anonymous accounts, connect to the server as the MySQL root user to access the mysql database, then issue the following statements:

mysql> DELETE FROM user WHERE User=";

The DELETE statement removes accounts that have an empty value in the User column of the user table that lists MySQL accounts, and FLUSH PRIVILEGES tells the server to reload the grant tables so the changes take effect.You may also want to check the other grant tables in the mysql database for rows that have empty User values. Those rows can be removed, too.

Make Sure MySQL Accounts Have Passwords

Any MySQL accounts without passwords should either be removed or assigned passwords. To find such accounts, look in the user table of the mysql database using the following query:

mysql> SELECT * FROM user WHERE Password=";

The accounts corresponding to any rows returned by this query are not secure and should be assigned passwords. To do this, you must specify both the user name and hostname associated with an account. Let’s say that the values in the User and Host columns for an account having no password are baduser and myhost. If you want to assign a password of mypw to that account, then the SQL would look like this::

  mysql> UPDATE user SET Password=PASSWORD('mypw')
       -> WHERE User='baduser' AND Host='myhost';

You can also use a SET PASSWORD statement:

mysql> SET PASSWORD FOR 'baduser'@'myuser'=PASSWORD('mypw');

If you assign passwords with UPDATE, issue a FLUSH PRIVILEGES statement afterward to tell the server to reload the grant tables so the changes take effect.

Audit existing MySQL accounts for unsecure settings or excessive privileges.

Check the MySQL accounts listed in the user table of the MySQL database where the grant tables are located. Remove all anonymous accounts that allow clients to connect without even knowing a user name. Remove accounts that don’t have passwords, or assign passwords to them if you need to keep them. If an account is associated with a hostname specifier that is a pattern, make sure that the pattern is narrowly defined. Revoke any and all privileges that an account doesn’t really need, such as FILE, PROCESS, or SHUTDOWN.

Follow a consistent process when creating new MySQL accounts.

Require each account to have a non-empty user name and password, avoid use of wildcards in hostname specifiers, and grant only those privileges that are really needed (especially global privileges).


In this tutorial, we went over some basic procedures for securing a MySQL database .To successfully secure your database, you need to think about creating users in a smart, consistent way: always require a username and password, revoke unnecessary privileges, and put the proper permissions on configuration files. From the server perspective, always run as an unprivileged user, provide regular backups.

im krishna Teja, im a computer science engineer by qualification, a physics teacher by profession and a programmer by interest. I'm an expert in building visually stunning web apps using javascript, ...