As our database size increases, more persons will be needed to maintain it. MySQL, like most other database engines, allows the option of having multiple users access a database.
This is done using privileges. By allowing different users different privileges on a database, we are able to assign different tasks to users and also ensure that these users do not have more permission than is necessary to carry out their tasks.
For example, we could make the administrator have full control of the database, make a cashier have only SELECT privileges, and a manager have SELECT, UPDATE and DELETE privileges.
In MySQL, we grant privileges to a user using the GRANT command. The tutorial below explains.
The most common form of the GRANT command is:
GRANT privilege_name ON database_name.table_name(s) TO user_name@location IDENTIFIED BY 'password';
How exactly to use this command will become clearer when you see the example below.
If we wanted to grant all privileges on our "company" database to a database administrator we would use:
GRANT ALL PRIVILEGES ON company.* TO 'administrator'@'localhost' IDENTIFIED BY 'password';
Note a few things:
If we wanted to grant privileges on our "company" database to a remote user, we could specify their IP address or hostname like so:
GRANT ALL PRIVILEGES ON company.* TO 'remoteuser'@'192.168.22.56' IDENTIFIED BY 'password';
Or use the wildcard % symbol to allow a login from anywhere:
GRANT ALL PRIVILEGES ON company.* TO 'remoteuser'@'%' IDENTIFIED BY 'password';
If we wanted to grant some privileges on our "company" database to a user, we simply list the commands they should be able to use like so:
GRANT SELECT,INSERT,UPDATE,DELETE ON company.* TO 'user'@'%' IDENTIFIED BY 'password';
Now you've seen examples of granting all privileges, granting privileges to remote users, and granting only some privileges. By combining the principles in each, you are able to grant just the right privileges to a particular user at their particular location.
Once the desired privileges have been granted, the final step is to run:
This clears the privileges cache and makes our recently granted privileges work properly. We hope this MySQL tutorial was helpful.