| The underlying data that is changed by the GRANT and REVOKE statements is stored in the database called mysql . Rather than using GRANT and REVOKE , you can modify the tables in this database directly if you know what you are doing. You can also read the data in them to help you resolve any privilege problems that may crop up. If you modify these tables directly, you will need to execute the statement flush privileges; before the changes will take effect. There are six tables in the mysql database: -
user -
db -
host -
tables_priv -
columns_priv -
func Only the first five have to do with user privileges. (The func table stores user-defined function information that is beyond the scope of this book.) The first three tables ” user , db , and host ”are used to determine whether you are allowed to connect to the database. All five of the privilege tables are used to determine whether you have the right to execute any given command. Understanding the user Table The user table contains information about a user's global privilege set. The user table contains the following columns : -
Scope columns These are used to determine when a row is relevant. These are the scope columns: Host : Where the user is connecting from User : The username Password : The user's password, as encoded by the PASSWORD() function -
Privilege columns Each one corresponds to one of the global privileges. They can have the value Y (if the user has the global privilege) or N (if the user does not). These are the privilege columns: Select_priv Insert_priv Update_priv Delete_priv Index_priv Alter_priv Create_priv Drop_priv Grant_priv References_priv Reload_priv Shutdown_priv Process_priv File_priv Show_db_priv Super_priv Create_tmp_table_priv Lock_tables_priv Execute_priv Repl_slave_priv Repl_client_priv -
Secure connection columns These represent the information from the REQUIRE clause of the grant statement. These are the secure connection columns: ssl_type ssl_cypher x509_issuer x509_subject -
Resource limitation columns These represent any limitations on user resource use you may have specified at the end of a GRANT statement. These are the resource limitation columns: max_questions max_updates max_connections Understanding the db Table The database table stores a user's privileges for particular databases. It contains the following columns: -
Scope columns MySQL uses these to determine when a row of privileges is relevant. If you have different rules for different hosts , leave the host field blank and then create a corresponding set of rows in the host table to give more information. These are the scope columns: Host Db User -
Privilege columns These specify whether the combination of Host , Db , and User have each of the listed privileges. Again these columns can contain the values Y or N . These are the privilege columns: Select_priv Insert_priv Update_priv Delete_priv Index_priv Alter_priv Create_priv Drop_priv Grant_priv Create_tmp_table_priv Lock_tables_priv Understanding the host Table MySQL consults the host table when it finds a blank host entry in the db table. You will not get this effect from a GRANT statement, but you can set it up manually. This table contains the following columns: -
Scope columns MySQL uses these to determine when a row of privileges is relevant. Each row here gives information for a single database accessed from a single host. These are the scope columns: Host Db -
Privilege columns These specify whether the combination of Host and Db have each of the listed privileges. Again, these columns can contain the values Y or N . These are the privilege columns: Select_priv Insert_priv Update_priv Delete_priv Index_priv Alter_priv Create_priv Drop_priv Grant_priv Create_tmp_table_priv Lock_tables_priv Understanding the tables_priv Table The tables_priv table expresses user privileges that relate to individual tables. It contains the following columns: -
Scope columns These work as they do in the previous three tables. Here we also have the Table_name privilege, which lists the specific table that a grant applies to. These are the scope columns: Host Db User Table_name -
Grant columns These store information about who granted this privilege and when. These are the grant columns: Grantor Timestamp -
The Table_priv column This is a set that determines what privileges the Host/Db/User has on the table listed in Table_name . It can contain the following values: Select , Insert , Update , Delete , Create , Drop , Grant , References , Index , and Alter . -
The Column_priv column This is a set that tells us what privileges the user has over all the columns in this table. It can contain the following values: Select , Insert , Update , and References . If a privilege is missing here, MySQL can look in the columns_priv table for more detailed information about what is and what is not allowed with this table's columns. Understanding the columns_priv Table The columns_priv table expresses user privileges relating to individual columns. It contains the following columns: -
Scope columns These determine when a row in this table is relevant. These are the scope columns: Host Db User Table_name Column_name -
The Column_priv column This is a set that determines which privileges have been granted to the combination outlined by the scope column. It can contain the following values: Select , Insert , Update , and References . -
The Timestamp column This column tells us when this privilege was granted.  |
