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-
