How the permissions system works MySQL privilege system ensures that all users can strictly do what they assume are allowed to do. When you connect to a MySQL server, your identity is determined by the host you connect to and the user name you specify, and the system grants permissions based on your identity and what you want to do. MySQL considers your hostname and user name in its identity because there is a small reason to assume that a given user belongs to the same person on the Internet. For example, bill that the user connects from WhiteHouse.gov does not have to be connected to the same person as Bill from Mosoft.com. MySQL by allowing you to differentiate on different hosts happens to have the same name on the user to handle it: You can grant bill a permission set for connections from WhiteHouse.gov, and a different set of permissions for connections from Microsoft.com. MySQL access control consists of 2 phases: Phase 1: The server checks if you are allowed to connect. Phase 2: Assuming you can connect, the server checks every request you make. See if you have enough permissions to implement it. For example, if you discard a table from a table selection (select) row in a database or from a database, the server determines that you have SELECT permissions on the table or drop permissions on the database. The server uses the user, DB, and host tables in the MySQL database in two stages of access control, where the fields are as follows: Table name user DB host scope field host host Host user DB DB Password user permission field S Elect_priv 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 to access Second phase of control (request confirmation), if the request involves a table, the server can refer to the TABLES_PRIV and Columns_priv tables separately. The fields for these tables are as follows: Table name Tables_priv columns_priv Range field host host DB DB USER user table_name TABLE_NAME column_name permission field Table_priv Column_pri V column_priv other fields Timestamp Timestamp grantor each authorization table contains a range field and a permission field. The Range field determines the scope of each entry in the table, that is, the context in which the entry applies. For example, the host and user values for a user table entry are thomas.loc.gov and Bob will be used to validate Bob's connection to the server from the host thomas.loc.gov. Similarly, the value of the host, user, and DB fields for a DB table entry is thomas.loc.gov, Bob, and reports will be used when Bob accesses the reports database from the host join Thomas.loc.gov. The Tables_priv and Columns_priv tables contain a range field that indicates the combination of the table or table/column that each entry applies to. For the purpose of checking access, the comparison of the host value is case-insensitive. The User, Password, DB, and TABLE_NAME values are case-sensitive. The column_name value is ignored in MySQL3.22.12 or later versions of the case. The Permission field indicates the permissions granted by a table entry, that is, what action can be implemented. The server combines information from various authorization tables to form a complete description of the user's permissions. The rules used for this are in 6.8 access control, Phase 2: Request confirmation description. The Range field is a string, as described below; the default value for each field is an empty string: The field name type is Host char (Password), the User char (+), and the Tables_priv and Columns_priv tables are CHAR (60)) in the user, DB, and host tables, all permission fields are declared as enum (n,y)-Each can have a value of N or Y, and the default value is N. In the Tables_priv and Columns_priv tables, the permission fields are declared as Set fields: Table Name field names possible collection 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 simply put, the server uses this authorization table: The User table Range field determines Whether to allow or deny incoming connections. For allowed connections, the Permission field indicates the global (superuser) permissions of the user. The DB and host tables are used together: the DB table Range field determines which database the user can access from which host. The permission field determines which action is allowed. When you want a given DB entry to be applied to several hosts, the host table is used as an extension of the DB table. For example, if you want a user to be able to use a database from several hosts on your network, set the host entry in the user's DB table to NULL, and then move each of those hosts into the host table. This mechanism is described in detail in 6.8 access control, Phase 2: request confirmation. Tables_priv and Columns_priv tables are similar to DB tables, but they are more refined: they are applied at the table and column levels rather than at the database level. Note Administrative permissions (reload, shutdown, and so on) are only specified in the user table. This is because administrative operations are operations of the server itself and are not specific databases, so there is no reason to list such permissions in other authorization tables. In fact, you only need to ask the user table to decide if you want to perform a management operation. File permissions are also specified in the user table only. It is not administrative permissions, but the ability to read or thank the files on the server host is independent of the database you are accessing. When the MYSQLD server starts, the authorization table content is read once. The changes to the authorization table take effect when the 6.9 permission changes take effect description. When you modify the contents of the authorization table, it is a good idea to make sure that you change the permission settings the way you want. To help diagnose the problem, see 6.13 "Access denied causes" error. For advice on security issues, see 6.14 How to make MySQL security against decryption master. A useful diagnostic tool is the mysqlaccess script, which is provided by Carlier Yves to MySQL for distribution. Call mysqlaccess using the--HELP option to find out how it works. Note: mysqlaccess only checks access only with the user, DB, and host tables. It does not check for table or column-level permissions. 6.7 access Control, Phase 1: Connection confirmation When you try to join a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify the identity by supplying the correct password. If not, the server completely recognizance your access, otherwise, the server accepts the connection, then enters Phase 2 and waits for the request. Your identity is based on 2 information: You connect from that host Your MySQL username identity check is performed using the 3 user table (Host, user, and password) scope fields. The server accepts the connection only if a user table entry matches your host name and user name and you provide the correct password. The User table range field can be specified as follows: A host value can be either a hostname or an IP number, or localhost indicates a local host. You can use the wildcard characters "%" and "_" in the Host field. A host value of% matches any hostname, and a blank host value is equivalent to%. Note that these value matches can create a connection to your server to any host! Wildcard character is 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 incoming connection with a blank username, the user is considered to be an anonymous user (a user without a name), rather than the actual name specified by the customer. This means that a blank user name is used for further access checks during the connection (i.e., during Phase 2). The password field can be blank. This does not mean matching any password, it means that the user must not specify a password to connect. A non-blank password value represents an encrypted password. MySQL does not store passwords in plain text format that anyone can see, instead, a user-supplied password that is being attempted to join IS encrypted (using the password () function) and compared to an already encrypted version stored in the user table. If they match, the password is correct. The following example shows how the combination of the values of the host and user entries in the various user tables applies to incoming connections: the host value of the user value is matched by the entry connection thomas.loc.gov Fred Fred, from Thomas.loc.gov connection Thomas.lo c.gov any user, from thomas.loc.gov connection% Fred Fred, connect from any host to% any user, connect from any host%.loc.gov Fred Fred, connect x.y.% Fred Fred from any host in the loc.gov domain, From X.y.net, x.y.com,x.y.edu and other joins. (This may be useless) 144.155.166.177 Fred Fred, connecting from a host with a 144.155.166.177 IP address 144.155.166.% Fred Fred, any host connection from the 144.155.166 Class C subnet Since you can be in hos The T field uses the IP wildcard value (for example, 144.155.166.% matches each host on a subnet), and it's possible that someone might be trying to explore this ability, by naming a host for 144.155.166.somewhere.com. To prevent such attempts, MySQL does not allow the hostname to match with a number and a point start, so that if you use a host named 1.2.foo.com, its name will never match the host column in the authorization table. Only one IP number can match the IP wildcard value. An incoming connection can be matched by more than one entry in the user table. For example, a connection by Fred from thomas.loc.gov matches multiple entries as described above. If there is more than one match, how does the server choose which entry to use? The server solves this problem by sorting after it has been read into the user table at startup, and then when a user tries to connect, the first matching entry is used in order to navigate through the entry in the ordered sequence. The user table sort works as follows, assuming the user table looks like this: +-----------+----------+-| Host | User | ... +-----------+----------+- | % | Root | ... | % | Jeffrey | ... | localhost | Root | ... | localhost | | ... +-----------+----------+-when the server reads in a table, it is in the first order of the most specific host values (% in host joins means "any host" and is the least specific). Entries with the same host value are arranged in the order in which the most specific user values are first (a blank user value means "anyone" and is the least specific). The final sorted user table looks like this: +-----------+----------+-| Host | User | ... +-----------+----------+- | localhost | Root | ... | localhost | | ... | % | Jeffrey | ... | % | Root | ... +-----------+----------+-When a connection is attempted, the server browses the sorted entry and uses the first match found. For a connection by Jeffrey from LocalHost, the localhost entry in the host column first matches. Entries that have a blank user name match the host name and user name of the connection. (The%/jeffrey entry will also match, but it is not the first match in the table.) This is another example. Suppose the user table looks like this: +----------------+----------+- | Host | User | ... +----------------+----------+- | % | Jeffrey | ... | thomas.loc.gov | | ... +----------------+----------+-the sorted table looks like this: +----------------+----------+-| Host | User | ... +----------------+----------+- | thomas.loc.gov | | ... | % | Jeffrey | ... +----------------+----------+-a connection by Jeffrey from Thomas.loc.gov is matched by the first entry,
http://www.bkjia.com/PHPjc/532232.html www.bkjia.com true http://www.bkjia.com/PHPjc/532232.html techarticle How the permissions system works MySQL privilege system ensures that all users can strictly do what they assume are allowed to do. When you connect a MySQL server, your identity is from you ...
