AD LDAP Auth without creating Auth tables

[GregD]

We want to perform AD Auth but Web2Py seems to need to create the Auth Tables OR it want to see existing Auth tables. 

Our problem is that we're using an older database Firebird 1.5 and can't seem to get Web2Py to recognize Auth tables from that database.

Is there a way to do AD Auth without Web2Py needing to see Auth Tables?  After all, the username and password is stored in AD.

Thoughts?

Greg

[Niphlod]

web2py definitely needs the auth tables to work with any auth backend (even LDAP).
Of course username and pwd are stored in AD, but AD doesn't have, e.g, the user id.
web2py uses auth_* tables for dealing with RBAC, and even if you don't need granular permissions, groups etc, it still has to map a user to a unique integer(ish) id.
Moreover, auth_* tables assure you that you won't continuously hit the auth backend once authenticated (sort of a "cached" copy of what is needed is written into the database).

You shouldn't have problems with firebird .... what is the exception your app raises when trying to create the auth tables ?

[GregD]

Niphlod,

Thanks for the response.  I understand it better now.  

web2py generates this message

DatabaseError: ('Error while executing SQL statement:\n- SQLCODE: -607\n- unsuccessful metadata update\n- Column: ID not defined as NOT NULL - cannot be used in PRIMARY KEY constraint definition', -607, 335544

I think its because its trying to create a table with ID as primary key, but it does not include NOT NULL in the creation of the table.

this is from the sql.log file:

CREATE TABLE auth_user(

    id INTEGER PRIMARY KEY,

    first_name VARCHAR(128),

    last_name VARCHAR(128),

    email VARCHAR(512),

    username VARCHAR(128),

    passwrd VARCHAR(512),

    registration_key VARCHAR(512),

    reset_password_key VARCHAR(512),

    registration_id VARCHAR(512)

);

firebird will not allow this create statement unless it has NOT NULL added.

[Niphlod]

Very strange, since fdb is included as an adapter for some time now. I guess firebird adoption is definitely not widespread.
This should be addressed in DAL at creation time.
Can you provide the syntax that web2py should use vs the sintax that it actually uses ? Maybe open a ticket on google code so it doesn't get lost.

[GregD]

Niphlod,

I manually defined the auth tables.  However, system is now saying auth_user already exist.

<class 'fdb.fbcore.DatabaseError'> ('Error while executing SQL statement:\n- SQLCODE: -607\n- unsuccessful metadata update\n- Table AUTH_USER already exists', -607, 335544351)

Version

web2py™ Version 2.7.4-stable+timestamp.2013.10.14.15.16.29

Python Python 2.7.5: c:\python27\python.exe (prefix: c:\python27)

Traceback

1.

2.

3.

4.

5.

6.

7.

8.

9.

10.

11.

12.

13.

14.

15.

16.

17.

18.

19.

20.

21.

22.

23.

Traceback (most recent call last):

  File "C:\web2py_src\web2py\gluon\restricted.py", line 217, in restricted

    exec ccode in environment

  File "C:/web2py_src/web2py/applications/unionhall/models/db.py", line 19, in <module>

    primarykey=['id']

  File "C:\web2py_src\web2py\gluon\dal.py", line 7935, in define_table

    table = self.lazy_define_table(tablename,*fields,**args)

  File "C:\web2py_src\web2py\gluon\dal.py", line 7972, in lazy_define_table

    polymodel=polymodel)

  File "C:\web2py_src\web2py\gluon\dal.py", line 1002, in create_table

    self.create_sequence_and_triggers(query,table)

  File "C:\web2py_src\web2py\gluon\dal.py", line 3684, in create_sequence_and_triggers

    self.execute(query)

  File "C:\web2py_src\web2py\gluon\dal.py", line 1836, in execute

    return self.log_execute(*a, **b)

  File "C:\web2py_src\web2py\gluon\dal.py", line 1830, in log_execute

    ret = self.cursor.execute(command, *a[1:], **b)

  File "c:\python27\lib\site-packages\fdb-1.4-py2.7.egg\fdb\fbcore.py", line 3323, in execute

    self._ps._execute(parameters)

  File "c:\python27\lib\site-packages\fdb-1.4-py2.7.egg\fdb\fbcore.py", line 3024, in _execute

    "Error while executing SQL statement:")

DatabaseError: ('Error while executing SQL statement:\n- SQLCODE: -607\n- unsuccessful metadata update\n- Table AUTH_USER already exists', -607, 335544351)

Error snapshot  help

<class 'fdb.fbcore.DatabaseError'>(('Error while executing SQL statement:\n- SQLCODE: -607\n- unsuccessful metadata update\n- Table AUTH_USER already exists', -607, 335544351))

I'm really close to finishing this up.  Just need to get auth working correctly, replace the password field since its a reserved word in Firebird.  Also need to figure out what to do with the Role field in auth_group because firebird used "role" for something.  I had to create field called au_group_role.  But don't know how to integrate that into web2py auth scheme.

db.define_table('auth_user',

                Field('id', 'integer', notnull=True),

                Field('first_name', 'string', length=128),

                Field('last_name', 'string', length=128),

                Field('email', 'string', length=512),

                Field('registration_key', 'string', length=512),

                Field('reset_password_key', 'string', length=512),

                primarykey=['id']

                )

db.define_table('auth_group',

                Field('id', 'integer', notnull=True),

                Field('au_grp_role', 'string', length=512),

                Field('description', 'text'),

                primarykey=['id'])

attempting to use this line to deal with auth_user.password field

auth.settings.password_field = 'passwrd' # In Firebird password is a reserved word; so using passwrd instead

What can I do about auth_group.role?  

Thoughts?

[Niphlod]

if "password" and "role" are reserved keywords, then there is a huge issue because what needs to be changed is how auth creates the scheme by default (and I don't see it happening in web2py for a small percentage of firebird users)...

One thing at a time though. We need to figure out the problem when creating the id (assuming your initial report still stands)

BTW

Field('id', 'integer', notnull=True),

should be
Field('id', 'id', notnull=True)
so you can skip the primarykey definition.

Once that works fine, please send out the sql.log generated, so we can fix the 'id' creation at DAL side: if you're correct about the report, then all tables created by DAL can't work (because every table created by DAL by default has a PK defined as 'id').

Then, I think you can use the super-experimental new feature in dal that is called "rname".
Basically the "model" of the table remains fixed, but the name of the table (or of the fields) are "translated" to rname when talking to the backend.
Currently it's tested only in SQLite, Postgresql, Mysql and MSSQL.

That being said, what you need practically is to replace any field that is a "reserved keyword" in Firebird with something that isn't, passing the name of the field. You can try passing the already quoted "password" string

Field('password', ....., rname='"password"')

or choose a totally different one, such as

Field('password', ....., rname='"a_totally_different_one"')
(watch out the quotes..... there are two single quotes to delimit the variable name in python.... the variable is the name of the column surrounded by double quotes, that are passed as they are to the backend)

DAL will still work with this table, but when asking for

db.table.password == 'a'

what will happen is

WHERE "a_totally_different_one" = 'a'


BTW: why the need to work with firebird 1.6 since 2.5.2 is out ?
One thing is trying to access a legacy table.....but why bother creating tables for the app in a really old backend that is only going to provide more and more headaches?

[GregD]

Niphlod,

Thanks for all your help.  We have the app working now.  To answer your questions:

1.  yes, password and role are reserved words in Firebird 1.5; Not sure about 2.x version though

2.  I can see uses for rname down the road.  Glad its in the works.

3.  We're using Firebird 1.5 because its the customer's production database system.  They haven't upgraded to the latest version.  

4.  Our new approach was to create two DALs db and db2.  db is sqlite and will take care of requirement to implement Auth.  db2 is the connection to firebird 1.5.  We will create forms and query logic around the db2 connection.

This was a good project; it taught us a lot about Auth.

Thanks,