MySQL in a Nutshell [28]
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 options 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 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 X.509 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 X.509 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';
GRANT: Time and number of connection limits
GRANT privilege[,...] [(column[,...])][, ...]
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]
[type of connection restrictions]
[WITH [MAX_QUERIES_PER_HOUR count |
MAX_UPDATES_PER_HOUR count |
MAX_CONNECTIONS_PER_HOUR count |
MAX_USER_CONNECTIONS count] ...]
You can use the WITH clause along with the MAX_QUERIES_PER_HOUR option to specify the maximum number of queries that a user account may execute per hour. The MAX_UPDATES_PER_HOUR option is used to give the maximum number of UPDATE statements that may be issued per hour by the user account. The maximum number of connections by a user account to the server per hour can be set with the MAX_CONNECTIONS_PER_HOUR option. The default values for these three options are all 0. This value indicates that there is no limit or restrictions for these resources. The MAX_USER_CONNECTIONS option is used to set the maximum number of simultaneous connections the given user account may have. If this value is not set or is set to 0, the value of the system variable max_user_connections is used instead. Here is an example of how a user might be limited in such a way:
GRANT SELECT ON catalogs.*
TO 'webuser'@'%'
WITH MAX_QUERIES_PER_HOUR 1000
MAX_CONNECTIONS_PER_HOUR 100;
This account is designed for large numbers of users running queries through a web server. The statement creates the webuser user and allows it to read tables from the catalogs database. The user may not run more than 1,000 queries in an hour and may establish only 100 connections in an hour.
To change an existing user account’s resources without changing the account’s existing privileges,