How the Mysql permission system works_PHP tutorial

How the Permission System Works The MySQL permission system ensures that all users can do exactly what they are assumed to be allowed to do. When you connect to a MySQL server, your identity is determined by the host you connect from and the username you specify. Permissions are granted based on your identity and what you want to do. MySQL considers your hostname and username in determining identity because there is little reason to assume that a given user belongs to the same person on the Internet. For example, the bill a user connects to from whitehouse.gov does not have to be the same person as the bill a user connects to from mosoft.com. MySQL handles this by allowing you to differentiate between users who happen to have the same name on different hosts: you can grant bill one set of permissions 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 whether you allow the connection. 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 select rows from a table in the database or drop a table from the database, the server determines that you have select permission on the table or drop permission on the database. The server uses the user, db and host tables in the mysql database in the two stages of access control. The fields in these authorization tables are as follows: Table name user db host range field Host Host Host User Db Db Password User permission field Select_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 For the second phase of access control (request confirmation), if the request involves a table, the server can additionally Refer to the tables_priv and columns_priv tables. The fields of these tables are as follows: Table name tables_priv columns_priv Scope field Host Host Db Db User User Table_name Table_name Column_name Permission field Table_priv Column_priv Column_priv Other fields Timestamp Timestamp Grantor Each grant table contains scope fields and permission fields. The scope field determines the scope of each entry in the table, that is, the context in which the entry applies. For example, a user table entry with Host and User values ​​of thomas.loc.gov and bob would be used to authenticate bob's connection to the server from the host thomas.loc.gov. Similarly, the values ​​of the Host, User and Db fields of a db table entry are thomas.loc.gov, bob and reports will be used when bob connects to thomas.loc.gov from the host to access the reports database. The tables_priv and columns_priv tables contain scope fields that indicate the table or table/column combination to which each entry applies. For purposes of checking access, comparing Host values ​​ignores case. User, Password, Db and Table_name values ​​are case sensitive. The Column_name value is case-insensitive in MySQL 3.22.12 or later versions. The permissions field indicates the permissions granted by a table entry, that is, what operations can be performed. The server combines information from various authorization tables to form a complete description of the user's permissions. The rules used for this are described in 6.8 Access Control, Phase 2: Request Authentication. Range fields are strings, as described below; the default value for each field is the empty string: Field Name Type Host CHAR(60) User CHAR(16) Password CHAR(16) Db CHAR(64) (tables_priv and columns_priv tables for CHAR(60)) In the user, db, and host tables, all permission fields are declared as ENUM(N,Y)--each can have the value N or Y, and the default value is N. In tables_priv and columns_priv In the table, the permission fields are declared as SET fields: Table name Field name Possible 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 Simply put, the server uses an authorization table like this: The user table scope field determines whether to allow or deny incoming connections. For allowed connections, the permissions field indicates the user's global (superuser) permissions. The db and host tables are used together: The db table range field determines which database the user can access from which host. Permissions fields determine which operations are allowed. The host table is used as an extension of the db table when you want a given db entry to apply to several hosts. 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 hosts table. This mechanism is described in detail in 6.8 Access Control, Phase 2: Request Authentication. tables_priv and columns_priv tables are similar to db tables, but more sophisticated: they apply at the table and column level rather than at the database level. Note that administrative permissions (reload, shutdown, etc.) are only specified in the user table. This is because administrative operations are those of the server itself and are not database specific, so there is no reason to list such permissions in other authorization tables. In fact, you only need to consult the user table to determine whether you perform an administrative operation. File permissions are also only specified in the user table. It is not an administrative permission, but your ability to read or access files on the server host is independent of the database you are accessing.When the mysqld server starts, the contents of the authorization table are read once. Changes to the authorization table take effect in 6.9 When Permission Changes Take Effect Description. When you modify the contents of an authorization table, it's a good idea to make sure you change the permission settings the way you want. To help diagnose the problem, see 6.13 Causes of "Access Denied Caused" Errors. For advice on security issues, see 6.14 How to make MySQL secure against decryption experts. A useful diagnostic tool is the mysqlaccess script, provided by Carlier Yves for the MySQL distribution. Use the --help option to call mysqlaccess to find out how it works. Note: mysqlaccess only checks access using the user, db and host tables. It does not check table or column level permissions. 6.7 Access Control, Phase 1: Connection Verification When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can authenticate your identity by supplying the correct password. If not, the server fully honors your access, otherwise, the server accepts the connection, then enters phase 2 and waits for the request. Your identity is based on 2 pieces of information: The host you are connecting from Your MySQL username The identity check is performed using 3 user table (Host, User and Password) scope fields. The server only accepts the connection if a user table entry matches your hostname and username and you provide the correct password. Range fields in the user table can be specified as follows: A Host value can be a hostname or an IP number, or localhost indicates the local host. You can use wildcard characters "%" and "_" in the Host field.A Host value % matches any hostname, and a blank Host value is equivalent to %. Note that matching these values ​​can create a connection from any host to your server! Wildcard characters are not allowed in the User field, but you can specify a blank value, which will match any name. If the user table entry matching an incoming connection has a blank username, the user is considered an anonymous user (a user without a name), rather than the name actually assigned by the client. 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 does not mean matching any password, it means the user must connect without specifying a password. A non-blank Password value represents an encrypted password. MySQL does not store passwords in a plain text format that anyone can read. Instead, the password provided by a user who is trying to connect is encrypted (using the PASSWORD() function) and compared to the already encrypted version stored in the user table. If they match, the password is correct. The following example shows how various combinations of values ​​from the Host and User entries in the user table are applied to incoming connections: Connections with Host values ​​and User values ​​matched by entries thomas.loc.gov fred fred, connections to thomas from thomas.loc.gov .loc.gov any user, connecting % fred fred from thomas.loc.gov, connecting % from any host any user, connecting %.loc.gov fred fred, connecting x.y.% from any host in the loc.gov domain fred fred, connect from x.y.net, x.y.com, x.y.edu, etc. (This may not be useful) 144.155.166.177 fred fred, Connect from the host with the IP address 144.155.166.177 144.155.166.% fred fred, Connect from any host on the 144.155.166 Class C subnet Since you can use IP wildcards in the Host field value (for example, 144.155.166.% matches every host on a subnet), it is possible that someone might attempt to explore this capability by naming a host 144.155.166.somewhere.com. To prevent such attempts, MySQL does not allow matching of host names that begin with a number and a dot, so that if you use a host named something like 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 more than one matches, how does the server choose which entry to use? The server solves this problem by sorting the user table after reading it on startup, and then when a user attempts to connect, the entries are browsed in sorted order, and the first matching entry is used. The user table sorting works as follows, assuming that the user table looks like this: +-----------+----------+- | Host | User | ... +-- ---------+----------+- | % | root | ... | % | jeffrey | ... | localhost | root | ... | localhost | | ... +-----------+----------+- When the server reads from the table, it sorts it in order of the most specific Host value first ( % in the Host column means "any host" and is the least specific). Entries with the same Host value are ordered with the most specific User value first (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 attempted, the server looks through the sorted entries and uses the first match found. For a connection from localhost by jeffrey, the localhost entry in the Host column is matched first. Those entries with a blank username match the connected hostname and username. (The %/jeffrey entry will also match, but it won't be the first match in the table.) Here's another example. Assume 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 link by jeffrey from thomas.loc.gov matched by the first entry,