The GRANT function is used both to create new users, and to assign privileges to users. Its syntax is:

mysql>GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
      ON {tbl_name | * | *.* | db_name.*}
      TO user_name [IDENTIFIED BY 'password']
      [, user_name [IDENTIFIED BY 'password'] ...]
      [WITH GRANT OPTION]

An understanding of how GRANT works is best gained through examples. In the first example, GRANT is used to add a new user to the database. This user will be used to access the widgets database:

mysql>GRANT usage ON *.* TO widgetAdmin@localhost
    ->IDENTIFIED BY 'ilovewidgets';

This will create a new user named widgetAdmin, capable of connecting to the MySQL database server via the host localhost using the password ilovewidgets. Keep in mind that this only grants connection privileges. It will not allow the user to do anything with the MySQL server! Go ahead and switch to the mysql database and execute the following query:

mysql>SELECT * FROM user;

Notice that the row containing the widgetAdmin user has N values for all of the privileges. This is good, since the user table contains a user’s global privilege settings. To clarify this, if a Y value is set for any user’s privilege in the user table, that user can apply that privilege to any MySQL database. Therefore, it is almost always best to set all privileges to N within this table.

So how then are user privileges assigned for a particular database? This is easily done just like the usage privilege was set in the previous example. For example, assume that the administrator wanted to grant user widgetAdmin with SELECT, INSERT, UPDATE and DELETE privileges for the widget database. This is accomplished using the following GRANT command:

mysql>GRANT SELECT, INSERT, UPDATE, DELETE
    ->ON widgets.* TO widgetAdmin@localhost;

Upon execution, the user widgetAdmin can immediately begin using these privileges.

The privileges introduced thus far are not the only ones available to the administrator. Table 1-1 provides a listing of all available privileges.

Table 1-1: Privileges available for use within GRANT and REVOKE commands

To view the new privilege table updates, execute the following query:

mysql>SELECT * FROM db;

Notice that a row has been added to the db table for user widgetAdmin, with Y values assigned to the SELECT, INSERT, UPDATE and DELETE fields.

Incidentally, it is also possible to bypass the usage query, instead both creating the new user and assigning user privileges simply by executing a variation of the previous query:

mysql>GRANT SELECT, INSERT, UPDATE, DELETE
    ->ON widgets.* TO widgetAdmin@localhost
    ->IDENTIFIED BY 'ilovewidgets';

Assuming that the user widgetAdmin did not yet exist when this query is executed, both the user and db tables will be updated with the necessary rows.