Mar
10
2017
--

Troubleshooting MySQL access privileges issues: Q & A

MySQL access privileges

MySQL access privilegesIn this blog, I will provide answers to the Q & A for the Troubleshooting MySQL Access Privileges Issues webinar.

First, I want to thank everybody for attending the February 23 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: Should the root@localhost user be given ALL privileges or Super privileges? Does All include Super privileges also?

A: Yes, you should have a user with all privileges. Better if this user has access from localhost only.

ALL

  includes

SUPER

.

Q: We have users who connect via a laptop that get dynamic IP addresses, so granting access with a server name is an easier way to manage these users. Can I grant access to a MySQL database with a hostname as opposed to an ipaddress? For example “myname@mymachine.mydomain.com” as opposed to “myname@10.10.10.10”?  Is the host cache/performance_schema required for this?

A: Yes, you can.

But it looks like I was not clear about host cache. Host cache is an internal structure that is always available and contains answers from DNS server. You cannot enable or disable it. Until version 5.6, you also could not control it. For example, if the cache got corrupted the only thing you could do is to restart the server. Version 5.6 the table

HOST_CACHE

 was introduced to Performance Schema. With this table you can examine the content of the host cache and truncate it if needed.

Q: If there are multiple entries in the user table that match the connecting user (e.g., with wildcards, hostname, and IP), what rules does MySQL use to select which is used for authentication?  Does it try multiple ones until it gets a password match?

A: Not, mysqld does not try to hack your passwords. Instead it sorts the user table by name and host in descending order as I showed on slide #37 (page 110). Then it takes the first matching row. So if you created users

foo@somehost

,

foo@some%

 and

foo@1.2.3.4

, and you connect as foo from

somehost

, mysqld first checks the user name and then chooses the first matching row

foo@somehost

. If you instead connect as

foo

 from

someotherhost

, mysqld chooses 

foo@some%

. An IP-based host is chosen if either mysqld started with option

skip-networking

 or if

1.2.3.4

  points to a host whose name does not start with “some”.

Mixing IP-based and name-based hosts is dangerous in situations when the same host can be resolved as

somehost

 or

1.2.3.4

. In this case, if something goes wrong with the host cache or DNS server, the wrong entry from the user table can be chosen. For example, if you initially had three hosts:

uniquehost

 (which resolves as

1.2.3.4

),

somehost

 (which resolves as

4.3.2.1

) and

someothershost

 (which resolves as

4.3.2.2

). Now you decided to re-locate

uniquehost

 to a machine with IP

1.2.3.5

 and use IP

1.2.3.4

 for the host with name

someyetanotherhost

. In this case, the clients from the machine with IP

1.2.3.4

 will be treated as

foo@some%

, which isn’t what you want.

To demonstrate this issue, I created two users and granted two different privileges to them:

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)

Now I modified my

/etc/hosts

  file and pointed address

192.168.0.4

  to name

Thinkie

:

127.0.0.1   localhost
# 127.0.1.1   Thinkie
192.168.0.4 Thinkie

Now, if I connect as sveta both Thinkie and 192.168.0.4 are resolved 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

Now I modified the 

/etc/hosts

  file and pointed

Thinkie

  back to

127.0.0.1

  (

localhost

):

127.0.0.1   localhost
127.0.1.1   Thinkie
# 192.168.0.4 Thinkie

But host

192.168.0.4

 still resolves to

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 a stalled host cache, which can be easily observable with 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 I truncated table

host_cache

 the numeric host resolves 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

Q: What privileges are required for a non-root or non-super user to be to use mysqldump to dump the database and then restore it on a different server?

A: Generally you should have

SELECT

  privilege on all objects you are going to dump. If you are dumping views, you also should have

SHOW VIEW

  privilege in order to run

SHOW CREATE TABLE

. If you want to dump stored routines/events, you need access to them as well. If you use option

--lock-tables

or

--lock-all-tables

, you should have the 

LOCK

  privilege.

Q: If the max_connection value is reached in MySQL, can root@localhost with ALL privilege still login, or with Super privilege user can login?

A:

ALL

 includes

SUPER

, so a user with

ALL

  privilege can login. Just note there can be only one such connection, thus do not grant

SUPER

 

or ALL

 privilege to the application user.

Q: Is it possible to remove a priv at a lower level? In other words, grant select and delete at the database level, but remove delete for a specific table?  Or can privs only be added to?

A: Not, MySQL will reject such a statement:

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'

Q: How can we have DB user roles… like a group of grants for a particular role?

A: You have several options.

  1. Use MariaDB 10.0.5 or newer. You can read about roles support in MariaDB here
  2. Use MySQL 8.0. You can read about roles in MySQL 8.0 here
  3. With MySQL 5.7: imitate roles as I showed on slide 19 (pages 53 – 60)
  4. With MySQL 5.5 and 5.6: use the same method as shown on slides, but use the custom authentication plugin that supports proxy users.
  5. Always: create a template with privileges, assign privileges to each user manually.

Q: How would you migrate role simulation with proxy users to actual roles in MySQL 8.x?

A: I would drop the proxied user and create a role with the same privileges instead, then grant the proxy user the newly created role instead of

PROXY

.

Q: Is there a plugin to integrate Active Directory and MySQL in order to use Active Directory groups?

A: There is commercial Windows Authentication Plugin, available in versions 5.5 and newer. You can also use the open source Percona PAM authentication plugin and connect it to Active Directory the same way as can be done for LDAP. There is a blog post describing how to do so, but I’ve never used this method myself.

Q: Can we use central auth with MySQL?

A: Yes, with the help of the PAM Plugin. There are tutorials for LDAP and Active Directory. You may use similar methods to setup other kinds of authentications, such as Kerberos.

Jan
11
2017
--

Reinstall MySQL and Preserve All MySQL Grants and Users

MySQL Grants and Users

MySQL Grants and UsersIn this blog post, we’ll look at how to preserve all MySQL grants and users after reinstalling MySQL.

Every so often, I need to reinstall a MySQL version from scratch and preserve all the user accounts and their permissions (or move the same users and privileges to another server).

As of MySQL 5.7, MySQL does not make this easy! MySQL SHOW GRANTS only shows permissions for one user, and the method suggested on StackExchange – dumping tables containing grants information directly – is not robust (as Rick James mentions in the comments). It also doesn’t work between different MySQL versions.

This problem is easily solved, however, with the pt-show-grants tool from Percona Toolkit (which serves pretty much as a mysqldump for user privileges).  

All you need to do is:

  1. On the source, or to backup MySQL privileges, run:
pt-show-grants > grants.sql

  1. On the target, or to restore MySQL privileges, run:
mysql  < grants.sql

  1. If you would like to clean up the old privileges from MySQL before loading new ones, use:
pt-show-grants --drop  --ignore root@localhost | grep "^DROP USER " | mysql

This removes all the users (except the root user, which you will need to connect back and load new privileges).

With Percona Toolkit, preserving your grants and user privileges is easy!

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com