[Zope] Successful exUserFolder Plone implementation example! odbc conne ction, user authenticated in Plone ...

27 Sep 2003

      Hey all,

Wanted to share some specifics because this shouldn't have taken 3 hours!

Install exUserFolder
    Why? Want to authenticate users against a database or external source
instead of Plone or Zope native acl_users.
    How long?  Hopefully less than 1 hour with this doc!
Environment:
    Windows 2000 Server, Plone 1.0.5

(Instead of speaking generically and full of theory and foo-bar crap and "
... " everywhere we NEED the fricking code example, I am going to list EXACT
specifics for my environment.)

Specific Steps:
1) At "Root Folder" in the ZMI, add (Z ODBC Database Connection) mine is:
[bosdev_odbc]
    which connects to an Interbase (Borland) database.  (It shouldn't matter
what db you use.  It's easy to test your connection with any sql you want.

2) Download exUserFolder http://www.zope.org/Members/TheJester/exUserFolder
<http://www.zope.org/Members/TheJester/exUserFolder>  0-10-10 was latest
non-dev release.  Extract files to Product directory.  Restart Zope.

3) In ZMI, Inside my plone site [/dev], (NOT at the root), deleted the
acl_users folder.

4) In ZMI, in Plone site /dev, Added "ex User Folder"
    Choose Sources:
        Auth Source: MySql, Property Source: MySql, Membership Source:
Basic, Group: Null

   MySQL Auth: (follow your own database schema of course)
    Database conn: "bosdev_odbc"
    Table: "login_user"
    Username column: "login_name"
    Password: "webpassword"
    Roles: "description"  (not doing anything useful with this yet, but give
it a valid field.)

   Edit Basic Membership:
        fill in site name & email, checked "member" for default role

   Choose Auth Type:
    Standard basic auth (for form login)
    Cache Timeout: 1800

    After that ... /dev/acl_users in the ZMI yielded a list of 10 users ...
and "Next 10 results" 
    Had users in the ZMI, but the authentication didn't work yet.

5) How to make the Auth Work?  What is Missing?
This part wasn't documented (in readme or anywhere) w/ specifics

None of the SQL in /  <http://lemon:8238/dev/manage_workspace> dev/
<http://lemon:8238/dev/acl_users/manage_workspace> acl_users/
<http://lemon:8238/dev/acl_users/mysqlAuthSource/manage_workspace>
mysqlAuthSource (left panel in ZMI) address authorizing a user.  

[P: is the root of my plone directory.]
In P:\Data\Products\exUserFolder\mysqlAuthSource\mysqlAuthSource.py
    There's a comment about what has to be done:
 #
 # You can define this to go off and do the authentication instead of
 # using the basic one inside the User Object
 #
 remoteAuthMethod=None

6) Define remoteAuthMethod:
Where? P:\Data\Products\exUserFolder\mysqlAuthSource\mysqlAuthSource.py
Why? Because I chose "MySql" while setting up, that is the folder to modify
the mysqlAuthSource.py.  (it's in several directories)

In this file, I had to add 3 chunks of code:

6a) define the dtml method

 #
 # You can define this to go off and do the authentication instead of
 # using the basic one inside the User Object
 #
 # remoteAuthMethod=None
 def remoteAuthMethod (self, username, password):
            """ Do auth! """
     result=self.sqlAuthUser(username=username, password=password)
     return result

6b) Adding the SQL object

# Already there ...
 def delSQLQueries(self):
  sqllist=self.objectIds('Z SQL Method')
  self.manage_delObjects(ids=sqllist)

# Must add this inside the def delSQLQueries(self):
      sqlAuthUser=SQL(
       'sqlAuthUser',
       'Auth One User',
       self.connection,
       'table=%s username:string password:string'%(self.table),
       _sqlAuthUser)

      self._setObject('sqlAuthUser', sqlAuthUser)

6c) The SQL
(at the very bottom of the file)

_sqlAuthUser="""
SELECT * FROM <dtml-var table>
where <dtml-var usernameColumn>=<dtml-sqlvar username type=string>
and   <dtml-var passwordColumn>=<dtml-sqlvar password type=string>
"""

7) Save file, restart zope, test login ...

8) Fix python errors ... restart zope  :-)

9) Successful login!

10) Current Problems:
Upon logging in correctly (case-sensitive user & pass),
This site encountered an error trying to fulfill your request. The errors
were: 
Error Details 
Error Type 
sql.error
Error Value 
('42000', -104, "[Easysoft][Interbase]Dynamic SQL Error, SQL error code =
-104, Token unknown - line 2, char 28, 'key'")

But, Upon logging in 2nd time, all is well -- the user properties get set,
and the Home Directory is created.

11) 'key' is present here:
Z SQL Method /  <http://lemon:8238/dev/manage_workspace> dev/
<http://lemon:8238/dev/acl_users/manage_workspace> acl_users/
<http://lemon:8238/dev/acl_users/mysqlPropSource/manage_workspace>
mysqlPropSource/
<http://lemon:8238/dev/acl_users/mysqlPropSource/sqlInsertProperty/manage_wo
rkspace> sqlInsertProperty 
Arguments: key value username temp:int

SQL:
INSERT INTO UserProperties (prop_key, username, value, istemporary)
VALUES(<dtml-sqlvar key type=string>,
       <dtml-sqlvar username type=string>,
    <dtml-sqlvar value type=string>,
    <dtml-sqlvar temp type=int>)

So, something with my schema isn't ready for this yet.
I'll post again with the details, but I wanted to get this out there
since it seemed I wasn't the only one hurdling over this problem of getting
exUserFolder
going!

cheers,
jason

Jason LeMonier 
Software Engineer 
Retail Technologies International 
Office   916.605.7262 
Mobile  415.595.0969 
Fax      916-914-2132 
jlemonier@RetailPro.com 

I wish programmers would skip 85% of all theory and just paste snippets of
code!