MySQL uses a series of grant tables to store users and their privileges. The tables are ordinary MyISAM tables* that live in the mysql database. Storing the security information in grant tables makes a lot of sense, but it also means that if the server isn’t configured correctly, any user can make security changes by altering the data in those tables!
MySQL’s grant tables are the heart of its security system. MySQL now gives you nearly full control of security with the GRANT, REVOKE, and DROP USER privileges. However, manipulating the grant tables used to be the only means of performing certain tasks. For instance, in old MySQL versions, the only way to remove a user completely was to DELETE that user from the user table, then FLUSH PRIVILEGES.
We don’t recommend changing the grant tables directly, but you should still understand how they work so you can debug unexpected behavior. We encourage you to examine the grant table structures with DESCRIBE or SHOW CREATE TABLE, especially after you use GRANT and REVOKE to change privileges. You’ll learn more from doing that than from reading about them. Here are the grant tables in the order in which MySQL consults them when checking whether a user is authorized to do a specific operation:
user
Each row contains a user account (the username, hostname, and encrypted password) and the user’s global privileges. MySQL 5.0 adds optional per-user limits, such as the number of connections the user is allowed to have.
db
Each row contains database-level privileges for a specific user.
host
Each row contains privileges to one database for a user connecting from a given host. The entries are “merged” with entries in db when checking database-level access. Though we list it as a grant table, the GRANT and REVOKE commands never modify the host table. You can only add and remove entries manually. We recommend that you do not use this table to prevent maintenance problems and confusing behavior later.
tables_priv
Each row contains table-level privileges for a specific user and table. It also contains view privileges.
columns_priv
Each row contains column-level privileges for a specific user and column.
procs_priv (new in MySQL 5.0)
Each row contains privileges for a specific user and stored routine (stored procedure or function).
Source of Information : OReIlly High Performance MySQL Second Edition
MySQL’s grant tables are the heart of its security system. MySQL now gives you nearly full control of security with the GRANT, REVOKE, and DROP USER privileges. However, manipulating the grant tables used to be the only means of performing certain tasks. For instance, in old MySQL versions, the only way to remove a user completely was to DELETE that user from the user table, then FLUSH PRIVILEGES.
We don’t recommend changing the grant tables directly, but you should still understand how they work so you can debug unexpected behavior. We encourage you to examine the grant table structures with DESCRIBE or SHOW CREATE TABLE, especially after you use GRANT and REVOKE to change privileges. You’ll learn more from doing that than from reading about them. Here are the grant tables in the order in which MySQL consults them when checking whether a user is authorized to do a specific operation:
user
Each row contains a user account (the username, hostname, and encrypted password) and the user’s global privileges. MySQL 5.0 adds optional per-user limits, such as the number of connections the user is allowed to have.
db
Each row contains database-level privileges for a specific user.
host
Each row contains privileges to one database for a user connecting from a given host. The entries are “merged” with entries in db when checking database-level access. Though we list it as a grant table, the GRANT and REVOKE commands never modify the host table. You can only add and remove entries manually. We recommend that you do not use this table to prevent maintenance problems and confusing behavior later.
tables_priv
Each row contains table-level privileges for a specific user and table. It also contains view privileges.
columns_priv
Each row contains column-level privileges for a specific user and column.
procs_priv (new in MySQL 5.0)
Each row contains privileges for a specific user and stored routine (stored procedure or function).
Source of Information : OReIlly High Performance MySQL Second Edition
|
|
0 comments
Post a Comment