Mysql Security Guide http://clyan.hongnet.com/ 子 (2001-04-23 16:53:15)
As a MySQL system administrator, you have a responsibility to maintain your data security and integrity of your MySQL database system. This paper mainly introduces how to establish a secure Mysql system, from both perspectives within the system and external networks, to provide you with a guide. This article mainly considers the following security: Why is security important, should you prevent the attack? How to deal with the risks (internal security) of the server? Connect the client's client risk (external security), how to deal? MySQL administrators are responsible for ensuring the security of database content, allowing these data records that can only be accessed by those properly authorized, which involves internal security and external security of the database system. Internal security is concerned about the file system level, that is, prevents the mysql data directory (DATADIR) attacking people (legal or stealing) that have accounts in the server host. If the permissions of the data directory content are over-granted, each person can simply replace files corresponding to those database tables, then ensure that the authorization table settings accessed through the network are correct, which is meaningless. External security is concerned about the customer's client from the outside through the network, ie, protect the MySQL server from an attack from the connection to the server through the network. You have to set the MySQL authorization table such that they do not allow access to the server-managed database content unless the valid username and password are provided. Let's take a detail how to set up the file system and authorization menu mysql to implement the two-level security of MySQL. First, internal security - guarantee the security of the data directory access, external security - guarantee network access security Appendix 1 small test Appendix 2 make a new MySQL installation safer ------------- -------------------------------------------------- - 1, internal security - guarantee the security of data catalogs ----------------------------------- --------------------------- mysql server provides a flexible permission system by an authorization table in the MySQL database. You can set the contents of these tables, allow or reject customers to access the database, which provides a security means that you prevent unauthorized network access to your database attack, however, if other users on the host can directly access the data directory content, establish a pair Good security from the network access to you unless, unless you know that you are the only user who logs in to the MySQL server to run the host, you need to care about the possibility of access to the data directory on this machine. The following is the content you should protect: Database file. Obviously, you have to maintain the private use of the database of the server. The database owner usually and should consider the security of the database content, even if they don't want, the database content will be exposed when the database content is exposed, not the security of the poor data directory. Log file. General and update logs must be secure because they contain query text. Anyone who has access to the log file can monitor the operations through the database. More important to consider log file security is also recorded, such as Grant and Set Password, etc., the general and update logs contain texts with sensitive queries, including passwords (MySQL use password encryption, but it has completed settings Applicable to the future connection. Set the process design of a password. GRANT or SET Password and other queries, and these queries are recorded in the log file in normal text). If an attacker is like the read permissions of the day file, simply run GREP on the log file to find words such as GRANT and Password to discover sensitive information.
Obviously, you don't want other users on the server host to have a copy of the database directory file because they can override your status files or database table files, but read permissions are also very dangerous. If a database table file can be read, steal the file and get the mysql itself, it is very troublesome, why? Because you have to do the following: Install your own "special" MySQL server on the server host, but there is a port, socket, and data directory that is different from the official server version. Run mysql_install_db to initialize your data directory, which gives you permission to access your server as a MySQL root user, so you have full control over the server access mechanism, which also creates a TEST database. Will correspond to the TEST directory where you want to steal the table file to copy to the database directory of your server. Start your server. You can access the database table at will, show tables from test displays the copy of your stolen table, select * displays all of them all of the content. If you are really vicious, disclose permissions to any anonymous users of your server so that anyone can connect to the server from anywhere to access your TEST database. You will now publish the stolen database table. Consideration, from the opposite angle, do you want others to do this? of course not! You can determine if your database contains unsafe files and directories by performing the ls -l command under the database. Find files and directories with Groups and "Other User" permissions. Below is a part of an unsafe data directory list:% ls -l Total 10148 Drwxrwr-x 11 mysqladm Wheel 1024 May 8 12:20. Drwxr-xr-x 22 root Wheel 512 May 8 13:31 .. drwx - ---- 2 mysqladm mysqlgrp 512 Apr 16 15:57 menagerie drwxrwxr-x 2 mysqladm wheel 512 Jan 25 20:40 mysql drwxrwxr-x 7 mysqladm wheel 512 Aug 31 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 correct permissions, while others are not. This case is the result after a period of time. Less limitations are set by the earlier versions of the updated version than the updated version (note more restricted directory Menageria and TMP have a closer date). MySQL Current Version Make sure these files can only be read by users running the server. Let us fix these privileges so that only server users can access them. Your main protection tool comes from the tools for setting files and directory belongings and patterns provided by UNIX file system itself. Here is what we have to do: enter the directory% CD DATADIR Setting All file owners in the data directory ownership with accounts used to run the server (you must do this with root). Use mysqladm and mysqlgrp as the username and group name of the account using Mysqladm and MySQLGRP. You can use one of the following commands to change the master: # chown mysqladm.mysqlgrp. # Find. -Follow -type d -print | xargs chown mysqladm.mysqlgrp Set your data directory and database directory model makes them can only be read by MySQLADM Take it, this prevents other users from accessing your database directory. You can use one of the following commands to run as root or mysqladm.
% Chmod -r go-rwx.% Find. -Follow -type d -print | xargs chmod go-rwx data directory content of the master and mode for mysqladm settings. Now you should guarantee that you always run the server at a mysqladm user, because it is now uniquely available users (in addition to root) that is accessing the database directory. After completing these settings, you will ultimately get the following data directory permissions:% ls -l Total 10148 DRWXRWX --- 11 mysqladm mysqlgrp 1024 May 8 12:20. Drwxr-xr-x 22 root Wheel 512 May 8 13:31 .. drwx ------ 2 mysqladm mysqlgrp 512 APR 16 15:57 Menager DRWX ------ 2 mysqladm mysqlgrp 512 Jan 25 20:40 MySQL DRWX ------ 7 mysqladm mysqlgrp 512 Aug 31 1998 SQL-BENCH DRWX ------ 2 mysqladm mysqlgrp 1536 May 6 06:11 Test DRWX ------ 2 mysqladm mysqlgrp 1024 May 8 18:43 TMP .... --------- ---------------------------- 2 External Safety - Guaranteed the security of the network --------- --------------------------- mysql security system is flexible, which allows you to set user permissions in a variety of different ways. In general, you can use standard SQL statements Grant and Revoke statements, they modify you to control customer access, however, you might consist of Mysql that does not support these statements (before 3.22.11 these statements Careless to work), or you find that user permission does not work in the way you want. For this situation, understand how the Mysql authorization form is helpful, how to use them to determine access, such an understanding allows you to increase, delete or modify user rights by direct modifying an authorization table, it also allows you to check these tables Diagnostic permission problem. For how to manage user account, see "Mysql User Management". For the Detailed Description of Grant and Revoke statements, see "MySQL Reference Manual". 2.1 The structure of the mysql authorization table is controlled by the client of the MySQL database through the network connection server. These tables are located in the MySQL database and initialize (run the mysql_install_db script) during the first installation of MySQL. The license list has 5 tables: User, DB, Host, Tables_Priv and Column_Priv.
TABLE 1 user, db and host authorization table structure to access the range of columns user db host Host Host Host User Db Db Password User database / table permissions column 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 tables_priv and columns_priv lien table structure to access the range of the column tables_priv columns_priv Host Host Db Db User User Table_name Table_name Column_name permission column Table_priv Column_priv content authorization table has the following purposes: user table The USER table lists users who can connect to the server and their passwords, and it specifies which global (superuser) permission they can. Any permissions enabled in the User table are global permissions and apply to all databases. For example, if you enable Delete permissions, users listed here can delete records from any table, so we must carefully consider before you do this. The DB Table DB table lists the database, and the user has permission to access them. The permissions specified here apply to all tables in a database. The Host Table Host Table is combined with the DB table to control the access to the database on a particular host 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. Tables_priv table Tables_priv table Specifies a table-level permissions, and a permission specified here is suitable for all columns of a table. Column_Priv Table Columns_Priv Table Specifies the list of level permissions. The permissions specified here apply to a specific column for a table. In the "No GRANT Set User" section, we discuss how the GRANT statement works to modify these games, and how you can express the same effect by directly modifying authorization. Tables_priv and columns_priv tables are introduced in MySQL 3.22.11 (at the same time as 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 the old version to 3.22.11 or update, there is no TABLES_PRIV and Column_Priv table, run the mysql_fix_privileges_tables script to create them. MySQL doesn't have a ROWS_PRIV table because it does not provide record-level permissions, for example, you can't limit users who contain a particular column value in the table. If you do need this ability, you must be programmed with application. If you want to perform a suggested record-level lock, you can use the get_lock () function. The authorization table contains two columns: determines where a range of permissions uses the range and determines which permission to grant. 2.1.1 Authorization Table Scope List Delegation Table Specifies when the permissions in the table are used.
Each authorization table entry contains when the user is used to specify when the permissions are applied to a given user from a given host. Other tables contain additional range columns, such as a DB table contains a DB column indicates which database is applied to. Similarly, Tables_Priv and Column_Priv tables contain a range field, narrowing a specific column of a particular table or a table in a database. 2.1.2 Authorization Table Right Liege Authorization Table also includes the right limit column, which points to the user who specifies the user specified in the range column. The permissions supported by MySQL are shown in the following table. This table uses the authority name of the GRANT statement. There is a significant connection to the GRANT statement for the vast majority of the names in the USER, DB and HOST tables. If select_priv corresponds to SELECT permissions. 2.1.3 Database and Significance The following permissions are applied to the database and table operations. ALTER allows you to use ALTER TABLE statements, which is actually a simple first level, you have to do it by other permissions, which you want to do what you want to do with the database. CREATE allows you to create a database and table, but you don't allow you to create an index. DELETE allows you to remove existing records from the table. DROP allows you to delete (discard) databases and tables, but do not allow deletion of indexes. INDEX allows you to create and delete an index. References is currently not available. SELECT allows you to retrieve data from the table using the SELECT statement. The SELECT statement that does not involve the table is unnecessary, such as select now () or SELECT 4/2. Update allows you to modify existing records in the table. 2.1.4 Administration Permissions The following permissions are used to control manageable operations for the operation of the server or user authorization capabilities. FILE allows you to tell the server to read or write the files on the server host. This permission should not be awarded, it is very dangerous, see "avoiding the risk of authorization form". The server is really carefully to maintain this permission within a certain range. You can only read the files that anyone can read. The file you are writing must not be an existing file, which prevents you from overriding the server to override important files, such as / etc / passwd, or data directory that belongs to others. If you authorize File permissions, make sure you don't run the server with UNIX's root user, because root can create new files anywhere in the file system. If you run the server with a non-privileged user, the server can only create a file in a directory that users can access. GRANT allows you to grant your own permissions to others, including GRANT. Process allows you to view information about the running thread (process) within the server by using the Show Process statement or the mysqladmin process command. This permission also allows you to kill threads with a kill statement or 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 large number of server management operations. You can send a flush statement, you can also refer to commands of mysqladmin's Reload, Refresh, Flush-hosts, flush-logs, flush-privileges, and flush-tables. Shutdown allows you to close the server with MySQLADMIN SHUTDOWN. In the User, DB, and Host tables, each permission is specified in a separate column. These columns are declared as an enum ("n", "y") type, so the default value of each right is "N". Permissions in Tables_Priv and Columns_priv are represented in a set, which allows permissions to be specified in any combination with a single column. These two agents are updated more than three tables, which is why they use more effective reasons. (It is possible that in the future, the user, the 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', 'Reference', 'INDEX', 'Alter') The column_priv column in the Coloums_Priv table is defined as: set ('select', 'INSERT', 'UPDATE', 'References') column permissions less permissions, because less authority is meaningful. For example, you can create a table, but you can't create an isolated column. The User table contains some columns: file_priv, process_priv, reload_priv, shutdown_priv, in other authorization forms. These permissions are used to allow the server to perform an operation with any particular database or table. If you allow a user to turn off the database according to what the current database is turned off. 2.2 How to Control Customer Access When you use MySQL, customer access control has two phases. The first phase occurs when you try to connect the server. The server finds the User table to see if it can find an entry to match your name, you are being connected to the host and the password you provide. If there is no match, you cannot connect. If there is a match, establish a connection and continue the second phase. At this stage, for each query you sent, the server checks the license table see if you have enough permissions to perform the query, and the second phase continues to your end with the server dialogue. This section details the principle of the MYSQL server to match the connection request or query of the authorization table entry, which includes the type of legal value in the authorized table range column, combined with the authority information in the form of the authorization table and the entry The order of the inspection. 2.2.1 Score content Some range columns require text values, but most of them allow wildcards or other special values. Host a host column value can be a hostname or an IP address. Value Localhost means the local host, but it only matches when you use a localhost hostname, not when you are using the host name. If your local host name is pit.snake.net and there is two records to you in the USER table, one has a host value or localhost, and the other has pit.snake.net, which has a record of localhost. You match when you connect to LocalHost, which matches only when connecting pit.snake.net. If you want customers to connect in two ways, you need to have two records in the User table. You can also specify the Host value with wildcard. You can use SQL mode characters "%" and "_" and have the same meaning as the LIKE operator is used in a query (not allowing the regex operator). SQL mode characters can be used for host names and IP addresses. If the% Wisc.edu matches the host in any Wisc.edu domain, and% .edu matches the host of any education college. Similarly, 192.168.% Matches the host of 192.168 B net network, and 192.168.3.% Matches any host in 192.168.3 C. subnet. % Value matches all hosts and can be used to allow a user to connect from anywhere. A blank Host value is equivalent to%. (Exception: In the DB table, a blank host value is "further check the HOST table", which is introduced in "Query Access Verification".) From MySQL 3.23, you can also specify a belonging to the network The IP address of the address of the network mask, such as 192.168.128.0/17 Specifies a 17-bit network address and matches its IP address to be 192.168.128 17th. The User username must be text or blank.
A blank value matches any user. % As a USER value does not mean blank, the opposite matches the% name on a literal, which may not be what you want. When a coming connection is verified by the USER table, the record contains a blank user value, and the customer is considered an anonymous user. The Password password can be empty or non-empty, and wildcards are not allowed. An empty mouth order does not mean matching any password, which means that the user must not specify a password. The password is stored in a crypt value, not a literal text. If you store a password on the PASSWORD column, the user will not be able to connect! The GRANT statement and the mysqladmin password command automatically encrypts the password, but if you use a command such as INSERT, Replace, Update, or Set Password, you must use Password ("new_password") instead of simple "new_password" to specify your password. DB In the Columns_Priv and Tables_PRIV tables, the DB value must be a real database name (on the field), not allowed to mode and blank. In DB and Host, the DB value can specify or use SQL mode character '%' or '_' to specify a wildcard. One '%' or blank matches any database. Table_name, column_name These columns must be a table or column name, not allowed to be modeled and blank. Some range columns are considered sensitive by the server, and the rest is not. These principles are summarized in the table below. Special note that the table_name value is always considered sensitive, even if the case sensitive sensitivity of the table name in the query is dependent on the file system of the host running (UNIX is sensitive, and Windows is not). Table 3 Size-sensitive column of authorization table Host User Password DB Table_name Column_name Sensitive Sensitivity No Yes Yes Yes No 2.2.2 Query Access Authentication Every time you send a query, the server checks if you have enough permission execution It checks in the order of User, DB, Tables_Priv, and Column_Priv, knows that it makes appropriate access or all of the tables have been searched. More specifically: Server Checking the User Table Match the record you start to connect to see what global permissions you have. If you have enough and they are enough, the server performs it. If your global permissions are not enough, the server is looking for you in the DB table and adds the permissions in the record to your global permissions. If the result is sufficient to check the query, the server executes it. If your global and database-level combination is insufficient, the server continues to look up, first in the Tables_PRIV table, then columns_priv table. If you still have no permissions after checking all the tables, the server rejects you attempt to perform the query. Use Boolean computing terms, authorization lists are used by servers: user or tables_priv or columns_priv You may doubt what the previous description only references 4 authorization forms, but actually 5. In fact, the server is checking access: 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 use Grant and Revoke to manage user permissions, you never need to consider the Host table. But what you work, you use this: When the server checks the database level permissions, it looks for the customer to find the DB table. If the Host column is empty, it means "check the Host table to find which host can access the database."
The server finds the same DB column value as the record from the DB table in the HOST table. If there is no Host Record Matching the client host, the database level permissions are not granted. If any of these records do have a HOST column value of the connected client host, the DB table record, and the HOST table record combine to generate customer database level permissions. However, the permission is combined with a logical and (with), which means that the customer does not have this permission unless a given permissions are in both tables. In this way, you can grant a basic permissions set in the DB table and then use the Host table to selectively disable them for a particular host. If you can allow all hosts in your domain to access the database, but close the database permissions of the hosts in the unsafe zone. The previous description has no doubt that the access check sounds a fairly complicated process, especially if you think the server permission check, however, this process is very fast, because the server is actually not from the authorization table The query lookup information. Instead, it reads the contents of the table in the launch, and then verifies that the query is a copy of the memory. This greatly enhances the performance of access check operations. But there is a very obvious side effect. If you directly modify the content of the authorization form, the server will not know the change in privileges. For example, if you add a new user to the USER table with a INSERT statement to add a new user, the user named in the record will not connect to the server. This is a very confused thing for administrators (sometimes the experienced older). The solution is very simple: tell the server overload authorization form after you change them, you can send a Flush Privilege or execute mysqladmin flush- Privileges (or if you have an old version that doesn't support flush-privilege, with mysqladmin reload.). 2.2.3 Range Column Match Sequence The MYSQL server is a record in a specific way to authorize the logs, and then browsing the records from the sequential browsing. The first match found determines the records used. Understanding the ordering order of mysql use is important, especially for the USER table. When the server reads the contents of the USER table, it sorts the record according to the value in the Host and USER columns, the Host value determines the effect (the same Host value is rowed together, and then sorted according to the USER value). However, sorting is not a step (sorted by words), it is just part of. It is necessary to keep in mind that the words on the literal are preferred. This means that if you are connecting to the server from the client.your.net connection server and Host has both values, the first first election. Similarly,%. Your.NET is preferred in% .NET, then%. This is also like this to match the IP address. In short, the more specific, the more preferred. See the instance of the appendix of this article. 2.3 Avoiding Authorization Table Risks This session This section introduces some of the prevention measures in your authorization, as well as the risk of optional choices. In general, you have to "" to grant superuser privileges, ie not enabled permissions in the entries in the USER table, and use other licensing forms to limit user rights to databases, tables, or columns. Permissions in the User table allow you to affect your server operation or access any table in any database. Don't grant permissions to the MySQL database. A user with authorized meter database privilege may modify the table to get permissions to any other database. Granting allowing a user to modify the permissions of the mysql database table, also actually give the user with a global GRANT permission. If the user can modify the table directly, this is also equivalent to any GRANT statement that you can imagine. File permissions are particularly dangerous, don't authorize it easily.
The following is something that people who have File permissions: create Table etc_passwd (pwd_entry text); load data infile "/ etc / passwd" INTO TABLE etc_passwd; select * from etc_passwd; After these statements, users have already owned you The content of the password file. In fact, any public readable files on the server can be accessed through users with users with file permissions. File privileges can also be utilized to hazard the database on systems that do not set sufficient file permissions. This is why you should set the data directory that can only be read by the server. If the file corresponding to the database table can be read by anyone, not only the user of the user server account readable, any user with file permissions can also be connected to the network and read them. The following demonstrates this process: Create a table with a longblob column: user test; create Table TMP (B longblob); read the contents of the database table file you want to steal, then write the content Enter 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 instfile "y.frm" Fields escaped by " "Lines terminated by" "; delete from tmp; loading data infile" ./other_db/x.isd "inTo Table Tmp Fields escaped by" Lines Terminated By "; select * from tmp instfile" y.Isd "Fields Escaped BY "" Lines Terminated By "; delete from tmp ;/other_db/x.ism" INTO TABLE TMP FIELDS Escape "" Lines Terminated By "; select * from tmp intfile" yism " Now you have a new table y, it contains the contents of other_db.x and you have a full access to it. To avoid people in the same way, set the permissions on your data directory according to the instructions in "1 Internal Security - Protect your Data Directory". You can also use the Skip-Show-Database option to limit users when you start the server, use Show Databases and Show Tables for them without access. This helps prevent users from finding information about their databases and tables that they cannot access. ALTER permissions can be used in undesired ways. Assume that you want USER1 to access Table1 but cannot access Tables2. A user who has Alter privileges can rename Table2 to Table1 by using ALTER TABLE. Beware of GRANT permissions. Two users with different privileges but have GRANT privileges allows each other's rights more powerful. 2.4 No GRANT Settings Users If you have a MySQL version earlier than 3.22.11, you cannot use the Grant (or revoke statement to set the user and its access, but you can directly modify the content of the license table. This is easy if you understand how the GRANT statement changes the authorization form.
Then you can do the same thing yourself by manual insert statement. When you send a GRANT statement, you specify a username and hostname, there may be a password. This user generates a USER table record, 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 recorded authority column. Where do you want to leave God is a GRANT statement to encrypt a password, and INSERT is not, you need to use the password () function encryption password in Insert. If you specify database level permissions, user names, and host names are recorded in the User and Host columns of the DB table. You authorize the database recorded in the DB column, the permissions you grant in the permission column. For table-level and column-level permissions, the effect is similar. Create a record in Tables_Priv and Column_Priv tables to record username, hostname, and database, and related tables and columns. The permissions granted are recorded in the permissions column. If you still remember the previous introduction, you should be able to make GRANT even if you don't have a GRANT statement. Remember When you directly modify the license table, you will notify the server to overload the authorization form, otherwise he doesn't know your changes. You can execute a mysqladmin flush-privileges or mysqladmin reload command to force an overload. If you forget to do this, you will doubt why the server does not do what you want to do. The following GRANT statement creates a super user with all rights. Including the ability to authorize 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, open all permissions, because here is a super user ( Global) Where the authority is stored, the same thing to use insert statement is: INSERT INTO USER VALUES ("LocalHost", "Anyname", Password ("passwd"), "y", "y", "y" , "Y", "y", "y", "y", "y", "y", "y", "y") you may find it is not Work, you have to look at your MySQL version. The structure of the authorized form has changed and you may not have 14 permission in your User table. Use Show Column to find each of your authorization tables, adjust your Insert statement accordingly. The following GRANT statement also creates a user who has a superuser identity, but only one single permission: Grant reluctions on *. * To flush @ localhost identified by "flushpass" Insert statement of this example is simpler than the previous, it is easy to list Column name and only one permission column.
All other columns will be set to the default "N": Insert Into User ("LocalHost", "Flush", Password ("FlushPass"), "Y") Database level permissions with one ON DB_NAME. * clause instead of on *. * Licensing: GRANT ALL ON SAMPLE. * To boris @ localhost identified by "ruby" This permission is not globally, so they do not store in the User table, we still need to be in the User table Create a record (make the user can connect), but we also need to create a DB table record log 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", "N", "Y" "Y", "y") "n" column is for GRANT permissions; a GRANT statement with a database level at the end, you want to set it as "y". To set a table-level or column level, you use the INSERT statement for Tables_Priv or Column_Priv. Of course, if you don't have a GRANT statement, you will not have these tables because they appear simultaneously in MySQL. If you do have these tables and want to manually operate in order for some reason, you must know that you can't enable permissions separately. You set TABLES_PRIV.TABLE_PRIV or columns_priv.column_priv columns to set the permission value that contains you want to enable. For example, to enable SELECT and INSERT permissions to a table, you have to set Table_Priv to "SELECT, INSERT" in the relevant tables_priv record. If you want to modify the permissions for a user with a MySQL account, use Update instead of INSERT, whether you have increased or revoked 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 authority, you can look at Mysqlaccess and MySQL_SETPERMISSIONS scripts with MySQL.
-------------------------------------------------- -------------- Appendix 1 Try ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------- In you have just installed a MySQL server, add a allowed to connect Mysql User, use the following statement: grant all on samp_db. * To fred@*nake.net identified "cocoa", Fred happens to have an account on the server host, so he tries 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 permissions form and how the server uses the USER table log matching the customer connection. When you initialize your database with mysql_install_db, it creates a User table similar to this: Host User localhost pit.snake.net localhost pit.snake.net root root head two records allow root to specify localhost or hostname to connect to the local server, then Two allows an anonymous users to connect from a local location. When the Fred user is added, Host User localhost pit.snake.net%. Snake.net root root fred When the server is started, it reads the record and sort them (first press the host, then press the host) Users), the more specific, the more you are in front: host user localhost localhost pit.snake.net pit.snake.net%. Snake.net root root fred has two records of localhost, and the record of root is in the first First, because it is more specific than null value. Pit.Snake.Net records are similar. All of this is the HOST value without any wildcard, so they are ranked in front of the Fred record, especially before the anonymous user is located in FRED. The result is that the record of an empty user name in the HOST column matches the record of the%. Snake.net when Fred is trying to connect from the localhost connection. The password of this record is empty because the default anonymous user has no password. Because a password is specified when the Fred connection is specified, the connection is failed. What you have to remember is that although the user can specify the user can be convenient from its connection. But there is a problem when you connect from your local host, as long as you reserve anonymous user records in a Table 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, have db, tables_priv, and columns_priv.
