Mysql database security issues explained

The security of database systems includes many aspects. Since in many cases, the database server allows clients to connect from the network, the security of client connections has a very important impact on the security of the MySQL database.

　Do not provide a password on the client's command line

　When using a client such as mysql, mysqladmin, etc. to connect to the MySQL server with a user identity, you need to provide a password for the connection.

　1. You can provide the password on the command line

　shell>mysql –u root –pmypass

　Note that there must be no space between the -p option and the password, otherwise you will be prompted to enter the password and an error will be reported.

　You can also use the long format

　shell>mysql –user=root –password=mypass

　Now you can examine the consequences of doing so:

　On Unix, $ps –aux | grep mysql

　On win9x , you can hold down the Ctrl+Alt+Del keys, and on NT you can open the Task Manager.

What did you find? You found that the password was clearly displayed in front of you. So, don't do this at any time.

　So you need to let the client prompt you for the password:

　shell>mysql –u root –p

　You can also use the option file to provide the password, but note that for security reasons, the password cannot be stored in the option file. You can provide only the password option and have the client prompt you for a password.

　Using SSH to encrypt client connections

　This is a note on how to use SSH to get a secure connection to a remote MySQL server (David Carlson).

　Install an SSH client on your windows machine - I used a free SSH client from . Other useful links:

　.

　 Start SSH. Set hostname = your MySql server name or IP address, set userid = your username to log in to your server.

　 Click "local forwords". Set local port: 3306, host: localhost, remote port: 3306

　Save everything or you will have to do it again next time.

　 Log in to your server using SSH.

　Start some ODBC applications (such as Access).

Create a new file and connect to mySQL using the ODBC driver, just like you would normally do, except use user "localhost" for the server.

Done. It works fine with a direct Internet connection.

　　Do not use the root user of Unix to run the MySQL daemon process

　　Do not run the MySQL daemon process as the root user of Unix. mysqld can run as any user, you can also create a new Unix user mysql to make everything more secure. If you are running mysqld as another Unix user, you do not need to change the root username in the user table, because the MySQL username has nothing to do with the Unix username.

　You can edit the mysql.server startup script mysqld as another Unix user. Or use an options file. See Chapter 2 for details on how to start the MySQL server as a non-root user.

　Security of database directory

　MySQL server provides a very flexible permission system through the authorization table in the mysql database, ensuring the security of data accessed from the network. However, if other users on the server host have direct access to the server directory, then your server's data is still unsafe.

Normally you may use an unprivileged Unix user to execute the daemon. Check that the Unix user running mysqld is the only user with read/write permissions in the database directory.

　Possible Security Vulnerability

　Obviously, you would not give other users on the server host write access to the database directory files, but just read access is also very dangerous.

Since queries like GRANT and SET PASSWORD are logged, the regular and update log files contain sensitive query text about passwords. If an attacker has read access to these logs, he can easily find the plain text of the password by looking for sensitive words such as GRANT or PASSWORD in the log files.

　Read access to table files is also dangerous, and it is trivial to steal the file and make MySQL display the contents of the table in plain text. You can follow the following steps:

　　1. Install a new MySQL distribution, either on another host or on the current server host, using different ports, sockets and data from the official server document.

　2. Copy the corresponding files of the stolen table to the test directory in the new service database directory

　3. Then you can start the crime server and access the contents of the stolen table at will.

Set up appropriate database directory permissions in Unix

If you want to eliminate these security holes, you need to arrange the ownership of the database directory and all files and directories in it so that only the dedicated account that starts the server can access them. The steps are as follows:

　1. Switch to the root user

　$su

　2. Set the ownership of the database directory and all file directories in it to the account running the server. In this book, this account is always assumed to be mysql, and all groups are set to the root group

　 %chown –R mysql:root DATADIR

　3. Modify the permissions of the database directory and all file directories in it so that only the owner can read and write

　%chmod –R go-rwx DATADIR

　Set the appropriate database directory permissions in the NT system

　In the NT system The security of the database directory may be relatively simple:

Readers may think of changing all directory files to be readable and writable by only a certain account administrator, for example. However, there is a problem with this, that is, you can manually start an independent server in the administrator account. If the method of letting the mysql system service start automatically is not feasible, the solution is to make the database directory also readable and writable by users in the administrators group. In this way, the MySQL server can be started automatically using the system service method, or it can be started in any account using net start mysql.

Another problem is that if you are a user in the non-administrators group or from the network, you cannot establish a database connection because you do not have read rights to the database directory. If you want to use it normally, you also need write rights. The solution is to allow users in the SYSTEM group to read and write to the database directory.

Due to license and cost reasons, it is usually recommended that you use MySQL on a Linux server and use it on a Windows platform for testing or data entry work. However, if you want to use it on Windows, you can pay attention to some contents in this section.

　 MySQL options that affect security

The following mysqld options affect security:

--secure

IP numbers returned by the gethostbyname() system call are checked to ensure that they resolve back to the original hostname. This makes it more difficult for outsiders to gain access by impersonating other hosts. This option also adds some clever hostname checking. In MySQL 3.21, selection is turned off by default because it sometimes takes a long time to perform reverse parsing. MySQL 3.22 caches hostnames and has this option enabled by default.

　 --skip-grant-tables

　 This option causes the server to not use the permission system at all. This gives everyone full access to all databases! (You can tell a running server to start using grant tables again by executing a mysqladmin reload.)

　　 --skip-name-resolve

　 The hostname is not used parse. All Host column values ​​in the authorization table must be IP numbers or localhost.

　 --skip-networking

　 TCP/IP connections are not allowed on the network. All connections to mysqld must be made via Unix sockets. This option is not appropriate for systems using MIT-pthreads, because the MIT-pthreads package does not support Unix sockets.