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?
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.
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.
Source - http://dev.mysql.com/doc/refman/5.5/en/information-schema.html
You should be taking at the proper commands for modifying accounts and flush PRIVILEGES
to reload the PRIVILEGES.
The I_S USER_PRIVILEGES table provides information about global privileges. This information comes from the mysql.user
grant table.
This is a nonstandard table. It takes its values from the mysql.user
table.