Starting with version 3.22.11, MySQL provides a simple way to manage users' access. Using Non-Standard Commands Grant and Revoke provided by MySQL, you can create users and give them its appropriate permissions without having to care about the storage form in the five tables mentioned earlier. Use the grant grant command to create a new user, specify the user password and increase user permissions. Its format is as follows: mysql> grant on -> to [Identified By "] -> [with grant option]; as you can see, there are many content to be filled in this command. Let us introduce them one by one, and eventually give some examples to make you have a understanding of their collaboration. Is a list of permissions you want to give in a comma. You can specify the permissions can be divided into three types: Database / Dataset / Data Route Permission: ALTER: Modify the existing data table (for example, add / delete columns) and indexes. CREATE: Create a new database or data sheet. Delete: Delete the record of the table. Drop: Delete data tables or databases. Index: Establish or delete an index. Insert: Increase the record of the table. SELECT: Displays the record of the search table. Update: Modify the record already existing in the table. Global Management Permissions: File: Read and write files on the MySQL server. Process: Display or kill service threads belonging to other users. Reload: Heavy Duty Access Control Table, Refresh Log, etc. Shutdown: Close the MySQL service. Special permissions: All: Allow anything (like root). USAGE: Only logins - Other do not allow it. The characteristics of MySQL involved in these permissions, some of which we have not seen yet, and most of them are familiar. The area for these permissions is defined. *. * Means permission valid for all databases and data sheets. DBNAME. * means that all data tables in the database named DBNAME are valid. DBNAME.TBLNAME means that only data tables named TBLNAME named DBNAME are valid. You can even use the list of data columns in parentheses after the permissions given to specify only these columns (later we will see such examples). Specifies users who can apply these privileges. In MySQL, a user is specified by the host name / IP address of the username and the computer used by the user. Both values can use% wildcard (such as Kevin @% will allow you to log in from any machine from any machine to enjoy the permissions you specify). Specifies the password used by the user to connect the MySQL service. It is enclosed in square brackets, indicating that Identified By "" is an option in the grant command. The password specified here will replace the original password. If you do not specify a password for a new user, you don't need a password when he is connected. The optional WITH GRANT OPTION section in this command specifies that the user can use the grant / revoke command to give other users to other users. Please use this feature carefully - although this problem may not be so obvious! For example, users who have this feature may share their permissions to each other, which may not be seen at first. Let us see two examples.
Create a user named DBManager, he can use the password managedb to connect MySQL from Server.host.net, and only access all the contents of the database named DB (and you can give this permission to other users), which can be used below GRANT command: mysql> grant all on db. * -> to dbmanager@server.host.net -> Identified by "managedb" -> with grant option; now changing the user's password for funkychicken, command format as follows: mysql> Grant Usage on *. * -> to dbmanager@server.host.net -> Identified by "funkychicken"; note that we did not give any additional privileges (the usage permission can only be logged in), but the user already exists Will be changed. Now let us build a new user-named Jessica, he can connect from any machine from the Host.NET field to MySQL. He can update the name of the user in the database and the email address, but do not need to check the information about other databases. That is to say that he has only permission to the DB database (for example, SELECT), but he can perform an UPDATE operation on the Name column of the UserS table and the email column. The command is as follows: mysql> grant select on db. * -> to jessica@%.host.net -> Identified by "jssrules"; mysql> grant update (name, email) on db.users -> to jessica@%.host .NET; Please note that in the first command we use the% (wildcard) symbol when specifying the host name that Jessica can be used to connect. In addition, we did not give him the ability to deliver his permissions to other users, because we didn't bring the with grant option in the last command. The second command demonstrates how to give permissions to specific data columns with a comma-separated column in parentheses behind the permissions. Using Revoke As you expect, the revoke command is used to remove the permissions previously given. The syntax of the command is as follows: mysql> revoke [()] -> on from; the functionality of each part in this command is the same as the above gravine. To remove Jessica's DROP permission (for example, if he often incorrectly deletes the database and table), you can use the following command: mysql> revoke drop on *. * From idiot@%.host.net; remove a user The login permission is probably unique to use revoke. Revoke all on *. * Removes all the permissions of the user, but he can also log in. To completely delete a user, you need to delete the corresponding record in the USER table: mysql> delete from user -> Where user = "iDiot" And host = "%. Host.net"; Access Control Skills Due to the impact of access control system work in MySQL, you must know two features before establishing your users. When the established user can only log in to the MySQL service from the computer running in MySQL service (that is, you need their telnet to the server and run the MySQL client program, or use server-side scripting languages like PHP Communication), you probably ask some contents that should be filled with your own grant command. If the service is running at www.host.net.
