Border = "0" marginwidth = "0" marginheight = "0" src = "/ ads / ad_txt_tom300.htm" frameborder = "no" width = "302" scrolling = "no" height = "160"> mysql has a set of advanced However, non-standard security / authorization system, mastering its authorization mechanism is the first step in which the mysql database must be taken. For a person who is familiar with SQL basic operation, it is also a part of Mysql all knowledge. . By uncovering the operation mechanism of its authorization system, I hope that everyone can better operate and use this excellent database system.
This article mainly refers to some of the contents in Chapter 6 of the User's Manual attached to Mysql installation. If you have any questions, please contact me (lidong@wh.027.net)
1. What is the main role of authorization mechanism?
The basic role of the authorization mechanism is to give the user on a host to SELECT, INSERT, UPDATE, and DETETE permissions. The additional features also include allowing anonymous to use a database, using some specific functions of MySQL, such as: Load Data Infile. Note here, please note that the username in MySQL and the username in the UNIX system do not matter. Although many client programs allow you to log in with the current username, the most standard approach is or through -user options. 2. How does the authorization mechanism work?
The combination of hosts and users in MySQL is considered unique to the unique logo. For example, the user Lee on the host 1 and host 2 is actually different, and their use of mysql can also be different. The core issue of the entire authorization mechanism is to resolve the use of the user's use of a user logged in from a host. You can test the permission of the user's database operation on the host by script mysqlaccess. All authorization information is stored in the User, Host, and DB tables of database MySQL. We can connect to this database via mysql mysql, and display the contents in each data table via Select * from User (or DB, Host). The permissions granted in the User table are basic authorizations of the entire authorization mechanism, that is, the definition in the user is suitable for any user host, unless otherwise defined in the DB table, therefore, for the user It is best to authorize a database based on a database. The main purpose of the Host table is to maintain a list of "security" servers. When the permission to consider a certain user / host on a database, we also need to study the matching search mechanism of the authorization mechanism:
Second, in the authorization mechanism, a user's password can be encrypted, and the method that must be encrypted and encrypted is Password ('password'). If you fill in the password directly, it will cause the database to be unacceptable. The permissions granted in the User table are basic authorizations of the entire authorization mechanism, that is, the definition in the user is suitable for any user host, unless otherwise defined in the DB table, therefore, for the user It is best to authorize a database based on a database. The main purpose of the Host table is to maintain a list of "security" servers. When the permission to consider a certain user / host on a database, we also need to study the matching search mechanism of the authorization mechanism:
First, we need to introduce the concept of unmanage characters, including "%", which means arbitrary (host, user or database), and if a record is empty, it also means any meaning. Second, in the authorization mechanism, a user's password can be encrypted, and the method that must be encrypted and encrypted is Password ('password'). If you fill in the password directly, it will cause the database to be unacceptable. From our display of these three tables, we can see that each record in these three tables contains a description of a user's license, several related authorization mechanisms in the mysql database are searched. The order is: User, DB, Host. That is, we will first retrieve the USER data table, find the first matching record, we call the recorded record in the USER data table first match the priv; then search the DB table, get the appropriate authorization. If the Host field is empty in the DB data table, and the host is also included in the Host field of the HOST table, in this case, for a user, you can add it in the User table. Some of the "Y" permission settings are set. If the HOST field in the DB table is not empty, then it will not affect the authorization of the user / host. After understanding this, we need to discuss the priority of the search for records in various data sheets, that is, how to determine the first match record, this is not the natural order in accordance with the record in the data sheet. definite. The priority arrangements of each record in each data sheet are as follows: (1) USER Table: Determine the order of User after the first Host. The search rules are as follows: do not include the records of the strokes, including records, empty records. In the same HOST, continue to arrange, rules, and the above as described in User. (2) DB table: The order of retrieval is determined according to the Host field: does not include a record of the stroke character, contains the record, empty record. (3) HOST table: The order of the retrieval is determined according to the Host field: does not include a record of the stroke, including the record, empty record. We use the following example to explain the rules that match the search: Remember if you change these data tables, you must use mysqladmin reload to take effect. Below is how the demo system is search: --------- ------- - | Host | User | ... ------- ---- --------- - |% | root | ... |% | jeffrey | ... | localhost | root | ... | localhost | | ... - -------- -------- - The order in which you should be: localhost / root localhost / any any / jeffrey any / root, if you want to connect to the database in LocalHost users Jeffrey If the authorization shall be based on the permissions specified by the localhost / "Arbitrary" line, please pay attention to this, because if an inappropriate configuration is completely likely to make you unable to make it normally Use this database system.
Let's take a look at an example of adding a user: need to add a "Custom" user, he connects from the host 'localhost', 'Server.domain' and 'Whitehouse.Gov' to the database, his password is " "Stupid", for the database 'BankAccount' he just wants to access from "localhost", and the "Customer" database should be accessed by the above three hosts. We do its operations through the following SQL statements. Shell> mysql mysql. mysql> INSERT INTO USER (Host, User, Password) Values ('localhost', 'Custom', Password ('stupid')); mysql> Insert Into User (Host, User, Password) VALUES (' Server.domain ',' Custom ', Password (' stupid ')); MySQL> Insert Into User (Host, User, Password) VALUES (' Whitehouse.gov ',' Custom ', Password (' stupid '));
mysql> insert into db (host, db, user, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv) values ( 'localhost', 'bankaccount', 'custom', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); MySQL> INSERT INTO DB (Host, DB, User, SELECT_PRIV, INSERT_PRIV, UPDATE_PRIV, DELETE_PRIV, CREATE_PRIV, DROP_PRIV) VALUES ('%', 'Customers ",' Custom ' , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y');
3. Authorized data sheet
Authorized Data Sheets Targets include SELECT, INSERT, UPDATE, and DELETE, the operations for tables and databases include Create and DROP. Other authorizations include Load Data INFILE and SELECT INTO OUTFILE and management commands: Shutdown, Reload, Refresh and Process. The three authorized data sheets are as follows: USER Table Field Type of Marthen Note Host Char (60) PRI " "User Char (16) PRI" "Password Char (16) -" "SELECT_PRIV ENUM ('N', 'Y') - N INSERT_PRIV Enum ('N', 'Y') - n Update_Priv Enum ('N', 'Y') - N delete_priv enum ('n', 'y') - n create_priv enum ('n', 'y') - n Drop_Priv Enum ('n', 'y') - N Reload_priv enum ('N ',' Y ') - N Shutdown_Priv Enum (' N ',' Y ') - N Process_Priv Enum (' N ',' Y ') - N File_Priv ENUM (' N ',' Y ') - NDB Table Field Type Makada recognition Host Char (60) Pri "" DB Char (64) Pri "" User Char (16) PRI "" SELECT_PRIV ENUM ('N', 'Y') - N INSERT_PRIV ENUM ('N', 'Y' ) - n Update_priv enum ('n', 'y') - n delete_priv enum ('n', 'y') - n create_priv enum ('n', 'y') - n Drop_Priv Enum ('n', ' Y ') - N
The Host table is only used in the case where Host appears empty in the DB data item. Field Type BMA recognition Host Char (60) Pri "" DB Char (64) Pri "" SELECT_PRIV ENUM ('N', 'Y') - N INSERT_PRIV Enum ('N', 'Y') - n Update_Priv Enum 'N', 'y') - n delete_priv enum ('n', 'y') - n create_priv enum ('n', 'y') - n Drop_Priv Enum ('n', 'y') - N- N- N- N DRETE_PRIV ENUM
You can use the host symbol in the data table.
4, the most common cause of Access Denied error
(1) Whether you create a mysql license table via mysql_install_db script, you can test via mysql -u root, and should not have errors if it is correct. Or, do you have a file as: user.ISD, usually its location is in install_dir / var / mysql / user.ISD. (2) When you initially use, you should use mysql -u root mysql to access the database, or operate as root. (3) Do I use MySQLADMIN RELOAD to update after authorization? (4) When the purpose is tested, you should use the --without-grant-table option to start the MySQLD service, you can change the relevant content of the license table at this time, or use mysqlaccess to check if your authorization is in place. (5) No password is set, the result will also occur, and when using the -p option, there is no space between -ppassword. 5, how to make MySQL more secure?
(1) Use the password for each mysql user. Remember, if you don't add a password, others can access your database via mysql --user other_user database, and the system will also give you the corresponding warning information when using MySQL. (2) Do not start the MySQL service with the root method. MySQL can be started in any user. You can start database services by adding a new user. This will not have an impact on the system, because Mysql users and Unix users are different. (3) Do not give any permissions such as 'Process_Priv', 'File_Priv'. (4) If you don't trust your DNS, you should use IP to replace the host name. Be careful with the host name with the match under any circumstances. Some of the following options can affect the security of your system: - Secure as the names, you can get the system to get safe because it can check the consistency of the IP address. (Really connected IP addresses and by parsing IP addresses), but this makes MySQL to be visited by people outside the firewall when the firewall is working. - Skip-grant-tables Normally do not use this option, which can be tried to access your system unrestrictedly. - Skip-name-resolve does not parse the host name. All hostnames in the authorized data sheet must be IP addresses or 'localhost'. --Skip-networking is not allowed to be connected via the network. All connections must pass through UNIX Socket.
