Use LoginManager with a SQL Database

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

Version history

Version: 0.9.6 (30 March 2001)

Added a link to the How-To: Install ZPatterns & LoginManager from BwanaZulia

Version: 0.9.5 (20 March 2001)

Changed some links, another clarification in step 8

Version: 0.9.4 (31 January 2001)

Clarification, where to put the Z SQL methods (based on an e-mail by J B Bell). Fixed some typos. Added advice to backup data.fs .

Version: 0.9.3 (18 January 2001)

Added some additional steps (based on an e-mail by Phil Mayers), updated Zope version.

Version: 0.9.2 (25 August 2000)

Added some additional steps and version numbers.

Version: 0.9.1 (August 2000)

Fixed some typos and simplyfied the DTMLMethods.

Version: 0.9 (August 2000)

First release.

Used Product Versions

• Zope 2.2.5
• ZPatterns-0-4-0a5
• LoginManager-0-8-7a1
• ZMySQLDA-1-2-0

Introduction

With Zope 2.1.x i used the GenericUserFolder(GUF) together with a MySQL database for user authentication.

As GUF is deprecated (and doesn't work with Zope >= 2.2 ??) i tried the new LoginManager

Notice: I didn't define my own User Source or Login Method. I use the Generic User Source provided by LoginManager.

See also How-To: Use GenericUserFolder with an SQL database from hippy. This is how i set up my GUF system (except some minor difference in the database configuration: MySQL and other table names and columns).

I copied most of hippy's How-To, used the same order of steps and modified it to my actual setup.

Steps 6 and 7 changed in 0.9.3, see a message by Phil Mayers (title: "Help with getting started with Login Manager ") or a thread starting with LoginManager broken?

There is a now a How-To from dlpierson, which describes a version of LoginManager with SQL and Skinscript.

Steps

1. Make a backup of the data.fs
2. Create the database tables
3. Install ZPatterns, LoginManager, Zope DA
4. Prepare your users
5. Add a LoginManager from the list of available objects (def. values)
6. Add a Database Connection
7. Create the Z SQL methods as like for the GUF
8. Customize the GUS instance as like for the GUF
9. Create user forms to manage passwords.
10. Set permissions to protect the use database.
11. Test it and move it to the desired level

1. Make a backup of your data.fs

As a change in the authentication mechanism can lock you out of the management screens:

2. Database tables

Here is a dump of my MySQL database.

I use the auto_increment feature and the encrypt() function of MySQL, so you have to change it if you use another database.

CREATE TABLE users (
   user_id char(25) NOT NULL,
   cpasswd char(25) NOT NULL,
   Id int(11) DEFAULT '0' NOT NULL auto_increment,
   PRIMARY KEY (user_id),
   KEY Id (Id)
);
CREATE TABLE groups (
   group_id int(11) DEFAULT '0' NOT NULL auto_increment,
   groupname varchar(50) NOT NULL,
   PRIMARY KEY (group_id)
);

CREATE TABLE user_group_map (
   user_id int(11) DEFAULT '0' NOT NULL,
   group_id int(11) DEFAULT '0' NOT NULL,
   KEY user_id (user_id, group_id)
);

3. Install ZPatterns, LogginManager, Zope DA (e.g. ZMySQLDA)

Install the products as documented in the product install instructions.

See also the How-To: Install ZPatterns & LoginManager by BwanaZulia.

4. Prepare your users

• log in as superuser
• create a new user with manager and owner roles (in the standard acl_users folder)
• this user should also be in the database
• log out and log in as the new user
• create a test folder

5. Add a LoginManager from the list of available objects

In the folder of your choice add the LoginManager from the list of available objects. Select the GenericUserSource as User Source, leaving the other properties with their default values.

6. Add a Database Connection

Add a Zope DA connection object (e.g. Z MySQL Database connection) for your SQL database in the acl_users folder.

7. Create the SQL methods to access the database (in the acl_users folder)

Changed in 0.9.3, see a message by Phil Mayers

XXX todo: SQL_add_user

Methods:

  update users set cpasswd = 
  encrypt(<dtml-sqlvar password type=string>) 
  where user_id = <dtml-sqlvar username type=string>

  select user_id as username from users 
  where user_id=<dtml-sqlvar username type=string>

  select cpasswd as real_password, 
  encrypt(<dtml-sqlvar password type=string>,left(cpasswd,2)) as salt from users
  where user_id = <dtml-sqlvar username type=string>

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

Addition from Phil Mayers (see link above):

Once you've completed step 7, I recommend taking a copy of "folder" i.e.

/root
  /folder
    (...etc...)
  /copy_of_folder
    (...etc...)

If you break it, you can get back to /root, delete folder, copy "copy_of_folder" back to "folder" and not have to start from scratch. Very time saving.

8. Customize the LoginManger instance to call the SQL methods

Changed in 0.9.3, see a message by Phil Mayers

Now you must add the follwing DTML Methods to the acl_users folder:

XXX todo: xuserDomains and xuserAdd

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

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

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

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

  <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>

Addition from Phil Mayers (see link above):

Edit & test the DTML methods, then when you're sure they work, batch rename them (i.e. tick the boxes next to all three, then click rename). They'll be renamed as an atomic unit, and hopefully the LoginManager will work.

Clarification: the new method names have to be "userAuthenticate", "userExists" and "userRoles" (without quotes).

9. Create user forms to manage passwords

To demonstrate how to create forms that can be used to administer you users try the following for an response method in the acl_users folder:

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>
<TR>
  <TD ALIGN="LEFT" VALIGN="TOP">
  </TD>
  <TD ALIGN="LEFT" VALIGN="TOP">
  <INPUT TYPE="SUBMIT" NAME="submit" VALUE=" Ok ">
  </TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>

create a DTMLDocument called change_password_report containing:

<dtml-var standard_html_header>

<dtml-call "REQUEST.set('val','0')">
<dtml-in "SQL_get_password(password=password,username=username)">
    <dtml-if "_['sequence-item'].salt==_['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',first_password)">
    <dtml-call SQL_change_password>
    <h2>Your Password has been changed</h2>
    <p>Thank you.</p>
    <p><a href=<dtml-var URL2>>Back</a></p>

  <dtml-else>

    <h2>Failed</h2>
    <p>Passwords don't match</p>
    <p><a href=<dtml-var HTTP_REFERER>>Back</a></p>

  </dtml-if>

<dtml-else>

  <h2>Failed</h2>
  <p>Old Password does not match.</p>
  <p><a href=<dtml-var HTTP_REFERER>>Back</a></p>

</dtml-if>

<dtml-var standard_html_footer>

10. Set permissions to protect the use database

XXX I think the security changes in Zope 2.2 has obsoleted this step.XXX

11. Test it and move it to the desired level (e.g. root)

• test it
• make a backup of the data.fs
• login as superuser (??, dont remember if this is necessary)
• delete the root acl_users
• copy and paste the loginManager to root