httpd-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tom Donovan <donov...@bellatlantic.net>
Subject Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)
Date Sun, 17 May 2009 12:50:53 GMT
KaiGai Kohei wrote:
> Tom Donovan wrote:
>> KaiGai Kohei wrote:
>>> I'm now trying to set up mod_authn_dbb for authentication purpose.
>>> However, I faced to a concern for AuthDBDUserRealmQuery directive.
>>>
>>> The example shows the query:
>>>   AuthDBDUserRealmQuery \
>>>       "SELECT password FROM authn WHERE user = %s AND realm = %s"
>>>
>>> But, I would like to set up the query as follows:
>>>   AuthDBDUserRealmQuery \
>>>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount 
>>> WHERE uname = %s"
>>>                                   ^^... to be realm                to 
>>> be user ... ^^
>>>
>>> It seems to me we have no way to put the replacement of the given
>>> realm prior to username. Am I missing anything?
>>>
>>
>> One common solution to the 'order of parameters' problem is to create 
>> a stored procedure in your database.  For example, if you are using 
>> MySQL 5.0+, you can create a stored procedure like this:
>>
>>   DROP PROCEDURE IF EXISTS digest;
>>   CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
>>     SELECT md5(concat(uname ,':',realm ,':',upass)) FROM uaccount 
>> WHERE uname = username;
>>
>> Then in your conf file use:
>>
>>   AuthDBDUserRealmQuery "CALL digest(%s, %s)"
> 
> Thanks for your idea.
> 
> But it still remains a matter for me.
> The mod_authn_dbd allows to export extra fields as environment variables
> with AUTHENTICATE_<field name>. But SQL function (generically) returns
> one-dimensional value, so this idea cannot allow to return anything related
> to authenticated users except for hash-value.
> 
> What I would like to do is to fetch a security context to be assigned to 
> users.
> My mod_selinux module enables to assign a security context prior to 
> invocation
> of contents handler based on a certain environment variable. So, I would 
> like
> to fetch an extra information related to authorized user.
> For the purpose, we need more flexibility to place parameters in query.
> 
> Thanks,

Yes, SQL *functions* only return a single value - but if your database supports SQL *stored

procedures* (like the example), they return a set of rows; including any extra values to be
assigned 
to environment variables.  For example:

    DROP PROCEDURE IF EXISTS digest;
    CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
      SELECT md5(concat(uname,':', realm ,':', upass)), uctx AS CONTEXT, uexpiration AS EXPIRES
      FROM uaccount WHERE uname = username;

When httpd executes the CALL statement from:

    AuthDBDUserRealmQuery "CALL digest(%s, %s)"

this will authenticate the user, and if successful - it will also set the two httpd environment

variables AUTHENTICATE_CONTEXT and AUTHENTICATE_EXPIRES to values from the database.

Stored procedures are available in MySQL, Oracle, and several other databases - but some databases,

like PostgreSQL and SQLite, do not support them.

-tom-

Mime
View raw message