I've been using mod_auth_mysql for a year or two now and am very very happy with it. However, there is one feature I would love to see implemented.
At the moment, I can't use mod_auth_mysql for projects that require Resources to be stored in a database. Generally, the permissions on who can access the resource are stored as meta data in each record. Unfortunately, there doesn't appear to be a way to get mod_auth_mysql to query a separate table to check the permissions of a resource before serving it. For example, I would like to use mod_auth_mysql to check the users permissions against the requested resource before using mod_rewrite to direct the User to a PHP file which returns the resource.
I thought one way would be to add a config parameter like AuthMySQLGroupTable which would allow you to assign a table to perform another check against, but I realised that this could get messy very quickly. To me the ideal solution would be to specify a stored procedure for mod_auth_mysql to call:
AuthMySQLStoredProc "spMyUserAuth";
mod_auth_mysql could call the specified stored proc then pass through a IN_OUT INT parameter which the stored procedure could set to either 1 (for authorized) or any other value for failed auth. It could then be left up to the web master to implement a stored procedure that determines if the user specified is authenticated/authorized.
That way, it would be trivial to 1) authenticate the user and 2) authorize the user for access to the requested resource. Additional parameters could be passed to the specified stored procedure:
IN_OUT success INT, IN username VARCHAR(30), IN password VARCHAR(30), IN dns VARCHAR, IN ipaddress VARCHAR, IN filename VARCHAR, IN virtualhostname VARCHAR, IN protocol VARCHAR, IN requestmethod VARCHAR, IN arguments VARCHAR, IN requestline VARCHAR, IN path VARCHAR
So I could create a stored procedure like so:
CREATE PROCEDURE spMyUserAuth( IN_OUT success INT, IN username VARCHAR(30), IN password VARCHAR(30), IN dns VARCHAR, IN ipaddress VARCHAR, IN filename VARCHAR, IN virtualhostname VARCHAR, IN protocol VARCHAR, IN requestmethod VARCHAR, IN arguments VARCHAR, IN requestline VARCHAR, IN path VARCHAR )
BEGIN
END;
Obviously this would only be a feature that works with MySQL 5.
What do you guys think? Is this a good idea?
Cheers,
Ben
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
First of all, we don't get called for resources stored in a database - only resources directly accessed by Apache.
Second, we are not notified what the resourse itself is. We get the userid and password the user enters, plus some basic security information (i.e. valid users/groups, if specified). But not the resource itself.
So as you can see, this type of request is impossible. I'm afraid you'll have to find another way to do it.
Jerry
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Isn't the resource directly accessed by Apache indicated by the path name?
Example:
HTTP/1.1 GET /myresource.php?id=123
The resource identifier is the query string part. If it is possible for mod_auth_mysql to pass the path(including the query string) information through to the stored procedure, the responsibility for determining whether or not the user is authorized to access that resource would be the responsibility of the stored procedure.
I suggested that mod_auth_mysql only act on the value returned by the stored procedure, this would mean that it would not need to know what the resource is: it merely delegates this to the stored procedure. If the stored procedure returns "true", then authorisation and authentication has been given, and Apache can proceed to serve the request. If "false" or any other value is returned, then the authentication/authorization has failed.
I'm not asking that mod_auth_mysql be responsible for querying the resource itself; I want mod_auth_mysql to delegate this to the stored procedure itself. It would be the responsability of the stored procedure to determine if a) the user is authenticated and b) the user is authorized to access the resource. Either of these may return false. Only a true from both of these would continue the original request.
The point of all this is that I'm curious whether or not it would be possible to authenticate and authorize via MySQL BEFORE serving the file. Currently, authorization has to be executed in a script before fetching and returning a resource from the database. If this can be done, this would move all authorisation and authentication to occur BEFORE the PHP script is even run.
Given that all that is required to determine whether or not a user is authorized to access a resource is generally determined by examining the details of the request, and that mod_auth_mysql actually captures the bulk of this info for use in authentication lookups (the %d, %h, etc type variables), passing this info through to the stored procedure would provide all that is required to determine which resource was requested.
I'd love to hear your thoughts on this.
Cheers,
Ben
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
You can officially flame me: talk about not reading the f***ing manual!
I've just come across the line where you can specify more than one table under AuthMySQLUserTable and join them using AuthMySQLUserCondition. I could specify the tablename that contains my resources, join them using AuthMySQLUserCondition by working the %q placeholder and compare the username against the owner name of the resource that way.
I'm really sorry to have wasted your time, it appears that everything I wanted is already in place.
Thanks for the help though!
Cheers,
Ben
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Yeah, looks like it could work. After a bit more googling I realised I should be using mod_authz_dbm for this, but as it doesn't connect to MySQL, it doesn't really fit the bill.
Cheers,
Ben
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi everyone,
I've been using mod_auth_mysql for a year or two now and am very very happy with it. However, there is one feature I would love to see implemented.
At the moment, I can't use mod_auth_mysql for projects that require Resources to be stored in a database. Generally, the permissions on who can access the resource are stored as meta data in each record. Unfortunately, there doesn't appear to be a way to get mod_auth_mysql to query a separate table to check the permissions of a resource before serving it. For example, I would like to use mod_auth_mysql to check the users permissions against the requested resource before using mod_rewrite to direct the User to a PHP file which returns the resource.
I thought one way would be to add a config parameter like AuthMySQLGroupTable which would allow you to assign a table to perform another check against, but I realised that this could get messy very quickly. To me the ideal solution would be to specify a stored procedure for mod_auth_mysql to call:
AuthMySQLStoredProc "spMyUserAuth";
mod_auth_mysql could call the specified stored proc then pass through a IN_OUT INT parameter which the stored procedure could set to either 1 (for authorized) or any other value for failed auth. It could then be left up to the web master to implement a stored procedure that determines if the user specified is authenticated/authorized.
That way, it would be trivial to 1) authenticate the user and 2) authorize the user for access to the requested resource. Additional parameters could be passed to the specified stored procedure:
IN_OUT success INT, IN username VARCHAR(30), IN password VARCHAR(30), IN dns VARCHAR, IN ipaddress VARCHAR, IN filename VARCHAR, IN virtualhostname VARCHAR, IN protocol VARCHAR, IN requestmethod VARCHAR, IN arguments VARCHAR, IN requestline VARCHAR, IN path VARCHAR
So I could create a stored procedure like so:
CREATE PROCEDURE spMyUserAuth( IN_OUT success INT, IN username VARCHAR(30), IN password VARCHAR(30), IN dns VARCHAR, IN ipaddress VARCHAR, IN filename VARCHAR, IN virtualhostname VARCHAR, IN protocol VARCHAR, IN requestmethod VARCHAR, IN arguments VARCHAR, IN requestline VARCHAR, IN path VARCHAR )
BEGIN
END;
Obviously this would only be a feature that works with MySQL 5.
What do you guys think? Is this a good idea?
Cheers,
Ben
Ben,
Sorry, this isn't going to be possible.
First of all, we don't get called for resources stored in a database - only resources directly accessed by Apache.
Second, we are not notified what the resourse itself is. We get the userid and password the user enters, plus some basic security information (i.e. valid users/groups, if specified). But not the resource itself.
So as you can see, this type of request is impossible. I'm afraid you'll have to find another way to do it.
Jerry
Hi Jerry,
Isn't the resource directly accessed by Apache indicated by the path name?
Example:
HTTP/1.1 GET /myresource.php?id=123
The resource identifier is the query string part. If it is possible for mod_auth_mysql to pass the path(including the query string) information through to the stored procedure, the responsibility for determining whether or not the user is authorized to access that resource would be the responsibility of the stored procedure.
I suggested that mod_auth_mysql only act on the value returned by the stored procedure, this would mean that it would not need to know what the resource is: it merely delegates this to the stored procedure. If the stored procedure returns "true", then authorisation and authentication has been given, and Apache can proceed to serve the request. If "false" or any other value is returned, then the authentication/authorization has failed.
I'm not asking that mod_auth_mysql be responsible for querying the resource itself; I want mod_auth_mysql to delegate this to the stored procedure itself. It would be the responsability of the stored procedure to determine if a) the user is authenticated and b) the user is authorized to access the resource. Either of these may return false. Only a true from both of these would continue the original request.
The point of all this is that I'm curious whether or not it would be possible to authenticate and authorize via MySQL BEFORE serving the file. Currently, authorization has to be executed in a script before fetching and returning a resource from the database. If this can be done, this would move all authorisation and authentication to occur BEFORE the PHP script is even run.
Given that all that is required to determine whether or not a user is authorized to access a resource is generally determined by examining the details of the request, and that mod_auth_mysql actually captures the bulk of this info for use in authentication lookups (the %d, %h, etc type variables), passing this info through to the stored procedure would provide all that is required to determine which resource was requested.
I'd love to hear your thoughts on this.
Cheers,
Ben
Hi Jerry,
You can officially flame me: talk about not reading the f***ing manual!
I've just come across the line where you can specify more than one table under AuthMySQLUserTable and join them using AuthMySQLUserCondition. I could specify the tablename that contains my resources, join them using AuthMySQLUserCondition by working the %q placeholder and compare the username against the owner name of the resource that way.
I'm really sorry to have wasted your time, it appears that everything I wanted is already in place.
Thanks for the help though!
Cheers,
Ben
OK, glad to see you found a way to do it.
I hadn't thought of that - but if it works for you, great.
Yeah, looks like it could work. After a bit more googling I realised I should be using mod_authz_dbm for this, but as it doesn't connect to MySQL, it doesn't really fit the bill.
Cheers,
Ben