By default, new users do not have the permission to do anything except logging in, which is not very useful. So the next thing we need to do is give them the permissions that they need. Over time, we may need to remove or revoke the privileges we gave them earlier, and from time to time we'll want to look up a user to see what privileges they have.
This is done using the
GRANT statement. Using this statement, we will be able to GRANT users the appropriate permissions. GRANT statements have the following basic pattern:
GRANT <privileges> ON <database> TO <user>;
We customize the <privileges>, <database>, and <user> parts as needed. The <user> section should match the 'username'@'host' part of the CREATE statement. Otherwise, we'll be creating a new user. We can also add an IDENTIFIED BY 'password' section to the end of the GRANT statement if we want to change the password of the user (or add a password to a user that doesn't have one).
The following are some examples. The first one grants all privileges, including the ability to GRANT privileges to other users, on all databases and the user can log in from anywhere. We should not often set up users with such broad authority, but when we do, we need to make sure that we use an appropriate CREATE USER statement first and assign the user a password (or assign the password here).
Note
If a user doesn't exist before we use the GRANT statement, the user will be created, but if the user doesn't exist and our GRANT statement doesn't include an IDENTIFIED BY 'password' section, then the user will be created without a password. So, it's a good habit to first create the user with a password, and then grant the user the rights that they need.
GRANT ALL ON *.* TO 'robert'@'%' WITH GRANT OPTION;
The following example is for a standard set of permissions for a regular user who needs read and write access to a database called serv. If a user just needs read access, we can assign the user the SELECT privilege. By specifying serv.* as the database, the user only has these rights on the tables in the serv database. Multiple privileges are separated by commas.
GRANT SELECT,INSERT,UPDATE,DELETE ON serv.* TO 'jeffrey'@'localhost';
The following user has read access (SELECT) to just the staff table in the edu database, and the user has the GRANT OPTION privilege so that they can grant that same right to other users.
GRANT SELECT ON edu.staff TO 'david'@'localhost' WITH GRANT OPTION;
The following example gives a user all rights on the logan database. We'll also limit this user to 100 queries per hour, just because we can. The limit will apply to every database that can be accessed by the quentin user and not just to queries that the user runs on the logan database.
GRANT ALL ON logan.* TO 'quentin'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
Sometimes it becomes necessary to remove a privilege or two from a user, or to give them more privileges. Giving additional privileges is easy; just run an additional GRANT statement with the new rights and they will be added to the user's set of permissions. To remove privileges, we use the REVOKE statement. It has the following pattern:
REVOKE <privileges> ON <database> FROM <user>;
To remove a GRANT OPTION privilege, specify it in the <privileges> section along with any other privileges being revoked. Each permission being removed should be separated from the others with a comma (,). The following example removes the DELETE and GRANT OPTION permissions from the todd user:
REVOKE DELETE,GRANT OPTION ON cust.* FROM 'todd'@'%';
To remove all privileges from a user ('neil'@'%.example.com' in this example), we use the following special command:
REVOKE ALL,GRANT OPTION FROM 'neil'@'%.example.com';
Generally, it is preferred to use the DROP USER statement, described in the Removing users section later on in this chapter, instead of removing all privileges from a user as we are doing here. Of course, we need to customize the user part to match the user for whom we are removing privileges. The statement is also special in that it must be used as written even if the user doesn't have the GRANT OPTION privilege. If we remove the GRANT OPTION privilege from it, the statement won't run.
To show the permissions granted to a user, we use the SHOW GRANTS command. It has the following pattern:
SHOW GRANTS FOR <user>;
All we have to do is customize the <user> part with the information of the user we want to look at. The following is an example of this:
SHOW GRANTS FOR 'dieter'@'10.2.200.4';
The output of the SHOW GRANTS command is a GRANT statement that encapsulates all of the user's privileges. This is useful if you want to give another user exactly the same privileges. For example, the output of the preceding SHOW GRANTS command might be as follows:
+------------------------------------------------------+ | Grants for [email protected] | +------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'dieter'@'10.2.200.4' | +------------------------------------------------------+
We can simply copy the GRANT statement in the output and change the <user> part to create a user with the exactly same privileges.