The security of MySQL databases generally includes the following aspects:
- Common security factors: Use strong passwords and do not assign unnecessary permissions to users to prevent ATTACKS on SOL.
- Security of the installation procedure: Ensure that data files, log files, and program files specified during MySQL installation are stored in secure places and unauthorized users cannot read or write data.
- Access control security: includes defining accounts and related permission Settings in the database.
- MySQL Network security: Only valid hosts are allowed to connect to the server, and account permissions are required.
- Data security. Ensure that you have backed up MySQL database files, configuration files, and log files reliably. A perfect backup mechanism is a prerequisite for data security.
The MySOL database system can connect, query, and perform other operations depending on the access control list (ACL). The MySQL permission list is shown in Table 1.
Table 1 MySQL permissions and functions
| The right to limit | As with |
|---|---|
| CREATE | Permission to create databases, tables, and indexes |
| DROP | Permission to create databases, tables, and views |
| GRANT OPTION | Allows you to add and delete permissions for other accounts |
| LOCK TABLES | Allows the user to lock the data table with this statement |
| EVENT | Permission to execute the EVENT |
| ALTER | Modify data permissions |
| DELETE | Permission to delete data records |
| INDEX | Create permission to drop indexes |
| INSERT | The permission to insert data into a table |
| SELECT | The permission to query data from the database |
| UPDATE | Permission to update data records |
| CREATE TEMPOPARY TABLES | Permission to create temporary tables |
| TRIGGER | Permission to execute the trigger |
| CREATE VIEW | Permission to create a view |
| SHOW VIEW | Permission to run SHOW CREATE VIEW |
| ALTER ROUTINE | Permission to modify or delete a stored procedure |
| CREATE ROUTINE | Permission to create a stored procedure |
| EXECUTE | Permission to execute a stored procedure or function |
| FILE | Grant read and write permission to host files on the server |
| CREATE TABLESPACE | Permission to create a tablespace |
| CREATE USER | Create the permission to change the MySQL account |
| PROCESS | Permissions to display information about processes running on the server |
| RELOAD | Allow users to use FLUSH statements |
| REPLICATION CLIENT | You can use SHOW MASTER STATUS and SHOW SLAVE STATUS |
| REPLICATION SLAVE | Allows a secondary server to connect to the current server |
| SHOW DATABASES | You can use SHOW DATABASES to view database information |
| SHUTDOWN | Allows users to disable MySQL services |
| SUPER | Allows administrative actions such as shutting down server processes |
| ALL | Represents all available permissions |
All accounts and passwords are stored in the user table in the MySQL database, so users and passwords can be added, deleted, or changed using mysqladmin or SQL statements. Note that the MySQL account access information must contain host information. For example, by default, root is not allowed to log in to a remote host.
- Create a username User that can connect to the MySQL database on the local computer. Set the password to User*123. Create username as follows:
mysql> CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘User*123’
Where, the meanings of each parameter are:
-
Username indicates the created username.
-
Host specifies the host on which the user can log in. Localhost is used if the user is local, and the wildcard % is used if you want the user to log in from any remote host.
-
IDENTIFIED BY Specifies the user’s login password.
-
User*123 is the login password of the User. The password can be empty. If the password is empty, the User can log in to the server without the password.
The user is successfully created if the following information is displayed:
Query OK, 0 rows affected (0.70 SEC)
Note When setting a password for username, follow the password setting rules, that is, the password must contain uppercase and lowercase letters, special symbols, and digits. In addition, the password must be 8 characters long.
- To grant permissions to username, run the following command:
mysql> GRANT ALL ON test1.* TO ‘username’@’localhost’;
To enable this user to authorize other users, run the following command:
mysql> GRANT ALL ON test1.* TO ‘username’@’localhost’ WITH GRANT OPTION;
Among them, the meanings of each part are:
- ALL indicates operation permissions, such as SELECT, INSERT, and UPDATE. To grant ALL permissions, use ALL.
- ON is used to specify which libraries and tables the permissions are for.
- Test1 indicates the name of the database.
- * indicates the name of the table. If you want to grant the user permission to perform operations on all databases and tables, * indicates the name of the table..
- TO indicates that the permission is granted TO a user. For example, username’@’localhost indicates the username user, @ is followed by the restricted host, and can be the IP address, IP segment, domain name, or %. % indicates anywhere.
- IDENTIFIED BY Specifies the user’s login password, which can be omitted.
- WITH GRANT OPTION indicates that the user can GRANT its own permission to others.
Note that if the WITH GRANT OPTION is not specified when creating an action user, the user cannot use the GRANT command to create a user or authorize another user.
Table 2 describes the common authorization operations of the GRANT command.
Table 2 Common methods and functions of the GRANT command
| The method of | As with |
|---|---|
| GRANT permission ON database name. Table name TO username @host name | Grant permissions to a particular form in a particular database |
| GRANT permission ON database name * TO username@host name | Grant permissions to all forms in a particular database |
| GRANT permission ON*.* TO username@host name | Grant permissions to all forms in all databases |
| GRANT permission 1, 2 ON database name * TO username@host name | Grant multiple permissions to all forms in a particular database |
| GRANT ALL PRIVILEGES ON .TO username @host name | Grant full permissions to all forms in all databases |
You can also use GRANT to add privileges to a user repeatedly. For example, if you give a user a select privilege and then an INSERT privilege, the user has both select and INSERT privileges.
Rules for granting user rights. Permission control is for security reasons. Therefore, follow the following principles:
- Grant only the minimum required permissions to the user. For example, if a user needs to be deleted, only deletet permission is granted to the user. You do not need to grant update, INSERT, or SELECT permission to the user.
- When creating a user, restrict the user to a specified IP address or Intranet IP address segment.
- Delete users without passwords when initializing the database. When the database is installed, users are automatically created that do not have passwords by default.
- Set a complex password for each user.
- Periodically delete unnecessary users, reclaim permissions, or delete users.
- View user permissions, as shown in Figure 3.
View the permissions of a user as shown in Figure 4.
- Change the user name and password. The command is as follows:
mysql> rename user ‘username’@’localhost’ to ‘user’@’localhost’; mysql> SET PASSWORD FOR ‘user’@’localhost’ = PASSWORD(‘123456’);
- Delete the permissions granted to users as shown below:
mysql> REVOKE ALL ON test.* FROM ‘user’@’localhost’; Query OK, 0 rows affected(0.12 sec)
Note that ALL, test1.* need to be consistent with the authorization section.
- To delete a user, run the following command:
mysql> DROP USER ‘user’@’localhost’;