Troubleshooting MySQL Access Permissions: Frequently Asked Questions
In February of this year, Sveta Smirnova (Percona’s lead engineer) held a webinar dedicated to solving problems with access rights in MySQL . Recording and webinar slides are available here . We offer you a small overview of the most popular questions on this topic.
What rights should be given to root @ localhost: ALL or Super? Do rights include All and Super rights too?
You must have a user with full rights. It is better if this user has access only from localhost. ALL rights include SUPER rights.
We have users connecting from laptops who get dynamic IP addresses, so giving access through the server name is the easiest way to manage these users. Is it possible to provide access to the MySQL database using the host name and not the IP address? For example, myname@mymachine.mydomain.com instead of “myname@10.10.10.10”? Does this host cache / performance_schema require?
')
Yes you can. But it looks like I didn’t explain enough what a host cache is. This is an internal structure that is always available and contains responses from the DNS server. You cannot turn it on or off. Until version 5.6, you also could not control it. For example, if the cache was damaged, the only thing you could do was restart the server. In version 5.6, the HOST_CACHE table was introduced in Performance Schema. Using this table you can check the contents of the host cache and, if necessary, clear it.
If there are several entries in the user table that correspond to the connecting user (for example, through templates, host name and IP), which rules does MySQL choose, which one will be used for authentication? Will he check everyone until he gets a password match?
No, mysqld is not trying to crack your passwords. Instead, it sorts the user table by name and host in descending order, as shown on slide 37 (p. 110). Then it takes the first matching line. That is, if you created users foo @ somehost, foo @ some% and foo@1.2.3.4, and connect as foo from somehost, mysqld first checks the username and then selects the first matching line foo @ somehost. If instead you connect as foo from someotherhost, mysqld chooses foo @ some%. An IP-based host is selected, either if mysqld is started with the skip-networking option, or if 1.2.3.4 points to a host whose name does not begin with “some”.
Mixing IP-based hosts with name-based hosts is dangerous in situations where the same host can be accepted as somehost and 1.2.3.4. In this case, if something goes wrong with the host cache or the DNS server, an incorrect entry from the user table may be selected. Suppose you initially had three hosts: uniquehost (which is converted as 1.2.3.4), somehost (which is converted as 4.3.2.1) and someothershost (which is converted as 4.3.2.2). Now you decide to move uniquehost to the machine with IP 1.2.3.5 and use IP 1.2.3.4 for the host named someyetanotherhost. In this case, the clients from the IP 1.2.3.4 machine will be considered as foo @ some%, and this is not what you wanted.
To demonstrate this case, I created two users and gave them two different sets of rights:
Then I changed the / etc / hosts file and specified the address 192.168.0.4 for the name Thinkie:
Now if I connect as sveta, and Thinkie, and 192.168.0.4 will be converted to the same host:
After that, I changed the / etc / hosts file and again linked Thinkie to 127.0.0.1 (localhost):
But the host 192.168.0.4 is still being converted to a Thinkie:
The reason for this is the outdated host cache, which is clearly seen in the Performance Schema:
After clearing the host_cache table, the numeric host is converted as I expect:
What rights are required for non-root and non-super users to use mysqldump to reset the database and then restore it on another server?
As a rule, you should have SELECT rights for all objects that you intend to reset. If you drop views, you must also have SHOW VIEW permissions to run SHOW CREATE TABLE. If you want to reset stored procedures / events, you also need access to them. If you use the --lock-tables or --lock-all-tables option, you must have LOCK rights.
If the max_connection value is reached in MySQL, can root @ localhost be logged in with ALL rights or a user with Super rights?
ALL includes SUPER, so that a user with ALL rights can log in. But keep in mind that such a connection can be only one, so do not grant SUPER or ALL rights to the application user.
Is it possible to remove a privilege at a lower level? In other words, to allow to select and delete at the database level, but to prohibit the deletion for a specific table? Or privileges can only add?
No, MySQL will reject such a query:
How can you create user roles ... as a group of grants for a specific role?
You have several options:
How would you migrate role modeling with proxies to actual roles in MySQL 8.x?
I would delete the proxy user and create a role with the same rights, and then assign this new role to the proxy user instead of PROXY.
Is there a plugin for integrating Active Directory and MySQL to use Active Directory groups?
There is a commercial authentication plugin Windows Authentication Plugin , available in versions 5.5 and newer. You can also use the Percona PAM authentication plugin, an open source authentication plugin, and connect it to Active Directory in the same way as for LDAP. There is an article that describes how to do this, but I myself have never used this method.
Can I use centralized authentication in MySQL?
Yes, using the PAM plugin. There are instructions for LDAP and Active Directory. You can use similar methods to set up other types of authentication, such as Kerberos.
Friends, if working with MySQL is a daily task for you, be sure to come to us at PG Day'17 Russia . Sveta Smirnova , Petr Zaitsev and other Percona specialists prepare for you fascinating reports and master classes on the structure and functioning of MySQL in the section on open source databases.
What rights should be given to root @ localhost: ALL or Super? Do rights include All and Super rights too?
You must have a user with full rights. It is better if this user has access only from localhost. ALL rights include SUPER rights.
We have users connecting from laptops who get dynamic IP addresses, so giving access through the server name is the easiest way to manage these users. Is it possible to provide access to the MySQL database using the host name and not the IP address? For example, myname@mymachine.mydomain.com instead of “myname@10.10.10.10”? Does this host cache / performance_schema require?
')
Yes you can. But it looks like I didn’t explain enough what a host cache is. This is an internal structure that is always available and contains responses from the DNS server. You cannot turn it on or off. Until version 5.6, you also could not control it. For example, if the cache was damaged, the only thing you could do was restart the server. In version 5.6, the HOST_CACHE table was introduced in Performance Schema. Using this table you can check the contents of the host cache and, if necessary, clear it.If there are several entries in the user table that correspond to the connecting user (for example, through templates, host name and IP), which rules does MySQL choose, which one will be used for authentication? Will he check everyone until he gets a password match?
No, mysqld is not trying to crack your passwords. Instead, it sorts the user table by name and host in descending order, as shown on slide 37 (p. 110). Then it takes the first matching line. That is, if you created users foo @ somehost, foo @ some% and foo@1.2.3.4, and connect as foo from somehost, mysqld first checks the username and then selects the first matching line foo @ somehost. If instead you connect as foo from someotherhost, mysqld chooses foo @ some%. An IP-based host is selected, either if mysqld is started with the skip-networking option, or if 1.2.3.4 points to a host whose name does not begin with “some”.
Mixing IP-based hosts with name-based hosts is dangerous in situations where the same host can be accepted as somehost and 1.2.3.4. In this case, if something goes wrong with the host cache or the DNS server, an incorrect entry from the user table may be selected. Suppose you initially had three hosts: uniquehost (which is converted as 1.2.3.4), somehost (which is converted as 4.3.2.1) and someothershost (which is converted as 4.3.2.2). Now you decide to move uniquehost to the machine with IP 1.2.3.5 and use IP 1.2.3.4 for the host named someyetanotherhost. In this case, the clients from the IP 1.2.3.4 machine will be considered as foo @ some%, and this is not what you wanted.
To demonstrate this case, I created two users and gave them two different sets of rights:
mysql> create user sveta@Thinkie; Query OK, 0 rows affected (0,01 sec) mysql> create user sveta@'192.168.0.4'; Query OK, 0 rows affected (0,00 sec) mysql> grant all on *.* to 'sveta'@'Thinkie'; Query OK, 0 rows affected (0,00 sec) mysql> grant all on db1.* to 'sveta'@'192.168.0.4'; Query OK, 0 rows affected (0,00 sec) Then I changed the / etc / hosts file and specified the address 192.168.0.4 for the name Thinkie:
127.0.0.1 localhost # 127.0.1.1 Thinkie 192.168.0.4 Thinkie Now if I connect as sveta, and Thinkie, and 192.168.0.4 will be converted to the same host:
sveta@Thinkie:$ mysql -hThinkie -usveta ... mysql> select user(), current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | sveta@Thinkie | sveta@thinkie | +---------------+----------------+ 1 row in set (0,00 sec) mysql> show grants; +--------------------------------------------------+ | Grants for sveta@thinkie | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' | +--------------------------------------------------+ 1 row in set (0,00 sec) mysql> q Bye sveta@Thinkie:$ mysql -h192.168.0.4 -usveta ... mysql> show grants; +--------------------------------------------------+ | Grants for sveta@thinkie | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' | +--------------------------------------------------+ 1 row in set (0,00 sec) mysql> select user(), current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | sveta@Thinkie | sveta@thinkie | +---------------+----------------+ 1 row in set (0,00 sec) mysql> q Bye After that, I changed the / etc / hosts file and again linked Thinkie to 127.0.0.1 (localhost):
127.0.0.1 localhost 127.0.1.1 Thinkie # 192.168.0.4 Thinkie But the host 192.168.0.4 is still being converted to a Thinkie:
sveta@Thinkie:$ mysql -h192.168.0.4 -usveta ... mysql> select user(), current_user(); +---------------+----------------+ | user() | current_user() | +---------------+----------------+ | sveta@Thinkie | sveta@thinkie | +---------------+----------------+ 1 row in set (0,00 sec) mysql> show grants; +--------------------------------------------------+ | Grants for sveta@thinkie | +--------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'sveta'@'thinkie' | +--------------------------------------------------+ 1 row in set (0,00 sec) mysql> q Bye The reason for this is the outdated host cache, which is clearly seen in the Performance Schema:
sveta@Thinkie:$ mysql -uroot ... mysql> select * from performance_schema.host_cacheG *************************** 1. row *************************** IP: 192.168.0.4 HOST: Thinkie HOST_VALIDATED: YES SUM_CONNECT_ERRORS: 0 COUNT_HOST_BLOCKED_ERRORS: 0 COUNT_NAMEINFO_TRANSIENT_ERRORS: 0 COUNT_NAMEINFO_PERMANENT_ERRORS: 0 COUNT_FORMAT_ERRORS: 0 COUNT_ADDRINFO_TRANSIENT_ERRORS: 0 COUNT_ADDRINFO_PERMANENT_ERRORS: 0 COUNT_FCRDNS_ERRORS: 0 COUNT_HOST_ACL_ERRORS: 0 COUNT_NO_AUTH_PLUGIN_ERRORS: 0 COUNT_AUTH_PLUGIN_ERRORS: 0 COUNT_HANDSHAKE_ERRORS: 0 COUNT_PROXY_USER_ERRORS: 0 COUNT_PROXY_USER_ACL_ERRORS: 0 COUNT_AUTHENTICATION_ERRORS: 0 COUNT_SSL_ERRORS: 0 COUNT_MAX_USER_CONNECTIONS_ERRORS: 0 COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS: 0 COUNT_DEFAULT_DATABASE_ERRORS: 0 COUNT_INIT_CONNECT_ERRORS: 0 COUNT_LOCAL_ERRORS: 0 COUNT_UNKNOWN_ERRORS: 0 FIRST_SEEN: 2017-03-02 23:19:32 LAST_SEEN: 2017-03-02 23:20:31 FIRST_ERROR_SEEN: NULL LAST_ERROR_SEEN: NULL 1 row in set (0,00 sec) mysql> truncate performance_schema.host_cache; Query OK, 0 rows affected (0,00 sec) mysql> q Bye After clearing the host_cache table, the numeric host is converted as I expect:
sveta@Thinkie:$ mysql -h192.168.0.4 -usveta ... mysql> show grants; +----------------------------------------------------------+ | Grants for sveta@192.168.0.4 | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO 'sveta'@'192.168.0.4' | | GRANT ALL PRIVILEGES ON `db1`.* TO 'sveta'@'192.168.0.4' | +----------------------------------------------------------+ 2 rows in set (0,00 sec) mysql> select user(), current_user(); +-------------------+-------------------+ | user() | current_user() | +-------------------+-------------------+ | sveta@192.168.0.4 | sveta@192.168.0.4 | +-------------------+-------------------+ 1 row in set (0,00 sec) mysql> q Bye What rights are required for non-root and non-super users to use mysqldump to reset the database and then restore it on another server?
As a rule, you should have SELECT rights for all objects that you intend to reset. If you drop views, you must also have SHOW VIEW permissions to run SHOW CREATE TABLE. If you want to reset stored procedures / events, you also need access to them. If you use the --lock-tables or --lock-all-tables option, you must have LOCK rights.
If the max_connection value is reached in MySQL, can root @ localhost be logged in with ALL rights or a user with Super rights?
ALL includes SUPER, so that a user with ALL rights can log in. But keep in mind that such a connection can be only one, so do not grant SUPER or ALL rights to the application user.
Is it possible to remove a privilege at a lower level? In other words, to allow to select and delete at the database level, but to prohibit the deletion for a specific table? Or privileges can only add?
No, MySQL will reject such a query:
mysql> show grants for sveta@'192.168.0.4'; +----------------------------------------------------------+ | Grants for sveta@192.168.0.4 | +----------------------------------------------------------+ | GRANT USAGE ON *.* TO 'sveta'@'192.168.0.4' | | GRANT ALL PRIVILEGES ON `db1`.* TO 'sveta'@'192.168.0.4' | +----------------------------------------------------------+ 2 rows in set (0,00 sec) mysql> revoke update on db1.t1 from sveta@'192.168.0.4'; ERROR 1147 (42000): There is no such grant defined for user 'sveta' on host '192.168.0.4' on table 't1' How can you create user roles ... as a group of grants for a specific role?
You have several options:
- Use MariaDB 10.0.5 or newer. You can read about supporting MariaDB roles here .
- Use MySQL 8.0. Read about roles in MySQL 8.0 here .
- Using MySQL 5.7: simulate the roles as I showed on slide 19 (pp. 53-60).
- Using MySQL 5.5 and 5.6: use the same method as on the slides, but use a custom authentication plugin that supports proxy users.
- Always: create a template with rights, assign rights for each user manually.
How would you migrate role modeling with proxies to actual roles in MySQL 8.x?
I would delete the proxy user and create a role with the same rights, and then assign this new role to the proxy user instead of PROXY.
Is there a plugin for integrating Active Directory and MySQL to use Active Directory groups?
There is a commercial authentication plugin Windows Authentication Plugin , available in versions 5.5 and newer. You can also use the Percona PAM authentication plugin, an open source authentication plugin, and connect it to Active Directory in the same way as for LDAP. There is an article that describes how to do this, but I myself have never used this method.
Can I use centralized authentication in MySQL?
Yes, using the PAM plugin. There are instructions for LDAP and Active Directory. You can use similar methods to set up other types of authentication, such as Kerberos.
Friends, if working with MySQL is a daily task for you, be sure to come to us at PG Day'17 Russia . Sveta Smirnova , Petr Zaitsev and other Percona specialists prepare for you fascinating reports and master classes on the structure and functioning of MySQL in the section on open source databases.
Source: https://habr.com/ru/post/328290/
All Articles