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.
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
The following SQL statement would allow
toby access to all tables in
GRANT ALL PRIVILEGES ON `friends`.* TO 'toby'@'localhost'; FLUSH PRIVILEGES;
In this statement:
ALLcan be replaced with specific, comma-separated individual privileges that the user should have.
*means every table within the
localhostcan be replaced with anything or a wildcard that informs MySQL that the user could come from any valid host using the
It is important to run the
FLUSH PRIVILEGEScommand straight after to ensure that the database engine is updated with the changes.