The ALL PRIVILEGES Specifier
The ALL or ALL PRIVILEGES privilege specifier is shorthand for “all privileges available at a given privilege level”, with the exception of GRANT OPTION. For example, granting ALL at the global or table level would grant all global privileges or all table-level privileges:
mysql> GRANT ALL ON afs.fee TO 'robg'@'host.abc.com';
The robg user now has all global privileges on the fee table in the afs database when connecting from the host.abc.com server.
Using Wild Cards
Both the asterisk (*) and percentage symbol (%) can be used to broaden the GRANT command's scope. For instance, to apply privileges to all MySQL databases, use *.*:
mysql> GRANT ALL ON *.* TO 'robg'@'host.abc.com'; mysql> GRANT USAGE ON *.* TO 'robg'@'host.abc.com' WITH MAX_QUERIES_PER_HOUR 50;
MySQL stores global privileges in the mysql.user table.
Likewise, privileges can be limited to a particular database by applying the “*” wildcard character after the dot:
mysql> GRANT ALL ON mydb.* TO 'robg'@'host.abc.com'; mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON mydb.* TO 'anna_f'@'localhost';
MySQL stores database privileges in the mysql.db table.
You may specify the table name without the database prefix (<table name> rather than <database name>.<table name>) to apply the privileges to the default database:
mysql> GRANT ALL ON invoice TO 'robg'@'host.abc.com';
MySQL stores table privileges in the mysql.columns_priv table.
The INSERT, SELECT, and UPDATE privileges apply specifically to table columns. Multiple privileges can be granted at once by separating each with a comma. Each column(s) for which to apply the privilege is/are enclosed within parentheses:
mysql> GRANT SELECT (col1), INSERT (col1,col2) ON mydb.mytbl TO 'username'@'hostname';
MySQL stores column privileges in the mysql.columns_priv table.
The ALTER ROUTINE, CREATE ROUTINE, EXECUTE, and GRANT OPTION privileges apply to stored routines and functions. They can be granted at the global and database levels. With the exception of CREATE ROUTINE, these privileges can be applied to individual routines:
mysql> GRANT CREATE ROUTINE ON mydb.* TO 'someuser'@'somehost'; mysql> GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost';
MySQL stores routine-level privileges in the mysql.procs_priv table.
For a more detailed description of each privilege, see the MySQL docs.
Specifying Account Names and Passwords
As we've seen, MySQL supports specifying the user value in the form user_name@host_name. Separate quotation marks around the user name and host name are necessary to specify a user_name string that contains special characters (such as “-”), or a host_name string containing special characters or wildcard characters (such as “%”); for example, 'test-user'@'%.com'. Otherwise, you can omit the quotes.
You can specify wildcards in the host name, but not in the user name. For example, user_name@'%.example.com' applies to user_name for any host in the example.com domain, and user_name@'192.168.1.%' applies to user_name for any host in the 192.168.1 class C subnet.
The simple form user_name is a synonym for user_name@'%'.
To refer to an anonymous user, specify an account with an empty user name with the GRANT statement:
GRANT SELECT ON test.* TO ''@'localhost' ...
In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account. Don't be too generous with those privileges!
Today we saw how MySQL's sophisticated access control and privilege system allows you to create comprehensive access rules for handling client operations and prevent unauthorized accessing of database resources. We also learned how MySQL grants privileges according to a user's identity - determined by the host he/she is connecting from and his/her username – as well as what action he/she wants to perform.