MySQL Security


It is known as the security of the site to allow or disable user actions on the site and its features. The security policy is the first stage in developing a database application, and a set of application security criteria and rules that governs user access to database objects is known as an application security policy. The following topics are covered in this chapter about application security and MySQL Database features:

MySQL general security issues
Security Guidelines :

  • Except for the MySQL root account, no one can access the MySQL database's user table.
  • Control MySQL access with the GRANT and REVOKE statements. Don't grant any privileges that aren't necessary, and never grant privileges to all hosts.
  • Simple text passwords should never be stored in your database. Use SHA2(), SHA1(), MD5(), or another hashing method to save the hash value differently. Make an effort to create a password that is difficult to guess.
  • Use a firewall and put MySQL behind it if at all possible.
  • MySQL's default user port is 3306; untrusted hosts should not access this port. Using a tool like nmap, you may scan the ports from the Internet. You can use this command to see if the port is open from a remote machine: shell> telnet server host 3306. The port is blocked if the telnet hangs or the connection is rejected. If you obtain a relationship but only get trash characters, the port is open and should be closed on your firewall or router unless you have a compelling reason to keep it open.
  • Some programmes use the MySQL database for a variety of reasons. Never place your faith in the user's input data.

Keeping Passwords Secure:

  • Password Security End-User Guidelines
  • Use the -p or —password option on the command line with no password value given. Here is the shell command> -u user-id -p database name MySQL
  • It will not be seen when you type in the password.
  • Make a backup of your password in an option file. For example, under Unix, you can save your password in the [client] file of the .my.cnf file in your home directory and set the file access mode to 400 or 600 to keep it safe.
  • According to MySQL administrator guidelines, the password for user accounts is stored in the MySQL.user table. As a result, nonadministrative accounts should not have access to this table. Passwords for user accounts must be reset regularly.

Passwords and Logging: Passwords can be written in plain text in SQL statements like CREATE USER, GRANT, and SET PASSWORD, as well as statements that use the PASSWORD() function. If the MySQL server logs these statements exactly as they are typed, anyone with access to the logs can see the passwords. Statement logging has been changed in MySQL 5.6.3 to prevent passwords from appearing in plain text for the following statements:

GRANT... IDENTIFIED BY...SET PASSWORD...SLAVE START... PASSWORD =... CREATE USER... IDENTIFIED BY...GRANT... IDENTIFIED BY...SET PASSWORD...SLAVE START... PASSWORD =... (as of 5.6.4) CREATE SERVER... OPTIONS(... PASSWORD...) CREATE SERVER... OPTIONS(... PASSWORD...) CREATE SERVER... OPTIONS( (as of 5.6.9) OPTIONS(... PASSWORD...) ALTER SERVER (as of 5.6.9)

  • MySQL password hashing: The MySQL database's user table contains a list of user accounts. Each MySQL account can be given a password, albeit the user table only stores a hash value generated from the password rather than the cleartext version.
  • Password Hashing Changes in MySQL 4.1 Have Implications for Application Programs: For applications that utilise PASSWORD() to generate passwords for their purposes, upgrading to MySQL version 4.1 or later may cause compatibility issues. PASSWORD() should only be used to manage passwords for MySQL accounts. Thus applications shouldn't do this.
  • Validate passwords with the validate password plugin (available as of MySQL 5.6.6).