How To: Use GenericUserFolder with an SQL database

Created by . Last modified on 2003/08/05.

How To: Use GenericUserFolder with an SQL Database.

Version: 1.0

Date: December 1999

Introduction

This How-To shows how to customize GenericUserFolder(GUF) to use an SQL database as its repository for usernames, passwords, roles and anything else you want to store about a user.

I used a Postgres database and some of the SQL may be specific to Postgres, particularly the use of 'nextval' and 'currval' for controlling unique indexes (I don't know enough about other RDBMS to know whether this is portable, perhaps you could advice?).

This How-To also describes how to patch Zope to allow the 'crypt' module to be called from DTML. This enables GUF to store all its passwords in an encrypted format.

I assume that you already have a database installed and accessible from Zope.

The following tutorial is a result of notes I made while learning how to deploy GUF in my particular curcumstance, it broken down into the following steps:

• 1. Create the database tables.
• 2. Patch Zope to allow the crypt module to be called.
• 3. Create an instance of GUF.
• 4. Create a database connection.
• 5. Create the SQL methods to access the database.
• 6. Customize the GUF instance to call the SQL methods.
• 7. Create user forms to manage passwords.
• 8. Set permissions to protect the use database.
• 9. Force GUF to see the changes.

1. Create the database tables.

Create the following tables in the database (this is the postgres SQL to create the tables). I choose the normalize the database and use unique ids to link the tables together, you could do without the ids and just use the username and groupname as keys if you like:

CREATE SEQUENCE "user_id_seq";
CREATE SEQUENCE "group_id_seq";

CREATE TABLE "users" (
 "user_id" int4 NOT NULL,
 "username" text,
 "password" text);

CREATE TABLE "groups" (
 "group_id" int4,
 "groupname" text);

CREATE TABLE "user_group_map" (
 "user_id" int4,
 "group_id" int4);

CREATE UNIQUE INDEX "users_pkey" on "users" using btree ( "user_id"
"int4_ops" );

2. Patch Zope to allow the crypt module to be called.

If you want to encrypt your passwords you have two options:

1. a. if you don't mind changing the source code you can patch the Zope sources to add 'crpyt' to the list of modules that Zope allows to be called from DTML.
2. b. if you don't want to change the source code you can add an External Method to perform the encryption.

I only deal with the first method, because it is the one I chose. You can skip this step and return to it after you have everything running without encryption.

Find the file [ZOPEHOME]/lib/python/DocumentTemplate/DT_Util.py, you will need to have access to the filesystem on which Zope is installed to be able to do this.

Take a copy of the file encase you mess things up.

Find the part of the file that looks like this:

 d['string']=string
 d['math']=math
 d['whrandom']=whrandom

 try:
    import random
    d['random']=random
 except: pass    

Insert the follow code fragment (is purpose is self explanatory):

 try:
    import crypt
    d['crypt']=crypt
    except: pass

The code should now look like this:

 d['string']=string
 d['math']=math
 d['whrandom']=whrandom

 try:
    import crypt
    d['crypt']=crypt
    except: pass

 try:
    import random
    d['random']=random
    except: pass    

You must now restart Zope so that the changes to the source will take effect.

3. Create an instance of GUF.

The ground work is now in place and you can start the work inside Zope. First task is to set up GUF as it comes 'out-of-the-box'. You should read the instructions on the README tab of the product.

It is best to play with acl_users folders in a Zope folder that does not contain anything important until you get it working. It is possible to lock yourself out of a folder if you get things wrong.

Create a new folder to testing things out call it 'test_guf' and select both 'Create public interface' and 'Create user folder'.

Delete the 'acl_users' folder in 'test_guf'.

Create a GenericUserFolder called 'acl_users' in 'test_guf'.

Set the permissions as directed in GUF readme

Now ensure that you can log in as jorge:secret

4. Create a database connection.

Insert a 'ZPyGreSQLDA' object in the acl_users folder and give it the connection string for the database created in step 1.

5. Create the SQL methods to access the database.

Now we must create the SQLMethods used to access the database. We create the following methods:

This is not a complete list of all of the functions that you may need to perform, for instance you would probably want to delete users, change roles, add roles etc. but it gives a reasonable idea from which you can role your own.

The contents of these methods follows:

  insert into users 
  values (nextval('user_id_seq'),
   <dtml-sqlvar username type=string>,
   <dtml-sqlvar password type=string>)

  <dtml-var sql_delimiter>

  insert into user_group_map (user_id, group_id)
  select currval('user_id_seq'), group_id
  from groups 
  where groupname = 'Subscriber'

   update users 
      set password = <dtml-sqlvar password type=string>
   where 
      username = <dtml-sqlvar username type=string>

  select password as real_password
  from users
  where users.username = <dtml-sqlvar username type=string>

   select groupname
   from groups, users, user_group_map
   where
       users.username = <dtml-sqlvar username type=string>
   and
       users.user_id = user_group_map.user_id 
   and 
       groups.group_id = user_group_map.group_id

  select username from users

6. Customize the GUF instance to call the SQL methods.

Now we must change the DTML in the GUF default methods so that they call our new SQL methods:

If you are going to use encrypted passwords change userAuthenticate to:

<dtml-in "SQL_get_password(username=username)"> 
<dtml-if "_.crypt.crypt(password,'ab')==_['sequence-item'].real_password">
<dtml-return "_.int('1')">
</dtml-if>
</dtml-in>

<dtml-return "_.int('0')">

If you are not using encrypted passwords change userAuthenticate to:

<dtml-in "SQL_get_password(username=username)"> 
<dtml-if "password==_['sequence-item'].real_password">
<dtml-return "_.int('1')">
</dtml-if>
</dtml-in>

<dtml-return "_.int('0')">

change userList to:

<dtml-return "SQL_get_user_list()">

change userRoles to:

<dtml-call "REQUEST.set('ret','')">

<dtml-in "SQL_get_roles(username=username)">
<dtml-call "REQUEST.set('ret', ret + ' ' + _['sequence-item'].groupname)">
</dtml-in>

<dtml-return ret>

7. Create user forms to manage passwords.

To demonstrate how to create forms that can be used to administer you users try to following for and response method:

create a DTMLMethod called change_password_form containing:

<HTML>
<HEAD>
<TITLE>Change Password</TITLE>
</HEAD>
<BODY BGCOLOR="#FFFFFF" LINK="#000099" VLINK="#555555">

<h1>Change Password</h1>

<FORM ACTION="change_password_report" METHOD="POST">
<TABLE>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">
<STRONG>Username</STRONG>
</TD>
<TD ALIGN="LEFT" VALIGN="TOP">
<INPUT TYPE="text" NAME="username" SIZE="20">
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">
<STRONG>Password</STRONG>
</TD>
<TD ALIGN="LEFT" VALIGN="TOP">
<INPUT TYPE="PASSWORD" NAME="password" SIZE="20">
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">
<STRONG>New Password</STRONG>
</TD>
<TD ALIGN="LEFT" VALIGN="TOP">
<INPUT TYPE="PASSWORD" NAME="first_password" SIZE="20">
</TD>
</TR>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">
<STRONG>Confirm</STRONG>
</TD>
<TD ALIGN="LEFT" VALIGN="TOP">
<INPUT TYPE="PASSWORD" NAME="second_password" SIZE="20">
</TD>
</TR>

create a DTMLDocument called change_password_report called:

<!--#var standard_html_header-->

<dtml-call "REQUEST.set('val','0')">

<dtml-in "SQL_get_password(username=username)"> 
<dtml-if "_.crypt.crypt(password,'ab')==_['sequence-item'].real_password">
 <dtml-call "REQUEST.set('val','1')">
</dtml-if>
</dtml-in>

<dtml-if "val == '1'">
<dtml-if "first_password==second_password">

<dtml-call "REQUEST.set('password',_.crypt.crypt(first_password,'ab'))">
<dtml-call SQL_change_password>
<h2>Your Password has been changes</h2>
<p>Thank you.</p>

<dtml-else>

<h2>Failed</h2>
<p>Passwords don't match</p>
<p>First: <dtml-var first_password></p>
<p>Second: <dtml-var second_password></p>

</dtml-if>

<dtml-else>

<h2>Failed</h2>
<p>Old Password does not match.</p>

</dtml-if>

<!--#var standard_html_footer-->

8. Set permissions to protect the use database.

It is now time to setup the permissions on the GUF objects to ensure that the SQL methods can only be called the code that we have created.

Create a user defined role called Authoriser in the acl_users folder do not give it any permissions.

set the 'Use Database Methods' permission for 'Authoriser' on all the SQLMethods created above.

set the Proxy role "Authoriser" on:

• change_password_report
• userAuthenticate
• userList
• userRoles

9. Force GUF to see the changes

All is now ready. Because of some oddities in the permissions stuff in Zope we must flush the GUF cache before any of out changes will come in to affect.

From the 'cache' tab of the acl_users folder click the flush button to have all the changes take effect.