mysql权限管理

一、权限

命令标识

授权表中对应的列

说明

CREATE

Create_priv

创建数据库、表或索引

CREATE TEMPORARY TABLES

Create_tmp_table_priv

创建临时数据表

CREATE ROUTINE

Create_routine_priv

创建函数或存储

CREATE VIEW

Create_view_priv

创建视图

CREATE USER

Create_user_priv

创建用户

EXECUTE

Execute_priv

执行函数或存储过程

INDEX

Index_priv

建立索引

REFERENCES

References_priv

建立约束

DROP

Drop_priv

删除表

SELECT

Select_priv

查询数据

INSERT

Insert_priv

插入数据

UPDATE

Update_priv

更新数据

DELETE

Delete_priv

删除数据

LOCK TABLES

Lock_tables_priv

锁定表格

SHOW DATABASES

Show_db_priv

列出数据库

SHOW VIEW

Show_view_priv

列出视图

USAGE

只有登录权限,其他权限都没有

ALL

所有权限,除了WITH GRANT OPTION

ALTER

Alter_priv

更改数据表

ALTER ROUTINE

Alter_routine_priv

更改函数或存储过程

PROCESS

Process_priv

显示连接进程和中断连接进程

FILE

File_priv

载入文件

RELOAD

Reload_priv

可以用FLUSH

REPLICATION CLIENT

Repl_client_priv

可以检查Masters和Slaves

REPLICATION SLAVE

Repl_slave_priv

在Slave里的特殊权限

SHUTDOWN

Shutdown_priv

关闭MySQL

WITH GRANT OPTION

Grant_priv

可以将自己拥有的权限赋给其他用户

SUPER

Super_priv

执行kill线程,change master、purge master logs、set global等命令的权限

create tablespace

Create_tablespace_priv

创建表空间

Event

Event_priv

确定用户能否创建、修改和删除事件

Trigger

Trigger_priv

确定用户能否创建和删除触发器

二、权限级别

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权限。

The BACKRONYM MySQL Vulnerability (CVE-2015-3152)

连接

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:

–ssl option should enforce SSL

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.

Oracle MySQL SSL证书验证安全限制绕过漏洞(CVE-2015-3152)

Redefining –ssl option

[UPDATE:  Please see related post regarding oCERT assessment 2015-003.]

MySQL clients have long had a –ssl option.  Casual users may think specifying this option will cause clients to secure connections using SSL.  That is not the case:

D:\mysql-5.6.13-winx64>bin\mysql -uroot -P3307 --ssl
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> \s
--------------
bin\mysql  Ver 14.14 Distrib 5.6.13, for Win64 (x86_64)

Connection id:          2
Current database:
Current user:           root@localhost
SSL:                    Not in use
...

This behavior is clearly explained in the manual:

For the server, this option specifies that the server permits but does not require SSL connections.

For a client program, this option permits but does not require the client to connect to the server using SSL. Therefore, this option is not sufficient in itself to cause an SSL connection to be used. For example, if you specify this option for a client program but the server has not been configured to permit SSL connections, an unencrypted connection is used.

Of course, an account can be defined on the server side to require SSL connections (with the REQUIRE SSL option for GRANT syntax), but until MySQL Server 5.7.3, it’s not possible for a user to demand SSL be used for a given client connection.  When connecting to a server which isn’t configured to support SSL, a client will accept non-SSL connections even though a number of SSL-related configuration options are defined:

D:\mysql-5.6.13-winx64>bin\mysql -uroot -P3307 --ssl  --ssl-ca=D:\certs\ca-cert.pem 
 --ssl-verify-server-cert --ssl-key=D:\certs\client-key.pem --ssl-cert=D:\certs\client-cert.pem
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> \s
--------------
bin\mysql  Ver 14.14 Distrib 5.6.13, for Win64 (x86_64)

Connection id:          10
Current database:
Current user:           root@localhost
SSL:                    Not in use
...

That’s problematic in a world that increasingly cares about securing client/server communication made over the network, and in MySQL Server 5.7.3, this is fixed by redefining the –ssl option to require SSL.  Here’s an example:

D:\mysql-5.7.4-m14-winx64>bin\mysql -uroot --ssl
ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn't support it

There is a corresponding change made in libmysql, adding a MYSQL_OPT_SSL_ENFORCE option for the mysql_options() method, allowing libmysql-based clients or drivers to implement the same behavior as in 5.7 clients. Note that this behavior was actually originally introduced in MySQL Server 5.7.3 DMR (not the just-released 5.7.4 DMR, though it’s also there). Because this is a behavioral change, it has not been back-ported to MySQL Server 5.6.

CVE-2015-3152 SSL Certificate Validation Security Bypass Vulnerability

[oCERT-2015-003] MySQL SSL/TLS downgrade Apr 29 2015 02:00PM
Andrea Barisani (lcars ocert org)

#2015-003 MySQL SSL/TLS downgradeDescription:

The MySQL project is an open source relational database management system.

A vulnerability has been reported concerning the impossibility for MySQL users
(with any major stable version) to enforce an effective SSL/TLS connection
that would be immune from man-in-the-middle (MITM) attacks performing a
malicious downgrade.

While the issue has been addressed in MySQL preview release 5.7.3 in December
2013, it is perceived that the majority of MySQL users are not aware of this
limitation and that the issue should be treated as a vulnerability.

The vulnerability lies within the behaviour of the ‘–ssl’ client option,
which on affected versions it is being treated as “advisory”. Therefore while
the option would attempt an SSL/TLS connection to be initiated towards a
server, it would not actually require it. This allows a MITM attack to
transparently “strip” the SSL/TLS protection.

The issue affects the ssl client option whether used directly or triggered
automatically by the use of other ssl options (‘–ssl-xxx’) that imply
‘–ssl’.

Such behavior is clearly indicated in MySQL reference manual as follows:

For the server, this option specifies that the server permits but does not require
SSL connections.

For a client program, this option permits but does not require the client to
connect to the server using SSL. Therefore, this option is not sufficient in
itself to cause an SSL connection to be used. For example, if you specify this
option for a client program but the server has not been configured to permit
SSL connections, an unencrypted connection is used.

In a similar manner to the new ‘–ssl’ option behaviour, users of the MySQL
client library (Connector/C, libmysqlclient), as of MySQL 5.7.3, can take
advantage of the MYSQL_OPT_SSL_ENFORCE option to enforce SSL/TLS connections.

The vulnerability also affects the MySQL forks MariaDB and Percona Server, as
the relevant 5.7.3 patch has not been pulled, at the time of this advisory, in
their respective stable versions.

Affected version:

MySQL <= 5.7.2

MySQl Connector/C (libmysqlclient) < 6.1.3

Percona Server, all versions

MariaDB, all versions

Fixed version:

MySQL >= 5.7.3

MySQl Connector/C (libmysqlclient) >= 6.1.3

# 现在emm里面的如下:

[emm@emm2 target]$ find /opt/emm -name *mysql*
/opt/emm/uusafe_lib/mysql-connector-java-5.1.36.jar
[emm@emm2 target]$

Percona Server, N/A

MariaDB, N/A

Credit: vulnerability report from Adam Goodman, Principal Security Architect
at Duo Security.

CVE: CVE-2015-3152 (MariaDB, Percona)

Timeline:

2015-03-20: vulnerability report received
2015-03-23: contacted Oracle Security
2015-04-04: oCERT sets embargo date to April 29th
2015-04-20: reporter confirms MariaDB is affected
2015-04-22: contacted MariaDB and affected vendors, assigned CVEs
2015-04-23: contacted Percona
2015-04-29: advisory release

References:
https://github.com/mysql/mysql-server/commit/3bd5589e1a5a93f9c224badf983
cd65c45215390

Redefining –ssl option


http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-3.html
https://mariadb.atlassian.net/browse/MDEV-7937
https://bugs.launchpad.net/percona-server/+bug/1447527

Permalink:
http://www.ocert.org/advisories/ocert-2015-003.html


Andrea Barisani | Founder & Project Coordinator
oCERT | OSS Computer Security Incident Response Team

<lcars (at) ocert (dot) org [email concealed]> http://www.ocert.org
0x864C9B9E 0A76 074A 02CD E989 CE7F AC3F DA47 578E 864C 9B9E
“Pluralitas non est ponenda sine necessitate”