To use this website fully, you first need to accept the use of cookies. By agreeing to the use of cookies you consent to the use of functional cookies. For more information read this page.

Granting privileges in MySQL6.1

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:

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

SQL
	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.
Feedback 👍
Comments are sent via email to me.