MySQL Grant Privileges

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.

MySQL Grant Syntax

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.

MySQL Grant All Privileges Example

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:

• company.* - * is a wildcard symbol, so it means all tables in the 'company' database
• administrator@localhost - the name to the left of the @ symbol is the username, and the name to the right should be the hostname or IP address where the user is allowed to connect from. In this example, 'administrator' is only allowed to connect from 'localhost' which is the local machine where the database server is installed.

MySQL Grant Remote Access Example

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';

MySQL Grant Some Privileges Example

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:

FLUSH PRIVILEGES;

This clears the privileges cache and makes our recently granted privileges work properly. We hope this MySQL tutorial was helpful.