Full security requires security of the network, hosts, databases, and applications. Most of these are the same, regardless of which database vendor you are using. However, if you’re using MySQL for the first time, there are some differences in database security. There is a lot of information here on general security.
The primary difference is in how connections to the database are authenticated. With Microsoft SQL, this is done through SQL Server or Windows logins that have access to the instance and users that have access to individual databases. Permissions are granted to users or roles (which are then assigned to users) to access objects or perform various functions. Similarly, Oracle allows DBAs to create users and then assign privileges or roles to those users.
MySQL, being open source, allows for external pluggable authentication. With some restrictions, you can integrate MySQL authentication with LDAP, Kerberos, Windows logins, or any other authentication system. This article will only discuss the default, built-in authentication.
MySQL users, roles, and privileges are referred to as Access Control and Account Management. Accounts are stored in the user table in the mysql database. It’s very important to keep access to this table restricted to the ‘root’ user. It’s also important not to modify this table directly. All changes to security should be done through the GRANT commands.
Unlike SQL Server or Oracle, MySQL user names (also known as account names) are a combination of a name and a client host. They are specified as ‘user_name’@’host_name’. You can use wildcards in the hostname, so ‘user_name’@’%’ or even ‘user_name’@’198.51.100.%’ are acceptable. The simple value ‘user_name’ is an acceptable synonym for ‘user_name’@’%’.
You can use this to only allow connections from some accounts (such as service accounts) from specific application servers, while preventing those accounts from connecting to your database from unauthorized hosts. In general, the number of users who can connect from any host should be limited.
As with other RDBMS, it is recommended to grant permissions to roles, then to grant those roles to individual users, rather than granting permissions to individual users. This makes permission management easier, as you can change permissions to large numbers of users at once.
Permissions can be granted for administrative purposes (CREATE USER, DROP TABLE, etc.), as well as allowing users to SELECT, UPDATE, or INSERT on tables, or even down to the column level. A full list of permissions can be seen here.
If you are new to MySQL, the flexibility of pluggable authentication, account privileges based on hostname, and 50+ administrative privileges can be overwhelming. Also, how can you monitor your database to ensure that others aren’t making changes to your security that leave you exposed? Idera’s SQL Diagnostic Manager for MySQL can help. It is a MySQL monitoring tool that has over 600 out-of-the-box monitors that sample and record many aspects of your database, including security. These monitors, with their built-in advice, can keep you informed on security of your database, as well as checking for best practices.
Idera’s SQL Diagnostic Manager for MySQL is available for a fully-function two-week trial. You can see details and download it here!