MySQL Security Guide

mysql| Security | Security as a MySQL system administrator, you are responsible for maintaining the data security and integrity of your MySQL database system. This article mainly describes how to build a secure MySQL system, from the system internal and external network two angles, to provide you with a guide.

This article mainly considers the following security-related issues:


• Why is security so important that you should guard against those attacks?
• The risks that the server faces (internal security), how do you handle it?
• Client risk for connecting to the server (external security), how to handle it?
The MySQL administrator has the responsibility to secure the contents of the database so that the data records can only be accessed by those users who are properly authorized, which involves internal security and external security of the database system.
Internal security is concerned with file system-level issues, that is, preventing MySQL data directories (DATADIR) from being attacked by people (legitimate or stolen) who have an account on the server host. If the permissions of the data directory content are excessively granted so that everyone can simply replace the files that correspond to those database tables, it makes no sense to make sure that the control of the client's authorization table for access through the network is set correctly.

External security is concerned with customers who connect to the server from the outside through the network, that is, to protect the MySQL server from attacks from connections to the server over the network. You must set up the MySQL authorization Table (grant table) so that they do not allow access to server-managed database content unless a valid username and password are provided.

Here is a detailed description of how to set up file system and authorization table MySQL, to achieve MySQL two security.


I. Internal security-Securing Data Directory Access
The MySQL server provides a flexible permission system through the authorization table in the MySQL database. You can set the contents of these tables, allow or deny customer access to the database, which provides you with the security to prevent unauthorized network access against your database, but if other users on the host have direct access to the contents of the data directory, establishing good security over the network access to the database is no help to you, Unless you know that you are the only user who is logged on to the MySQL server running the host, you need to be concerned about the likelihood that other users on this machine will gain access to the data directory.

Here are the things you should protect:


• Database files. Obviously, you want to maintain the private nature of the server-managed database. Database owners often and should consider the security of database content, even if they do not want to, should also consider when the content of the database open, rather than through poor data directory security to expose the content.
• Log file. General and update logs must be secured because they contain the query text. Anyone who has access to the log file can monitor what the database has been doing.
The more important consideration of log file security is that queries such as Grant and set password are also documented, and the general and update log contains text with sensitive queries, including passwords (MySQL uses password encryption, but it is not applied to subsequent connections after it has been set up). The process of setting a password is designed like a query such as grant or set password, and these queries are recorded in plain text in a log file. If an attacker has read access to a daily file, simply run grep on the log file to find the words such as grant and password to discover sensitive information.
Obviously, you don't want other users on the server host to have write access to the database directory files, because they can rewrite your state files or database table files, but Read permissions are also dangerous. If a database table file can be read, steal files and get MySQL itself, in plain text to display the contents of the table is also very troublesome, why? Because you have to do the following things:


• Install your own "custom" MySQL server on the server host, but have a different port, socket, and data directory from the official server version.
• Run mysql_install_db Initialize your data directory, which gives you access to your server as a MySQL root user, so you have full control over the server access mechanism, and it also creates a test database.
• will correspond to the test directory in the database directory where you want to steal the copy of the table file to your server.
• Start your server. You are free to access the database tables, show table from test shows that you have a copy of the stolen table, and SELECT * Displays the entire contents of any one of them.
• If you are really vicious, expose your permissions to any anonymous user on your server so anyone can connect to the server from anywhere to access your test database. You will now be posting the stolen database tables to the public.
Considering, from the opposite point of view, do you want someone to do this to you? Of course not! You can determine whether your database contains unsafe files and directories by executing the LS-L command in the database record. Find files and directories that have the group and other user permission settings. The following is a list of unsafe data directories:

　
% Ls-l
Total 10148
Drwxrwxr-x MYSQLADM Wheel 1024 May 8 12:20.
Drwxr-xr-x root Wheel 8 13:31.
DRWX------2 mysqladm mysqlgrp APR 15:57 Menagerie
Drwxrwxr-x 2 mysqladm wheel 20:40 MySQL
Drwxrwxr-x 7 Mysqladm Wheel AUG 1998 Sql-bench
Drwxrwxr-x 2 Mysqladm Wheel 1536 May 6 06:11 test
DRWX------2 Mysqladm mysqlgrp 1024 May 8 18:43 tmp
....


As you can see, some databases have the right permissions, while others are not. This case is the result of a period of time. Less restrictive permissions are set by older versions of servers that have less stringent permissions than newer versions (note that more restrictive directories Menageria and TMP have more recent dates). The current version of MySQL ensures that these files can only be read by users running the server.

Let's fix these permissions so that only the server users can access them. Your primary protection tool comes from the Setup files and the directory owner and mode tools provided by the UNIX file system itself. Here's what we're going to do:


• Enter the directory
% CD DataDir


• Set all files in the data directory owner to be owned by the account used to run the server (you must perform this step with root). Use Mysqladm and mysqlgrp as the user name and group name for this account in this article. You can use one of the following commands to change the owner:
# chown Mysqladm.mysqlgrp.

# Find. -follow-type D-print | Xargs Chown Mysqladm.mysqlgrp


• The pattern of setting up your data directory and database directory allows them to be read only by Mysqladm, which prevents other users from accessing the contents of your database directory. You can run as root or mysqladm with one of the following commands.
% Chmod-r go-rwx.

% find. -follow-type D-print | Xargs chmod go-rwx


• The owner and mode of the data directory content are MYSQLADM settings. Now you should make sure that you always run the server as a mysqladm user, because now this is the only user (except root) that accesses the database directory permissions.
After you complete these settings, you should eventually get the following data directory permissions:

% Ls-l
Total 10148
DRWXRWX---mysqladm mysqlgrp 1024 May 8 12:20.
Drwxr-xr-x root Wheel 8 13:31.
DRWX------2 mysqladm mysqlgrp APR 15:57 Menagerie
DRWX------2 mysqladm mysqlgrp 20:40 MySQL
DRWX------7 Mysqladm mysqlgrp Aug 1998 Sql-bench
DRWX------2 Mysqladm mysqlgrp 1536 May 6 06:11 test
DRWX------2 Mysqladm mysqlgrp 1024 May 8 18:43 tmp
....



Ii. External Security-Securing network access
The MySQL security system is flexible and allows you to set user permissions in many different ways. Generally, you can use the standard SQL statements GRANT and REVOKE statements, and they modify the authorization form for you to control client access, however, You may have an older version of MySQL that does not support these statements (these statements do not work before 3.22.11), or you find that user permissions do not seem to work the way you want them to. In this case, it is helpful to understand the structure of the MySQL authorization table and how the server can use them to determine access rights, which allows you to add, delete, or modify user permissions by directly modifying the authorization table, and it also allows you to diagnose permissions issues when checking those tables.

For information on how to manage user accounts, see "MySQL User management." For a detailed description of GRANT and REVOKE statements, see the MySQL reference manual.

2.1 The structure and content of the MySQL authorization table
Access to the MySQL database by customers who connect to the server over the network is controlled by the contents of the authorization table. These tables are located in the MySQL database and initialized (run the mysql_install_db script) during the first installation of MySQL. There are 5 tables in the authorization table: User, DB, host, Tables_priv, and Columns_priv.

Table 1 user, DB, and host authorization table structures
Access Scope columns

User DB Host
Host host Host
User DB DB
Password User
Database/table Permission columns
Alter_priv Alter_priv Alter_priv
Create_priv Create_priv Create_priv
Delete_priv Delete_priv Delete_priv
Drop_priv Drop_priv Drop_priv
Index_priv Index_priv Index_priv
Insert_priv Insert_priv Insert_priv
References_priv References_priv References_priv
Select_priv Select_priv Select_priv
Update_priv Update_priv Update_priv
File_priv Grant_priv Grant_priv
Grant_priv
Process_priv
Reload_priv
Shutdown_priv
　
Table 2 structure of genus Tables_priv and Columns_priv

Access Scope columns
Tables_priv Columns_priv
Host Host
DB db
User User
TABLE_NAME TABLE_NAME
column_name
Permission columns
Table_priv Column_priv

The contents of the authorization table are used for the following purposes:


User Table
The user table lists the users and their passwords that can connect to the server, and it specifies what global (superuser) permissions they have. Any permissions that are enabled in the user table are global permissions and apply to all databases. For example, if you have the Delete permission enabled, the users listed here can delete records from any table, so think carefully before you do so.

DB table
The DB table lists the databases, and the user has permission to access them. The permissions specified here apply to all tables in a database.

Host Table
The host table is used in conjunction with the DB table to control the access of a particular host to the database at a better level, which may be better than using DB alone. This table is not affected by the grant and REVOKE statements, so you may find that you are not using it at all.

Tables_priv Table
The Tables_priv table specifies table-level permissions, and one of the permissions specified here applies to all columns of a table.

Columns_priv Table
The COLUMNS_PRIV table specifies column-level permissions. The permissions specified here apply to a specific column of a table.
In the "Do Not grant users" section, we discuss how the grant statement works with modifying these tables, and how you can achieve the same effect by directly modifying the authorization table.

Tables_priv and Columns_priv tables are introduced in the MySQL 3.22.11 version (along with the grant statement). If you have an earlier version of MySQL, your MySQL database will have only user, DB, and host tables. If you upgrade from older versions to 3.22.11 or updates without tables_priv and Columns_priv tables, run mysql_fix_privileges_tables scripts to create them.

MySQL does not have a rows_priv table because it does not provide record-level permissions, for example, you cannot limit the number of rows in a table that contain a specific column value. If you really need this ability, you have to use application programming to provide it. If you want to perform the recommended record-level locking, you can do so using the Get_lock () function.

An authorization table contains two columns: a range column that determines when a permission is used, and a permission column that determines which permissions are granted.

2.1.1 Authorization Table Range column
The Authorization Table scope column specifies when the permissions in the table are to be applied. Each authorization table entry contains the user and the host column specifies when permissions are applied to a given user's connection from a given host. Other tables contain additional scope columns, such as a DB table that contains a DB column that indicates which database the permissions are applied to. Similarly, the Tables_priv and Columns_priv tables contain range fields, narrowing down to specific columns in a particular table or table within a database.

2.1.2 Authorization Table Permission column
Authorization tables also contain permission columns that indicate what permissions the user specified in the scope column has. The permissions supported by MySQL are shown in the following table. The table uses the permission name of the GRANT statement. There is a clear correlation between the names of most rights columns in the user, DB, and host tables and the GRANT statement. such as Select_priv corresponds to the SELECT permission.

2.1.3 Database and Table permissions
The following permissions apply to operations on databases and tables.


· Alter
Allows you to use the ALTER TABLE statement, which is actually a simple first level permission that you must have by other permissions, which see what you want to do with the database.

· CREATE
Allows you to create databases and tables, but does not allow indexes to be created.

· DELETE
Allows you to delete existing records from the table.

· DROP
Allows you to delete (discard) databases and tables, but does not allow deletion of indexes.

· INDEX
Allows you to create and delete indexes.

· REFERENCES
Not at the moment.

· SELECT
Allows you to retrieve data from a table using the SELECT statement. It is not necessary for a SELECT statement that does not involve a table, such as select Now () or select 4/2.

· UPDATE
Allows you to modify the existing records in the table.
2.1.4 Administrative rights
The following permissions are used for administrative operations that control the operation of the server or user authorization capability.


· FILE
Allows you to tell the server to read or write files on the server host. This permission should not be granted casually, it is dangerous, see "Evade authorization table risk". The server is indeed more cautious about keeping this right within a certain range. You can only read files that anyone can read. The file you are writing must not be an existing file, which prevents you from forcing the server to rewrite important files, such as/etc/passwd or data directories belonging to other people's databases.
If you authorize file permissions, make sure that you do not run the server as root for UNIX because root can create new files anywhere on the file system. If you run a server as an unprivileged user, the server can only create files in directories that are accessible to users.


· GRANT
Allows you to grant your own permission to others, including Grant.

· PROCESS
Allows you to view information about the threads (processes) that are running within the server by using the show process statement or the mysqladmin process command. This permission also allows you to kill the thread with the KILL statement or the Mysqladmin kill command.
You can always see or kill your own thread. Process permissions give you the ability to do these things to any thread.


· RELOAD
Allows you to perform a number of server management operations. You can issue flush statements, and you can also refer to mysqladmin reload, refresh, flush-hosts, Flush-logs, Flush-privileges, and Flush-tables commands.

· SHUTDOWN
Allows you to shut down the server with mysqladmin shutdown.
In the user, DB, and host tables, each permission is specified in a separate column. These columns are all declared as an enum ("N", "Y") type, so the default value for each right is "N". Permissions in Tables_priv and Columns_priv are represented as a set, which allows permissions to be specified in any combination with a single column. These two tables are newer than the other three tables, which is why they use a more efficient representation. (possibly in the future, user, DB, and host tables are also represented by a set type.) ）

The Table_priv column in the Tables_priv table is defined as:

SET (' Select ', ' Insert ', ' Update ', ' Delete ', ' Create ', ' Drop ', ' Grant ', ' References ', ' Index ', ' Alter ')
The Column_priv column in the Coloums_priv table is defined as:

SET (' Select ', ' Insert ', ' Update ', ' References ')
Column permissions are less than table permissions because of the lesser permissions at the column level. For example you can create a table, but you cannot create an orphaned column.

The user table contains columns for permissions that do not exist on other authorization tables: File_priv, Process_priv, Reload_priv, and Shutdown_priv. These permissions apply to actions that are not relevant to any particular database or table that you want the server to perform. It is pointless to allow a user to shut down a database based on what the current database is.

2.2 How the server controls customer access
When you use MySQL, there are two stages to customer access control. The first stage occurs when you try to connect to the server. The server looks up the user table to see if it can find an entry that matches your name, the host you are connecting from there, and the password you provide. If there is no match, you cannot connect. If there is a match, establish the connection and proceed to the second stage. At this stage, for each query you send out, the server checks the authorization table to see if you have sufficient permissions to execute the query, and the second stage continues until you end the conversation with the server.

This section details the rules used by MySQL servers to match authorization table entries to incoming connection requests or queries, including the type of valid value in the scope column of the authorization table, the way in which the permission information in the authorization table is combined, and the order in which entries in the table are checked.

2.2.1 Range Column Content
Some range columns require literal values, but most of them allow wildcard characters or other special values.


· Host
A host column value can be either a hostname or an IP address. A value localhost means a local host, but it only matches when you use a localhost hostname, not when you are using the host name. If your local hostname is pit.snake.net and there are two records for you in the user table, one has a host value or localhost, and the other has a pit.snake.net, the localhost record will only match when you connect localhost, others in the connection P Match when It.snake.net. If you want to allow customers to connect in two ways, you need to have two records in the user table.

You can also specify a host value with a wildcard character. You can use the pattern characters "%" and "_" in SQL and have the same meaning when you use a LIKE operator in a query (no regex operators are allowed). SQL mode characters can be used for both host names and IP addresses. such as%wisc.edu matches any host within the wisc.edu domain, while%.edu matches any educational college host. Similarly, 192.168.% matches any host in the 192.168 Class B subnet, while 192.168.3.% matches any host in the 192.168.3 Class C subnet.

The% value matches all hosts and can be used to allow a user to connect from anywhere. A blank host value is equal to%. (Exception: In the DB table, a blank host value is "Further checks the host table", as described in "Query access validation.") ）

From MySQL 3.23, you can also specify an IP address that indicates the netmask that is used for the network address, such as 192.168.128.0/17 to specify a 17-bit network address and match its IP address to any host 192.168.128 the top 17 bits.


· User
The user name must be literal or blank. A blank value matches any user. % as a user value does not mean blank, instead it matches a literal% name, which may not be what you want.

When a incoming connection is validated against the user table, the record that matches it contains a blank user value, and the customer is considered an anonymous subscriber.


· Password
The password value can be either empty or non-empty, and wildcard characters are not allowed. A null password does not mean matching any password, it means that the user must not specify a password.

The password is stored as an encrypted value, not as a literal text. If you store a literal password in the password column, the user will not be able to connect! The GRANT statement and the mysqladmin Password command automatically encrypt passwords for you, but if you use commands such as INSERT, REPLACE, UPDATE, or set password, you must use password ("New_password") Instead of a simple "new_password" to specify a password.


· Db
In the Columns_priv and Tables_priv tables, the DB value must be a true database name (literally), and no schema or whitespace is allowed. In DB and host, the DB value can be specified either literally or using the SQL mode character '% ' or ' _ ' to specify a wildcard character. A '% ' or white space matches any database.

· Table_name,column_name
The values in these columns must be literal table or column names, and no pattern or whitespace is allowed.
Some range columns are considered case sensitive by the server, and the rest is not. These principles are summarized in the following table. Note that table_name values are always treated as case sensitive, even if the case sensitivity of the table names in the query is treated as if the file system of the host on which the server is running (Unix is case sensitive and Windows is not).

Table 3 case sensitivity for authorization table range columns
Column
Host
User
Password
Db
table_name
column_name
Case sensitivity
No
Yes
Yes
Yes
Yes
No


2.2.2 Query Access Verification
Every time you issue a query, the server checks to see if you have sufficient permissions to execute it, checking in the order of user, DB, Tables_priv, and Columns_priv, knowing that it determines that you have the appropriate access rights or that you have searched all the tables for nothing. More specifically:


• The server checks the user table to match the record of your start connection to see what global permissions you have. If you have and they are sufficient for the query, the server executes it.
• If your global permissions are insufficient, the server looks for you in the DB table and adds the permissions in that record to your global permissions. If the result is sufficient for the query, the server executes it.
• If your global and database-level combination of permissions is not enough, the server continues to find, first in the Tables_priv table, and then columns_priv the table.
• If you still do not have permission after checking all tables, the server rejects your attempt to execute the query.
In the terms of Boolean operations, permissions in authorization tables are used by the server:

User or Tables_priv OR columns_priv

You may be wondering why the previous description only refers to 4 authorization tables, but there are actually 5. In fact, the server checks access permissions like this:

User or (DB and host) or Tables_priv or Columns_priv

The first simpler expression is because the host table is not affected by the grant and REVOKE statements. If you always manage user rights with Grant and revoke, you never have to consider the host table. But its working principle you should know:


• When the server checks database-level permissions, it looks for the DB table for the customer. If the host column is empty, it means "check the host table to find out which host can access the database."

• The server looks in the host table for the same DB column value as the record from the DB table. If no host record matches the client host, database-level permissions are not granted. If any one of these records does have a host column value that matches the connection's client host, the DB table record and the host table record combine to generate the client's database-level permissions.
However, permissions are combined with a logical and (with), which means that the client does not have that permission unless a given permission is available in two tables. In this way, you can grant a basic set of permissions in the DB table and then use the host table to selectively disable them for a particular host. If you can allow access to the database from all hosts in your domain, but turn off the database permissions of those hosts in the less secure zone.

The preceding description undoubtedly makes the access check sound a rather complicated process, in particular, you assume that the server has permission checks on every query you make, but this process is fast because the server does not actually look up information from the authorization table for each query, instead it reads the contents of the table into memory at startup, Then verify that the query uses an in-memory copy. This greatly improves the performance of the access check operation. But there is a very obvious side effect. If you modify the contents of the authorization table directly, the server will not be aware of the change in permissions.

For example, if you add a new user by adding a new record to the user table with an INSERT statement, the user named in the record will not be able to connect to the server. This is very confusing for administrators--and sometimes for experienced veterans--when the solution is simple: After you change them, tell the server to overload the authorization table content, and you can send a flush privileges or execute mysqladmin Flush-privileges (or if you have an older version that does not support flush-privileges, use mysqladmin reload. ）。

2.2.3 Range Column Matching order
The MySQL server sorts the records in the authorization table in a particular way, and then the incoming connections are matched by a sequential browsing of the records. The first match found determines which records are used. It is important to understand the sort order used by MySQL, especially for the user table.

When the server reads the contents of the user table, it sorts records based on the values in the host and the user columns, and the host value is determined (the same host values are sorted together and then ordered according to the user value). However, the sort is not a code order (sorted by word), it is only partly. Keep in mind that the literal word takes precedence over the pattern. This means that if you are connecting to a server from Client.your.net and the host has client.your.net and%.your.net two values, the first one is selected. Similarly,%.your.net takes precedence over%.net, then%. The same is true for IP address matching.

In a word, the more specific the more priority. You can see the examples in the appendix of this article.

2.3 Avoid authorization table risk
This session introduces some of the precautions you have given when you authorize, as well as the risks associated with the choice of unknown value. Generally, you will be "stingy" to grant superuser privileges, that is, do not enable permissions in entries in the user table, and use other authorization tables to restrict user rights to databases, tables, or columns. Permissions in the user table allow you to affect the operation of your server or access any table in any database.

Do not grant permissions to the MySQL database. A user with permissions to the authorization table database might modify the table to obtain permissions on any other database. Granting permission to allow one user to modify a MySQL database table also actually gives the user a global grant permission. If the user can modify the table directly, it is equivalent to being able to emit any grant statement that you can imagine.

File permissions are particularly dangerous and do not authorize it easily. Here's what a person with file permissions can do to remove:

CREATE TABLE etc_passwd (pwd_entry TEXT);
LOAD DATA INFILE "/etc/passwd" into TABLE etc_passwd;
SELECT * from ETC_PASSWD;

After these statements are issued, the user already has the contents of your password file. In fact, any content that is publicly readable on the server can be accessed over the network by a user who has file permissions.

File permissions can also be used to compromise a database on a system that does not have permission to set sufficient rights. That's why you should set the data directory to read only by the server. If a file corresponding to a database table can be read by anyone, not just the user server account users can read, any users with file permissions can also connect and read through the network. This procedure is shown below:


• Create a table with a LONGBLOB column:
USER test;
CREATE TABLE tmp (b longblob);


• Use the table to read each content corresponding to the database table file you want to steal, and then write the contents of the table to a file in your own database:

LOAD DATA INFILE "./other_db/x.frm" into TABLE tmp
FIELDS escaped by "" LINES terminated by "";
SELECT * from tmp into outfile "Y.FRM"
FIELDS escaped by "" LINES terminated by "";
DELETE from TMP;
LOAD DATA INFILE "./OTHER_DB/X.ISD" into TABLE tmp
FIELDS escaped by "" LINES terminated by "";
SELECT * from tmp into outfile "Y.ISD"
FIELDS escaped by "" LINES terminated by "";
DELETE from TMP;
LOAD DATA INFILE "./other_db/x.ism" into TABLE tmp
FIELDS escaped by "" LINES terminated by "";
SELECT * from tmp into outfile "Y.ism"

• Now you have a new table Y, which contains other_db.x content and you have carte blanche to access it.
To avoid being attacked in the same way, set the permissions on your data directory according to the instructions in the "first part of internal security-protecting your data directory." You can also use the--skip-show-database option when you start the server to restrict users to using show databases and show TABLES for databases to which they do not have access rights. This helps prevent users from finding information about databases and tables that they cannot access.

ALTER permission can be used in a way that is not desired. Suppose you want User1 to have access to Table1 but not to tables2. A user with ALTER permission can rename table2 to Table1 to cynical by using ALTER TABLE.

Beware of grant permissions. Two users with different permissions, but with grant permissions, can make each other's rights more powerful.

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:


• You can connect as root from the local host (localhost) without specifying a password. The root user has all the permissions (including administrative privileges) and can do anything. (By the way, MySQL Superuser and Unix superuser have the same name, they have nothing to do with each other.) ）
• Anonymous access is granted to a database that the user can start with a local connection named Test and any name Test_. Anonymous users can do anything with the database, but without administrative privileges.
A multiple-server connection from a local host is allowed, regardless of whether the connected user is using a localhost hostname or a real host name. Such as:

% mysql-h localhost test

% mysql-h pit.snake.net Test

The fact that you're connecting to MySQL with root or not even specifying a password means that the initial installation is unsafe, so the first thing you should do as an administrator is to set the root password, and then depending on how you set the password, you can also tell the server to overload the authorization table because it knows the change. (When the server starts, it overloads the table into memory and may not know that you have modified them.) ）

For MySQL 3.22 and above, you can set the password with mysqladmin:

% mysqladmin-u root password yourpassword

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

% mysql-u root MySQL
Mysql>update user SET Password=password ("YourPassword") WHERE user= "root";

If you have the old version of MySQL, use MySQL and update.

After you have set up your password, check to see if you need to tell the server to overload the authorization table by running the following command:

% mysqladmin-u Root Status

If the server still lets you connect to the server with root without specifying a password, overload the authorization table:

% mysqladmin-u Root Reload

After you set the password for root (and if you need to overload the authorization table), you will need to specify the password at any time when you connect to the server as root.