The MySQL security database

When MySQL is installed, it automatically creates a database called mysql. All the information used to protect your data is stored in this database, including account names, hostnames, passwords, and permissions.
Permissions are stored in columns. The format of each column name is permission_priv. For instance, the column containing ALTER permissions is named alter_ priv. The value in each permission column is Y or N, meaning yes or no. So, for instance, in the user table (described in the following list), there would be a row for an account and a column for alter_priv. If the account field for alter_priv contains Y, the account can be used to execute an ALTER query. If alter_ priv contains N, the account doesn’t have permission to execute an ALTER query. The mysql database contains the following tables that store permissions:

• user table: This table stores permissions that apply to all the databases and tables. It contains a row for each valid account that includes the column’s user name, hostname, and password. The MySQL server will reject a connection for an account that does not exist in this table.
• db table: This table stores permissions that apply to a particular database. It contains a row for the database, which gives permissions to an account name and a hostname. The account must exist in the user table for the permissions to be granted. Permissions that are given in the user table overrule permissions in this table. For instance, if the user table has a row for the account designer that gives INSERT privileges, designer can insert into all the databases. If a row in the db table shows N for INSERT for the designer account in the PetCatalog database, the user table overrules it, and designer can insert in the PetCatalog database.
• host table: This table controls access to a database depending on the host. The host table works with the db table. If a row in the db table has an empty field for the host, MySQL checks the host table to see whether the db has a row there. In this way, you can allow access to a db from some hosts but not from others. For instance, suppose you have two databases: db1 and db2. The db1 database has sensitive information, so you want only certain people to see it. The db2 database has information that you want everyone to see. If you have a row in the db table for db1 with a blank host field, you can have two rows for db1 in the host table. One row can give all permissions to users connecting from a specific host, whereas another row can deny privileges to users connecting from any other host.
• tables_priv table: This table stores permissions that apply to specific tables.
• columns_priv table: This table stores permissions that apply to specific columns.

You can see and change the tables in mysql directly if you’re using an account that has the necessary permissions. You can use SQL queries such as SELECT, INSERT, and UPDATE. If you’re accessing MySQL through your employer, a client, or a Web hosting company, it is unlikely that you will be given an account that has the necessary permissions.