Difference Between information_schema.user_privileges and mysql.user

information-schemaMySQL

I have the notion that privileges set in mysql.user should reflect in information_schema.user_privileges but when I try to alter some privileges in mysql.user and queried information_schema.user_privileges they did not match? Am i understanding it wrong?

Best Answer

The table mysql.user is a MyISAM table that contains maps global database privileges. These privileges are mapped into INFORMATION_SCHEMA.USER_PRIVILEGES when MySQL is started.

WARNING !!! Be careful running UPDATE commands against mysql.user. You could easily forget to run FLUSH PRIVILEGES;.

According to the MySQL Documentation on FLUSH PRIVILEGES

Reloads the privileges from the grant tables in the mysql database.

The server caches information in memory as a result of GRANT, CREATE USER, CREATE SERVER, and INSTALL PLUGIN statements. This memory is not released by the corresponding REVOKE, DROP USER, DROP SERVER, and UNINSTALL PLUGIN statements, so for a server that executes many instances of the statements that cause caching, there will be an increase in memory use. This cached memory can be freed with FLUSH PRIVILEGES.

When you execute GRANT and REVOKE commands, the following authentication tables are changed

  • mysql.user
  • mysql.db
  • mysql.tables_priv
  • mysql.columns_priv

FLUSH TABLES; is executed afterwards.

You really have to know the table layouts to understand how to change privileges manually.

For example, I created a user called pam@localhost with the following privileges

mysql> show grants for pam@localhost;
+------------------------------------------------------------------------------------------------------------+
| Grants for pam@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'pam'@'localhost' IDENTIFIED BY PASSWORD '*F925CA006C127B610C43AB06E16F92EF8712F90B' |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb1` TO 'pam'@'localhost'                                        |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb3` TO 'pam'@'localhost'                                        |
| GRANT SELECT, INSERT ON `edwards`.`pamela_tb2` TO 'pam'@'localhost'                                        |
+------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql>

What does mysql.user look like for pam@localhost ?

mysql> select * from mysql.user where user='pam' and host='localhost'\G
*************************** 1. row ***************************
                  Host: localhost
                  User: pam
              Password: *F925CA006C127B610C43AB06E16F92EF8712F90B
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type:
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string:
      password_expired: N
1 row in set (0.00 sec)

mysql>

What ??? Where are the SELECT privileges ???

They are located in mysql.tables_priv

mysql> select * from mysql.tables_priv where user='pam' and host='localhost'\G
*************************** 1. row ***************************
       Host: localhost
         Db: edwards
       User: pam
 Table_name: pamela_tb1
    Grantor: redwards@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert
Column_priv:
*************************** 2. row ***************************
       Host: localhost
         Db: edwards
       User: pam
 Table_name: pamela_tb2
    Grantor: redwards@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert
Column_priv:
*************************** 3. row ***************************
       Host: localhost
         Db: edwards
       User: pam
 Table_name: pamela_tb3
    Grantor: redwards@localhost
  Timestamp: 0000-00-00 00:00:00
 Table_priv: Select,Insert
Column_priv:
3 rows in set (0.00 sec)

mysql>

This is how mysqld expects user privileges to be organized and distributed in the grant tables.

If you update all the privileges in mysql.user for pam@localhost to 'Y' and run FLUSH PRIVILEGES;, you will elevate pam@localhost to a global superuser.

This is why you are better off doing GRANT and REVOKE and trusting mysqld to get it right.

Related Question