2

I have discovered a difference between show grants for user@host and the mysql.db table. Why is this?

How can I resolve this? We are worried about security.

I ran a show grants against users in mysql.users. In mysql.users the user is user@xxx.xxx.xxx.xx and when I run show grants it says GRANT USAGE TO user@xxx.xxx.xxx.xx

This worried me as this user also seems to be able to access a database.

When I looked in mysql.db and information_schema.schema_privileges I can see the same user as user with a host of xxx.xxx.% with full access to that database.

What gives?

Edit - Deeper Explanation:

Here is what I have: 

show grants for 'myuser'@'xxx.xxx.xxx.xxx';

It returns:

GRANT USAGE ON *.* TO 'myuser'@'xxx.xxx.xxx.xxx' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxxxxxxx'

If I run:

select host,db,user from mysql.db where user='myuser';

I get: 

+----------+------------+------------+
| host     | db         | user       |
+----------+------------+------------+
|xxx.xxx.% | somedb     | myuser     |
+----------+------------+------------+
Alienz
  • 118
  • 2
  • 9

1 Answers1

2

Don't worry.

The mysql.user table mitigates specific global privileges on all databases.

If a user is restricted to a specific database, you will only see database specific grants in the mysql.db table. A user restricted to a database does not need SUPER, PROCESS, SHUTDOWN, and privileges like these.

If you create a user withglobal privileges (thus landing the user in mysql.user), you cannot revoke global privileges only to demote the user to a specfic database. If you try to do so with REVOKE commands, you will see the user in mysql.user with the original Y values on all columns and the same user in mysql.db with Y values on database specific privileges.

You must completely drop the user and create the user fresh with the lower privileges.

Here is an example based on the comments

mysql> show grants for advdb@'%';
+-----------------------------------------------------------------------------+
| Grants for advdb@%                                                          |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'advdb'@'%' IDENTIFIED BY PASSWORD '................' |
| GRANT ALL PRIVILEGES ON `advertpro`.* TO 'advdb'@'%'                        |
+-----------------------------------------------------------------------------+

The GRANT USAGE simply allows you to know what MD5 password is involved with authentication (if the password is not blank). Note that advdb can login from any IP address and only access the advertpro database.

The user does not need PROCESS privilege unless you want the user to see all processes in the processlist, even the one that belong to others.

The user does not need SUPER privilege unless you want the user to write in a read-only mysql instance, start and stop replication, zap binary logs, and things like these.

The user does not need SHUTDOWN privilege unless you want the user to shutdown mysql from the OS command line user the mysqladmin client program.

To copy the priv you are looking for,

  • SHOW GRANTS FOR 'user'@'xxx.xxx.xxx.%';
  • Copy the output to text file
  • Edit the host to xxx.xxx.xxx.xx
  • Add a semicolon
  • Paste that to the other server

OK let's hack that:

UPDATE mysql.db
    SET host='xxx.xxx.xxx.xxx'
    WHERE user='myuser'
    AND host='xxx.xxx.%'
    AND db='somedb';
FLUSH PRIVILEGES;

This should it !!!

To dump the MySQL Grants in SQL, you can do pt-show-grants It is much better to port the mysql grants using pt-show-grants.

Here is my personal emulation of what pt-show-grants does

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132
  • Yes but my question is, the "show grants for" ONLY shows "grant usage on" and not that the user has All privileges on database.* – Alienz Feb 08 '12 at 22:27
  • That is correct for a user restricted to a specific database. – RolandoMySQLDBA Feb 08 '12 at 22:29
  • You should see two lines: one from GRANT USAGE and one for the DB privs – RolandoMySQLDBA Feb 08 '12 at 22:30
  • The GRANT USAGE simply allows you to know what MD5 password is involved with authentication (if the password is not blank). – RolandoMySQLDBA Feb 08 '12 at 22:31
  • Thanks. I realize this. My point is that it doesn't have the second line. And the host in the usage line differs from the one listed in the mysql.db and information schema. There is no other user with this name. – Alienz Feb 08 '12 at 22:41
  • OK, just run `SHOW GRANTS FOR 'youruser'@'yourhost';` on the server that has the correct privs. Copy and paste that to the other server (add a semicolon). That should do it. – RolandoMySQLDBA Feb 08 '12 at 22:43
  • Problem here is that mysql considers xxx.xxx.xxx.xx and xxx.xxx.xxx.% as separate and distinct. – RolandoMySQLDBA Feb 08 '12 at 22:45
  • Please paste that in the quesiton body – RolandoMySQLDBA Feb 08 '12 at 22:49
  • Thanks for the solution. I know that would fix it. My question is why is this happening? Is this a known issue in mysql. This is a production server with thousands of users and dozens of databases. I am VERY scared to flush privileges at this point. – Alienz Feb 08 '12 at 23:01
  • You should find out if someone tried to revoke the privileges by hacking it or tried the REVOKE command with no prior success. Go to your command line, type cd and hit enter, and run `cat .mysql_history` and find out if someone did. – RolandoMySQLDBA Feb 08 '12 at 23:04
  • I did this before posting here as well. No luck. It may have happened before my time and the logs have rolled over since then. I think this may be the issue. Thanks for your help! – Alienz Feb 08 '12 at 23:07
  • I would but unfortunately I can't share anything due to hipaa and other laws applicable. – Alienz Feb 08 '12 at 23:12