MySQL Security Guide (3) (redirected)

MySQL Security Guide (3)


2.4 Setting up users without GRANT
If you have a MySQL version earlier than 3.22.11, you cannot use the GRANT (or REVOKE) statement to set 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 grant table. Then you can do the same thing yourself by issuing the INSERT statement manually.

When you issue a GRANT statement, you specify a username and hostname, and possibly a password. A user table record is generated for this user, and these values ​​are recorded in the User, Host, and PassWord columns. If you specify global permissions in a GRANT statement, these permissions are recorded in the record's Permissions column. What should be noted is that the GRANT statement encrypts the password for you, but INSERT does not. You need to use the PASSWORD() function in INSERT to encrypt the password.

If you specify database-level permissions, the username and hostname are recorded in the User and Host columns of the db table. The database you authorize it for 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 the username, hostname, and database, as well as related tables and columns. The permissions granted are recorded in the Permissions column.

If you remember the previous introduction, you should be able to do what GRANT does without the GRANT statement. Remember that when you modify the authorization table directly, you will tell the server to reload the authorization table, otherwise it will not know about your changes. You can execute a mysqladmin flush-privileges or mysqladmin The reload command forces a reload. If you forget to do this, you'll be left wondering why the server isn't doing what you want.

The following GRANT statement creates a superuser with ownership. Including the ability to delegate to others:

GRANT ALL ON *.* TO anyname@localhost IDENTIFIED BY "passwd"
WITH GRANT OPTION
This statement will create a record for anyname@localhost in the user table and open all permissions, because this is where the super user (global) permissions are stored. To do the same thing with the INSERT statement, 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 doesn't work, depending on your MySQL version. The structure of the authorization table has changed and you may not have 14 authorization columns in your user table. Use SHOW COLUMNS Find out each permission column your grant table contains and adjust your INSERT statement accordingly. The following GRANT statement also creates a user with superuser status, but with only a single permission:

GRANT RELOAD ON *.* TO flush@localhost IDENTIFIED BY "flushpass"
The INSERT statement in this example is simpler than the previous one, it easily lists the column names and specifies 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")
Use an ON for database level permissions db_name.* clause instead of ON *.* for authorization:

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 the user can connect), but we also need to create a db table record to record the 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 for GRANT permissions; with WITH for the last database level GRANT For the GRANT statement of OPTION, you need to set the column to "Y".

To set table-level or column-level permissions, you use the INSERT statement on tables_priv or columns_priv. Of course, if you don't have a GRANT statement, you won't have these tables since they all appear at the same time in MySQL. If you do have these tables and for some reason want to manually manipulate them, know that you cannot enable permissions with individual columns.

You set the tables_priv.Table_priv or columns_priv.Column_priv column to contain the permission value you want to enable. For example, to enable SELECT and INSERT permissions on a table, you set Table_priv to "Select,Insert" in the related tables_priv record.

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 delete a user, delete records from every table used by the user.

If you prefer to avoid issuing a query to directly modify the full authority table, you can take a look at the mysqlaccess and mysql_setpermissions scripts that come with MySQL.



Appendix 1 Quiz
After you have just installed a MySQL server, and you have added a user that is allowed to connect to MySQL, use the following statement:

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:

First consider how mysql_install_db establishes the initial permission table and how the server uses user table records to match customer connections. 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 localhost or hostname to connect to the local server, and the last two allow anonymous users to connect from local. After adding fred user,

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 user on the host), with the more specific being ranked first:

Host User
localhost
localhost
pit.snake.net
pit.snake.net
%.snake.net root

root

fred

There are two records for localhost ranked together, while the record for root is ranked first because it is more specific than null. The records for pit.snake.net are similar. All of these are literal Host values ​​without any wildcards, so they are ranked before the record for fred, especially anonymous users before fred.

The result is that when fred tries to connect from localhost, a record with an empty username in the Host column is matched before a record containing %.snake.net. The password for this record is empty because the default anonymous user does not have a password. Because Fred specified a password when connecting, there was a mismatch and the connection failed.

The thing to remember here is that although it is convenient to use wildcards to specify the hosts from which users can connect. But you will have problems connecting from localhost, as long as you keep anonymous user records in the table.

Generally, it is recommended that you delete anonymous user records:

mysql> DELETE FROM user WHERE User="";

Go one step further and delete any anonymous users in other authorization tables. Tables with User columns are db, tables_priv and columns_priv.

Appendix 2 Making a new MySQL installation more secure
After you install a new MySQL server yourself, you need to specify a directory for the MySQL root user (default no password), otherwise if you forget this, you will lose your MySQL In an extremely unsafe state (at least for a while).

On Unix (linux), after installing MySQL according to the instructions in the manual, you must run the mysql_install_db script to establish the mysql database containing the authorization table and initial permissions. On Windows, run the Setup program in the distribution to initialize the data directory and mysql database. It is assumed that the server is also running.

When you first install MySQL on your machine, the authorization table in the mysql database is initialized like this:

You can connect as root from localhost (localhost) without specifying a password. The root user has all rights (including administrative rights) and can do anything. (By the way, the MySQL superuser has the same name as the Unix superuser, and they have nothing to do with each other.)
Anonymous access is granted to users who can connect locally to databases named test and any database whose name starts with test_. Anonymous users can do anything to the database but have no administrative rights.
Connections to multiple servers from localhost are allowed, regardless of whether the connecting user uses a localhost hostname or a real hostname. Such as:

% mysql -h localhost test

% mysql -h pit.snake.net test

The fact that you connect to MySQL as root without even specifying a password just means that the initial installation is not secure, so the first thing you do as an administrator should be to set the root password, and then depending on the method you use to set the password, you can also Tells the server to reload the authorization table so that it is aware of this change. (When the server starts, it reloads the tables into memory and may not know you have modified them.)

For MySQL For versions 3.22 and above, you can use mysqladmin to set the password:

% mysqladmin -u root password yourpassword

For any version of MySQL, you can use the mysql program and directly modify the user authorization table in the mysql database:

% mysql -u root mysql
mysql>UPDATE user SET password=PASSWORD("yourpassword") WHERE User="root";

If you have an old version of MySQL, use mysql and UPDATE.

After you set the password, check if you need to tell the server to reload the authorization table by running the following command:

% mysqladmin -u root status

If the server still lets you connect as root without specifying a password, reload the authorization table:

% mysqladmin -u root reload

After you set the root password (and reload the authorization tables if necessary), you will need to specify the password any time you connect to the server as root.

The above is the content of MySQL Security Guide (3) (reprint). For more related articles, please pay attention to the PHP Chinese website (www.php.cn)!