The security mechanism of MySQL

MySQL mainly includes two kinds of users: root and ordinary users, the former is a super administrator, with all the rights provided by MySQL, while ordinary users can only have to create the user to give it permissions.

The security mechanism of MySQL mainly includes the authority mechanism, the user mechanism and the user's rights management.

The permissions provided by 1.MySQL

A table of permissions is stored under the system database MySQL, the most important of which is mysql.user,mysql.db,mysql.host.

1.1 System table Mysql.user

The table has 39 fields, which can be broadly divided into 4 categories, namely User fields, permission fields, security fields, and resource control fields.

User field: The User field contains three fields that are used primarily to determine whether a user is able to log on successfully.

Permission fields: The user table has a series of fields that end with a "_priv" string that determines the permissions of the users.

Security field: The security field mainly consists of 4 fields, the field containing the SSL field is mainly used for encryption, the field containing x509 is used primarily to identify the user.

Resource control fields: The Resource control field consists mainly of 4 fields, which are used primarily to control resources, and the default value for all resource control fields is 0, which means there are no restrictions.

1.2 System tables Mysql.db and Mysql.host

In the system database MySQL, the permissions in addition to the table user, there are table db and table host. Both tables store a user's permissions on the relevant database, which is roughly the same structure.

2. The user mechanism provided by MySQL

MySQL provides a complete set of user management mechanisms for database security and integrity. User management mechanisms include logging in and exiting the MySQL server, creating users, deleting users, modifying user passwords, and assigning permissions to users.

2.1 Logging in and exiting MySQL

2.1.1 Log in to MySQL:

The complete DOS command to connect to the MySQL server is as follows:

- | - - - -E "SQL statement"

Parameter description:

-H: Used to specify the address of the connected MySQL server, which can be expressed in two ways. The parameter hostname represents the host name, and the parameter HostIP represents the host IP address.

-P: Used to specify the port number of the connected MySQL server.

-U: Used to specify which user to connect to the MySQL server.

-P: Indicates that a password will be prompted.

DataBaseName: Used to specify which database to log into after connecting to the MySQL server. If not specified, the default is System database MySQL.

-E: Used to specify the SQL statement being executed.

Example:

Log in to the MySQL server database Cmpany through the user account root command as follows:

- 127.0. 0.1 - -P Company

- 127.0. 0.1 - - -E "select* from t_dept";

If you want to set a password directly in a specific connection, instead of setting it in the password prompt, you can do so by following the command, but the password needs to be directly appended to the parameter-p, and there must be no spaces in the middle.

- 127.0. 0.1 - -p123456 Company

2.1.2 Quit MySQL

The DOS command to exit the MySQL server is as follows:

EXIT | QUIT

2.2 Create a regular user account

2.2.1 To create a user account by executing the Create USERS statement

CREATE USERUsername[identified by [PASSWORD] 'Password']        [, username[identified by [PASSWORD] 'Password']] ...[, username[identified by [PASSWORD] 'Password']]

Examples are as follows:

CREATE USER ' Cjgong '@'localhost'by'123456';

2.2.2 Execute INSERT statement to create user

When inserting data records into system table Mysql.user, it is generally only necessary to insert Host,user, and password three fields. The syntax is as follows:

INSERT  into USER (HOST,USERVALUES('hostname','username') , PASSWORD ('PASSWORD');

2.2.3 execute the GRANT statement to create a user

GRANTPriv_type onDatabasename.tablename toUsername[identified by [PASSWORD] 'Password']       [, username[identified by [PASSWORD] 'Password']] ...[, username[identified by [PASSWORD] 'Password']]]

Examples are as follows:

GRANT SELECT  on company.t_dept      to ' cjogng2 '@'localhost'by'123456' ;

The statement can not only create a user account, but also assign permissions to it.

2.3 To modify the account password with Superuser user root

2.3.1 Changing the root user password with the mysqladmin command

- - "new_password"

Examples are as follows:

- - "123456"

2.3.2 to modify the root user password with the SET command

SET PASSWORD=PASSWORD ("New_password")

Examples are as follows:

SET PASSWORD=PASSWORD ("123456")

2.3.3 Update system tables Mysql.user data records modify the root user password

UPDATE USER SET PASSWORD=WHEREUSER= and HOST="localhost";

Examples are as follows:

UPDATE USER SET PASSWORD=PASSWORD ("123456WHEREUSER= and HOST ="localhost";

2.4 Using the Superuser root to modify the password of the ordinary user account

2.4.1 Modifying a normal user password with the grant command

The syntax is as follows:

GRANT  on database. Table     to User [identified by [PASSWORD] ' New_password ']

Examples are as follows:

GRANT SELECT,CREATE,DROPon*. *     to ' Cjgong '@'localhost'by'123456' 

2.4.2 Modifying a normal user password with the SET command

The syntax is as follows:

SET  for ' username '@'hostname'=PASSWORD ("New_password");

Examples are as follows:

SET  for ' Cjgong '@'localhost'=PASSWORD ("123456");

2.4.3 Update system tables Mysql.user data records modify the root user password

The syntax is as follows:

UPDATE USER SET PASSWORD=WHEREuser="user and HOST=" localhost ";

Examples are as follows:

UPDATE USER SET PASSWORD=WHEREUSER= and HOST="localhost";

2.4.4 Modifying a normal user password via the SET command (2)

Using a normal user to log in to the MySQL server, you can modify your secret through the set command.

The syntax is as follows:

SET PASSWORD=PASSWORD ("New_password");

Examples are as follows:

SET PASSWORD=PASSWORD ("123456");

2.5 Delete a normal user account

2.5.1 Delete a normal user through the drop USER statement

The syntax is as follows:

Drop user User1 [, User2] ...

Examples are as follows:

DROP USER ' Cjgong '@'localhost';

2.5.2 Delete system table Mysql.user data record Implementation Delete normal user account

DELETE  from USER WHERE USER =  and HOST="localhost"

Examples are as follows:

DELETE  from USER WHERE USER =  and HOST="localhost"

3. Rights Management

The security mechanism of MySQL