MySQL权限系统(一).The MySQL Access Privilege System 概述

纯属个人阅读，如有翻译错误，请指出

The primary function of the MySQL privilege system is to authenticate a user who connects from a given host and to associate that user with privileges on a database such as SELECT, INSERT,UPDATE, and DELETE. Additional functionality includes the ability to have anonymous users and to grant privileges for MySQL-specific functions such as LOAD DATA INFILE and administrative operations.

 

MySQL特权系统的主要功能是对从给定主机连接的用户进行身份验证，并将该用户与数据库的特权（如SELECT，INSERT，UPDATE和DELETE）相关联。 其他功能包括允许匿名用户和授予MySQL特定功能
（如LOAD DATA INFILE和管理操作）的权限。

 

 

There are some things that you cannot do with the MySQL privilege system:

• You cannot explicitly specify that a given user should be denied access. That is, you cannot explicitly match a user and then refuse the connection.

• You cannot specify that a user has privileges to create or drop tables in a database but not to create or drop the database itself.

• A password applies globally to an account. You cannot associate a password with a specific object such as a database, table, or routine.

在些事情你用MySQL的权限系统是处理不到的：

•您不能明确指定应拒绝给定用户访问。 也就是说，您不能明确的匹配用户，然后拒绝连接。

•您不能指定用户具有在数据库中创建或删除表的权限，但不能指定创建或删除数据库本身的权限。

•全局适用于帐户的密码。 您不能将密码与特定对象（如数据库，表或例程）关联。

 

The user interface to the MySQL privilege system consists of SQL statements such as CREATE USER, GRANT, and REVOKE. See Section 14.7.1, “Account Management Statements”.

Internally, the server stores privilege information in the grant tables of the mysql database (that is, in the database named mysql). The MySQL server reads the contents of these tables into memory when it starts and bases access-control decisions on the in-memory copies of the grant tables.

在内部，服务器将权限信息存储在mysql数据库（即名为mysql的数据库）的授权表中。 MySQL服务器在启动时将这些表的内容读取到内存中，并基于对授权表的内存中副本的访问控制决策。

 

The MySQL privilege system ensures that all users may perform only the operations permitted to them. As a user, when you connect to a MySQL server, your identity is determined by the host from which you connect and the user name you specify. When you issue requests after connecting, the system grants privileges according to your identity and what you want to do.

MySQL特权系统确保所有用户只能执行允许的操作。 作为用户，当您连接到MySQL服务器时，您的身份由您连接的主机和您指定的用户名决定。 在连接后发出请求时，系统会根据您的身份和您要执行的操作授予权限。

MySQL considers both your host name and user name in identifying you because there is no reason to assume that a given user name belongs to the same person on all hosts. For example, the user joe who connects from office.example.com need not be the same person as the user joe who connects from home.example.com. MySQL handles this by enabling you to distinguish users on different hosts that happen to have the same name: You can grant one set of privileges for connections by joe from office.example.com, and a different set of privileges for connections by joe from home.example.com. To see what privileges a given account has, use the SHOW GRANTS statement. For example:

SHOW GRANTS FOR 'joe'@'office.example.com';
SHOW GRANTS FOR 'joe'@'home.example.com';

MySQL在识别您时会考虑您的主机名和用户名，因为没有理由假定给定的用户名属于所有主机上的同一个人。 例如，从office.example.com连接的用户joe不需要是与
从home.example.com连接的用户joe相同的人员。 MySQL通过使您能够区分恰好相同名称的不同主机上的用户来处理这个问题：您可以通过joe从office.example.com
授予一组连接权限，并通过joe从home获取一组不同的特权 .example.com。 要查看给定帐户具有的特权，请使用SHOW GRANTS语句。 例如：

MySQL access control involves two stages when you run a client program that connects to the server:

Stage 1: The server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password.

Stage 2: Assuming that you can connect, the server checks each statement you issue to determine whether you have sufficient privileges to perform it. For example, if you try to select rows from a table in a database or drop a table from the database, the server verifies that you have the SELECT privilege for the table or the DROP privilege for the database.

当您运行连接到服务器的客户端程序时，MySQL访问控制涉及两个阶段：

阶段1：服务器根据您的身份接受或拒绝连接，以及是否可以通过提供正确的密码验证您的身份。

阶段2：假设您可以连接，服务器将检查您发出的每个语句，以确定您是否有足够的权限执行它。 例如，如果尝试从数据库中的表中选择行或从数据库中删除表，则服务器将验证您是否具有表的SELECT特权或数据库的DROP特权。

 

For a more detailed description of what happens during each stage, see Section 7.2.4, “Access Control, Stage 1: Connection Verification”, and Section 7.2.5, “Access Control, Stage 2: Request Verification”.

If your privileges are changed (either by yourself or someone else) while you are connected, those changes do not necessarily take effect immediately for the next statement that you issue. For details about the conditions under which the server reloads the grant tables, see Section 7.2.6, “When Privilege Changes Take Effect”.

如果您的权限在您连接时更改（由您自己或其他人），那么这些更改不会立即对您发出的下一条语句生效。 有关服务器重新加载授予表的条件的详细信息，请参见第7.2.6节“权限更改生效时”。

 

For general security-related advice, see Section 7.1, “General Security Issues”. For help in diagnosing privilege-related problems, see Section 7.2.7, “Troubleshooting Problems Connecting to MySQL”.