Search code examples
mysqlprivilegessql-grant

MySQL user and host precedence

I have two users, let's call them foo on two different databases:

Database_A:

mysql> select user,host from user where user = 'foo';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| foo | %         |
| foo | 10.%      |
| foo | localhost |
+-----------+-----------+
3 rows in set (0.01 sec)

Database_B:

mysql> select user,host from user where user = 'foo';
+-----------+-----------+
| user      | host      |
+-----------+-----------+
| foo | %         |
| foo | 10.%      |
| foo | localhost |
+-----------+-----------+
3 rows in set (0.00 sec)

Now the issue I am running into is trying to run a SQL script w/ the DROP command. When I connect to the database on Database_A, I get an error trying to connect:

mysql -A -hdatabase_1.foo.bar.domain.com -ufoo -pbar Database_A <dbtables.sql
Warning: Using a password on the command line interface can be insecure.
ERROR 1142 (42000) at line 22: DROP command denied to user 'foo'@'ip-10-128-0-143.ec2.internal' for table 'bar_table'

I can run this on the Database_B w/ no issues. So far, I have checked with the grants on both users and have logged into the MySQL shell w/ the same user and grants (foo@'10.%') but I can't run the SQL script on database_1. Here are what the grants look like for both Database_A and Database_B:

mysql> show grants for foo;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for foo@%                                                                                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD ON *.* TO 'foo'@'%' IDENTIFIED BY PASSWORD '<redacted>' REQUIRE SSL                                                                  |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `reference`.* TO 'foo'@'%' |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show grants for foo@'10.%';
+------------------------------------------------------------------------------------------------------------+
| Grants for foo@10.%                                                                                  |
+------------------------------------------------------------------------------------------------------------+
| GRANT FILE ON *.* TO 'foo'@'10.%' IDENTIFIED BY PASSWORD '<redacted>' |
+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Now I can't seem to figure out why one database server would work vs the other one (both are set up exactly the same w/ the user and in the my.cnf). Is there a way where I can log in specifically as foo@'%'? I am trying to run this from a remote EC2 instance (same VPC):

mysql -A -hdatabase_1.foo.bar.domain.com -ufoo -pbar Database_A <dbtables.sql
Solution

  • No, you can't force connection as a particular user profile. MySQL uses the first matching profile, according to its sort order.

    Read https://dev.mysql.com/doc/refman/8.0/en/connection-access.html:

    When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

    • Whenever the server reads the user table into memory, it sorts the rows.

    • When a client attempts to connect, the server looks through the rows in sorted order.

    • The server uses the first row that matches the client host name and user name.

    The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 198.51.100.13 and 198.51.100.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is nondeterministic.

    I would avoid giving different privileges to the same username, differing only by the hostname.

    Even though MySQL allows you to define different privileges depending on the client host, it's confusing to manage your authorizations this way. I've never seen a good reason to do that.

    If you need a distinct set of privileges, define a distinct username.