How to use permissions in MySQL

[Ground 0]

The system of the MySQL grant system is well advanced, but non-standard, based on the contents of the mysql database that you will find pre-installed on your server after running the mysql_install_db script. The first essential concept to keep in mind is that the user identification is not simply through its user name, but by combining this with the machine from which the user connects. So two users connecting with the same name but from two different addresses for MySQL are two different people. The first to be consulted when a user tries to connect to the server is the user table. In fact, the server tries to recognize the user based on the values contained in the 'Host' and 'User' of this table: once the user has been "approved", the access will be granted if it would type the password you specified on the table for that combination of user and host. Column 'Host' of the user table may contain host names (eg. www.mysql.com) or IP addresses. You can use the character "wild card" that represent a character (_) or "n" characters (%). So for example the value '%. Mysql.com' will be considered valid for any third-level domain belonging to mysql.com.

[Ground 0]

If you are using the IP address, you can also specify a mask to specify how many bits must be used to match the address, it should be noted that the number of bits have to be a multiple of 8 (8, 16, 24, 32). For example .198.66.0/255.255.255.0 the value '151 'means that the line is valid for the IP 151.42.168.0 to 151.42.168.255. In the column 'User' are evidently contained the names of users that can be connected. Here it is not allowed to use the wildcard, but is allowed to leave the value blank, in which case all users who connect from the host name (or IP address) will be considered relevant, use any name. In this case, however, will be considered by MySQL as 'anonymous', which means that the username with which you have submitted will not be valid when the checks will be made to the permissions for various operations. It is clear that with this system it may happen that a user, when trying to connect, can be recognized based on the contents of multiple rows of the table user: in fact we see an example:

+-----------+----------+-
 | Host | User |
 +-----------+----------+-
 |% | Sunikrew|
 |% | Noctury|
 | Localhost | Sunikrew|
 | Localhost | |
 +-----------+----------+-

In this situation, if the user connects from localhost Sunikrew, the first and the third row are able to recognize it, because the value of '%' is true for any host. Similarly, if there is Noctury (again from localhost), will be recognized in the second row but the fourth also, as the blank user name matches any user.

[Ground 0]

However, MySQL determines a single line to be used to recognize you (and then give it its privileges). This line is chosen first by recognizing the user to the host name and host names between the more specific one is preferred (ie without the wildcard), but here it looks for the user name, and in this case has the exact name the previously anonymous user. The first line that matches the username on the basis of these criteria is the one that will be used for authentication. So in our example above, when connecting from localhost Sunikrew will be recognized by the third line (more specific host name), while the first line will be used when connecting to other addresses. As for Luke, the situation can easily be misleading, because when you connect to localhost will be recognized according to the fourth line and not the second: for the recognition of host name takes precedence over that of the user name, and then the first line that meets the credentials. Of course, once you are approved, they must provide the correct password, the server will compare with that contained in the anonymous column of the user table.

[Ground 0]

Once you have access to the server, the user must have permission to work on various database. The user table contains several columns for permits ('Select_priv', 'Insert_priv', 'Update_priv' etc..) Each of which may contain the value 'Y' or 'N'. The presence of 'Y' means that the user is authorized to perform that work: the fact that you have permission on the user table implies that the permit is valid for all databases on the server. In essence, a user 'normal' should not have allowed this type on the user table. The db table instead contains permissions for individual database. The key of this table consists of User, Host and Db: so each line specifies what permissions a user has on a particular database. Again we find the wildcard for host, db, db column can also be blank for all databases. The user column, however, if it is empty, that is only for anonymous users. A special case is when the Host field of the db table is empty: in this case it comes in the host table, which specifies the permissions on different databases for different hosts. Here the key Host and Db, and once you have the wildcard on both columns. The empty value corresponds to the wildcard '%'. When using the host table, the permissions for the user are derived from 'found at the intersection of the permission granted by the db table with the host table, which means that the requested operation is allowed only if its value is' Y' on both tables, in the corresponding rows. Assuming that the user is connected to the machine 192.168.0.11 Paul who does not have any permission on the user table, we hypothesize that wants to work on the database purchases: in this case will be recognized from the first row of the db table that will enable it to select the but not the insert.

[Ground 0]

However, when Sunikrew will try to work on database sales, will be recognized by the second line, but does not include a host address, so the server will check the permissions on the host table. On this table the row belongs to that and is the first, as it coincides exactly with the address of its car and is valid for any database. This line, however, denies the permission to be on that select the insert, so that Sunikrew , despite the db table as authorized to select, is unable to perform any operation on database sales. If they logged in from another machine with address 192.168.0.%, At least it could do the select (not the insert, which are still denied by the db table). Even for those tables and then applies the principle that the values of the host, database and user are recognized preferentially by the more specific the content of the fields, when there are more lines that coincide with the request. This is why Paul can not work on database sales when you connect from 192.168.0.11: the second row of the table would allow the host to do so, but the former has a more specific and therefore prevails. There are also tables and tables_priv columns_priv, which contain even more specific privileges as related to a single table (in the first case) and even to a single column (in the second case). These tables are consulted when the privileges granted by the tables user, db and host are not sufficient to secure permission to perform the requested operation. Both contain the key fields Host, Db, and User, plus a column that identifies the table Table_name concerned, the table contains an additional column column_name columns_priv that specifies which column to apply the permissions. In these tables only the host field may contain wildcards or be blank (in this case the line is for the addresses), while the database must always be specified. In addition, these tables do not contain a column for each permit as the previous ones, but the columns that contain Column_priv Table_priv and all permits granted by the row.