Troubleshooting MySQL access privileges issues

下载 0

空白

发布于

1560

人观看

#信息技术

请加入Percona的首席技术服务工程师Sveta Smirnova，她将介绍“解决MySQL访问权限问题”。
是否有注册用户无法连接到MySQL服务器？陌生人修改他们不应该访问的数据？
MySQL支持一组丰富的用户权限选项，并允许您微调对服务器中每个对象的访问。最新版本支持的身份验证插件有助于创建更多的访问模式。
然而，在这么大的一组选项中发现错误是有问题的。对于拥有数百个用户的环境尤其如此，所有用户对多个对象都具有不同的权限。在本次网络研讨会中，我将向您展示如何破译错误消息并解开可能导致访问错误的复杂设置。我们还将介绍模拟访问权限错误的网络错误。
我们将讨论：
-MySQL支持哪些权限
-什么是赠款声明？
-如何存储权限
-如何找出特权不能正常工作的原因
-身份验证插件如何发挥作用
-最佳访问控制实践是什么

展开查看详情

1 .Troubleshooting MySQL access privileges issues February, 23, 2017 Sveta Smirnova

2 .Table of Contents •Typical Access Errors •Which privileges MySQL Supports •Plugins •SQL: GRANT and CREATE USER •Troubleshooting must haves •How privileges stored 2

3 .Typical Access Errors

4 .User cannot connect sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysqlmtr -P13001 -usveta webinar -p Enter password: ERROR 1045 (28000): Access denied for user ’sveta’@’localhost’ (using password: NO) 4

5 .User cannot connect sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysqlmtr -P13001 -usveta webinar -p Enter password: ERROR 1045 (28000): Access denied for user ’sveta’@’localhost’ (using password: NO) • Privileges do not exist 4

6 .User cannot connect sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysqlmtr -P13001 -usveta webinar -p Enter password: ERROR 1045 (28000): Access denied for user ’sveta’@’localhost’ (using password: NO) • Privileges do not exist • Wrong privileges assigned 4

7 .User cannot connect sveta@Thinkie:~/build/ps-5.7/mysql-test$ mysqlmtr -P13001 -usveta webinar -p Enter password: ERROR 1045 (28000): Access denied for user ’sveta’@’localhost’ (using password: NO) • Privileges do not exist • Wrong privileges assigned • Wrong host 4

8 .Unprivileged user has access to database mysql> show grants for sveta@localhost; +---------------------------------------------------------+ | Grants for sveta@localhost | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO ’sveta’@’localhost’ | | GRANT ALL PRIVILEGES ON ‘test‘.* TO ’sveta’@’localhost’ | +---------------------------------------------------------+ 2 rows in set (0.00 sec) sveta@Thinkie:$ mysqlmtr -P13001 -h192.168.0.4 -usveta webinar -e "show tables" +-------------------+ | Tables_in_webinar | +-------------------+ | test | +-------------------+ 5

9 .Unprivileged user has access to database mysql> show grants for sveta@localhost; +---------------------------------------------------------+ | Grants for sveta@localhost | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO ’sveta’@’localhost’ | | GRANT ALL PRIVILEGES ON ‘test‘.* TO ’sveta’@’localhost’ | +---------------------------------------------------------+ 2 rows in set (0.00 sec) sveta@Thinkie:$ mysqlmtr -P13001 -h192.168.0.4 -usveta webinar -e "show tables" +-------------------+ | Tables_in_webinar | +-------------------+ | test | +-------------------+ • Wrong privileges assigned 5

10 .Unprivileged user has access to database mysql> show grants for sveta@localhost; +---------------------------------------------------------+ | Grants for sveta@localhost | +---------------------------------------------------------+ | GRANT USAGE ON *.* TO ’sveta’@’localhost’ | | GRANT ALL PRIVILEGES ON ‘test‘.* TO ’sveta’@’localhost’ | +---------------------------------------------------------+ 2 rows in set (0.00 sec) sveta@Thinkie:$ mysqlmtr -P13001 -h192.168.0.4 -usveta webinar -e "show tables" +-------------------+ | Tables_in_webinar | +-------------------+ | test | +-------------------+ • Wrong privileges assigned 5 • Wrong host

11 .Wrong Privileges mysql> create database car_; Query OK, 1 row affected (0.00 sec) mysql> create database care; Query OK, 1 row affected (0.00 sec) mysql> grant all on car_.* to sveta@localhost; Query OK, 0 rows affected (0.00 sec) sveta@Thinkie:~$ mysqlmtr -P13001 -usveta care -e "show tables"; +----------------+ | Tables_in_care | +----------------+ | test | +----------------+ 6

12 .Wrong Privileges mysql> create database car_; Query OK, 1 row affected (0.00 sec) mysql> create database care; Query OK, 1 row affected (0.00 sec) mysql> grant all on car_.* to sveta@localhost; Query OK, 0 rows affected (0.00 sec) sveta@Thinkie:~$ mysqlmtr -P13001 -usveta care -e "show tables"; +----------------+ | Tables_in_care | +----------------+ | test | +----------------+ • Typo 6

13 .Wrong Privileges mysql> create database car_; Query OK, 1 row affected (0.00 sec) mysql> create database care; Query OK, 1 row affected (0.00 sec) mysql> grant all on car_.* to sveta@localhost; Query OK, 0 rows affected (0.00 sec) sveta@Thinkie:~$ mysqlmtr -P13001 -usveta care -e "show tables"; +----------------+ | Tables_in_care | +----------------+ | test | +----------------+ • Typo 6 • Misuse of wildcards

14 .Wrong host mysql> create user sveta@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant all on test.* to sveta@localhost; Query OK, 0 rows affected (0.00 sec) sveta@Thinkie:$ mysqlmtr -P13001 -h192.168.0.4 -usveta webinar -e "show tables" +-------------------+ | Tables_in_webinar | +-------------------+ | test | +-------------------+ 7

15 .Wrong host mysql> create user sveta@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant all on test.* to sveta@localhost; Query OK, 0 rows affected (0.00 sec) sveta@Thinkie:$ mysqlmtr -P13001 -h192.168.0.4 -usveta webinar -e "show tables" +-------------------+ | Tables_in_webinar | +-------------------+ | test | +-------------------+ • Mix of socket and TCP protocol 7

16 .Wrong host mysql> create user sveta@localhost; Query OK, 0 rows affected (0.00 sec) mysql> grant all on test.* to sveta@localhost; Query OK, 0 rows affected (0.00 sec) sveta@Thinkie:$ mysqlmtr -P13001 -h192.168.0.4 -usveta webinar -e "show tables" +-------------------+ | Tables_in_webinar | +-------------------+ | test | +-------------------+ • Mix of socket and TCP protocol • Mix of numeric and name-based hosts 7

17 .Slave cannot connect mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master ... Slave_IO_Running: Connecting Slave_SQL_Running: Yes ... Last_IO_Errno: 2003 Last_IO_Error: error connecting to master ’repl@127.0.0.1:13000’ - retry-time: 60 retries: 1 Last_SQL_Errno: 0 8

18 .Slave cannot connect mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master ... Slave_IO_Running: Connecting Slave_SQL_Running: Yes ... Last_IO_Errno: 2003 Last_IO_Error: error connecting to master ’repl@127.0.0.1:13000’ - retry-time: 60 retries: 1 Last_SQL_Errno: 0 • User does not exist 8

19 .Slave cannot connect mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Connecting to master ... Slave_IO_Running: Connecting Slave_SQL_Running: Yes ... Last_IO_Errno: 2003 Last_IO_Error: error connecting to master ’repl@127.0.0.1:13000’ - retry-time: 60 retries: 1 Last_SQL_Errno: 0 • User does not exist • User does not have REPLICATION SLAVE privilege 8

20 .SSL-related Errors • Something wrong with certificate sveta@Thinkie:~$ mysqlmtr -P13002 --ssl-ca=./std_data/cacer.pem \ --ssl-key=./std_data/client-key.pem --ssl-cert=./std_data/client-cert.pem ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1) 9

21 .SSL-related Errors • Something wrong with certificate sveta@Thinkie:~$ mysqlmtr -P13001 --ssl-ca=./std_data/cacer.pem \ --ssl-key=./std_data/client-key.pem --ssl-cert=./std_data/client-cert.pem ERROR 2026 (HY000): SSL connection error: SSL is required but the server doesn’t support it 9

22 .SSL-related Errors • Something wrong with certificate • Plain connection used sveta@Thinkie:~$ mysqlmtr -P13001 --ssl-mode=PREFERRED Welcome to the MySQL monitor. Commands end with ; or \g. ... mysql> \s -------------- ../bin/mysql Ver 14.14 Distrib 5.7.16-9, for Linux (x86_64) using 6.3 Connection id: 751 Current database: Current user: root@localhost SSL: Not in use 9

23 .Which privileges MySQL Supports

24 .Fine details • For objects • Databases • Tables • Views • Stored routines • Columns • Indexes 11

25 .Fine details • For objects • By action • CREATE/DROP/ALTER/SHOW/LOCK • INSERT/UPDATE/DELETE/EXECUTE • SELECT • EVENT/TRIGGER/REFERENCES 11

26 .Fine details • For objects • By action • Administrative • CREATE USER/PROXY • REPLICATION CLIENT/REPLICATION SLAVE • RELOAD/SHUTDOWN • SUPER • USAGE • ALL 11

27 .Options • PASSWORD [EXPIRE ...] 12

28 .Options • PASSWORD [EXPIRE ...] • SSL • REQUIRE or not • X509/CIPHER • ISSUER/SUBJECT 12

29 .Options • PASSWORD [EXPIRE ...] • SSL • Resources • MAX QUERIES PER HOUR • MAX UPDATES PER HOUR • MAX CONNECTIONS PER HOUR • MAX USER CONNECTIONS 12

0点赞

0收藏

0下载