How Mysql permission system works

Permission system working principle MySQL permission system ensures that all users can strictly perform what they assume is 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. The system grants permissions based on your identity and what you want to do. MySQL considers SyntaxHighlighter. all ();

Permission system working principle MySQL permission system ensures that all users can strictly perform what they assume is 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. The system grants permissions based on your identity and what you want to do. MySQL considers your host name and username in identity authentication because there is a small reason for assuming that a given user belongs to the same person on the Internet. For example, the user's bill from whitehouse.gov does not have to be the same person as the bill from mosoft.com. MySQL allows you to differentiate users with the same name on different hosts for processing: you can assign a permission set to bill from the whitehouse.gov connection, assign a different permission set for the connection from microsoft.com. MySQL access control involves two phases: Phase 1: The server checks whether you are allowed to connect. Phase 2: If you can connect, the server checks every request you send. Check whether you have sufficient permissions to implement it. For example, if you select a row from a table in the database or discard a table from the database, the server determines that you have the 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 two phases of access control. The fields in these authorization tables are as follows: table name: user db host range Field Host User Db Password User permission field Select_priv privilege parameter Update_priv privilege parameter Delete_priv e_priv privilege drop_priv Gra Nt_priv Grant_priv Reload_priv Shutdown_priv Process_priv File_priv the second stage of access control (request confirmation). If the request involves a table, the server can also refer to the tables_priv and columns_priv tables. The fields of these tables are as follows: table name tables_priv columns_priv range Field Host Db User Table_name Column_name permission field Table_priv Column_priv other fields Timestamp Grantor each authorization table contains the range Field and permission field. The range field determines the scope of each entry in the table, that is, the context of the entry. For example, if the Host and user values of a User table entry are thomas.loc.gov and bob, they will be used to verify the connection of bob from the Host thomas.loc.gov to the server. Similarly, the Host, User, and db fields of a Db table entry are thomas.loc.gov, bob, and reports, which are used when bob connects thomas.loc.gov from the Host to access the reports Database. The tables_priv and columns_priv tables contain a range field, indicating the table or table/column combination applicable to each entry. For the purpose of checking access, comparing Host values is case-insensitive. User, Password, Db, and Table_name values are case sensitive. The Column_name value is case-insensitive in MySQL3.22.12 or later versions. The permission field specifies the permissions granted by a table entry, that is, what operations can be performed. The server combines various authorization table information to form a complete description of user permissions. For this purpose, the rules used are in access control 6.8, Phase 2: request validation description. The range Field is a string, as described below; the default value of each field is a null string: Field Name Type Host CHAR (60) User CHAR (16) Password CHAR (16) Db CHAR (64) (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 one can have a value of N or Y, and the default value is N. in tables_priv and columns_priv tables, permission fields are declared as SET fields: tables_priv Table_priv Select, Insert, Update, Delete, Create, Drop, Grant, References, index, Alter tables_priv Column_priv Select, Insert, Update, Refer In short, the server uses the following authorization table: the user table range field determines whether to allow or reject incoming connections. For allowed connections, the permission field specifies the global (Superuser) permissions of the user. Db and host tables are used together: the db table range field determines the host from which the user can access the database. The permission field determines which operation is allowed. When you want to apply a given db entry to several hosts, the host table is used as an extension of the db table. For example, if you want a user to use a database from several hosts on your network, set the Host entry in the user's db table to null, then, move each of those hosts to the host table. This mechanism is described in detail in access control 6.8, Phase 2: request validation. Tables_priv and columns_priv tables are similar to db tables, but more refined: they are applied at the table and column level, not at the database level. Note that the management permission (reload, shutdown, and so on) is only specified in the user table. This is because administrative operations are performed by 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 determine whether to perform a management operation. The file permission is only specified in the user table. It is not administrative, but the ability to read or thank files on the server host is independent of the database you are accessing. When the mysqld server is started, read the authorization table once. The change to the authorization table takes effect. description of when the 6.9 permission change takes effect. When you modify the content of the authorization table, make sure that you change the permission settings as you want is a good idea. To help diagnose the problem, see the cause of the 6.13 "access rejection" error. For advice on security issues, see section 6.14 on how to prevent and decrypt MySQL security experts. A useful diagnostic tool is the mysqlaccess script, which is provided by Carlier Yves to MySQL for distribution. Use the -- help option to call mysqlaccess to find out how it works. Note: mysqlaccess only checks access using user, db, and host tables. It does not have checklist or column-level permissions. 6.7 access control, Phase 1: connection confirms that when you try to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can supply the correct password to verify your identity. Otherwise, the server accepts the connection and enters stage 2 and waits for the request. Your identity is based on two pieces of information: you use the three user table (Host, User, and Password) range fields to check the identity of your MySQL user connected from that Host. The server accepts the connection only when a user table entry matches your host name and user name and you provide the correct password. The range field in the user table can be specified as follows: a Host value can be a Host name, an IP number, or localhost indicates the local Host. You can use the wildcard characters "%" and "_" in the Host field. A Host value % matches any Host name. a blank Host value is equivalent to %. Note that these values match to create a host connected to your server! Wildcard characters are not allowed in the User field, but you can specify a blank value, which matches any name. If the user table matches the incoming connection entry with a blank user name, the user is considered 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 connection (that is, during stage 2 ). The Password field can be blank. This does not mean that any password is matched. it means that the user must not specify a password for connection. A non-blank Password value indicates the encrypted Password. MySQL does not store passwords in plain text format that anyone can read. On the contrary, the PASSWORD provided by a user attempting to connect is encrypted (using the PASSWORD () function ), and compare it with the encrypted version that stores 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 applies to the incoming connection: thomas.loc.gov fred, connect any user from thomas.loc.gov to thomas.loc.gov, connect % fred from thomas.loc.gov, connect % any user from any host, connect % .loc.gov fred from any host, connect x. y. % fred, connected from x.y.net, x.y.com, x.y.edu, etc. (This may be useless) 144.155.166.177 fred, from a host with a 144.155.166.177 IP address connection 144.155.166.% fred, any Host connection from the 144.155.166 Class C subnet since you can use the IP wildcard value in the Host field (for example, 144.155.166.% matches each host on a sub-network.) it is possible that someone may attempt to explore this capability by naming a host as 144.155.166.somewhere.com. To prevent such an attempt, MySQL does not allow matching the host name starting with a number and a vertex. in this way, if you use a host named 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 from thomas.loc.gov by fred matches multiple entries as described above. If more than one match exists, how does the server choose which one to use? When the server starts, it reads the user table and sorts the table to solve this problem. when a user tries to connect to the table, the entries are browsed in order, and the first matched entries are used. The sorting of the user table is as follows. assume that the user table looks like this: + ----------- + ---------- +-| Host | User |... + ----------- + ---------- +-| % | root |... | % | jeffrey |... | localhost | root |... | localhost |... + ----------- + ---------- +-when the server reads data from the table, it is sorted in the order of the most specific Host values (% in the Host column means "any Host" and is the least specific ). Entries with the same Host value are sorted in the order of the most specific User values (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 browses the sorted entries and uses the first match found. For a connection from localhost by jeffrey, the localhost entries in the Host column match first. Those entries with blank usernames match the connection host name and username. (%/Jeffrey entries 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 |... + ---------------- + ---------- +-the sorted table looks like this: + ------------------ + ---------- +-| Host | User |... + ---------------- + ---------- +-| thomas.loc.gov |... | % | jeffrey |... + ---------------- + ---------- +-a connection from thomas.loc.gov by jeffrey is matched by the first entry,