Storing Plone users in an SQL database

In the past, a couple of Plone sites we have built had large numbers of users for whom it was easier to store their details in an SQL Backend rather than Plone itself. When it came to bringing one of those system up-to-date it was appropriate to review the process and perhaps use a different approach such as a full-on LDAP deployment. During this review process, we learned how to deploy a new product and figured a solution to a possible use case.

Deploying pas.plugins.sqlalchemy

Unfortunately the help documents on the web did not help us install this product and it took some research to discover much simpler instructions buried deep in the plone.users mailinglist ; so deep it remains hidden. Here is what we did:

• add pas.plugins.sqlalchemy to the eggs section in buildout.cfg

• add pas.plugins.sqlalchemy to the zcml section in buildout.cfg

• adding collective.saconnect did not seem to work for us or at least was not helpful so do not install this product

• add a definition to the instance section in your buildout.cfg appropriately modified to point to the database you want to use; best to make sure you can get this to connect first (see code below)

  zcml-additional =
  <configure xmlns="http://namespaces.zope.org/zope"
             xmlns:db="http://namespaces.zope.org/db">
    <include package="z3c.saconfig" file="meta.zcml" />
    <db:engine xmlns="http://namespaces.zope.org/db"
               name="pas"
               url="mysql://user:password <at> host/database" />
    <db:session xmlns="http://namespaces.zope.org/db"
                name="pas.plugins.sqlalchemy"
                engine="pas" />
  </configure>

• you can now startup your Plone instance

• from the Plone control panel, add the PAS SQL Plugin product: this step should both connect to the database and create the database schema with empty tables

• to activate the plugin, access the ZMI and navigate to the acl_users folder and the plugins sub-folder. Review each plugin-type and if an sql option is available, change its precedence to suit your purposes.

Provided you have set the precedence of sql in the User Adder plugins (step 7) you can add new users and they will be stored in your SQL database. Notice that the settings in step 4 apply throughout an instance. If you have several Plone instances within a single Zope instance, then each Plone instance with the PAS SQL Plugin activated will share the same user SQL database - as in the Use Case below.

Use Case for multiple sites

Note: the following notes only apply for a group of Plone sites; although the use case may be general, this solution is specific to Plone

Suppose you require a number of related sites all of which relate broadly to the same group of users:

• Paid-up members or Active members: assigned as Members and possibly Contributors
• Lapsed members: having a login but no role assignment
• Various organisational sub-sets of members: assigned the various management roles of Editor, Reviewer, Manager, etc
• Anonymous visitors: obviously have no login

In your sites, you want to allow members to be able to logon and authenticate themselves. Then depending on the site and the specific user, various roles can be assigned to allow access to different types of content. When deploying this plugin, user information is stored in the database but user and group permissions remains in Plone. Using this behaviour we can use group definitions to control who can do what in specific sites; all with a minimum of tweaking the Plone instances. First the minimal changes:

1. set sql to the top of all its relevant plugin types except for Group Management - we want Plone instances to drive this part of user management
2. set Intranet/Extranet Workflow as the default for each site
3. for the internally_published state, under the Permissions tab, switch on Authenticated permission for both view and access options (this is optional and depends what you want Lapsed Members to see)
4. activate the changes for the workflow
5. in each site, add a membership Group with a roles you want all Active members to have
6. for each site, add additional groups for any special subsets you may want setting roles as required
7. add members in one site assigning each to appropriate groups for that site
8. in additional sites, add members to any groups peculiar to these individual sites

Having done all this, you should now get the following behaviour

• Anonymous users: can only see Externally Published content
• Lapsed Members: can also see Internally Published material if they are logged in
• Active Members: can see all content except items marked as private and whatever additional roles you have assigned
• Special Members: certain members will have greater access depending on the roles they have been assigned in each site

These behaviours are easily managed simply by adding users to one or more groups. Further, a new user only needs added in one site and usually Group membership can be assigned at that time (except for Groups which are specific to individual sites). Active Membership can also be controlled by an external application updating the SQL database independent of any Plone instance i.e. for lapsed members, remove their appropriate Group membership via the database. This procedure will affect all related sites immediately without having to do anything else.