MySQL performance tuning and architecture design Note: Three MySQL security management

4.1 Database system security related factors

1, perimeter network

2, Host:

3, the database itself

Access authorization related modules are mainly composed of two parts:

One is the basic User Management module: mainly responsible for the user login link related basic permissions control

Another is Access authorization control module: Check the visitors who have entered the door anytime and anywhere, verify whether they have permission to access the data that the request is made to access. Through the calibrator can get the data smoothly, but not through the verification of the interviewees , can only receive "access exceeded the" relevant feedback.

Three lines of defense diagram:

4, Code: SQL injection--great harm, impossible to guard against

4.2 Introduction to the Privilege system

4.2.1, the relevant permission information is primarily stored in several system tables called Grant tables. Mysql.user,mysql.db,mysql. Host,mysql.table_priv,mysql.column_priv because the permissions table data is relatively small, and access is very frequent, so MySQL at boot time will be all the permissions information load into memory, So when we manually modify the permissions to execute the flush privileges command to reload the MySQL permission information. If you do not need to execute flush privileges with Grant,revoke,drop user, the memory is modified when the table is modified. Create user to add users, note that the newly added user has only the initial user permission, and the Create user command also updates the memory. It is recommended to use Grant,revoke,drop user,create user for permission control.

5.6 Above does not contain 5.6 5.6 or more including 5.6

4.2.2, permission granting and removal:

Grant Authorization

REVOKE Revoke Permissions

Update the GRANT Tables series table.

When authorizing a user, you need to specify not only the user name but also the visiting host. If you specify only the user name, then MySQL will assume that: ' username ' @ '% ' authorization, to remove user rights, also specify the visiting host.

View a user right: "Show GRANTS for ' username ' @ ' hostname '; Querying permission information in the Grant Tables series table

4.2.3, permission level: Five levels of permissions in MySQL

1,global level: Global control permissions, all permissions information is saved in the Mysql.user table .

Scope: All permissions are for the entire mysqld and are valid for all tables and all fields under the database.

2,global level mainly has the following permissions:

3, to grant the global level permissions, you only need to execute the grant command, with the *. * To specify the scope of the application is global, when there are multiple permissions need to be granted, do not need multiple authorization, just need to separate all the required permissions by commas (,): for example:

GRANT select,update,delete,insert on *. * to ' username ' @ ' localhost ';

4,database level

At global level, above the other three level

Scope: Specifies all objects under the database

Permission settings: Compared to the global level, a few permissions are missing: CREATE user,file,process,reload,replication client,replication slave,show DATABASES, Shutdown,super,useage, without any additional privileges

Implementation method: Two kinds:

1,grant ALTER on test.* to ' username ' @ ' localhost ';

2,use DATABASENAME

GRANT DROP on * to ' username ' @ ' locahost ';

3, at the time of authorization, if you grant multiple users the same permissions, you can authorize multiple users

GRANT CREATE on test.* to ' username1 ' @ ' localhost, ' username2 ' @ ' localhost '

Show Grants Form Username@localhost

5,table level

Scope: Below global level,database level, above column level,routine level

Implementation: GRANT INDEX on Test.t1 to ' USERNAME ' @ ' Localhsot ';

Permission settings: alter,create,delete,drop,index,insert,select,update

6,column level:

Scope: Below global,database,table level, above routine level

Implementation: GRANT Select (id,value) on test.t2 to ' ab ' @ ' Localhsot ';

Note: When a user inserts (insert) data into a table, if the user does not have insert permission on a column, the data for that column is inserted into the default value padding

7,routeine level:

Scope: Below the other 4 levels

Permission settings: Execute,alter routine two kinds

Implementation: GRANT EXECUTE on TEST.P1 to ' username ' @ ' localhost ';

8,grant Permissions:

A user with grant permissions can authorize other users with the permissions that they own, usually with the following: With GRANT option to achieve the purpose of authorization.

9, we can grant all available permissions for a level to a user through the grant all statement:

Grant all on TEST.T4 to ' ABC ';

Note: The above five permissions: Table,column,routine the objects that are dependent upon authorization must be present, unlike database level, which can be authorized when the current database does not exist.

4.2.4 access Control Implementation principle:

1, Composition:

User Management module: whether a user exists

The main thing is: The Host,username,password in the user table, these three items

Access Control module: monitor each action of the visitor

All authorized users are stored in the Mysql.user table

Description: The user Management module determines whether visitors can enter the door, the access control module to determine what each guest could not take what

2, Customer Management module process:

3, rights control module flow: detailed Explanation: Page45

SELECT * from Test.t4 where status= ' SS ';

4.3mysql Access Authorization policy:

1, learn about visiting Host: ' username ' @ '% ' Grant all host permissions

2, Understanding Users: Read only, write only, backup, manage, Access specific database, table

3, bit work category

4, ensure absolutely necessary this has GRANT option permission

MySQL performance tuning and architecture design Note: Three MySQL security management