MySQL Security Guide (3)

The ORACLE tutorial is: MySQL Security Guide (3) (go ). MySQL Security Guide (3)

Author: Yan Zi



2.4 set users without GRANT
If you have a MySQL version earlier than 3.22.11, you cannot use the GRANT (or REVOKE) statement to set user and access permissions, but you can directly modify the content 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 by manually issuing the INSERT statement.

When you issue a GRANT statement, you specify a username and host name, and there may be passwords. Generate a user table record for this User, 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 permission column of the record. The GRANT statement encrypts the PASSWORD for you, but INSERT is not. You need to use the PASSWORD () function to encrypt the PASSWORD in 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 permission 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 granted permissions are recorded in the permission column.

If you still remember the previous introduction, you should be able to do what GRANT can do even without using the GRANT statement. Remember, when you directly modify the authorization table, you will notify the server to reload the authorization table, otherwise he will not know your change. You can execute a mysqladmin flush-privileges or mysqladmin reload command to force an overload. If you forget to do this, you will wonder why the server does not do what you want.

The following GRANT statement creates a Super User with ownership. Includes the ability to grant permissions 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 superuser (global) Permission is stored and the INSERT statement is used to do the same thing, statement:

Insert into user VALUES ("localhost", "anyname", PASSWORD ("passwd "),
"Y ", "Y", "Y ")
You may find it does not work, depending on your MySQL version. The authorization table structure has changed and you may not have 14 permission columns in your user table. Use show columns to locate each permission column in your authorization table and adjust your INSERT statement accordingly. The following GRANT statement also creates a user with the superuser identity, but has only one permission:

Grant reload on *. * TO flush @ localhost identified by "flushpass"
In this example, the INSERT statement is simpler than the previous one. It is easy to list column names and only specify one permission column. All other columns are set to the default "N ":

Insert into user (Host, Password, Reload) VALUES ("localhost", "flush", PASSWORD ("flushpass"), "Y ")
For database-level permissions, use an ON 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 ), however, we also need to create a db table 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", "N", "Y ")

The "N" column is the GRANT permission. You must set this column to "Y" for a GRANT statement WITH the grant option at the database level at the end ".

To set table-level or column-level permissions, you use the INSERT statement for tables_priv or columns_priv. Of course, if you do not have a GRANT statement, you will not have these tables because they appear in MySQL at the same time. If you do have these tables and want to manually operate them for some reason, you must be aware that you cannot enable permissions with individual columns.

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

If you want to modify the permissions of a user with a MySQL account, use UPDATE instead of INSERT, no matter whether you add or revoke permissions. To completely delete a user, delete records from each table used by the user.

If you want to avoid sending a query to directly modify the full table, you can check the MySQL mysqlaccess and mysql_setpermissions scripts.



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

Grant all on samp_db. * TO fred @ * .snke.net IDENTIFIED "cocoa"

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 the user table record to match the customer connection. When you use mysql_install_db to initialize your database, it creates a user table like this:

Host User
Localhost
Pit.snke.net
Localhost
Pit.snke.net root
Root



The first two records allow root users to specify localhost or host name to connect to the local server, and the last two records allow anonymous users to connect from the local server. When the user fred is added,

Host User
Localhost
Pit.snke.net
Localhost
Pit.snke.net
% .Snke.net root
Root


Fred

When the server is started, it reads records and sorts them (first by host and then by user on the host). The more specific, the more advanced:

Host User
Localhost
Localhost
Pit.snke.net
Pit.snke.net
% .Snke.net root

Root

Fred

The two records with localhost are listed together, and the root record is ranked first, because it is more specific than the null value. The record of pit.snke.net is similar. All of these are literal Host values without any wildcard characters, so they are placed before the fred record, especially for anonymous users.

The result is that when fred tries to connect from localhost, the record of an empty username in the Host column matches before the record containing % .snke.net. The password for this record is blank because the default anonymous user does not have a password. Because a password is specified during the fred connection, a mismatch occurs and the connection fails.

It is very convenient to specify the host from which the user can connect using wildcards. However, you may encounter problems when connecting from the local host, as long as you keep anonymous user records in the table.

Generally, we recommend that you delete anonymous user records:

Mysql> delete from user WHERE User = "";

Furthermore, you can delete any anonymous users in other authorization tables. Tables with User columns include db, tables_priv, and columns_priv.

Appendix 2 makes a new MySQL installation safer
After you install a new MySQL server, you need to specify a directory for the root user of MySQL (no password by default). Otherwise, if you forget this

[1] [2] Next page

The ORACLE tutorial is: MySQL Security Guide (3) (go ). MySQL is in an extremely insecure state (at least for a period of time ).

On Unix (Linux), after installing MySQL according to instructions in the manual, you must run the mysql_install_db script to create the mysql database and initial permissions containing the authorization table. 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 a machine, the authorization table in the mysql database is initialized as follows:

You can connect to the local host as a root without specifying a password. Root users have all permissions (including management permissions) and can do anything. (By The Way, MySQL Super Users have the same name as Unix Super Users, and they have nothing to do with each other .)
Anonymous Access authorized users can connect to a database named test and any database named test _ locally. Anonymous Users can do anything to the database, but have no management permission.
Multi-server connection from the local host is allowed, regardless of whether the connected user uses a localhost host name or a real host name. For example:

% Mysql-h localhost test

% Mysql-h pit.snke.net test

The fact that you use root to connect to MySQL does not even specify a password means that the initial installation is not secure. Therefore, as an administrator, the first thing you need to do is to set the root password, then, based on how you set the password, you can also tell the server to reload the authorization table because it knows the change. (When the server starts, it reloads the table to the memory and may not know that you have modified them .)

For MySQL 3.22 and later versions, you can use mysqladmin to set a password:

% Mysqladmin-u root password yourpassword

For any MySQL version, 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 earlier MySQL version, use mysql and UPDATE.

After you set the password, run the following command to check whether you need to tell the server to reload the authorization table:

% Mysqladmin-u root status

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

% Mysqladmin-u root reload

After you set the root password (and if you need to reload the authorization table), you need to specify the password at any time when connecting to the server as root.

Previous Page [1] [2]