#14 Stored Procedure

closed
nobody
None
5
2007-10-11
2007-03-19
Ben Davies
No

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

Discussion

  • Jerry Stuckle

    Jerry Stuckle - 2007-10-11

    Logged In: YES
    user_id=1049703
    Originator: NO

    Authorization of resources stored in a database is not possible with mod_auth_mysql. These resources are not authenticated by Apache, and therefore there is no way mod_auth_mysql will be called for authentication.

    Additionally, calling stored procedures could significantly slow the system down, and would not be recommended.

     
  • Jerry Stuckle

    Jerry Stuckle - 2007-10-11
    • status: open --> closed
     

Log in to post a comment.