Using SQLPASPlugin for mysql user authenticating

I was randomly searching the Internet for some nice new articles about Plone when I stumbled into SQLPASPlugin. After some reading I start to give it a try on a project that we where working on.

The project had a database including over 3000 users who could login at a administration tool built with  Zope 2. The current Mysql database was connected to Zope using ExUserFolder. This worked fine for Zope, however, we are porting the application to Plone now so we needed another solution.

I created a new plone instance and configured my buildout to include SQLPASPlugin and MySQLDA to connect to Mysql. The buildout.cfg part looks something like this :

parts =
    …
    zmysqlda
    …
[productdistros]
recipe = plone.recipe.distros
urls =
    http://plone.org/products/sqlpasplugin/releases/1.0/SQLPASPlugin-1.0.tar.gz
    …
[zmysqlda]
recipe = cns.recipe.zmysqlda
target = ${productdistros:location}

In my case,  the Mysql bindings are already installed on my system. If there are not installed you need the MySQL-python package :http://pypi.python.org/pypi/MySQL-python/1.2.2

After installation it's time to run buildout (./bin/builout) again and start Plone (./bin/instance fg). I created a Plone site and went to the portal_quickinstaller. Here I found SQLPASPlugin, and installed it.

In the root of my Plone site I created a Z MySQL Database connection, which connected to the Mysql server on my local machine witch includes the database with the user tables.

Time to take a look at the SQLPASPlugin configuration screen (SQL Authentication), found in the control panel of Plone. I configured the table, username, and password column.

For me this worked perfectly because all my users where in the table passwd ( created for exuserfolder a long time ago ) having a username and password column. If you don't have a database yet follow the link at the bottom for an SQL script.

The SQL Authentication control panel

Now i went to the ZMI of my Plone site and had a look at the acl_user folder. There where three new items there, named source_users, source_roles and source_properties. Because all the users already have roles defined in the current zope application I mapped these roles using the following screen :

The roles view in the ZMI

That was easy, mapping the roles table to my passwd table, the rols_col_username column to my username column and the roles_col_rolename column to my roles column.

It was time to take a look at the source_properties tab :

Filled in source_properties

Here I discovered that it was possible to map property fields to columns in my database.

My passwd table did not had a fullname or email field, they were stored in different tables. So I created a view :

CREATE VIEW propertiesview as SELECT username,'' as password, email,
telefoon  as phone, concat(name,' ',prefix,' ',lastname) as fullname
from usertable;

This way I could map all the fields that I needed (not all shown here, but you can select more if you need) as properties for my users and even put some fields together to create the fullname field. All the fields in the view could be mapped  by filling them in by col_mapping as:

selected_field / property_name_in_plone (for example : In the image above as fullname/fullname)

However, after configuring this all, my properties didn't show up, so it was time to take a look at the plugin registry of acl_users. There was a link named 'Properties Plugins', I clicked it and found out it was looking for properties using the mutable_properties plugin fist, so i moved my source_properties plugin to the top.

The properties plugin view

Now it was time to try it all and perform a login. To bad, it failed because ExUserFolder crypted all my passwords in a different way as the standard options available in SQLPasPlugin. So for now we made a little change to the PlainEncrypter in encrypt.py (line 79) of SQLPASPlugin.

def validate(self, reference, attempt):
    """Validate attempt against reference."""
    pwd = crypt.crypt(attempt, reference[:2])
    return reference == crypt.crypt(attempt,reference[:2])

Now my plain password posted in the login_form are crypted the way ExUserFolder did it, so the comparison will succeed now.

Now I could login using the usernames and passwords from my existing MySQL database and I also found out that my fullname and email address are shown correct now! ( made some dance moves here! \o/ :D )

References:

• How To Setup SQLPASPlugin to Authenticate Against A PostgreSQL Database
• SQLPasPlugin
• Mysql package for python
• ZMySQLDA

Database example:

• http://www.macusers.com.jm/articles/files/sqlpas_db.sql/view

Good to know:

If you want to change password encryption:

By default, SQLPASPlugin currently stored passwords as plain text. You may configure it to use MD5 or SHA encryption algorithms.

• In the root of your Plone site in the ZMI, go to the acl_users folder (note: the acl_users folder at the root of your plone site, NOT your Zope root).
• Go to the source_users folder.
• Select the properties tab.
• In default_encryption enter sha or md5 or crypt!

IMPORTANT: A VERY STUPID THING TO KNOW!

• Z SQL Methods are limiting resultsets to a maximum of 1000 results default. (ARG!)
• In my case I lost over 2k users in the user overview and it took me (not for the fist time) over an hour to figure out why!
• Solution: Go to your ZMI, acl_users -> source_users -> sqlLoadAllUsers -> Advanced and set maximum rows to retrieve to 0 (no limit)