MySQL for Python : Creating Users and Granting Access - GRANT access in MySQL

After creating a user account, one still needs to tell MySQL what kind of privileges to assign to it. MySQL supports a wide range of privileges (see the table of privileges on page 9). A user can only grant any privilege that they have themselves.

As with creating a user, granting access can be done by modifying the mysql tables directly. However, this method is error-prone and dangerous to the stability of the system and is, therefore, not recommended.

Important dynamics of GRANTing access

Where CREATE USER causes MySQL to add a user account, it does not specify that user's privileges. In order to grant a user privileges, the account of the user granting the privileges must meet two conditions:

• Be able to exercise those privileges in their account

• Have the GRANT OPTION privilege on their account

Therefore, it is not just users who have a particular privilege or only users with the GRANT OPTION privilege who can authorize a particular privilege for a user, but only users who meet both requirements.

Further, privileges that are granted do not take effect until the user's first login after the command is issued. Therefore, if the user is logged into the server at the time you grant access, the changes will not take effect immediately.

The GRANT statement in MySQL

The syntax of a GRANT statement is as follows:

GRANT <privileges> ON <database>.<table>
TO '<userid>'@'<hostname>';

Proceeding from the end of the statement, the userid and hostname follow the same pattern as with the CREATE USER statement. Therefore, if a user is created with a hostname specified as localhost and you grant access to that user with a hostname of '%', they will encounter a 1044 error stating access is denied.

The database and table values must be specified individually or collectively. This allows us to customize access to individual tables as necessary. For example, to specify access to the city table of the world database, we would use world.city.

In many instances, however, you are likely to grant the same access to a user for all tables of a database. To do this, we use the universal quantifier ('*'). So to specify all tables in the world database, we would use world.*.

We can apply the asterisk to the database field as well. To specify all databases and all tables, we can use *.*. MySQL also recognizes the shorthand * for this.

Finally, the privileges can be singular or a series of comma-separated values. If, for example, you want a user to only be able to read from a database, you would grant them only the SELECT privilege. For many users and applications, reading and writing is necessary but no ability to modify the database structure is warranted. In such cases, we can grant the user account both SELECT and INSERT privileges with SELECT, INSERT.

To learn which privileges have been granted to the user account you are using, use the statement SHOW GRANTS FOR <user>@hostname>;.

With this in mind, if we wanted to grant a user tempo all access to all tables in the music database but only when accessing the server locally, we would use this statement:

GRANT ALL PRIVILEGES ON music.* TO 'tempo'@'localhost';

Similarly, if we wanted to restrict access to reading and writing when logging in remotely, we would change the above statement to read:

GRANT SELECT,INSERT ON music.* TO 'tempo'@'%';

If we wanted user conductor to have complete access to everything when logged in locally, we would use:

GRANT ALL PRIVILEGES ON * TO 'conductor'@'localhost';

Building on the second example statement, we can further specify the exact privileges we want on the columns of a table by including the column numbers in parentheses after each privilege. Hence, if we want tempo to be able to read from columns 3 and 4 but only write to column 4 of the sheets table in the music database, we would use this command:

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%';

Note that specifying columnar privileges is only available when specifying a single database table&mdash;use of the asterisk as a universal quantifier is not allowed. Further, this syntax is allowed only for three types of privileges: SELECT, INSERT, and UPDATE.

A list of privileges that are available through MySQL is reflected in the following table:

Privilege	Function	Context
ALL	Grants all privileges to user	Databases, tables, or indexes
CREATE	Creates database objects	Databases, tables, or indexes
DROP	Drops database objects	Databases or tables
GRANT OPTION	Grants privileges to other users	Databases, tables, or stored routines
REFERENCES	Supported internally but otherwise unused	Databases or tables
ALTER	Allows use of ALTER TABLE	Tables
DELETE	Allows use of DELETE	Tables
INDEX	Enables creation and dropping of indexes	Tables
INSERT	Allows data insertion	Tables
SELECT	Allows reading from a database	Tables
UPDATE	Allows use of UPDATE	Tables
CREATE TEMPORARY TABLES	Allows user to create temporary tables	Tables
LOCK TABLES	Enables the use of LOCK TABLES for tables on which SELECT has been granted	Tables
TRIGGER	Allows the automation of certain events in a table under conditions set by the user	Tables
CREATE VIEW	Enables the creation and deletion of views	Views
SHOW VIEW	Allows the showing of views	Views
ALTER ROUTINE	Allows user to alter and delete stored routines	Stored routines
CREATE ROUTINE	Enables the creation of stored routines	Stored routines
EXECUTE	Allows the execution of stored routines	Stored routines
FILE	Enables file access on localhost	File access
CREATE USER	Enables the creation of users	Server administration
PROCESS	Enables the user to view all processes with SHOW PROCESSLIST	Server administration
RELOAD	Enables use of FLUSH	Server administration
REPLICATION CLIENT	Allows user to query about master and slave servers	Server administration
REPLICATION SLAVE	Allows slave servers to read binary logs from the master server	Server administration
SHOW DATABASES	Allows user to view available databases	Server administration
SHUTDOWN	Enables the use of mysqladmin shutdown	Server administration
SUPER	Enables the use of several superuser privileges	Server administration
ALL [PRIVILEGES]	Grants all privileges to the user that are available to the grantor	Server administration
USAGE	Allows access to the user	Server administration

MySQL does not support the standard SQL UNDER privilege and does not support the use of TRIGGER until MySQL 5.1.6.

More information on MySQL privileges can be found at http://dev.mysql.com/doc/refman/5.5/en/privileges-provided.html

Using REQUIREments of access

Using GRANT with a REQUIRE clause causes MySQL to use SSL encryption.

The standard used by MySQL for SSL is the X.509 standard of the International Telecommunication Union's (ITU) Standardization Sector (ITU-T). It is a commonly used public-key encryption standard for single sign-on systems. Parts of the standard are no longer in force. You can read about the parts which still apply on the ITU website at http://www.itu.int/rec/T-REC-X.509/en

The REQUIRE clause takes the following arguments with their respective meanings and follows the format of their respective examples:

• NONE: The user account has no requirement for an SSL connection. This is the default.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%';

• SSL: The client must use an SSL-encrypted connection to log in. In most MySQL clients, this is satisfied by using the --ssl-ca option at the time of login. Specifying the key or certificate is optional.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE SSL;


					  

• X509: The client must use SSL to login. Further, the certificate must be verifiable with one of the CA vendors. This option further requires the client to use the --ssl-ca option as well as specifying the key and certificate using --ssl-key and --ssl-cert, respectively.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE X509;


					  

• CIPHER: Specifies the type and order of ciphers to be used.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE CIPHER 'RSA-EDH-CBC3-DES-SHA';


					  

• ISSUER: Specifies the issuer from whom the certificate used by the client is to come. The user will not be able to login without a certificate from that issuer.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE ISSUER 'C=ZA, ST=Western Cape,
 L=Cape Town, O=Thawte Consulting cc, OU=Certification Services Division,CN=Thawte Server CA/emailAddress=server-certs@thawte.com';


					  

• SUBJECT: Specifies the subject contained in the certificate that is valid for that user. The use of a certificate containing any other subject is disallowed.

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' REQUIRE SUBJECT 'C=US, ST=California, 
L=Pasadena, O=Indiana Grones, OU=Raiders, CN=www.lostarks.com/emailAddress=indy@lostarks.com';


					  

Using a WITH clause

MySQL's WITH clause is helpful in limiting the resources assigned to a user. WITH takes the following options:

• GRANT OPTION: Allows the user to provide other users of any privilege that they have been granted

• MAX_QUERIES_PER_HOUR: Caps the number of queries that the account is allowed to request in one hour

• MAX_UPDATES_PER_HOUR: Limits how frequently the user is allowed to issue UPDATE statements to the database

• MAX_CONNECTIONS_PER_HOUR: Limits the number of logins that a user is allowed to make in one hour

• MAX_USER_CONNECTIONS: Caps the number of simultaneous connections that the user can make at one time

It is important to note that the GRANT OPTION argument to WITH has a timeless aspect. It does not statically apply to the privileges that the user has just at the time of issuance, but if left in effect, applies to any options the user has at any point in time. So, if the user is granted the GRANT OPTION for a temporary period, but the option is never removed, then the user grows in responsibilities and privileges, that user can grant those privileges to any other user. Therefore, one must remove the GRANT OPTION when it is not longer appropriate.

Note also that if a user with access to a particular MySQL database has the ALTER privilege and is then granted the GRANT OPTION privilege, that user can then grant ALTER privileges to a user who has access to the mysql database, thus circumventing the administrative privileges otherwise needed.

The WITH clause follows all other options given in a GRANT statement. So, to grant user tempo the GRANT OPTION, we would use the following statement:

GRANT SELECT (col3,col4),INSERT (col4) ON music.sheets TO 'tempo'@'%' WITH GRANT OPTION;


					  

If we want to limit the number of queries that the user can have in one hour to five, as well, we simply add to the argument of the single WITH statement. We do not need to use WITH a second time.

GRANT SELECT,INSERT ON music.sheets TO 'tempo'@'%' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 5;