An introduction to MySQL permissions

The order of precedence of the tables is as follows:
USER
|
DB/HOST
|
TABLES_PRIV
|
COLUMNS_PRIV

MySQL checks the user table first, if permission is not granted there, it will check the db and host tables, and, if further confirmation is required, the tables_priv and even the columns_priv tables. Be aware that excessive use of all these tables comes at a performance cost - if before every operation MySQL has to check permissions at a column level, it will be that much slower. Use what you need, no more, no less.

How to GRANT permissions

Hopefully the process has been easy to follow, but the burning question must be, how does anyone set these permissions! There are two ways - directly manipulating the tables, with INSERT, UPDATE and DELETE statements (which require MySQL to be reloaded, or the privileges flushed, for example with the FLUSH PRIVILEGES statement. Note also that if you add a record directly to the password field in the user table, you must use the PASSWORD() function. The alternative, more convenient in my opinion, is using a GRANT statement. The syntax of a GRANT statement is:
GRANT privilege ON table_or_database_name TO user@hostname IDENTIFIED BY 'password'.

The privileges are:

Database and Table names in a GRANT statement

So, some examples of GRANT in action:

mysql> GRANT SELECT ON *.* TO rushdi@localhost IDENTIFIED BY 'supa_password'
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'rushdi';
+-----------+--------+------------------+-------------+-------------+
| host      | user   | password         | select_priv | insert_priv |
+-----------+--------+------------------+-------------+-------------+
| localhost | rushdi | 0b3bcd316f1c8020 | Y           | N           |
+-----------+--------+------------------+-------------+-------------+

Note that the password has been automatically encrypted. The record also only appears in the user table, not the db table, as permission was granted to all databases. Another example:

mysql> GRANT INSERT ON mysql.* TO suretha@localhost IDENTIFIED BY 'supa_password2';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'Suretha';
+-----------+---------+------------------+-------------+-------------+
| host      | user    | password         | select_priv | insert_priv |
+-----------+---------+------------------+-------------+-------------+
| localhost | suretha | 30f59c271b923c47 | N           | N           |
+-----------+---------+------------------+-------------+-------------+

mysql> SELECT host,db,user,select_priv,insert_priv FROM db WHERE user='Suretha';
+-----------+-------+---------+-------------+-------------+
| host      | db    | user    | select_priv | insert_priv |
+-----------+-------+---------+-------------+-------------+
| localhost | mysql | suretha | N           | Y           |
+-----------+-------+---------+-------------+-------------+

Here records are added to both the user and db tables. To revoke permission, we use the REVOKE statement.

mysql> REVOKE SELECT ON *.* FROM rushdi@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'rushdi';
+-----------+--------+------------------+-------------+-------------+
| host      | user   | password         | select_priv | insert_priv |
+-----------+--------+------------------+-------------+-------------+
| localhost | rushdi | 0b3bcd316f1c8020 | N           | N           |
+-----------+--------+------------------+-------------+-------------+

Note that the record still appears in the table, so he can connect, but select_priv has been disabled.

mysql> REVOKE INSERT ON mysql.* FROM suretha@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT host,db,user,select_priv,insert_priv FROM db WHERE user='Suretha';
Empty set (0.00 sec)

mysql> SELECT host,user,password,select_priv,insert_priv FROM user WHERE user = 'Suretha';
+-----------+---------+------------------+-------------+-------------+
| host      | user    | password         | select_priv | insert_priv |
+-----------+---------+------------------+-------------+-------------+
| localhost | suretha | 30f59c271b923c47 | N           | N           |
+-----------+---------+------------------+-------------+-------------+

The record has been deleted from the db table, but still appears, with no permissions, in the user table.

Hopefully you're starting to find MySQL permissions flexible and easy to use. Although there is much more to explore, do not overuse the available options. You will make management more complex, and affect performance. Good luck!