Part 6.1Granting privileges in MySQL

SQL provides a layer of authority over the database, allowing certain users to access certain features of the database. This can be an excellent way to ensure data security.

It is often necessary to have a single user who has authority to do everything on a database but then for some web application a user who only has certain authorities over certain databases.

MySQL allows the database to be accessed in different ways by different users very easily, and the GRANT command allows users to be setup this way very easily.

Granting privileges

Adding privileges to a user is called granting privileges in MySQL.

The basic structure for granting privileges is as follows:

GRANT privilege[, privilege] ...
ON database.tables
TO account.host

Assume there exists a user called toby and a database called friends. The following SQL statement would allow toby access to all tables in the friends database.

	GRANT ALL PRIVILEGES ON `friends`.* TO 'toby'@'localhost';
	FLUSH PRIVILEGES;

In this statement:

• ALL can be replaced with specific, comma-separated individual privileges that the user should have.
• The * means every table within the friends database.
• The localhost can be replaced with anything or a wildcard that informs MySQL that the user could come from any valid host using the % symbol.
• It is important to run the FLUSH PRIVILEGES command straight after to ensure that the database engine is updated with the changes.