GRANT - Connection Restrictions Types

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

GRANT privilege[,...] [(column[,...])][,  . . . ] 
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]

[REQUIRE NONE |
[{SSL|X509}] 
[CIPHER 'cipher' [AND]] 
[ISSUER 'issue' [AND]] 
[SUBJECT 'subject']]

[time and number of connection limits] ...]

Explanation

This page of our MySQL documentation relates the MySQL statement, GRANT, which may be used to create new MySQL users, as well as granting user privileges. The text on this page relates to user restrictions.

A user can be restricted to certain types of connections with the REQUIRE clause. There are several options that may be given together with the keyword AND. Each option can be used only once in a statement.

Examples

Below are descriptions of each option and examples of them. They can make for better user security.

REQUIRE NONE is the default and indicates that no such restrictions are required. Encrypted and unencrypted connections from clients are permitted from the user that has properly authenticated.

The REQUIRE SSL option restricts the user account to only SSL encrypted connections. The mysql client of the user account would start the client with the --ssl-ca option, and also the --ssl-key and --ssl-cert if necessary.

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE SSL;

Use the REQUIRE X509 option to require the user account in MySQL to have a valid CA certificate. This does not require any specific certificate, though. The mysql client would need to be started with the --ssl-ca, --ssl-key, and --ssl-cert options. To simplify handling of these options, the user can put them in a options file in her home directory on the server (e.g., ~/.my.cnf). The following is a sample of what that options file would contain to conform to the user account restrictions:

[client]
ssl-ca=/data/mysql/cacert.pem
ssl-key=/data/mysql/rusty-key.pem
ssl-cert=/data/mysql/rusty-cert.pem

Use the REQUIRE CIPHER option to require that the user account use a given cipher method.

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE CIPHER 'EDH-RSA-DES-CBC3-SHA';

REQUIRE ISSUER is used to require the user to supply a valid X509 certificate issued by the given CA. Although the string given for an issuer may be lengthy, it must be written as one string without an embedded line break.

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE ISSUER '/C=US/ST=Louisiana/L=New+20Orleans/O=WorkRequesters/CN=cacert.workrequests.com/emailAddress=admin@workrequests.com';

The REQUIRE SUBJECT option requires that the X509 certificate used by the user account have the given subject.

GRANT ALL PRIVILEGES ON workrequests.* TO 'rusty'@'localhost'
IDENTIFIED BY 'her_password'
REQUIRE SUBJECT '/C=US/ST=Louisiana/L=New+20Orleans/O=WorkRequesters/CN=Rusty Osborne/emailAddress=rusty@workrequests.com';