MySQL in a Nutshell [34]
GRANT SELECT,INSERT,UPDATE ON workrequests.workreq
TO 'jerry'@'localhost' IDENTIFIED BY 'neumeyer3186';
Assuming the user jerry does not already exist, the first statement here creates the user and gives him SELECT privileges only for the workrequests database for all of its tables. This will allow him to read from the various tables but not edit the data. The second SQL statement grants jerry the right to add and change data in the workreq table of the workrequests database. This will allow him to enter work requests and make changes to them. The first statement causes an entry to be made to the db table in the mysql database. The second affects the tables_priv table. An entry is also made to the user table showing the user jerry, but he has no global privileges. This is the equivalent of granting just the USAGE privilege.
GRANT: Type of connection restrictions
GRANT privilege[,...] [(column[,...])][, ...]
ON [TABLE|FUNCTION|PROCEDURE] {[{database|*}.{table|*}] | *}
TO 'user'@'host' [IDENTIFIED BY [PASSWORD] 'password'][, ...]
[REQUIRE NONE |
[{SSL|X509} [AND]]
[CIPHER 'cipher' [AND]]
[ISSUER 'issue' [AND]]
[SUBJECT 'subject']]
[time and number of connection limits] ...]
A user can also 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. 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 been 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 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