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).
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.
3.145.91.254