1.Summary
The MySQL user management mechanism is powerful, flexible, and often misused. The mysql database contains the various permission tables that allow access to be controlled based on user, host, and the action being performed. You can update the tables directly with SQL statements (in which case flushing the tables activates the changes) or through the more convenient GRANT and REVOKE statements.
The first task in a new installation should be to issue a root password. Until then, anyone can connect as root and have full access to everything. You can use mysqladmin, the SET statement, or GRANT to do this.
MySQL allows SSL connections for added security. This is not installed by default because it has performance implications.
You also learned some general principles for securing your data:
-
Never issue a user the root password. They should always be connecting with another username.
-
Never give anyone access to the user table, even for reading. Just viewing the encrypted password is enough to potentially allow a user full access.
-
Always issue the minimum permissions you can. Issuing minimum permissions means that the user table contains N for all columns.
-
For critical data, it must be possible to trace changes made by individuals. In general, people interact with the database through an application. The burden for managing access on an individual level then usually falls on the application.
-
Ensure you cannot connect as the root user without a password from any server.
-
Passwords should never be stored in plain text and should not be dictionary words.
-
Check the user privileges every now and again and make sure that no one has granted anyone else inappropriate privileges.
2.Application Security
Most security holes are caused by poor applications. There are a number of common pitfalls to avoid:
-
Never trust user data. Always verify any data entered by a user.
-
Inserting quotations in a website form is a common cause of breakages. For example, an application takes a username and password and runs a query such as SELECT * FROM passwords WHERE username=’$username’ AND password=’$password’. Poorly designed applications will allow $password to contain something like aaa’;DELETE FROM passwords;. MySQL parsing the query thinks the single quote after the three a‘s is the end of the query and then happily performs the next query. Most languages have simple functions to avoid this, escaping any quotations in the string, such as mysql_real_escape_ string() in C or addslashes() in PHP.
-
Check the size of the data. A complex calculation may work well with a single digit number, but a 250-digit number passed by a user may cause the application to crash.
-
Remove any special characters from strings passed to MySQL.
-
Use quotes around numbers as well as strings.
3.System Security
By default, MySQL runs as its own user on Unix. The user and group are mysql. Never be tempted to allow anyone access to the system as the mysql user—it should only be for the database itself. MySQL also creates a separate directory where it places data files. This directory is accessible only to the mysql user. The default settings have been chosen for a reason; keep to these principles:
-
Separate the data directory.
-
Secure the data directory (no one else should be able to read, much less write, in the MySQL data directory).
-
Run MySQL as its own user.
4.Security Issues with LOAD DATA LOCAL
Restoring from a client machine may be convenient, but it does come with a security risk. Someone could use LOAD DATA LOCAL to read any files to which the user they are connecting as has access. They could do this by creating a table and reading from the table after they have loaded the data. If they were connecting as the same user as the web server, and have the right access to run queries, this becomes dangerous. By default, MySQL allows LOAD DATA LOCAL. To prevent this and disallow all LOAD DATA LOCAL, start the MySQL server with the –local-infile=0 option. You could also compile MySQL without the –enable-local-infile option.
[...] Excerpt from: Mysql Security,part 4 « GO IT WORLD | IT TECH | IT NEWS [...]