Mysql Chinese Reference Manual 7 (MySQL Access Right System) GRANT Setting Password Password

Mysql Chinese Reference Manual 7 (MySQL Access Right System) Reprinted

Translator: 子

〖

Back to〗 Forward〗

Translator: 子 (Clyan@sohu.com) Home: http://linuxdb.yeah.net6 MySQL Access Permission System Mysql has an advanced but non-standard security / permission system. This section describes its working principle. 6.1 Permission System What is the main function of the mysql permission system to confirm a user connected to a given host and give the user on a database. SELECT, INSERT, UPDATE, and DELETE. Additional features include an anonymous user and the ability to authorize and manage operations for MySQL-specific features such as LOAD DATA INFILE. 6.2 MySQL User Name and Passwords There are many different ways to use username and password by mysql:

MySQL is not related to the username of authentication purposes, unrelated to UNIX username (login name) or Windows username. By default, most mysql customers have tried to use the current UNIX username as a MySQL username, but this is only for convenience. The client allows you to specify a different name with the -u or --user option, which means that you can't make a database safer, unless all MySQL usernames have passwords. Anyone can try to connect to the server with any name, and if they specify any name without a password, they will succeed. MySQL username can be 16 characters; typically, UNIX username is limited to 8 characters. MySQL password does not matter to UNIX password. There is no need to be associated between your use of logging in to a UNIX machine password and your password you use on that machine. MySQL encryption password uses different algorithms used during UNIX login, see Section 7.4.12 Miscellaneous Function section describes the Password () and Encrypt () function portions. 6.3 Connection with MySQL Server When you want to access a MySQL server, MySQL client generally requires you to specify the connection parameters: You want to join the host, your username and your password. For example, MySQL customers can start this (optional parameters are included between "[" and "]"): shell> mysql [-h host_name] [- user_name] [- pyour_pass] -h, -u and Another form of the -p option is --Host = host_name, - user = user_name, and --password = your_pass. Note that there is no space between -p or -password = and the password followed by following it. Note: Specify a password on the command line is not safe! Any user on your system can discover your password by hitting such a command: PS auxww. See 4.15.4 Options file. For the connection parameters, mysql uses the default value:

The default host name is Localhost. The default user name is your UNIX login name. If there is no -p, no password is provided. This way, for a UNIX user Joe, the following command is equivalent: shell> mysql -h localhost -u joe shell> mysql -h localhost shell> MySQL -U Joe Shell> MySQL Other MySQL client has the same performance. On the UNIX system, when you make a connection, you can specify the different default values ​​to use so you don't have to call a client every time you enter them on the command line. This can have many ways to do: You can specify the connection parameters in the [Client] section of the configuration file of ".my.cnf" in your home directory. The relevant section of the file seems to be like this: [client] host = host_name user = user_name password = Your_pass See 4.15.4 Options file. You can specify connection parameters with environment variables. The host can be specified by mysql_host, the MySQL user name can be specified by User (only for Windows only), the password can be specified by mysql_pwd (but this is not safe, see the next section). If the connection parameter is specified in a variety of methods, the value specified on the command line is preferentially specified in the configuration file and the value specified in the environment variable, and the value specified by the configuration file is preferentially specified at the environment variable. 6.4 Make your password security with a way to specify your password by other users discovered. When you run a customer program, you can use the following methods to specify your password, and the risk assessment of each method:

Use one on the command line -Pyour_pass or -password = your_pass. This is very convenient but not safe, because your password is visible to the system status program (eg, PS), it can be called to display the command line by other user calls. (General MySQL customers use zero override command line parameters during their initialization, but still have a short interval time visible.) Use a -p or -password option (not specified Your_Pass value). In this case, the client program requests password from the terminal: shell> mysql - user_name - pen password: ******* Customer responds to "*" characters to the terminal as entering your password makes the bystander can't See it. Because it is not visible to other users, it is safer to enter your password compared to the command line. However, this method of entering a password is only suitable for your interactive running program. If you want to call a customer from a script running from non-interactive, there is no opportunity to enter the entry command from the terminal. Store your password in a configuration file. For example, you can list your password in [Client] in your home directory: [client] password = your_pass If you store passwords in ".my.cnf", the file should not Group or world readable or writable. Ensure that the access mode of the document is 400 or 600. See 4.15.4 Options file. You can store passwords in the mysql_pwd environment variable, but this method must be very unsafe and should not be used. Some of the PS includes options that display the environment of the running process; if you set mySQL_PWD, your password will be obvious to everyone, even on the PS system without such a version, it is assumed that there is no other method to observe the process environment. It is unbroken. In short, the safest way is to let the customer program prompt the password or specify a password in a properly protected ".my.cnf" file. 6.5 MYSQL Provided Permission Permissions Information Use User, DB, Host, Tables_Priv and Column_Priv tables in the MySQL database (ie in a database called MySQL). When Mysql is started and when the 6.9 permissions modify, the server reads the contents of these database tables. This manual is displayed in the following table, as well as a table name name and each permission in the license table, the context related to each permission: Permissions Column Context SELECT SELECT_PRIV Table Insert Insert_Priv Table Update Update_Priv Table Delete delete_priv table index Index_priv table alter Alter_priv create Create_priv database tables, database table or index drop Drop_priv grant Grant_priv table or database or table, or database table references References_priv reload Reload_priv management server management server shutdown Shutdown_priv process Process_priv server management file File_priv access files on the server SELECT, INSERT, UPDATE, and DELETE permissions allow you to perform operations on a database existing table.

SELECT statement only requires SELECT permissions if they retrieve lines from a table, you can perform a SELECT statement, and do not even have anything to access anything in the database. For example, you can use MySQL customers as a simple calculator: mysql> select 1 1; mysql> select pi () * 2; INDEX permissions allow you to create or discard (delete) indexes. Alter permissions allow you to use ALTER TABLE. CREATE and DROP permissions allow you to create new databases and tables, or discard (delete) existing databases and tables. Note: If you grant the mysql database's DROP permissions, the user will abandon the database that stores MySQL access rights! GRANT permissions allow you to give yourself to other users. FILE Permissions Give you a file on your Load Data Infile and SELECT ... INTO OUTFILE statement, any file that is granted this permissions can be read or written or written or written. The rest of the permissions are used to manage, it is implemented using the mysqladmin program. The following table shows the mysqladmin domain to give you commands to allow you to execute: Offers Handlers Allowed commands Reload Reload, refresh, flush-privileges, flush-hosts, flush-logs, flush-table Shutdown Shutdown Precess ProcessList, Kill The RELOAD command tells the server to read the authorization form, the refresh command cleans all the tables and turns on the record file, flush-privileges is a synonym of the relunts, and the other flush- * commands perform similar refresh functions, but the scope is more limited, and in some It may be more useful in some cases. For example, if you just want to clean the record file, Flush-logs is better than Refresh. The shutdown command turns off the server. The processList command displays the information of the thread executing within the server. Kill Command Kill the server thread. You can always show or kill your own thread, but you need Process permissions to display or kill threads that other users start. In general, only grant authority to those users who need them are a good idea, but you should test a specific warning when awarded a permission:

GRANT permissions allow users to give up their permissions to other users. Two users with different permissions and GRANT permissions can merge permissions. ALTER permissions can be used to overturn the permissions system by rename the table. File permissions can be abused on the server to read any world readable files to a database table, and then its content can be accessed with SELECT. Shutdown Permissions can be abused by the termination server to completely refuse to serve other users. Precess permissions can be used to check the ordinary text of the currently executed query, including setting or change password query. Permissions on the MySQL database can be used to change passwords and other access rights information. (Password is encrypted, so a malicious user cannot simply read them. However, there is enough permissions, the same user can replace a password with a different one.) There are things you can't use the mysql permission system: You can't clearly specify a given user to be rejected. That is, you can't significantly match a user and then refuse to connect. You can't specify a user who has the right to create or abandon a database in a database, and cannot create or discard the database itself. 6.6 Permissions System Working Principles MYSQL Permissions System Ensure that all users can strictly assume what they have been allowed. When you connect a MySQL server, your identity is determined by the host from the connected host and the user name you specify, the system grants permissions based on your identity and what you want to do. MySQL considers your hostname and user name in the identity, because there is a small reason to assume a given user to the same person on the Internet. For example, users connecting Bill from Whitehouse.gov does not have to be the same person from MOSOFT.COM. MySQL processes it by allowing you to distinguish between different hosts: You can give a different permissions set from the Whitehouse.gov connection with Bill, and grant a different permission set from Microsoft.com. MySQL Access Control contains 2 phases:

Phase 1: The server checks if you are allowed to connect. Phase 2: Assume that you can connect, the server checks for each request you emit. See if you have enough permissions to implement it. For example, if you select a table from a table in the database or discard a table from the database, the server determines that you have SELECT permissions or have DROP permissions on the database. The server uses the USER, DB and HOST tables in the MySQL database at two phases of the server, in which the fields in these authorization tables are as follows: Table Name User DB Host Range Field Host Host Host User DB DB Password User Permissions Field SELECT_PRIV Select_priv Insert_priv Insert_priv Insert_priv Update_priv Update_priv Update_priv Delete_priv Delete_priv Delete_priv Index_priv Index_priv Index_priv Alter_priv Alter_priv Alter_priv Create_priv Create_priv Create_priv Drop_priv Drop_priv Drop_priv Grant_priv Grant_priv Grant_priv RELOAD_PRIV SHUTDOWN_PRIV Process_Priv file_priv The second phase of the access control (request confirmation), if the request involves the table, the server can additionally refer to the Tables_Priv and Column_Priv table.

Field these tables are as follows: Table Name tables_priv columns_priv range of fields Host Host Db Db User User Table_name Table_name Column_name rights field Table_priv Column_priv Column_priv other fields Timestamp Timestamp Grantor Each grant table contains scope fields and privilege fields. The range field determines the scope of each entry in the table, that is, the context that the entries apply. For example, a USER table entry Host and User values ​​are 'Thomas.loc.gov' and 'Bob' will be used to confirm Bob 'from host Thomas.loc.gov's connection. Similarly, the value of a DB table entry Host, User and DB fields is 'Thomas.loc.gov', 'Bob' and 'Reports' will be used in Bob from the host connection Thomas.loc.loV to access the Reports database. Tables_priv and columns_priv tables contain range fields that indicate a combination of tables or tables / columns for each entry. For the purpose of checking access, the comparison Host value is ignored. USER, Password, DB and Table_name values ​​are case sensitive. The column_name value is ignored in mysql3.22.12 or later. The permissions field indicates the permissions granted by a table entry, that is, what operations can be implemented. The server combines various authorization forms to form a complete description of a user privilege. The rules used here are 6.8 Access Control, Phase 2: Request to confirm the description.

The range field is a string, as described below; the default value of each field is empty string: Field name type Host Char (60) User char (16) Password char (16) DB Char (64) (Tables_Priv and Column_Priv Table For char (60)) in the user, db, and host tables, all permissions fields are declared as enum ('n', 'y') - each of which can have value 'n' or 'y', and default The value is 'n'. In the Tables_Priv and Columns_Priv table, the permissions field is declared as a set field: the table name field name may set members TABLES_PRIV TABLE_PRIV 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE' , 'Drop', 'Grant', 'References', 'Index', 'Alter' tables_priv Column_priv 'Select', 'Insert', 'Update', 'References' columns_priv Column_priv 'Select', 'Insert', 'Update' , 'References' briefly, the server uses such an authorization form:

The USER table range field determines if the connection is allowed or rejected. For the allowable connection, the permissions field indicates the user's global (superuser) permission. DB and HOST tables are used together: DB table range field determines which host can access which database can be accessed. Permissions fields determine which one is allowed. When you want a given DB entry to a number of hosts, the Host table is used as an extension of the DB table. For example, if you want a user who can use a database from a number of hosts in your network, you have an empty value in the user's DB table, and then transfer each of those hosts into the Host table. This mechanism describes in detail at 6.8 access control, stage 2: request confirmation. Tables_priv and columns_priv tables are similar to the DB table, but more refined: they are applying in the table and column levels instead of being in the database level. Note Management Permissions (Reload, ShutDown, etc.) is only specified in the USER table. This is because management operations are the operation of the server itself and is not a specific database, so there is no reason to list such permissions in other authorization tables. In fact, just ask the User table to determine if you execute a management action. File permissions are also specified in the USER table. It is not administrative privilege, but your ability you read or thank the files on the server host independently of the database you are accessing. When the MySQLD server is started, read a license table content. Changes to the authorization form take effect at 6.9 permission changes to the description. When you modify the content of the authorization form, make sure you change the permissions settings in the way you want to change your permissions. To help diagnose problems, see 6.13 "Access Refuse to cause" wrong. For the advice on security issues, see how to make Mysql security to the masters of the masters 6.14. A useful diagnostic tool is a mysqlaccess script that is provided by Carlier Yves to mysql distribution. Use the --help option to call mysqlaccess to find out how it works. Note: MySQLACCESS only checks only by User, DB, and HOST tables. It does not check the list or level permission. 6.7 Access Control, Phase 1: Connection confirmation When you try to join a MySQL server, the server is based on your identity and whether you can accept or refuse to connect by supplying the correct password verification. If not, the server completely nested your access, otherwise, the server accepts the connection, then enter the phase 2 and wait for the request. Your identity is based on 2 information: You connect your MySQL username authentication from that host to use 3 USER tables (Host, User and Password) range fields. The server only accepts the connection when you match your hostname and username in a USER table entry and you provide the correct password. The USER table range can be specified as follows:

A host value can be a host name or an IP number, or 'localhost' pointing out the local host. You can use wildcard characters "%" and "_" in the Host field. A host value '%' matches any host name, a blank Host value equivalent to '%'. Note These value matches can create any host connecting to your server! Wildcard characters are not allowed in the User field, but you can specify a blank value that matches any name. If the User table matches the entry of the connection with a blank username, the user is considered an anonymous user (no name, not the name actually specified. This means that a blank username is used for further access checks during the connection (ie, during phase 2). The Password field can be blank. This doesn't mean matching any password, which means that the user must not specify a password to connect. Non-blank Password values ​​represent the password encrypted. Mysql does not store passwords in a pure text format that anyone can see. In contrast, the password provided by one user who is trying to join (using the password () function) and compares the encrypted version stored in the USER table. If they match, the password is correct. The following example shows how the combination of Host and User entries in various USER tables is applied to the connection: Host Value User Value is enclosed by the entries 'Thomas.loc.gov' 'Fred' Fred, from Thomas.loc .gov connection 'thomas.loc.gov' '' any user, from Thomas.loc.gov connection '%' 'Fred' Fred, from any host connection '%' 'any user, from any host connection'% .loc .gov '' Fred 'Fred, connect' XY% '' Fred 'Fred from any host in the Loc.gov domain, from XYNET, XYCOM, XYEDU, etc. (This may be useless) '144.155.166.177' 'Fred' Fred, from the host connection of 144.155.166.177 IP address' 144.155.166.% 'Fred' Fred, any host connection from 144.155.166 C subnet You can use the IP wildcard value (for example, '144.155.166.%') On a subnet), it is possible that someone may try to explore this capability. It is 144.155.166 by name a host. .somewhere.com. To prevent such an attempt, MySQL does not allow the host name starting with a number and a point, so if you use a host named 1.foo.com's host, its name will never match the Host Column in the license list. . Only one IP number can match the IP wildcard value. A arrival connection can be matched in the User table. For example, a connection from the connection from Thomas.loc.gov Multiple entries as described above. If more than one match, how do the server choose which entry? The server reads the User table after starting by sorting this problem, and then browsing the entry in the order of sorting, the first matching entry is used when a user tries to connect.

User Table Sorting works as follows, assuming that the USER table looks like this: ----------- -------- - | Host | User | ... - --------- ---------- - |% | root | ... |% | jeffrey | ... | localhost | root | ... | localhost | ... ----------- ---------- - When the server is read in the table, it is arranged in the first order of the most specific HOST value ( '%' Means "any host" in Host Lee and is the least specific). Entries with the same Host value are arranged in the first order of the most specific USER (a blank user value means "any user" and is the least specific). The final sorted User table looks like this: --------- -------- - | Host | User | ... -------- ----- ---------- - | localhost | root | ... | localhost | | ... |% | JEFFREY | ... |% | root | ... ----------- ---------- - When a connection is tried, the server browsses the sorted entry and uses the first match that is found. For a connection from the Localhost from Jeffrey, the 'localhost' entry of the Host column matches. Those entries with blank user names match the host name and username of the connection. ('%' / 'Jeffrey' entry will also match, but it is not the first match in the table.) This is another example. Assume that the USER table looks like this: ---------------- -------- - | Host | User | ... --- ------------- ---------- - |% | Jeffrey | ... | thomas.loc.gov | | ... ---- ------------ ---------- - Sorted table looks like this: ------------- - ---------- - | Host | User | ... -------------- ---------------------------- | Thomas.loc.gov | | ... |% | Jeffrey | ... -------------- ---------- - A connection from Jeffrey from Thomas.loc.gov is matched by the first entry, and a connection from Jeffrey from Whitehouse.gov is matched second. The universal misunderstanding is that it is considered to be a given username, when the server tries to match the connection to match, clearly named all the users will be first used. This is obviously not the fact. Previous examples illustrate this, where a connection from the Jeffrey from Thomas.loc.gov is not included in the entry that the 'jeffrey' is used as the User field value, but matches the topic without the username! If you have a server connection, print out the USER table and manually sort it. Look at where the first matches are performed.

6.8 Access Control, Phase 2: Request confirming that once you have established a connection, the server enters stage 2. For each request coming in here, the server checks if you have enough permissions to perform it, which is based on the type of operation you wish to perform. This is where the authority field in the license list works. These privileges can be coming from any one of a child User, DB, Host, Tables_Priv, or columns_priv. Authorization mesh with the Grant and Revoke command operations. See 7.26 GRANT and REVOKE syntax. (You can find out how to work with the 6.6 permission system is very helpful, which lists the fields present in each permission table.) The USER table is awarded the permission to give you on a global basis, which is no matter what the current database is Each applicable. For example, if the USER table grants you DELETE permissions, you can delete the row from any database on the server host! In other words, the USER table permissions are superuser privileges. It is wise to grant the permissions of the USER table to super users such as servers or database. For other users, you should set the permissions in the USER table to 'n' and authorize on the basis of a specific database, use the DB and HOST tables. DB and HOST tables granted database specific permissions. The value of the range field can be specified as follows: wildcard characters "%" and "_" can be used for the Host and DB fields of the two tables. The '%' Host value of the DB table means "any host", a blank Host value in the DB table means "consult a further information". A '%' or blank Host value in the Host table means "any host". A '%' or blank DB value in both tables means "any database." Match anonymous user in a blank user value in two tables. DB and HOST tables are read and sorted when the server is started (but it reads the USER table). The DB table is sorted on the Host, DB and User range fields, and the HOST table is sorted on the Host and DB range fields. For the USER table, the sort first places the most specific value and finally the most unspecified value, and when the server finds the entries, it uses the first match it finds. Tables_priv and columns_priv table grants tables and column specific permissions. The value in the range field can be specified as follows:

Wildcard "%" and "_" can be used in the HOST field used in two tables. One '%' or blank Host in two tables means "any host". DB, table_name and column_name fields in two tables cannot contain wildcard or blank. Tables_priv and columns_priv tables are sorted on Host, DB, and User fields. This is similar to the sort of the DB table, although because only the Host field can contain wildcards, sorting is simpler. Request confirms the process in the following description. (If you are familiar with the source code for access check, you will notice that the description here is slightly different from the algorithm used by the code. Description is equivalent to the actual code; it is just simpler.) Request (ShutDown, Reload, etc.), the server only checks the USER table entry because it is the only table that specifies the administrative privilege. If the entry license request is authorized, access is authorized, otherwise it will be rejected. For example, if you want to execute mysqladmin shutdown, your USER table entry does not grant ShutDown permissions for you, access or even check the DB or HOST table is rejected. (Because they do not include the shutdown_priv line, there is no need to do so.) Request for databases (INSERT, UPDATE, etc.), the server first looks for the user's global (superuser) permission. If the entry allows the request to be operated, access is authorized. If the global permissions in the USER table are not enough, the server determines a specific user database permissions by checking the DB and HOST tables:

The server finds a match on the Host, DB and User fields of the DB table. Host and User correspond to the host name and MySQL username connected to the user. The DB field corresponds to the database you want to access. If there is no entry of Host and User, access is rejected. If the entry in the DB table has a match and its Host field is not blank, the entry defines the user's database-specific permissions. If the Host field of the entries of the matching DB table is blank, it indicates that the Host list is listed that the host should be allowed to access the host. In this case, further look up in the Host table to discover the match on the Host and DB fields. If there is no Host table entry match, access is rejected. If there is a match, the user database-specific permissions are calculated in the intersection of the entry of the DB and Host tables, that is, the intersection of 'Y' permissions (not panel!) Calculated. (This allows you to grant a general permissions in the DB table entry, and then use the HOST table entry to select a host to select them based on a host.) After determining the specific permissions granted by DB and Host table entries The server adds them to global permissions granted by the USER table. If the result allows the request to operate, access is authorized. Otherwise, the server checks the table and column permissions of the user in the Tables_Privns_Priv table and add them to user permissions. Allow or reject access based on this result. With Boolean, a description of how to calculate a user permissions can be summarized: Global Privilegesor (Database Privilegesor Column Privilege "It may not be obvious, why, if the global user entries are permissions to find requests Not enough operation, the server will add this permissions to the specific permissions of the database, tables, and columns. The reason is that a request may require more than one type of permissions. For example, if you perform an insert ... SELECT statement, you have to INSERT and SELECT permissions. Your permissions must be so so that the USER table entry grants a permission and the DB table entry grands another one. In this case, you have the necessary permission execution request, but the server cannot distinguish between the two tables; the permissions granted by the two entries must be combined. The Host table can be used to maintain a "secure" server list. In TCX, the Host table contains a table of all machines on the local network, which is granted all permissions. You can also use the HOST table to specify unsafe hosts. Assuming that you have a machine public.your.domain, it is located in a public area you don't think is safe, you can use the following Host table entry allows access to all hosts from the network outside the machine: - ------------------ ---- - | Host | DB | ... ------------------------------------------------------------------------------------------------ ---- ---- - | Public.Your.Domain |% | ... (All permissions are set to 'n') |% .your.domain |% | ... (All permissions are set to " Y ') -------------------- - - Of course, you should always test your entry in the license list (for example, using mysqlaccess ) Let you make sure your access rights are actually set in a way you think. 6.9 Permissions Change When to take effect When MySQLD is started, all authorization form is read into the memory and take effect from that point. The modification of the authorization form will be noted immediately by the server immediately with Grant, Revoke or Set Password.

If you modify the authorization table manually (using INSERT, UPDATE, etc.), you should execute a Flush Privileges statement or run mysqladmin flush-privilege to tell the server to load the authorization form, otherwise your change will not take effect unless you restart the server. When the server notes that the authorization table is changed, the existing customer connection has the following effects: Table and column permission take effect at the next request of the customer. Database permission changes take effect in the next USE DB_NAME command. Changes in global permissions and password changes take effect in the next customer connection. 6.10 Establishing an initial mysql permission After installing MySQL, you install the initial access permissions by running scripts / mysql_install_db. See 4.7.1 Quick installation overview. Scripts / mysql_install_db script launches the MySQLD server, then initializes the license table, contains the following permissions collection:

Mysql root users are created as a super user who can do anything. The connection must be issued by the local host. Note: The born root password is empty, so anyone can connect without a password in root and is granted all permissions. An anonymous user is created, and he can do any period of a database with a 'Test' or the name starting with 'Test_', the connection must be issued by the local host. This means that any local user can connect and treat anonymous users. Other permissions are rejected. For example, a general user cannot use mysqladmin shutdown or mysqladmin processlist. Note: The initial permissions for Win32 are different. See 4.12.4 Run mysql on Win32. Since your installation initially opened the door, one of the things you should do is to specify a password for MySQL root users. You can do this (note, you use the password () function specified password): shell> mysql -u root mysqlmysql> Update user set password = password ('new_password') where user = 'root'; mysql> flush privileges; in MySQL In 3.22 and above, you can use the set password statement: shell> mysql -u root mysqlmysql> set password for root = password ('new_password'); another way to set your password is to use mysqladmin command: shell> mysqladmin -u Root password new_password Note: If you use the first method to update your password in the USER table, you must tell the server to read the authorization table again, because otherwise the change will not be noted. Once the root password is set, you must supply the password when you connect to the server as root. You may want the root password to be blank so you don't need to specify it or test when you implement additional installation, but it is guaranteed to use your installation before any real production work. Take a look at the scripts / mysql_install_db script to see how it installs the default permissions. You can use it as a foundation for how to increase other users. If you want the initial permissions to be different from those described above, you can modify it before you run mysql_install_db. In order to completely rebuild the ration form, remove all "* .frm", "*. Myi" and "* .myd" files in the directory containing the MySQL database. (This is a directory named "mysql" below the database directory. When you run mysqld --help, it is listed.) Then run the mysql_install_db script, which may first edit it to have the permissions you want. Note: For olders than MySQL 3.22.10, you should not delete the "* .frm" file. If you accidentally do, you should copy them in your MySQL distribution before running mysql_install_db. 6.11 Adding new user permissions to MySQL You can have 2 different ways to increase users: By using a GRANT statement or by direct operating the MySQL authorization table. A better way is to use the GRANT statement because they are more concise and seems to be less. The following example shows how to install new users using the MySQL client. These examples assume that rights are installed according to the default described by the previous section.

This means that in order to change, you must run on the same machine at MySQLD, you must be connected as a MySQL root user, and the root user must have INSERT permissions to the mysql database and RELOAD administration. Also, if you change the root user password, you must specify it as the following mysql command. You can add new users by issuing a GRANT statement: shell> mysql --user = root all priviles on *. * To monty @ localhost iDentified by 'Something' with grant option; mysql> grant all privileges on *. * To MONTY @ "%" Identified by 'Something' with grant option; mysql> grant reload, process on *. * to admin @ localhost; mysql> grant usage on *. * to Dummy @ localhost; these GRANT statements have 3 new users : Monty can connect to a complete super user from anywhere, but must use a password ('Something' Do this. Note We must issue a GRANT statement for Monty @ localhost and monty @ "%". If we add localhost entry The anonymous user entry for LocalHost is prioritized by the entries created by mysql_install_db from the local host connection. Because it has a more specific Host field value, it seems earlier in the User table. Admin can not A password is connected and a user who is awarded RELOAD and Process administrative privileges. This allows the user to execute mysqladmin refresh, mysqladmin flush- * command, and mysqladmin ProcessList. There is no authority related to the database. They can pass another A GRANT statement authorization. Dummy can use a user with a password connection, but only from the local host. Global permissions are set to 'n' - USAGE authority type allows you to set a user without permission. It assumes you will The database-related permissions will be granted later.

You can also add the same user access information directly by issuing an Insert statement, then tell the server to load the authorization table again: Shell> mysql --user = root mysqlmysql> Insert INTO User Values ​​('localhost', 'monty', password 'Something'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', ' Y ',' Y ',' Y ') mysql> Insert INTO User Values ​​('% ',' Monty ', Password (' Something '),' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ') Mysql> Insert Into User Set Host =' Localhost ' User = 'admin =' y ', process_priv =' y '; mysql> INSERT INTO User (Host, User, Password) Values ​​(' localhost ',' Dummy ','); MySQL> Flush Privileges; Depending on your MySQL version, for the above, you may have to use a different number 'y' value (there are fewer rights columns in 3.22.11.). For the Admin user, only the more readable INSERT expansion syntax with only version 3.22.11. Note that in order to set a super user, you only need to create a USER table entry, whose permissions character is set to 'Y'. There is no need for a DB or HOST table. The permissions column in the User table are not explicitly set by the last INSERT statement (for Dummy users), so those columns are given the default value 'n'. This is the same thing for Grant USAGE. The following example adds a user Custom, he can connect from host localhost, server.domain, and whitehouse.gov. He only wants to access the BankAccount database from localhost, access the Expenses database from Whitehouse.GOV and access the Customer database from all 3 hosts. He wants to use password stupid from all 3 hosts.

In order to use the GRANT statement to set a user's permissions, run these commands: shell> mysql --user = root mysqlmysql> Grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON BankAccount. * To custom @ localhost iDentified by 'stupid'; mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON expenses * TO custom@whitehouse.gov IDENTIFIED BY 'stupid';. mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON customer * TO custom. @ '%' Identified by 'stupid'; set user permissions by direct modification, running these commands (note, flush privileges): shell> mysql --user = root mysqlmysql> 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 ('Whitehouse.gov', 'Expenses',' 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 ('% '

, 'Customer', 'Custom', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y'); MySQL> Flush Privileges; Head 3 INSERT statements increase the USER table entry, allow User Custom uses a given password to connect from different hosts, but no license is granted (all permissions are set to default 'n'). The latter three INSERT statement adds a DB table entry to grant Custom's permissions to BankAccount, Expenses, and Customer database permissions, but only when accessing from the correct host. Typically, when the authorization table is directly modified, the server must be notified to load them again (with flush privileges) to make the permissions modification take effect. If you want to access permissions from any machine on a given domain, you can issue a grant statement: mysql> grant ... on *. * To myusername @ "%. MydomainName. COM "Identified by 'mypassword"; in order to do the same thing by direct modification of the license table, do this: mysql> Insert Into user value ('% .mydomainname.com ',' myusername ", password ('mypassword'), .. .); mysql> flush privileges; you can also use XMysqladmin, MySQL_Webadmin, even XMysql insert, change, and update values ​​in the authorization table. You can find these utilities in MySQL's Contrib directory. 6.12 How to set the password to illustrate an important principle in the example of the previous section: When you use the Insert or Update statement to store a non-empty password, you must encrypt it using the password () function. This is because stored passwords in an encrypted form in the USER table, not as plain text. If you forget this fact, you may try to set your password like this: shell> mysql -u root mysql mysql> Insert INTO USER (Host, User, Password) Values ​​('%', 'Jeffrey', 'Biscuit'); mysql> Flush privileges results are plain text values ​​'biscuit' as a password being stored in the USER table.

When User Jeffrey tries to connect to the server with this password, mysql client encrypts it and gives the result to the server, the server compares the value in the USER table (it is a plain text value 'biscuit') and encrypted password (and Not 'biscuit'), the comparison failure and the server refuses to connect: shell> mysql -u jeffrey -pbiscuit testaccess denied Because the password must be encrypted because when they are inserted into the USER table, the Insert statement should be as specified: mysql> Insert INTO USER (Host, User, Password) Values ​​('%', 'Jeffrey', Password ('Biscuit')); When you use the set password statement, you must use the password () function: mysql> set password for Jeffrey @ "%" = Password ('biscuit'); if you use a grant ... identified by statement or mysqladmin password command setting password, the password () function is unnecessary. They all consider encrypting the password for you, more you can specify a password 'biscuit': mysql> grant usage on *. * To jeffrey @ "%" Identified by 'biscuit'; or shell> mysqladmin -u jeffrey password Biscuit Note: Password () is not enacted in the same way encrypted in the UNIX password. You shouldn't assume that if your UNIX password is the same, Password () will result in the same encryption value that is stored in the UNIX password file. See 6.2 mysql username and password. 6.13 ACCESS DENIED error

When you try to join the MySQL server, if you touch the Access Denied error, the table below shows some action you can use to correct this problem: You are running mysql_install_db after installing MySQL, to set the initial authorization form Content? If not, do this. See 6.10 Set the initial mysql permission. Test initial rights by executing this command: shell> mysql -u root test server should let you connect. You should also guarantee that you have a file "user.myd" in the MySQL database directory. Usually, it is "path / var / mysql / user.myd", where the path is the path to the MySQL installation root directory. After a new installation, you should connect to the server and set your users and your access licenses: Shell> mysql -u root mysql server should let you connect, because MySQL root users have no password. Since it is also a security risk, when you are setting other MySQL users, set the root password is an important thing. If you try to connect as root and get this error: Access Denied for user: '@unknown' to database mysql This means that you don't have an entry in a USER column value of 'root' and mysqld can't be for you. Guestbook parsing host name. In this case, you must use the --skip-grant-table option to restart the server and edit your "/ etc / hosts" or "WindowsHosts" file to add an entry for your host. If you update an existing MySQL from a 3.22.11 version to version 3.22.11 or later, are you running a mysql_fix_privilege_tables script? If not, run it. When the GRANT statement becomes able to work, the structure of the authorized form is modified by MySQL 3.22.11. If you do a modification of the authorization form (using the INSERT or UPDATE statement) and your change seems to be ignored, remember, you must issue a Flush Privileges statement or execute a mysqladmin flush-privileges command to read the server again, otherwise you The change must be effectively efficient when the next server is restarted. Remember After you set the root password, you will not need to specify it until you are cleaning (FLUSH) permissions, because the server will not know that you change your password! If your permissions seem to have changed in a session, it may be a super user to change them. The license table is loaded again to connect, but it also affects existing connections, such as 6.9 permission changes. To test, start the MySQLD daemon with the --skip-grant-table option, then you can change the MySQL authorization form and use the mysqlaccess script to check if your changes have as scheduled. When you are satisfied with your change, execute mysqladmin flush-privileges tells the MySQLD server to start using the new permissions. Note: The licensed licensed table overrides the - Skip-grant-Tables option again. This allows you to tell the server to start using the license table without having to stop and restart it. If you have an access problem for a Perl, Python or ODBC program, try to connect with the server with mySQL -U user_name db_name or mysql -u user_name -pyour_pass db_name. If you can connect with a MySQL client, this is a problem with your program instead of accessing permissions.

(Note that there is no space between the -p and passwords; you can also use the -password = your_pass syntax specified password.) If you can't let your password work, remember if you set your password with insert, update, or set password statement, you have to use Password () function. If you specify your password with a grant ... indentified by statement or mysqladmin password command, the password () function is not required. See how to set the password 6.12. LocalHost is a synonym of your local hostname, and it is also if you don't explicitly specify the host and the customer tries to connect the default host. However, if you are running on a system using mit-pthreads, connecting localhost is not working (Localhost connection uses UNIX sockets, it is not supported by mit-pthreads), in order to avoid this problem on such systems, You should use the --host option to name the server host, which will make a TCP / IP connection to the MySQLD server. In this case, you must have your true host name in the User table on the server host. (Even if you run a client on the main unit of the server, this is true.) When trying to connect with the database with the database connection, if you get an Access Denied error, you may have related to the USER table. Question, by performing mysql -u root mysql and issues the following SQL statement check: mysql> select * from user; the result should contain an entry with Host and User columns to match your computer host name and your MySQL username. Access Denied error message will tell you that you are using which user tries to log in, you are trying to use which host is connected, and if you are using a password. Typically, you should have an entry in the USER table that correctly matches the host name and username given by the error message. If you try to connect from a host that is not running, you get the following error, then do not match the host row in the USER table: Host ... Is Not ALLOWED to Connect To this MySQL Server you can Correct it using the MySQL command line tool (on the server host!), You are trying to connect to the User / Host Name of the Upset to the USER table. If you are not running mysql 3.22 and you don't know the IP number or host name you are connecting from it, you should put a '%' entry as a Host column value in the USER table and use -log on the server machine The option restarts mysqld. After trying to connect from the client machine, the information in the MySQL record file will display how you truly connect. (Then use the actual hostname displayed on the record file instead of '%' entry in the USER table. Otherwise, you will have an unsafe system.) If mysql -u root test work but mysql -h your_hostname -u Root test causes Access Denied, then you may not have your host's correct name in the User table. One of the common problems here is to specify a unique hostname in the User table entry, but your system's name parsing routine returns a completely regular domain name (or opposite). For example, if you have a host in the User table, you are 'TCX', but your DNS tells Mysql your host name is 'tcx.subnet.se', the entry will not work.

Try to add an entry to the USER table, which contains the IP number of your host as the value of the Host column. (In addition, you can add an entry to the USER table, which has a Host value containing a wildcard such as 'tcx.%'. ​​However, the host name of "%" is not safe and does not recommend!) MySQL -U User_name Test works, but mysql -u user_name other_db_name does not work, for other_db_name, you don't have an entry in the DB table. When executing mysql -u user_name db_name on the server machine, it works, but when performing mysql -h host_name -u user_name db_name on other clients, it doesn't work, you don't put the client machine in the USER table or DB table. in. If you can't figure out why you get Access Denied, remove all HOST entries containing wildcard values ​​from the USER table (containing "%" or "_" entry). A very common error is to insert a new entry with host = '%' and user = 'some user', think this will allow you to specify that localhost is connected from the same machine. The reason why it does not work is that the default permissions include entries with host = 'localhost' and user = '', because the entry is more specific Host value 'localhost' than '%', when connected to localhost, it uses Point to new entries! The correct step is to insert the second entry of Host = 'localhost' and user = 'some_user', or delete host = 'localhost' and user = '' entry. If you get the following errors, you can have a problem related to the DB or HOST table: Access to database Denied If you select an empty value in Host column from the DB table, it is guaranteed to have one or more corresponding in the HOST table. The entry specifies which hosts in the DB table. If you get an error when you use the SQL command Select ... INTO OUTFILE or LOAD DATA INFILE, your entry in the User table may enable File permissions. Remember, the client program will use the connection parameters specified in the configuration file or environment variable. If you don't specify them on the command line, a customer seems to send the error default connection parameters, check your environment and ".my.cnf" files under your home directory. You can also check the system-wide MySQL configuration file, although it is more impossible to specify the connection parameters of that customer. See 4.15.4 Options file. If you don't have any options to run a customer, you get Access Denied, confirm that you are not specified in any options! See 4.15.4 Options file. If anything else fails, start the mysqld daemon with the tester (for example, - debug = d, general, query). This will print information about the host and user information that attempts to connect, and each command issued. See G.1 Debugging a MySQL server. If you have any other questions from the MySQL licensed form, and I feel that you must mail this problem to the maket table, always provide a MySQL authorization table's dumping copy (DUMP). You can use the mysqldump mysql command to pour the database table. As usual, mailing your questions with mysqlbug scripts. In some cases you might use - Skip-grant-tables to restart MySQLD to run mysqldump.

6.14 How to make MySQL securely when you connect to a Mysql server when you connect a MySQL server, you should usually use a password. The password is not transmitted in a clear text. All other information is transmitted as text that can be read by anyone. If you are worried about this, you can use a compression protocol (mysql3.22 and above) make things harder. Even in order to make everything safer, you should install SSH (see http://www.cs.hut.fi/ssh). With it, you can get an encrypted TCP / IP connection between a MySQL server with a mysql client. In order to make a mysql system security, you strongly ask you to consider the following recommendations:

Use passwords for all MySQL users. Remember, if other_user has no password, anyone can log in as any other person with mysql -u other_user db_name. For client / server applications, customers can specify any user names is a common practice. Before you run it, you can change all users passwords, or just mysql root passwords, like this: shell> mysql -u root mysqlmysql> Update user set password = password ('new_password') where user = 'root'; mysql> flush privilege; Do not run the mysql daemon as a ROOT user as a Unix. MySQLD can run in any user, you can also create a new UNIX user mysql makes everything safer. If you run mysqld as other UNIX users, you don't need to change the root username in the USER table, because MySQL username doesn't matter from UNIX username. You can edit the mysql.server to start the script mysqld as other UNIX users. Usually this is done with the su command. For more details, see how 18.8 runs as a general user runs mysql. If you put a UNIX root user password in the mysql.server script, make sure this script can only be readable to root. Check that UNIX users running MySQLD are unique users with read / write permissions in the database directory. Don't give Process permissions to all users. The output of MySQLADMIN ProcessList shows the currently executed query body, if another user issues an UPDATE User Set Password = Password ('not_secure') query, any user that is allowed to execute the command may be seen. MySQLD keeps an additional connection for users with Process permissions so that a MySQL root user can log in and check, even if all normal connections are used. Don't give File permissions to all users. Any user with this permission can write a file in a file system with mysqld daemon permission! In order to make this more secure, all files generated by Select ... INTO OUTFILE are readable for each person, and you cannot overwrite the existing files. File permissions can also be used to read any files available as UNIX users running the server. This may be abuse, for example, by loading "/ etc / passwd" in using Load Data, then it can be read with SELECT. If you don't trust your DNS, you should use IP numbers in the license list instead of host name. In principle, - Secure Options should make the host name more secure to mysQLD. In any case, you should use the host name containing wildcards very carefully! The following mysqld option affects security: - Secure is checked by the IP number returned by the gethostbyname () system to ensure that they resolve returns to the original host name. This makes it harder to some outsiders to get access to other hosts. This option also adds some smart hostname checks. In mysql3.21, select the default is turned off because it sometimes spends a long time to perform reverse parsing. MySQL 3.22 Cache Host Name and the option is enabled by default. - Skip-grant-tables This option causes the server to not use the power limit system at all. This gives everyone to completely access the power of all databases! (By executing mysqladmin reload, you can tell one of the running servers start using the license table again.