MySQL for Python
Integrate the flexibility of Python and the power of MySQL to boost the productivity of your Python applications
- Implement the outstanding features of Python’s MySQL library to their full potential
- See how to make MySQL take the processing burden from your programs
- Learn how to employ Python with MySQL to power your websites and desktop applications
- Apply your knowledge of MySQL and Python to real-world problems instead of hypothetical scenarios
- A manual packed with step-by-step exercises to integrate your Python applications with the MySQL database server
Introduction
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 specifi ed 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 quantifi er (‘*’). 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—use of the asterisk as a universal quantifi er 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:
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 certifi cate 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/
[email protected]’;
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;
More information on the many uses of WITH can be found at http://dev.mysql.com/doc/refman/5.1/en/grant.html
Granting access in Python
Using MySQLdb to enable user privileges is not more difficult than doing so in MySQL itself. As with creating and dropping users, we simply need to form the statement and pass it to MySQL through the appropriate cursor.
As with the native interface to MySQL, we only have as much authority in Python as our login allows. Therefore, if the credentials with which a cursor is created has not been given the GRANT option, an error will be thrown by MySQL and MySQLdb, subsequently.
Assuming that user skipper has the GRANT option as well as the other necessary privileges, we can use the following code to create a new user, set that user’s password, and grant that user privileges:
#!/usr/bin/env python
import MySQLdb
host = ‘localhost’
user = ‘skipper’
passwd = ‘secret’
mydb = MySQLdb.connect(host, user, passwd)
cursor = mydb.cursor()
try:
mkuser = ‘symphony’
creation = “CREATE USER %s@’%s'” %(mkuser, host)
results = cursor.execute(creation)
print “User creation returned”, results
mkpass = ‘n0n3wp4ss’
setpass = “SET PASSWORD FOR ‘%s’@’%s’ = PASSWORD(‘%s’)” %(mkuser,
host, mkpass)
results = cursor.execute(setpass)
print “Setting of password returned”, results
granting = “GRANT ALL ON *.* TO ‘%s’@’%s'” %(mkuser, host)
results = cursor.execute(granting)
print “Granting of privileges returned”, results
except MySQLdb.Error, e:
print e
If there is an error anywhere along the way, it is printed to screen. Otherwise, the several print statements are executed. As long as they all return 0, each step was successful.