MySQL Server permission table

MySQL Server permission table

The MySQL server uses a permission table to control user access to the database. The permission table is stored in the mysql database and initialized by the mysql_install_db script. These permission tables are user, db, table_priv, columns_priv, and host respectively. The following describes the structure and content of these tables:

• User permission table: records the information of user accounts allowed to connect to the server. The permissions in the table are global.

• Database permission table: records the operation permissions of each account on each database.

• Table_priv permission table: records data table-level operation permissions.

• Columns_priv permission table: records the operation permissions at the data column level.

• Host permission table: the database permission table provides more detailed control over database-level operation permissions on a given host. This permission table is not affected by the GRANT and REVOKE statements.

You have noticed that the preceding permissions are not limited to row-level settings. In MySQL, you only need to implement row-level control by writing a program (using the GET-LOCK () function.

MySQL has many versions, so the structure of the permission table varies with versions. In this case, use the mysql_fix_privilege_tables script to fix the issue. Run as follows:

% Mysql_fix_privilege_tables rootpassword # the MySQL root User Password is provided here.

It is best to upgrade to MySQL 4.0.4 at once, because the db tables 4.0.2 and 4.0.3 do not have the Create_tmp_table_priv and Lock_tables_priv permissions.

 

The MySQL permission table defines two parts: one part defines the permission range, that is, WHO (account) Where (client host) can access what (database, data table, data column ); define permissions in other parts, that is, control the operations that users can perform. The following are some common permissions that can be used directly in the GRANT statement.

• Create temporary tables: allows you to create temporary tables.

• EXECUTE: Permission to EXECUTE stored procedures. The stored procedures are not implemented in the current MySQL version.

• FILE, allowing you to read and write files on the server host through the MySQL server. However, there are some restrictions that only accessible files can be read by any user. Files written on the server must not exist to prevent important system files from being overwritten by files written on the server. Despite these restrictions, do not grant this permission to common users for security purposes. Do not run the MySQL server as the root user, because the root user can create files anywhere in the system.

• Grant option, which allows you to GRANT permissions to other users.

• Lock tables. You can use the lock tables statement to LOCK the data table.

• PROCESS, allowing you to view and terminate any client thread. The show processlist statement or mysqladmin processlist command can be used to view the thread. The KILL statement or mysqladmin kill command can terminate the thread. In versions 4.0.2 and later, the PROCESS permission is only available for viewing threads, and the thread termination capability is controlled by the SUPER permission.

• RELOAD allows you to perform database management operations, such as FLUSH and RESET. It also allows you to execute mysqladmin commands: reload, refresh, flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, and flush-threads.

• Replication client, which allows you to query the locations of the master server and slave server in the image mechanism.

• Replication slave allows a customer to connect to the master server in the image mechanism and request to send a binary change log. This permission should be granted to the account used by the slave server to connect to the master server. Before version 4.0.2, the slave server was connected with the FILE permission.

• Show databases controls the user's permission to execute the show databases statement.

• SUPER: Allows thread termination. Use the mysqladmin debug command, change master, purge master logs, and SET statement to modify global variables. SUPER also allows you to decrypt DES based on the key stored in the DES key file.

The user permission table has an ssl_type data column to show whether the connection uses encrypted connections and which type of connections are used. It is an ENUM type data column and may take the following values:

• NONE. The default value indicates no encrypted connection is required.

• ANY, which indicates the encrypted connection is required. It can be ANY type of encrypted connection. Set by GRANT's require ssl clause.

• X509 indicates that an encrypted connection is required and a valid X509 Certificate is required. Set by the REQUIRE X509 clause of GRANT.

• SPECIFIED indicates that the encrypted connection must meet certain requirements. Set the value of ISSUER, SUBJECT, or CIPHER in the REQUIRE clause. As long as the value of the ssl_type column is SPECIFIED, MySQL checks the values in the ssl_cipher (encryption algorithm), x509_issuer (certificate issuer), and x509_subject (certificate subject) columns. The column types of these columns are BLOB type.

There are several other columns in the user permission table that set account resource usage. If the number of all the following data columns is zero, there is no limit:

• Max_connections: the number of server connections per hour.

• Max_questions: Number of query commands per hour.

• Max_updates, the number of data modification class query commands that can be issued per hour.

Precautions for setting the permission table:

• Delete all anonymous users.

• Find all users without a password and reset the password. Run the following command to query empty password users:

  mysql> SELECT host,user FROM user WHERE password = '''';

• Do not use wildcards in the host.

• It is best not to use the user permission table for authorization, because the table has global permissions.

• Do not grant permissions of the mysql database to others because the database contains permission tables.

• Do not misuse the grant option permission.

• FILE Permission can access files in the FILE system, so pay attention to the authorization. A user with FILE Permission can view all readable files on the server by executing the following statement:

  mysql> CREATE TABLE etc_passwd(pwd_entry TEXT);mysql> LOAD DATA INFILE ''/etc/passwd'' INTO TABLE etc_passwd;mysql> SELECT * FROM etc_passwd；

  If the access permission on the data directory of the MySQL server is not properly set, the security vulnerability that allows users with FILE permission to access other people's databases will be left behind. Therefore, we recommend that you set the data directory to be read only by the MySQL server. The following describes how to use a user with FILE Permission to read database data that is not strictly configured in the data directory:

  Mysql> use test; mysql> create table temp (B longblob); mysql> show databases # displays the database name list, -- skip-show-database: mysql> load data infile ''can be disabled ''. /db/xxx. frm '''into table temp fields escaped by ''' lines terminated by '''; mysql> select * from temp into outfile' xxx. frm''fields escaped by ''' lines terminated by '''; mysql> delete from temp; mysql> load data infile ''. /db/xxx. MYD ''to table temp fields escaped by ''' lines terminated by '''; mysql> select * from temp into outfile ''' xxx. MYD ''fields escaped by ''' lines terminated by '''; mysql> delete from temp; mysql> load data infile ''. /db/xxx. MYI ''to table temp fields escaped by ''' lines terminated by '''; mysql> select * from temp into outfile ''' xxx. MYI ''fields escaped by ''' lines terminated by '''; mysql> delete from temp;

  In this way, your database will be copied to your local device. If the server is running under the root user, the harm is even greater, because the root user can perform any operations on the server. Therefore, do not use the root user to run the server.

• Only grant the PROCESS permission to a trusted user. This user can query the thread information of other users.

• Do not grant the RELOAD permission to irrelevant users because the permission can issue FLUSH or RESET statements. These are database management tools. Improper use of these permissions may cause database management problems.

• Do not grant the ALTER permission to a common user because the permission can be used to change the data table.

Modify the permission table using the GRANT statement:

• When you send a GRANT statement, the server creates a record in the user permission table and records your username, Host name, and Password in the User, Host, and Password columns. If global permissions are set, the settings are recorded in the corresponding permission column.

• If you set database-level permissions in GRANT, the User name and Host name you give will be recorded in the User and Host columns of the database permission table, and the database name will be recorded in the db column, the permission is recorded in the related permission column.

• The next step is to set permissions at the data table and data column level. The setting method is the same as above. The server records the user name, host name, database name, and corresponding data table name and data column name in the data table.

Deleting user permissions is actually to delete all corresponding account records in these permission tables.

Reproduced from: aspnetjia (http://www.aspnetjia.com)