Database-Related Requests

• For database-related requests, the server first checks the user's global (superuser) privileges by looking in the user table row.
  • If the row allows the requested operation, access is granted.
  • If the global privileges in the user table are insufficient, the server determines the user's database-specific privileges by checking the db and host tables.
    • The server looks in the db table for a match on the Host, Db, and User columns.
    • The Host and User columns are matched to the connecting user's hostname and MySQL username.
    • The Db column is matched to the database that the user wants to access.
    • If there is no row for the Host and User, access is denied.
    • If there is a matching db table row and its Host column is not blank, that row defines the user's database-specific privileges.
    • If the matching db table row's Host column is blank, it signifies that the host table enumerates which hosts should be allowed access to the database.
      • In this case, a further lookup is done in the host table to find a match on the Host and Db columns.
    • If no host table row matches, access is denied.
    • If there is a match, the user's database-specific privileges are computed as the intersection (not the union!) of the privileges in the db and host table entries.
      • This is how to grant general privileges in the db table row and then selectively restrict them on a host-by-host basis using the host table entries.

• After determining the database-specific privileges granted by the db and host table entries, the server adds them to the global privileges granted by the user table.
  • If the result allows the requested operation, access is granted.
  • Otherwise, the server successively checks the user's table and column privileges in the tables_priv and columns_priv tables, adds those to the user's privileges, and allows or denies access based on the result.
  • For stored routine operations, the server uses the procs_priv table rather than tables_priv and columns_priv.

When do privilege changes take effect?

---

---

© 2007 John Michael Pierobon

Notes