MYSQL权限系统实践操作，总结整理。匿名用户的问题直接搜索“匿名”
MySQL访问权限系统
权限系统工作原理
MySQL权限系统保证所有的用户只执行允许做的事情。当你连接MySQL服务器时，你的身份由
- 你从那儿连接的主机
- 你指定的用户名
来决定。连接后发出请求后，系统根据你的身份和你想做什么来授予权限。
MySQL权限控制包含2个阶段：
- 阶段1：服务器检查是否允许你连接。
- 阶段2：假定你能连接，服务器检查你发出的每个请求。看你是否有足够的权限实施它。例如，如果你从数据库表中选择(select)行或从数据库删除表，服务器确定你对表有SELECT权限或对数据库有DROP权限。
服务器在存取控制的两个阶段使用mysql数据库中的user、db
| 表名 | user | db |
|---|---|---|
| 列范围 | Host | Host |
| User | Db | |
| Password | User | |
| 权限列 | Select_priv | Select_priv |
| Insert_priv | Insert_priv | |
| Update_priv | Update_priv | |
| Delete_priv | Delete_priv | |
| Index_priv | Index_priv | |
| Alter_priv | Alter_priv | |
| Create_priv | Create_priv | |
| Drop_priv | Drop_priv | |
| Grant_priv | Grant_priv | |
| Create_view_priv | Create_view_priv | |
| Show_view_priv | Show_view_priv | |
| Create_routine_priv | Create_routine_priv | |
| Alter_routine_priv | Alter_routine_priv | |
| References_priv | References_priv | |
| Reload_priv | ||
| Shutdown_priv | ||
| Process_priv | ||
| File_priv | ||
| Show_db_priv | ||
| Super_priv | ||
| Create_tmp_table_priv | Create_tmp_table_priv | |
| Lock_tables_priv | Lock_tables_priv | |
| Execute_priv | ||
| Repl_slave_priv | ||
| Repl_client_priv | ||
| 安全列 | ssl_type | |
| ssl_cipher | ||
| x509_issuer | ||
| x509_subject | ||
| 资源控制列 | max_questions | |
| max_updates | ||
| max_connections | ||
| max_user_connections |
对存取控制的第二阶段(请求证实)，服务器执行请求验证以确保每个客户端有充分的权限满足各需求。除了user、db和host授权表，如果请求涉及表，服务器可以另外参考tables_priv和columns_priv表。tables_priv和columns_priv表可以对表和列提供更精确的权限控制。这些表的列如下：
| 表名 | tables_priv | columns_priv |
|---|---|---|
| 范围列 | Host | Host |
| Db | Db | |
| User | User | |
| Table_name | Table_name | |
| Column_name | ||
| 权限列 | Table_priv | Column_priv |
| Column_priv | ||
| 其它列 | Timestamp | Timestamp |
| Grantor |
为了对涉及保存程序的请求进行验证，服务器将查阅procs_priv表。该表具有以下列：
| 表名 | procs_priv |
|---|---|
| 范围列 | Host |
| Db | |
| User | |
| Routine_name | |
| Routine_type | |
| 权限列 | Proc_priv |
| 其它列 | Timestamp |
| Grantor |
服务器使用这样的授权表
-
user表范围列决定是否允许或拒绝到来的连接。对于允许的连接，user表授予的权限指出用户的全局(超级用户)权限。这些权限适用于服务器上的all数据库。
mysql> select * from user where user='testuser'\G *************************** 1. row *************************** Host: % User: testuser Select_priv: Y Insert_priv: Y Update_priv: Y Delete_priv: Y Create_priv: Y Drop_priv: Y Reload_priv: Y Shutdown_priv: Y Process_priv: Y File_priv: Y Grant_priv: N References_priv: Y Index_priv: Y Alter_priv: Y Show_db_priv: Y Super_priv: Y Create_tmp_table_priv: Y Lock_tables_priv: Y Execute_priv: Y Repl_slave_priv: Y Repl_client_priv: Y Create_view_priv: Y Show_view_priv: Y Create_routine_priv: Y Alter_routine_priv: Y Create_user_priv: Y Event_priv: Y Trigger_priv: Y Create_tablespace_priv: Y ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 plugin: mysql_native_password authentication_string: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 password_expired: N password_last_changed: 2019-02-19 19:54:44 password_lifetime: NULL account_locked: N -
db表范围列决定用户能从哪个主机存取哪个数据库。权限列决定允许哪个操作。授予的数据库级别的权限适用于数据库和它的表。
-
tables_priv和columns_priv表类似于db表，但是更精致：它们在表和列级应用而非在数据库级。授予表级别的权限适用于表和所有它的列。授予列级别的权限只适用于专用列。
-
procs_priv表适用于保存的程序。授予程序级别的权限只适用于单个程序。
-
管理权限(例如RELOAD或SHUTDOWN等等)仅在user表中被指定。这是因为管理性操作是服务器本身的操作并且不是特定数据库，因此没有理由在其他授权表中列出这样的权限。事实上，只需要查询user表来决定你是否执行一个管理操作。
-
FILE权限也仅在user表中指定。它不是管理性权限，但你在服务器主机上读或写文件的能力与你正在存取的数据库无关。
-
当mysqld服务器启动时，将授权表的内容读入到内存中。你可以通过FLUSH PRIVILEGES语句或执行mysqladmin flush-privileges或mysqladmin reload命令让它重新读取表。
-
当你修改授权表的内容时，确保你按你想要的方式更改权限设置是一个好主意。要检查给定账户的权限，使用SHOW GRANTS语句。
mysql> show grants for 'testuser'; +-----------------------------------------------+ | Grants for testuser@% | +-----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%' | +-----------------------------------------------+ mysql> show grants for 'testuser'@'%'; +-----------------------------------------------+ | Grants for testuser@% | +-----------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%' | +-----------------------------------------------+
MySQL提供的权限
账户权限信息被存储在mysql数据库的user、db、host、tables_priv、columns_priv和procs_priv表中。在MySQL启动时并在**“权限更改何时生效”**所说的情况时，服务器将这些数据库表内容读入内存。
| 权限 | 列 | 上下文 |
|---|---|---|
| CREATE | Create_priv | 数据库、表或索引 |
| DROP | Drop_priv | 数据库或表 |
| GRANT OPTION | Grant_priv | 数据库、表或保存的程序 |
| REFERENCES | References_priv | 数据库或表 |
| ALTER | Alter_priv | 表 |
| DELETE | Delete_priv | 表 |
| INDEX | Index_priv | 表 |
| INSERT | Insert_priv | 表 |
| SELECT | Select_priv | 表 |
| UPDATE | Update_priv | 表 |
| CREATE VIEW | Create_view_priv | 视图 |
| SHOW VIEW | Show_view_priv | 视图 |
| ALTER ROUTINE | Alter_routine_priv | 保存的程序 |
| CREATE ROUTINE | Create_routine_priv | 保存的程序 |
| EXECUTE | Execute_priv | 保存的程序 |
| FILE | File_priv | 服务器主机上的文件访问 |
| CREATE TEMPORARY TABLES | Create_tmp_table_priv | 服务器管理 |
| LOCK TABLES | Lock_tables_priv | 服务器管理 |
| CREATE USER | Create_user_priv | 服务器管理 |
| PROCESS | Process_priv | 服务器管理 |
| RELOAD | Reload_priv | 服务器管理 |
| REPLICATION CLIENT | Repl_client_priv | 服务器管理 |
| REPLICATION SLAVE | Repl_slave_priv | 服务器管理 |
| SHOW DATABASES | Show_db_priv | 服务器管理 |
| SHUTDOWN | Shutdown_priv | 服务器管理 |
| SUPER | Super_priv | 服务器管理 |
与MySQL服务器连接
当你想要访问MySQL服务器时，MySQL客户端程序一般要求你指定参数：
- MySQL服务器运行的主机名
- 姓名
- 密码
可以从命令行按照下述提示启动MySQL客户端(用shell>表示)：
shell> MySQL -h host_name -u user_name -pyour_pass
如果没有指定连接参数，MySQL客户端程序使用默认值：
- 默认主机名是localhost。
- 默认用户名在Windows中是ODBC，在Unix中是你的Unix登录名。
- 如果没有-p，则不提供密码。
这样, 对Unix用户joe，下列命令是等价的：
shell> MySQL -h localhost -u joe
shell> MySQL -h localhost
shell> MySQL -u joe
shell> MySQL
其它MySQL客户端程序类似。
访问控制, 阶段1：连接核实
当你试图连接MySQL服务器时，服务器基于你的身份以及你是否能通过供应正确的密码验证身份来接受或拒绝连接。如果不是，服务器完全拒绝你的访问，否则，服务器接受连接，然后进入阶段2并且等待请求。
你的身份基于2个信息
- 你从那个主机连接
- 你的MySQL用户名
身份检查使用user表(Host, User和Password)范围列执行。服务器只有在user表记录的Host和User列匹配客户端主机名和用户名并且提供了正确的密码时才接受连接。
在user表Host值的指定方法：
-
Host值可以是主机名或IP号，或’localhost’指出本地主机。
-
你可以在Host列值使用通配符字符“%”和“_”。
-
Host值’%‘匹配任何主机名，空Host值等价于’%’。它们的含义与LIKE操作符的模式匹配操作相同。例如，’%‘的Host值与所有主机名匹配，而’%.mysql.com’匹配mysql.com域的所有主机。
对于指定为IP号的Host值，你可以指定一个网络掩码，说明使用多少位地址位来评比网络号。例如：
mysql> GRANT ALL PRIVILEGES ON db.* TO david@'192.58.197.0/255.255.255.0';允许david从任何客户端用IP号client_ip来连接。下面条件为真的IP都允许连接。
client_ip & netmask = host_ip -
Password列可以是空的。这不是通配符，也不意味着匹配任何密码，它意味着用户必须不指定一个密码进行连接。
下面的例子显示出各种user表中Host和User值的组合如何应用于到来的连接：
| Host值 | User值 | 被条目匹配的连接 |
|---|---|---|
| ‘thomas.loc.gov’ | ‘fred’ | fred, 从thomas.loc.gov 连接 |
| ‘thomas.loc.gov’ | ‘’ | 任何用户, 从thomas.loc.gov连接 |
| ‘%’ | ‘fred’ | fred, 从任何主机连接 |
| ‘%’ | ‘’ | 任何用户, 从任何主机连接 |
| ‘%.loc.gov’ | ‘fred’ | fred, 从在loc.gov域的任何主机连接 |
| ‘x.y.%’ | ‘fred’ | fred, 从x.y.net、x.y.com,x.y.edu等联接。（这或许无用） |
| ‘144.155.166.177’ | ‘fred’ | fred, 从有144.155.166.177 IP地址的主机连接 |
| ‘144.155.166.%’ | ‘fred’ | fred, 从144.155.166 C类子网的任何主机连接 |
当服务器读取表时，它首先以最具体的Host值排序。主机名和IP号是最具体的。’%'意味着“任何主机”并且是最不特定的。有相同Host值的条目首先以最具体的User值排序(空User值意味着“任何用户”并且是最不特定的)。最终排序的user表看起来像这样：
+-----------+----------+-
| Host | User | …
+-----------+----------+-
| localhost | root | … ...
| localhost | | … ...
| % | jeffrey | … ...
| % | root | … ...
+-----------+----------+-
当客户端试图连接时，服务器浏览排序的条目并使用找到的第一匹配。对于由jeffrey从localhost的连接，表内有两个条目匹配：Host和User值为’localhost’和’‘的条目，和值为’%'和’jeffrey’的条目。'localhost’条目首先匹配，服务器可以使用。
还有一个例子。假定user表看起来像这样：
+----------------+----------+-
| Host | User | …
+----------------+----------+-
| % | jeffrey | …
| thomas.loc.gov | | …
+----------------+----------+-
排序后的表看起来像这样：
+----------------+----------+-
| Host | User | …
+----------------+----------+-
| thomas.loc.gov | | …
| % | jeffrey | …
+----------------+----------+-
由jeffrey从thomas.loc.gov的连接与第一行匹配，而由jeffrey从whitehouse.gov的连接被第二个匹配。
普遍的误解是认为，对给定的用户名，当服务器试图对连接寻找匹配时，明确命名那个用户的所有条目将首先被使用。这明显不符合事实。先前的例子说明了这点，在那里由jeffrey从thomas.loc.gov的连接没被包含’jeffrey’作为User列值的行匹配，但是由没有用户名的题目匹配！结果是，jeffrey被鉴定为匿名用户，即使他连接时指定了用户名。
注意！host先匹配，有可能匹配上匿名用户！
如果你能够连接服务器，但你的权限不是你期望的，你可能被鉴定为其它账户。要想找出服务器用来鉴定你的账户，使用CURRENT_USER()函数。它返回user_name@host_name格式的值，说明User和Host 值匹配user表记录。假定jeffrey连接并发出下面的查询：
mysql> SELECT CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| @localhost |
+----------------+
这儿显示的结果说明user表行有空的User列值。换句话说，服务器将jeffrey视为匿名用户。
访问控制, 阶段2：请求核实
- 一旦你建立了连接，服务器进入访问控制的阶段2。对在此连接上进来的每个请求，服务器检查你想执行什么操作，然后检查是否有足够的权限来执行它。这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、tables_priv或columns_priv表。
- （user表是全局权限，这是很高的权限级别）user表在全局基础上授予赋予你的权限，该权限不管当前的数据库是什么均适用。例如，如果user表授予你DELETE权限， 你可以删除在服务器主机上从任何数据库删除行！换句话说，user表权限是超级用户权限。只把user表的权限授予超级用户如服务器或数据库主管是明智的。对其他用户，你应该把在user表中的权限设成’N’并且仅在特定数据库的基础上授权。你可以为特定的数据库、表或列授权。
- db表授予数据库特定的权限。在这些表中的范围列的值可以采用以下方式：
- 通配符字符
%并_可用于两个表的Host和Db列。它们与用LIKE操作符执行的模式匹配操作具有相同的含义。如果授权时你想使用某个字符，必须使用反斜现引用。例如，要想在数据库名中包括下划线(‘_’)，在GRANT语句中用‘_’来指定。 - 在db表的
%Host值意味着“任何主机”，在db表中空Host值意味着“对进一步的信息咨询host表”（本节后面将描述的一个过程）。 - 在表中的’%'或空Db值意味着“任何数据库”。
- 在表中的空User值匹配匿名用户。
- 通配符字符
- tables_priv和columns_priv表授予表和列特定的权限。这些表的范围列的值可以如下被指定：
- 通配符“%”并“_”可用在使用在两个表的Host列。
- 在两个表中的’%'或空Host意味着“任何主机”。
- 在两个表中的Db、Table_name和Column_name列不能包含通配符或空。
请求核实步骤
- 对需要管理权限的请求(SHUTDOWN、RELOAD等等)，服务器仅检查user表条目，因为那是唯一指定管理权限的表。如果行许可请求的操作，访问被授权，否则拒绝。例如，如果你想要执行mysqladmin shutdown，但是由于user表行没有为你授予HUTDOWN权限，甚至不用检查db或host表就拒绝你的访问。（因为它们不包含hutdown_priv行列，没有这样做的必要。）
- 对数据库有关的请求(INSERT、UPDATE等等)，服务器首先通过查找user表行来检查用户的全局(超级用户)权限。如果行允许请求的操作，访问被授权。如果在user表中全局权限不够，服务器通过检查db表确定特定的用户数据库权限：
- 服务器在db表的Host、Db和User列上查找匹配。Host和User对应连接用户的主机名和MySQL用户名。Db列对应用户想要访问的数据库。如果没有Host和User的行，访问被拒绝。
- 如果db表中有匹配的行而且它的Host列不是空的，该行定义用户的数据库特定的权限。
- 在确定了由db表行授予的数据库特定的权限后，服务器把他们加到由user表授予的全局权限中。如果结果允许请求的操作，访问被授权。否则，服务器检查在tables_priv和columns_priv表中的用户的表和列权限并把它们加到用户权限中。基于此结果允许或拒绝访问。
用布尔术语表示，前面关于用户权限如何计算的描述可以这样总结：
user表： global privileges
|
db表： database privileges
|
table_priv表： table privileges
|
column_priv表： column privileges
一定要测试授权表中的行(例如，使用SHOW GRANTS或mysqlaccess)，确保你的访问权限实际按你期望的方式被设置。
权限更改何时生效
当mysqld启动时，所有授权表的内容被读进内存并且从此时生效。
当服务器注意到授权表被改变了时，现存的客户端连接有如下影响：
- 表和列权限在客户端的下一次请求时生效。
- 数据库权限改变在下一个USE db_name命令生效。
- 全局权限的改变和密码改变在下一次客户端连接时生效。
如果用GRANT、REVOKE或SET PASSWORD对授权表进行修改，服务器会注意到并立即重新将授权表载入内存。
如果你手动地修改授权表(使用INSERT、UPDATE或DELETE等等)，你应该执行mysqladmin flush-privileges或mysqladmin reload告诉服务器再装载授权表，否则你的更改将不会生效，除非你重启服务器。
如果你直接更改了授权表但忘记重载，重启服务器后你的更改方生效。这样可能让你迷惑为什么你的更改没有什么变化！
拒绝访问错误的原因
当你试着联接MySQL服务器时，如果碰到问题，下面各项可以帮助你纠正问题：
确保服务器在运行。如果服务器没有运行，则你不能连接服务器。如果你视图连接服务器并看到下述消息，可能是服务器没有运行：
shell> mysql
RROR 2003: Can't connect to MySQL server on 'host_name' (111)
shell> mysql
ERROR 2002: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
也可能服务器正在运行，但你可能使用与服务器上侦听的不一样的TCP/IP端口、命名管道或Unix套接字文件。你可以调用客户端程序，指定端口选项来指示正确的端口或套接字选项来指示正确的命名管道或Unix套接字文件。要找出套接字文件的地点，应：
shell> netstat -ln | grep mysql
MySQL用户账户管理
增加用户
可以用两种方式创建MySQL账户：
- 使用GRANT语句
- 直接操作MySQL授权表
下面的语句使用GRANT来设置四个新账户：
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' IDENTIFIED BY 'some_pass'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass'
WITH GRANT OPTION;
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
GRANT USAGE ON *.* TO 'dummy'@'localhost';
-
其中两个账户有相同的用户名monty和密码some_pass。两个账户均为超级用户账户，具有完全的权限可以做任何事情。一个账户(‘monty’@‘localhost’)只用于从本机连接时。另一个账户(‘monty’@’%’)可用于从其它主机连接。请注意monty的两个账户必须能从任何主机以monty连接。没有localhost账户，当monty从本机连接时，mysql_install_db创建的localhost的匿名用户账户将占先。结果是，monty将被视为匿名用户。原因是匿名用户账户的Host列值比’monty’@’%'账户更具体，这样在user表排序顺序中排在前面
测试：
mysql> select host,user from user where user='testuser'; +-----------+----------+ | host | user | +-----------+----------+ | % | testuser | +-----------+----------+ mysql> select host,user from user where user=''; +-----------+------+ | host | user | +-----------+------+ | localhost | | +-----------+------+ #----------> 匹配匿名用户，登陆失败 #mysql -S /home/mysql/data3148/tmp/mysql.sock -utestuser -p123 Warning: Using a password on the command line interface can be insecure. ERROR 1045 (28000): Access denied for user 'testuser'@'localhost' (using password: YES) mysql> GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'localhost' identified by '123'; mysql> select host,user from user where user='testuser'; +-----------+----------+ | host | user | +-----------+----------+ | % | testuser | | localhost | testuser | +-----------+----------+ mysql> select host,user from user where user=''; +-----------+------+ | host | user | +-----------+------+ | localhost | | +-----------+------+ #----------> 匹配testuser@localhost成功登陆 #mysql -S /home/mysql/data3148/tmp/mysql.sock -utestuser -p123 Warning: Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4256458 Server version: 5.7.20-log Source distribution Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> -
一个账户有用户名admin，没有密码。该账户只用于从本机连接。授予了RELOAD和PROCESS管理权限。这些权限允许admin用户执行
mysqladmin reload、mysqladmin refresh和mysqladmin flush-xxx命令，以及mysqladmin processlist。未授予访问数据库的权限。你可以通过GRANT语句添加此类权限 -
一个账户有用户名dummy，没有密码。该账户只用于从本机连接。未授予权限。通过GRANT语句中的USAGE权限，你可以创建账户而不授予任何权限。它可以将所有全局权限设为’N’。假定你将在以后将具体权限授予该账户
下面的例子创建3个账户，允许它们访问专用数据库。每个账户的用户名为custom，密码为obscure。
-- 第1个账户可以访问bankaccount数据库，但只能从本机访问。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON bankaccount.*
TO 'custom'@'localhost'
IDENTIFIED BY 'obscure';
-- 第2个账户可以访问expenses数据库，但只能从主机whitehouse.gov访问。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON expenses.*
TO 'custom'@'whitehouse.gov'
IDENTIFIED BY 'obscure';
-- 第3个账户可以访问customer数据库，但只能从主机server.domain访问。
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON customer.*
TO 'custom'@'server.domain'
IDENTIFIED BY 'obscure';
测试验证
-- t1能看到testdb的所有表，能查询不能写入
grant select on testdb.* to 't1'@'%' identified by '333';
-- t1只能看到testtbl1，能查询不能写入
grant select on testdb.testtbl1 to 't2'@'%' identified by '333';
-- t1 t2的user表中全是N
-- t1在DB表中记录权限，t2没有数据
mysql> select * from db where user = 't1'\G
*************************** 1. row ***************************
Host: %
Db: testdb
User: t1
Select_priv: Y
Insert_priv: N
Update_priv: N
Delete_priv: N
Create_priv: N
Drop_priv: N
Grant_priv: N
References_priv: N
Index_priv: N
Alter_priv: N
Create_tmp_table_priv: N
Lock_tables_priv: N
Create_view_priv: N
Show_view_priv: N
Create_routine_priv: N
Alter_routine_priv: N
Execute_priv: N
Event_priv: N
Trigger_priv: N
-- t2在tables_priv表中记录权限，t1没有数据
mysql> select * from tables_priv where user='t2'\G
*************************** 1. row ***************************
Host: %
Db: testdb
User: t2
Table_name: testtbl1
Grantor: aliyun_root@127.0.0.1
Timestamp: 0000-00-00 00:00:00
Table_priv: Select
Column_priv:
限制账户资源
方式一：限制MySQL服务器资源使用的一个方法是将max_user_connections系统变量设置为非零值。但是，该方法严格限于全局，不允许管理具体账户。并且，它只限制使用单一账户同时连接的数量，而不是客户端连接后的操作。
方式二：
- 账户每小时可以发出的查询数
- 账户每小时可以发出的更新数
- 账户每小时可以连接服务器的次数
做为使用该特性的先决条件，mysql数据库的user表必须包含资源相关的列。资源限制保存在max_questions、max_updates、max_connections和max_user_connections列内
要想用GRANT语句设置资源限制，使WITH子句来命名每个要限制的资源和根据每小时记数的限制值。例如，要想只以限制方式创建可以访问customer数据库的新账户，执行该语句：
GRANT ALL ON customer.* TO 't3'@'localhost'
IDENTIFIED BY '333'
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;
select max_questions,max_updates,max_connections,max_user_connections from user where user='t3';
+---------------+-------------+-----------------+----------------------+
| max_questions | max_updates | max_connections | max_user_connections |
+---------------+-------------+-----------------+----------------------+
| 20 | 10 | 5 | 2 |
+---------------+-------------+-----------------+----------------------+
要想设置或更改已有账户的限制，在全局级别使用GRANT USAGE语句(在*.*)。下面的语句可以将francis的查询限制更改为100：
GRANT USAGE ON *.* TO 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
该语句没有改变账户的已有权限，只修改了指定的限制值。
要想取消已有限制，将该值设置为零。例如，要想取消francis每小时可以连接的次数的限制，使用该语句：
GRANT USAGE ON *.* TO 'francis'@'localhost' WITH MAX_CONNECTIONS_PER_HOUR 0;
- 当账户使用资源时如果有非零限制，则对资源使用进行记数。
- 服务器运行时，它统计每个账户使用资源的次数。如果账户在最后一个小时的连接次数达到限制，该账户的进一步的连接被拒绝。类似地，如果账户达到查询或更新次数的限制，进一步的查询或更新被拒绝。在这种情况下，会给出相关错误消息。
- 根据每个账户进行资源计算，而不是根据每个客户端。例如，如果你的账户的查询限制为50,你不能通过两个客户端 同时连接服务器将限制增加到100。两个连接的查询被计算到一起。
- 可以为所有账户从全局重设当前的每小时资源使用记数，或单独重设给定的账户
- 要想将所有账户当前的记数重设为零，可以执行FLUSH USER_RESOURCES语句。还可以通过重载授权表来重设记数(例如，使用FLUSH PRIVILEGES语句或mysqladmin reload命令)。
- 将具体账户的限制重新授予任何值，可以将它设置为零。要想实现，按照前面所述使用GRANT USAGE，并将限制值指定为该账户当前的限制值。
- 计数器重设不影响MAX_USER_CONNECTIONS限制。
- 当服务器启动时所有记数从零开始。
测试MAX_USER_CONNECTIONS
mysql> grant usage on *.* to 't3'@'localhost' with MAX_USER_CONNECTIONS 1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
#mysql -ut3 -S /mysqldata/tmp3148/mysql.sock -p333
mysql>
#mysql -S /home/mysql/data3148/tmp/mysql.sock -ut3 -p333
ERROR 1226 (42000): User 't3' has exceeded the 'max_user_connections' resource (current value: 1)
设置账户密码
更改别人的密码
有权限修改mysql库的用户才能修改别人的密码
SET PASSWORD FOR 'jeffrey'@'%' = PASSWORD('biscuit');
GRANT USAGE ON *.* TO 'jeffrey'@'%' IDENTIFIED BY 'biscuit';
更改自己的密码
mysql> SET PASSWORD FOR 't3'@'%' = PASSWORD('123');
ERROR 1044 (42000): Access denied for user 't3'@'localhost' to database 'mysql'
-- 修改自己的密码可省略
mysql> SET PASSWORD = PASSWORD('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
使你的密码安全
在管理级别，你决不能将mysql.user表的访问权限授予任何非管理账户。
-
mysql -u francis -pfrank db_name这很方便但是不安全，因为你的密码对系统状态程序(例如ps)变得可见 -
在一个配置文件中存储你的密码。例如，在Unix中，你可在主目录的
“.my.cnf”文件中的[client]节列出你的密码chmod 600 .my.cnf[client] password=your_pass -
你可在MYSQL_PWD环境变量中存储密码。但是这种指定MySQL密码的方法是极不安全的
列权限操作实例
-- root登陆
create table tbl1(a int, b int, c int, d int);
insert into tbl1 values (1,2,3,4);
select * from tbl1;
+------+------+------+------+
| a | b | c | d |
+------+------+------+------+
| 1 | 2 | 3 | 4 |
+------+------+------+------+
grant select(a) on testdb.tbl1 to t4 identified by '333';
grant insert(a) on testdb.tbl1 to t6 identified by '333';
-- t4登陆：只能查询a列
mysql -ut4 -S /mysqldata/tmp3148/mysql.sock -p333
mysql> select * from tbl1;
ERROR 1142 (42000): SELECT command denied to user 't4'@'localhost' for table 'tbl1'
mysql> select a from tbl1;
+------+
| a |
+------+
| 1 |
+------+
mysql> show grants;
+-------------------------------------------------+
| Grants for t4@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 't4'@'%' |
| GRANT SELECT (a) ON `testdb`.`tbl1` TO 't4'@'%' |
+-------------------------------------------------+
-- t6登陆：只能插入a列
mysql -ut6 -S /mysqldata/tmp3148/mysql.sock -p333
mysql> insert into tbl1 (a) values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into tbl1 (b) values (1);
ERROR 1143 (42000): INSERT command denied to user 't6'@'localhost' for column 'b' in table 'tbl1'
mysql> show grants;
+-------------------------------------------------+
| Grants for t6@% |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO 't6'@'%' |
| GRANT INSERT (a) ON `testdb`.`tbl1` TO 't6'@'%' |
+-------------------------------------------------+
使用SSL
手册5.8.7需要再整理
Grant&Revoke
GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
TO user
[IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE
NONE |
[{SSL| X509}]
[CIPHER 'cipher' [AND]]
[ISSUER 'issuer' [AND]]
[SUBJECT 'subject']]
[WITH with_option [with_option] ...]
object_type =
TABLE
| FUNCTION
| PROCEDURE
with_option =
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
REVOKE priv_type [(column_list)] [, priv_type [(column_list)]] ...
ON [object_type] {tbl_name | * | *.* | db_name.*}
FROM user [, user] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...
授予的权限可以分为多个层级
-
全局层级
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。
GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。 -
数据库层级
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。
GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。 -
表层级
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。
GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。 -
列层级
列权限适用于一个给定表中的单一列。这些权限存储在
mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。 -
子程序层级
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。
权限表
| 权限 | 意义 |
|---|---|
| ALL [PRIVILEGES] | 设置除GRANT OPTION之外的所有简单权限 |
| ALTER | 允许使用ALTER TABLE |
| ALTER ROUTINE | 更改或取消已存储的子程序 |
| CREATE | 允许使用CREATE TABLE |
| CREATE ROUTINE | 创建已存储的子程序 |
| CREATE TEMPORARY TABLES | 允许使用CREATE TEMPORARY TABLE |
| CREATE USER | 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 |
| CREATE VIEW | 允许使用CREATE VIEW |
| DELETE | 允许使用DELETE |
| DROP | 允许使用DROP TABLE |
| EXECUTE | 允许用户运行已存储的子程序 |
| FILE | 允许使用SELECT…INTO OUTFILE和LOAD DATA INFILE |
| INDEX | 允许使用CREATE INDEX和DROP INDEX |
| INSERT | 允许使用INSERT |
| LOCK TABLES | 允许对您拥有SELECT权限的表使用LOCK TABLES |
| PROCESS | 允许使用SHOW FULL PROCESSLIST |
| REFERENCES | 未被实施 |
| RELOAD | 允许使用FLUSH |
| REPLICATION CLIENT | 允许用户询问从属服务器或主服务器的地址 |
| REPLICATION SLAVE | 用于复制型从属服务器（从主服务器中读取二进制日志事件） |
| SELECT | 允许使用SELECT |
| SHOW DATABASES | SHOW DATABASES显示所有数据库 |
| SHOW VIEW | 允许使用SHOW CREATE VIEW |
| SHUTDOWN | 允许使用mysqladmin shutdown |
| SUPER | 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。 |
| UPDATE | 允许使用UPDATE |
| USAGE | “无权限”的同义词 |
| GRANT OPTION | 允许授予权限 |
使用SHOW GRANTS来确定帐户拥有什么权限
FILEPROCESS
RELOAD
REPLICATION CLIENT
REPLICATION SLAVE
SHOW DATABASES
SHUTDOWN
SUPER
权限是管理性权限，只能进行全局授权（使用ON *.*语法）。
-
对于一个表，您可以指定的priv_type值只能是
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX, ALTER -
对于一个列（也就是，当您使用一个column_list子句时），您可以指定的priv_type值只能是
SELECT, INSERT, UPDATE -
子程序层级您可以指定的priv_type值只能是
ALTER ROUTINE, EXECUTE, GRANT OPTION
2929
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
