The MySQL Security Database - dummies

By Steve Suehring, Janet Valade

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 privileges.

Privileges are stored in columns. The format of each column name is privilege_priv, in which privilege is a specific account privilege. For instance, the column containing ALTER privileges is named alter_priv. The value in each privilege column is Y or N, meaning yes or no.

So, for instance, in the user table, 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 statement. If alter_priv contains N, the account doesn’t have privilege to execute an ALTER statement.

The mysql database contains the following tables that store privileges:

  • user table: This table stores privileges that apply to all the databases and tables. It contains a row for each valid account that includes the columns user name, hostname, and password. The MySQL server rejects a connection for an account that doesn’t exist in this table.

  • db table: This table stores privileges that apply to a particular database. It contains a row for the database, which gives privileges to an account name and a hostname. The account must exist in the user table for the privileges to be granted. Privileges that are given in the user table overrule privileges 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 privileges 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 privileges that apply to specific tables.

  • columns_priv table: This table stores privileges 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 privileges. 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, you probably don’t have an account with the necessary privileges.