Securing MySQL
In this section I discuss how to secure your MySQL databases, MySQL uses Access Control List (ACL) to allow users privileges to access the database, the list will contain what access a user will ge granted on an object, the object could be global or tied to a specific database, event, routine or even field.
I have already discussed how to setup a user in the user section, so I want to discuss the privilege and privilege levels there a number of them which i have listed in the table below
Privilege | Allows | Global |
DB |
Table |
Column |
Routine |
all | all privileges for the level | yes |
yes |
yes |
no |
no |
all privileges | same as all | yes |
yes |
yes |
no |
no |
alter | alter table | yes |
yes |
yes |
no |
no |
alter routine | alter and drop stored routines | yes |
yes |
yes |
no |
no |
create | create table | yes |
yes |
yes |
no |
yes |
create routine | create stored routines | yes |
yes |
no |
no |
no |
create temporary tables | create temporary tables | yes |
yes |
no |
no |
no |
create user | create/drop/rename user, revoke all privileges | yes |
no |
no |
no |
no |
create view | create view | yes |
yes |
yes |
no |
no |
delete | delete | yes |
yes |
yes |
no |
no |
drop | drop table | yes |
yes |
yes |
no |
no |
event | event creation | yes |
yes |
no |
no |
no |
execute | run stored routines | yes |
yes |
no |
no |
no |
file | select ... into outfile, load data infile |
yes |
no |
no |
no |
no |
index | create/drop index | yes |
yes |
yes |
no |
no |
insert | insert | yes |
yes |
yes |
no |
no |
lock tables | lock tables on all tables the user is allowed the select privilege | yes |
yes |
no |
no |
no |
process | show processlist | yes |
no |
no |
no |
no |
references | unused | yes |
yes |
yes |
yes |
no |
reload | flush | yes |
no |
no |
no |
no |
replication client | can determine master and slave hosts | yes |
no |
no |
no |
no |
replication slave | reading binary log events | yes |
no |
no |
no |
no |
select | select | yes |
yes |
yes |
yes |
no |
show databases | shows all databases, including those that the user has no privileges for | yes |
no |
no |
no |
no |
show view | show create view | yes |
yes |
yes |
no |
no |
shutdown | mysqladmin shutdown | yes |
no |
no |
no |
no |
super | change master, kill, purge master logs, set global, mysqladmin debug, one connection even if there are already max_connections | yes |
no |
no |
no |
no |
trigger | create/drop trigger | yes |
yes |
yes |
no |
no |
update | update | yes |
yes |
yes |
yes |
no |
usage | authentication only | yes |
yes |
yes |
no |
no |
with grant option | grant for the privilege specified | yes |
yes |
yes |
yes |
yes |
Here is a simple table that you can give to the type of user, this is only a guide, you can combine any of the roles if the user has a dual role
User Role | Privilege |
Application, read-only | select |
Application, read/write | delete, execute, insert, select, update |
Monitoring | usage |
Replication monitoring | replication client |
Replication slave | replication slave |
Schema Administrator | alter, alter routine, create, create routine, create view, drop, event, execute, index, show databases, trigger |
Developer | file |
DBA | create user, event, file, lock tables, process, flush, replication client, show databases, shutdown, super, with grant option |
There are a number of parameters that you can set in the my.cnf file to make the database more secure from external users or crackers, you can do things like binding the IP address, change the listening port, change the socket file location, here is a list of what you should be aware of
Option | Example usage | Description |
bind-address | bind-address=192.168.0.50 | binds the mysqld server to the specified IP address, only one IP address can be specified |
enable-named-pipe | enable-named-pipe | enables connectivity to mysqld on windows via named pipes |
port | port=3307 | specifies the port that mysqld listens on |
shared-memory | shared-memory | enables connectivity to mysqld on windows via shared memory, the client will connect using the protocol=memory option. |
skip-name-resolve | skip-name-resolve | mysqld will not lookup DNS names, this is off by default. |
skip-networking | skip-networking | mysqld will not listen on any port |
socket | socket=/tmp/mysqld.sock | specifies the location of the mysqld socket. |
You cannot have a security breach on data that you do not store, so try to remove any old data that you do not need, also store sensitive information in encrypted format, for example passwords should be store using one-way hash which means the algorithm for the one-way hash would have to be broken before they can decrypt your information.
Data flow is one of the most important aspects of data security, very often there are a few places where data is checked and secured and after that point the data is deemed to be trustworthy, below is an example of data flow
The above data flow shows only one way to limit the SQL injection, a malicious user could bypass the HTML form sending HTML POST or GET requests to the page performing the SQL query, because the page performing the SQL assumes the data has already been validated, SQL injection can occur.
The malicious could replace parts of the SQL code with his own SQL that could reveal information that you don't want him to see, newer firewall's can protect against this sort of thing but also make sure that your application does as well.
SQL injection | ## The original SQL code ## what if the malicious user types the following into the first field |
The other security point to make is that all sensitive data should be encrypted when set across a network, even if it an internal one, network sniffers can be used to obtain packets where a malicious user could then examine the packets for any unencrypted data, which may contain passwords or other sensitive information.
one way to encrypt data going across the wire is to setup a SSH tunnel, encrypting connectivity mysqld is not difficult to archive, encrypted connectivity is managed on a per user basis, by default the connection is not encrypted
setting up a encrypted connection | ## First check that you have encrypted support, you should see have_openssl and have_ssl they may be set ## setup a public and private key, the public key is stored in a certificate file and the private key is |