mysql| Security | Security MySQL Security Guide (3)
Author: Yan Zi
2.4 Without grant set the user
If you have a MySQL version earlier than 3.22.11, you cannot use the grant (or revoke) statement to set up users and their access rights, but you can directly modify the contents of the authorization table. This is easy if you understand how the GRANT statement modifies the authorization table. Then you can do the same thing yourself by making an INSERT statement by hand.
When you issue a grant statement, you specify a username and host name, and possibly a password. Generates a user table record for the users, and these values are recorded in the user, host, and password columns. If you specify global permissions in the GRANT statement, these permissions are recorded in the Permissions column of the record. Notice that the GRANT statement encrypts the password for you, and the insert is not, and you need to encrypt the password using the password () function in the insert.
If you specify database-level permissions, the user name and host name are recorded in the user and host columns of the DB table. The database you authorize is recorded in the DB column, and the permissions you grant are recorded in the Permissions column.
For table-level and column-level permissions, the effect is similar. Create records in the Tables_priv and Columns_priv tables to record user names, host names, and databases, as well as related tables and columns. The permissions that are granted are recorded in the Permission column.
If you remember the previous introduction, you should be able to do what Grant did without the grant statement. Remember that when you modify the authorization form directly, you will notify the server to overload the authorization form, otherwise he doesn't know about your change. You can execute a mysqladmin flush-privileges or mysqladmin reload command to force an overload. If you forget to do this, you'll wonder why the server doesn't do what you want to do.
The following grant statement creates a superuser who has ownership. Includes the ability to empower others:
GRANT all on *.* to Anyname@localhost identified by "passwd"
With GRANT OPTION
The statement creates a record for anyname@localhost in the user table, opens all permissions, because this is where the Superuser (global) permission is stored, to do the same thing with the INSERT statement, and the statement is:
INSERT into user VALUES ("localhost", "Anyname", PASSWORD ("passwd"),
"Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y".
You may find that it does not work, it depends on your MySQL version. The structure of the authorization table has changed and you may not have 14 permission columns in your user table. Use Show columns to find each permission column that your authorization form contains, and adjust your insert statement accordingly. The following grant statement also creates a user with Superuser identity, but only one single permission:
GRANT RELOAD on *.* to Flush@localhost identified by "Flushpass"
The INSERT statement for this example is simpler than the previous one, and it is easy to list the column names and specify only one permission column. All other columns will be set to the default "N":
INSERT into User (host,password,reload) VALUES ("localhost", "Flush", Password ("Flushpass"), "Y")
Database-level permissions are authorized with an on db_name.* clause instead of on *.*:
GRANT all on sample.* to Boris@localhost identified by "Ruby"
These permissions are not global, so they are not stored in the user table, we still need to create a record in the user table (so that users can connect), but we also need to create a DB table record database set permissions:
INSERT into User (Host,user,password) VALUES ("localhost", "Boris", Password ("Ruby")
INSERT into db VALUES ("localhost", "sample_db", "Boris", "Y", "Y", "Y", "Y", "Y", "Y", "N", "Y", "Y", "Y")
The "N" column is a grant permission, and a grant statement with GRANT option at the end of the database level is set to "Y".
To set table-level or column-level permissions, you use INSERT statements for Tables_priv or Columns_priv. Of course, if you don't have the GRANT statement, you won't have these tables because they appear in MySQL at the same time. If you do have these tables and want to manually manipulate them for some reason, know that you cannot enable permissions with separate columns.
You set Tables_priv. Table_priv or Columns_priv. The Column_priv column setting contains the permission values you want to enable. For example, to enable Select and Insert permissions on a table, you would set Table_priv to "Select,insert" in the related Tables_priv records.
If you want to modify permissions for a user with a MySQL account, use Update instead of INSERT, regardless of whether you add or revoke permissions. To completely remove a user, delete records from each table that the user uses.
If you are willing to avoid sending a query to directly modify your credentials, you can look at MySQL's own mysqlaccess and mysql_setpermissions scripts.
Appendix 1 Quiz
In your newly installed MySQL server, you added a user to allow MySQL to be connected with the following statements:
GRANT all on samp_db.* to fred@*.snake.net identified "cocoa"
And Fred happened to have an account on the server host, so he tried to connect to the server:
%mysql-u Fred-pcocoa samp_db
ERROR 1045:access denied for user: ' Fred@localhost ' (Using password:yes)
Why?
The reason is:
Think first about how mysql_install_db establishes the initial permission table and how the server uses the user table record to match the client connection. When you initialize your database with mysql_install_db, it creates a user table like this:
Host User
localhost
Pit.snake.net
localhost
Pit.snake.net Root
Root
The first two records allow root to specify the localhost or host name to connect to the local server, and the latter two allow anonymous users to connect locally. When the Fred user is added,
Host User
localhost
Pit.snake.net
localhost
Pit.snake.net
%.snake.net Root
Root
Fred
When the server starts, it reads the records and sorts them (first by host, then by the user on the host), the more specific the front:
Host User
localhost
localhost
Pit.snake.net
Pit.snake.net
%.snake.net Root
Root
Fred
Two records with localhost are lined up, and the record for Root is first, because it is more specific than the null value. Pit.snake.net's records are similar. All of these are literal host values without any wildcard characters, so they are in front of the Fred Record, especially before the anonymous user is in the line of Fred.
As a result, when Fred tries to connect from localhost, the record of an empty user name in the host column matches before the record that contains the%.snake.net. The password for this record is empty because the default anonymous user does not have a password. Because a password was specified when Fred was connected, it was a mismatch and the connection failed.
Keep in mind, though, that it is convenient to use wildcard characters to specify which hosts the user can connect to. However, you may have problems connecting from the local host as long as you keep the anonymous user record in the table.
Generally, it is recommended that you delete anonymous user records:
Mysql> DELETE from user WHERE user= "";
Further, delete any anonymous users in other authorization tables, and the table with the User column has db, Tables_priv, and Columns_priv.
Appendix 2 to make a new MySQL installation more secure
After you install a new MySQL server yourself, you need to specify a directory for the root user of MySQL (default no password), otherwise if you forget this, you will have your MySQL in a very insecure state (at least for a period of time).
On Unix (Linux), after installing MySQL in accordance with the manual instructions, you must run the mysql_install_db script to build the MySQL database and initial permissions that contain the authorization table. On Windows, run the Setup program in the distribution to initialize the data directory and the MySQL database. Assume that the server is also running.
When you first install MySQL on a machine, the authorization table in the MySQL database is initialized like this:
