一、权限

二、权限级别

1、global level 全局权限控制，所有的信息都保存在mysql.user表中。

2、database level 作用域为指定某个数据库中的所有对象，所有权限信息保存在mysql.db中。当执行grant命令时，通过“database.*”来限定作用域为database整个数据库；也可以通过use命令选定授权的数据库，然后通过“*”来限定作用域，这样授权的作用域实际上就是当前选定的整个数据库。

3、table level 作用范围是授权语句中指定数据库的指定表（database.table），权限信息保存在tables_priv中。

4、column level 作用域为某个指定的列，权限信息保存在columns_priv中。column level级别的权限仅有insert、select、update这三种。语法格式：grant select（id，value） on test.t2 to ‘abc’@‘%’。给abc用户授予 test数据库t2表的id、value列select权限.

5、routine level 针对的主要对象时procedure和function。目前暂时只有execute和alter routine两种。语法格式：grant execute on test.p1 to ‘abc’ @’%’;

6、with grant option。在授权时加上此命令，被授权用户有传递权限的权限。

三、权限查看和更改

1、新加权限或者用户。

  GRANT 权限 ON 库名.表名 TO 新用户名@主机名 IDENTIFIED BY ‘密码‘；

  例：grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;新加的用户名为test1 ，密码为abc，对所有表有增删查改的权限，在任何主机上可以登录。

2、查看权限。

   使用show grants 语句查看指定账户的权限；例如，要检查Host和User值分别为pc84.example.com和bob的账户所授予的权限，应通过语句：

mysql> SHOW GRANTS FOR ‘bob’@’pc84.example.com’;

3、更改权限。

   若通过直接修改权限表来更改权限，则修改完后都必须要执行“flush privileges”，通知mysql重新加载MySQL的权限信息；如果通过grant、revoke或drop user命令来修改权限，则不需要执行“flush privileges”命令。

4、权限更改何时生效

当mysqld启动时，所有授权表的内容被读进内存并且从此时生效。

当服务器注意到授权表被改变了时，现存的客户端连接有如下影响：

· 表和列权限在客户端的下一次请求时生效。

· 数据库权限改变在下一个USE db_name命令生效。

· 全局权限的改变和密码改变在下一次客户端连接时生效。

如果用GRANT、REVOKE或SET PASSWORD对授权表进行修改，服务器会注意到并立即重新将授权表载入内存。

如果你手动地修改授权表(使用INSERT、UPDATE或DELETE等等)，你应该执行mysqladmin flush-privileges或mysqladmin reload告诉服务器再装载授权表，否则你的更改将不会生效，除非你重启服务器。

如果你直接更改了授权表但忘记重载，重启服务器后你的更改方生效。这样可能让你迷惑为什么你的更改没有什么变化！

5、修改密码

当使用setpassword、insert、update更改密码时，必须使用PASSWORD()函数加密密码。若果不使用PASSWORD()函数，密码将不工作。

例如，下面的语句设置密码，但没能加密，因此用户后面不能连接：

    mysql> SET PASSWORD FOR ‘abe’@’host_name’ = ‘eagle’;

相反，应这样设置密码：

mysql> SET PASSWORD FOR ‘abe’@’host_name’ = PASSWORD(‘eagle’);

当使用GRANT或CREATE USER语句或mysqladmin password命令指定密码时，不需要PASSWORD()函数，它们会自动使用PASSWORD()来加密密码。

四、权限表列值的规则

1、user 、host 、db表中值的规则

· 通配符字符“%”和“_”可用于表的Host和Db列。它们与用LIKE操作符执行的模式匹配操作具有相同的含义。如果授权时你想使用某个字符，必须使用反斜现引用。例如，要想在数据库名中包括下划线(‘_’)，在GRANT语句中用‘\_’来指定。

·在db表的’%’Host值意味着“任何主机”，在db表中空Host值意味着“对进一步的信息咨询host表”。

·在host表的’%’或空Host值意味着“任何主机”。

·在三个表中的’%’或空Db值意味着“任何数据库”。

·在user、db表中的空User值匹配匿名用户

2、tables_priv和columns_priv表中值得规则

·通配符“%”并“_”可用在使用在两个表的Host列。

·在两个表中的’%’或空Host意味着“任何主机”。

·在两个表中的Db、Table_name和Column_name列不能包含通配符或空。

3、mysql.host表的特殊点

mysql.host不是通过grant或revoke权限来授予或去除的，必须手工通过insert、update和delete命令来修改其中的数据。其中的权限无法单独生效，必须与mysql.db权限表一起才能生效。当mysql.db中的信息不完整时，采取访问mysql.host。

当想在db表的范围之内扩展一个条目时，就会用到host表。举例来说，如果某个db允许通过多个主机访问的话，那么超级用户就可以让db表内将host列为空，然后用必要的主机名填充host表。

五、访问控制

阶段1：连接核实

当你试图连接MySQL服务器时，服务器基于你的身份以及你是否能通过供应正确的密码验证身份来接受或拒绝连接。如果不是，服务器完全拒绝你的访问，否则，服务器接受连接，然后进入阶段2并且等待请求。

你的身份基于2个信息：

·你从那个主机连接

·你的MySQL用户名

身份检查使用3个user表(Host, User和Password)范围列执行。服务器只有在user表记录的Host和User列匹配客户端主机名和用户名并且提供了正确的密码时才接受连接。

阶段2：请求核实

一旦你建立了连接，服务器进入访问控制的阶段2。对在此连接上进来的每个请求，服务器检查你想执行什么操作，然后检查是否有足够的权限来执行它。这正是在授权表中的权限列发挥作用的地方。这些权限可以来自user、db、host、tables_priv或columns_priv表。

六、query 处理权限校验流程

七、常做操作所需权限

1、备份

备份用户会通过mysqldump来做备份，一般只需要用到select和lock tables 两项权限。如果使用带-tab选项的mysqldump来做tab分界符文件的导出，或者是用select into outfile，那么还需要一个file权限。

例：grant select，lock tables，file on *.* to backup@localhost

为了保证许多备份操作的一致性，还会用到flush tables with read lock，所以还需要reload权限。

2、操作和监控

维护系统或修复故障需要用到kill或show命令，还需要关闭服务器。所以需要用到process和shutdown权限。

连接

Earlier this year, I – along with some members of our DevOps team – noticed some interesting behavior in libmysqlclient and the MySQL CLI: no matter how hard we tried (no matter how many MYSQL_OPT_SSL_* options we set) we could not make the client enforce the use of SSL. If the server claimed not to support it, the client would happily communicate over plain old, unencrypted TCP!

This means that MySQL clients are super-vulnerable to an attack along the lines of Moxie Marlinspike’s sslstrip: a Man-In-The-Middle attacker (to conform with academic norms, let’s call her Mallory) can intercept the MySQL server’s handshake packet, unset the flag that says “I support SSL”, and then observe every query and response, or even inject her own!

Now, if you’re an avid MySQL user, you might be thinking “but wait – I can just use the REQUIRE SSL option on my server…” Nope! That’s the beauty of ssl-stripping attacks: Mallory can initiate a bona-fide TLS session with the server, while continuing to speak plaintext with the client [1]. Or, to put it in somewhat more technical terms (it may be helpful to refer to the MySQL Protocol Spec), Mallory can:

1. Unset the CLIENT_SSL flag in the Initial Handshake Packet
2. Construct an SSL Connection Request Packet from the Handshake Response Packet (i.e. take the first 32 bytes of the Handshake Response Packet, and set the CLIENT_SSL flag)
3. Perform a TLS handshake with the server
4. Proxy traffic back and forth between the client and server (Since the server has seen one more packet than the client, Mallory will also have to increment and decrement packet sequence IDs for a few roundtrips until they reset).

Sadly, this sort of vulnerability is not at all uncommon; aside from HTTP (again, see sslstrip), just about all e-mail delivery across the internet has a similar – if not actually worse – affliction. However: it’s really hard to totally solve these issues for ancient, widely-implemented protocols like HTTP (HSTS notwithstanding) and SMTP without, well, breaking the internet. MySQL is a different story…

Actually, the good news is that the MySQL team has already realized this was a problem, and implemented a fix. Like, over a year ago. The bad news? The fix was only applied to MySQL 5.7.3 and later; 5.7.x is not yet even a GA release! (Also, the fix was applied to version 6.1.3 of the standalone libmysqlclient distribution). The worse news? In many cases, the “fix” is not enabled by default! So, while we haven’t collected any real data on the subject, we’re pretty confident that the vast majority of libmysqlclient users are affected by this issue.

Now, for most MySQL users, this vulnerability probably isn’t panic-worthy: as mentioned earlier, Mallory has to be in a position to perform a Man-In-The-Middle attack between the database and its client(s). It’s pretty typical for a database server to be adjacent to (or even on the same box as) its client – e.g. a web application server – so MITM attacks might not be a serious concern. We also expect that many MySQL users don’t bother to enable SSL at all. That said, it is clear from the libmysqlclient documentation that its developers intended to defend against this very threat:

MYSQL_OPT_SSL_VERIFY_SERVER_CERT (argument type: my_bool *)

Enable or disable verification of the server’s Common Name value in its certificate against the host name used when connecting to the server. The connection is rejected if there is a mismatch. This feature can be used to prevent man-in-the-middle attacks. Verification is disabled by default.

In all but the newest versions of libmysqlclient, the bolded claim above is demonstrably false.

So, in an effort to raise awareness about this issue, we reported it to oCERT, who worked with project maintainers and vendors to coordinate disclosure of an advisory; CVE-2015-3152 has been assigned.

UPDATE: Check out Todd Farmer’s response to the oCERT advisory. There’s some great information there!

Additionally: in compliance with current best practices, we have assigned a scary logo, website, and name. Behold:

This morning we received a report from oCERT which is being treated as a public security issue in the MySQL client.

In short it is possible for the MySQL client to silently fall back on a non SSL connection instead of aborting the connection, and as such communication will not be encrypted “in flight”, this is known documented behaviour,

This is now being assigned a CVE and an advisory is set for release April 29th, the body of the original notification follows.

—

This issue affects MariaDB, and very likely Percona. as well and is related
to https://mariadb.atlassian.net/browse/MDEV-7937

The issue concerns the impossibility for MySQL/MariaDB users (with any major
stable version) to enforce an SSL connection without possibility for a MITM
attach to perform a malicious downgrade.

The issue affects MySQL versions before 5.7.3. However, these fixes have not
been back-ported to previous major versions (5.5, 5.6, etc.), and MySQL 5.7
is not yet considered a stable release. Situation should be similar with
MariaDB.

Therefore the vast majority of MySQL/MariaDB users:

a) have no ability to enforce SSL use, except by patching code or
performing a major-version upgrade to a development release, and

b) are probably not aware of this limitation

The following links clearly illustrate the issue:

https://github.com/mysql/mysql-server/commit/3bd5589e1a5a93f9c224badf983cd65c45215390
http://mysqlblog.fivefarmers.com/2014/04/02/redefining-ssl-option/
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html

While technically this is documented behaviour, it represents a pretty bad
one and the feeling is that most users actually have no awareness of this.