Hi,
I just finished coding a PEAR Auth container module that authenticates against the MySource Matrix user base. MySource Matrix stores its user base in the sq_asset table with links into that table from the sq_asset_attribute and other related tables, so it doesn’t look like a standard set of tables that the PEAR Auth DB container can authenticate against.
This should allow other systems that use the PEAR Auth module (eg: YaWiki) to authenticate against the MySource Matrix user base.
You can install the code (assuming you have PEAR) as follows:
pear install http://www.babel.com.au/d.php/auth_container_matrix-0.1.tgz
There is a test harness for it in the tgz file, I have done some limited testing but consider this alpha code at the moment.
Del
Right, its installed. Here's my initial thoughts:
Your process seems back-to-front as well. I'd consider doing it this way:
- Find the Asset ID for the given username and check that the status is >= 16 (which means the user asset is live):
- Check that the type_code from Step 1 is inherited from the User asset type: [sql]SELECT type_code FROM sq_asset_type_inherited WHERE inherited_type_code LIKE type_code_from_step_one[/sql]
(Only if User is one of the results, should you continue).
- Get the password attribute for that asset type from the sq_asset_attribute table: [sql]SELECT attributeid FROM sq_asset_attribute WHERE type_code LIKE type_code_of_user_asset_found_in_step_1 AND type LIKE 'password'[/sql]
- Get the specific attribute from the sq_asset_attribute_value table based on the Asset ID of the User and the Attribute ID: [sql]SELECT custom_value FROM sq_asset_attribute_value WHERE assetid = step_one AND attributeid = step_two[/sql]
That'll give you the specific password for any user type and any future user type as well, without having to change your code.
Hope that helps.
No more posts with the word "yummy" in it
Well, the yummy is gone. Oh, wait…
[quote]Right, its installed. Here's my initial thoughts:
Your process seems back-to-front as well. I'd consider doing it this way:
- Find the Asset ID for the given username and check that the status is >= 16 (which means the user asset is live):
- Check that the type_code from Step 1 is inherited from the User asset type: [sql]SELECT type_code FROM sq_asset_type_inherited WHERE inherited_type_code LIKE type_code_from_step_one[/sql]
(Only if User is one of the results, should you continue).
- Get the password attribute for that asset type from the sq_asset_attribute table: [sql]SELECT attributeid FROM sq_asset_attribute WHERE type_code LIKE type_code_of_user_asset_found_in_step_1 AND type LIKE 'password'[/sql]
- Get the specific attribute from the sq_asset_attribute_value table based on the Asset ID of the User and the Attribute ID: [sql]SELECT custom_value FROM sq_asset_attribute_value WHERE assetid = step_one AND attributeid = step_two[/sql]
That'll give you the specific password for any user type and any future user type as well, without having to change your code. Hope that helps.[/quote]
I had thought about doing it that way around, although I hadn't spotted the asset_type_inherited table. I was trying to do things with as few database queries as possible, while still keeping things generic (I could have made all sorts of assumptions about the IDs but I assume those would have been invalid in the generic case).
A couple of things that threw me along the way:
- The owning_attributeid in the sq_asset_attribute_unique_value table points back to an attribute with a type code of user, even when the asset is of an inherited type (eg: backend_user). The attributeid values in sq_asset_attribute_value point back to the correct asset type code. So looking for user names means you only have to find the attribute with a name of username and a type code of user even if you're looking for a descendent of the user type. Hence my step 1.
- Since the only place where you can find usernames is in the sq_asset_attribute_unique_value table, it makes sense to look there in step 2, regardless of what you do in step 1. It seems like if you went through the process of finding the correct type code for the asset and using that to look in sq_asset_attribute_unique_value, you wouldn't find it, you'd have to use the type code = user.
- The status codes in the sq_asset table weren't obvious. Knowing that >=16 means live is a help.
- It's not obvious how to find an asset ID from a user name. Obviously you have to go to the sq_asset_attribute_unique_value table, looking for a custom_value. I guess I make the assumption that a custom_value matching the user name only works if the owning_attributeid matches whatever the attribute id of a user name is.
- Didn't want to make the assumption that every attribute of type password in sq_asset_attribute_value was actually a user's password. So I used the attribute name rather than the attribute type, after fetching all of the attributes.
I think my code works for all descendents of asset type user because of the fact that the attribute type in the unique table appears to be constant for all of those types. Once I've found the asset ID I no longer care what the asset type is, provided it has a username.
One more complicated question:
Why do you use a password type of crypt rather than MD5? crypt is specific to Unix, and in fact specific to which flavour of unix you use. MD5 would be more cross platform.
I have some code that does challenge-handshake authentication of usernames and passwords, but relies on the database storing MD5 password hashes rather than crypt type ones (it does some MD5 stuff in JavaScript at the client end). It'd be nice to integrate that into MySource but with the current password storage mechanism that's not feasible.
Mmm… and now I hand over to the real developers. Though I believe I asked your MD5 related question myself in the beginning.
Which is true, because the attribute actually comes from the user asset – the assets that inherit from user, inherit its attribute types as well.
[quote]* Since the only place where you can find usernames is in the sq_asset_attribute_unique_value table
- It's not obvious how to find an asset ID from a user name.[/quote]
Actually, the username is the "name" field in sq_asset as well.
[sql]SELECT assetid FROM sq_asset WHERE name LIKE 'username';[/sql]
[quote]* Didn't want to make the assumption that every attribute of type password in sq_asset_attribute_value was actually a user's password.[/quote]
True, that's why you want to find the password attribute and then do a select on the value table based on the assetid of the user asset as well as the attributeid of the password field.
[quote]* The status codes in the sq_asset table weren't obvious. Knowing that >=16 means live is a help.[/quote]
You can check core/include/init.inc for the various constant values – the status codes are in there too.
Hope that helps.
[quote]Actually, the username is the "name" field in sq_asset as well.
[sql]SELECT assetid FROM sq_asset WHERE name LIKE 'username';[/sql] :)[/quote]
No, it doesn't appear to be, at least not in my running copy.
In sq_asset.name I get the user's full name (eg: "D Elson") rather than their normal login name ("del").
Crap, that's what I get for testing too quickly. Sorry, mate – you're right. The username is only stored like I said it was when there is no First/Last Name configured as well.
Just released an updated version:
http://www.babel.com.au/d.php/auth_container_matrix-0.2.tgz
[quote]1. Find the Asset ID for the given username and check that the status is >= 16 (which means the user asset is live).[/quote]
Done.
[quote]2. Check that the type_code from Step 1 is inherited from the User asset type: [sql]SELECT type_code FROM sq_asset_type_inherited WHERE inherited_type_code LIKE type_code_from_step_one[/sql]
(Only if User is one of the results, should you continue).[/quote]
Done.
[quote]3. Get the password attribute for that asset type from the sq_asset_attribute table: [sql]SELECT attributeid FROM sq_asset_attribute WHERE type_code LIKE type_code_of_user_asset_found_in_step_1 AND type LIKE 'password'[/sql]
3. Get the specific attribute from the sq_asset_attribute_value table based on the Asset ID of the User and the Attribute ID: [sql]SELECT custom_value FROM sq_asset_attribute_value WHERE assetid = step_one AND attributeid = step_two[/sql][/quote]
I've left this the way it was – extract all of the attributes then figure out later which one is the password. It does save a few database hits, and in any case all of the attributes are potentially required by the PEAR object, so we may as well fetch them in one hit and search them later. I've explained this a bit more in the code.
[quote]That'll give you the specific password for any user type and any future user type as well, without having to change your code. Hope that helps.[/quote]
I still believe that I have that covered – it should work for any descendent of the user type, although now I'm explicitly checking in the inherit table, and also bailing if the user's status is < 16.
Given a username (in this case 'root'), you can use the following query to find the assetid of a user with the same login name. Note that it has sub-query as well and seems a little long because we dont make any assumptions about the attributeids in the system (as these can change system to system).
SELECT a.assetid
FROM sq_asset a, sq_asset_attribute at, sq_asset_attribute_value av
WHERE
a.type_code IN (SELECT type_code
FROM sq_asset_type_inherited
WHERE inherited_type_code = 'user')
AND at.name = 'username'
AND at.attributeid = av.attributeid
AND av.assetid = a.assetid
AND av.custom_value = 'root';Then you get an assetid, say '5' (the root user). To get the password, you can use the query:
SELECT av.custom_value
FROM sq_asset_attribute at, sq_asset_attribute_value av
WHERE
at.name = 'password'
AND at.attributeid = av.attributeid
AND av.assetid = '5'Of course, you can sub-query the first into the second so you only have one go. Then you can compare the passwords using the crypt function as shown in core/assets/users/user/user.inc (function comparePassword).
Why dont we use MD5's? I don't know. Blair is probably the best person to answer that as he made that decision before I got here. I'd suggest it wasn't based on any sort of logic.
It would be a pain to convert over current systems so I cant see it changing too soon (unless all our current systems use the MD5 implementation of crypt now - or only have one user account :)).
[quote]
SELECT a.assetid[/quote]
FROM sq_asset a, sq_asset_attribute at, sq_asset_attribute_value av
WHERE
a.type_code IN (SELECT type_code
FROM sq_asset_type_inherited
WHERE inherited_type_code = ‘user’)
AND at.name = ‘username’
AND at.attributeid = av.attributeid
AND av.assetid = a.assetid
AND av.custom_value = ‘root’;
Nice in theory, but doesn’t work on MySQL v3.x.x which doesn’t support nested SELECTs.
I know. You just do the nested select and then put the results into the first query.
New release:
pear install http://www.babel.com.au/d.php/auth_container_matrix-0.3.tgz
This does most of the lookups via the sq_asset table now, and reduces the number of SELECTs to two (by judicious juggling of the above query statements).
Note that I'm still taking the approach of fetching all of the attributes before testing the password, again in an effort to reduce the number of database hits, because packages that use PEAR Auth will most likely need them all fetched anyway.
I have now successfully set up YaWiki to authenticate against MySource Matrix using this module. YaWiki uses PEAR Auth to authenticate, and so it's a relatively simple job to change the Yawp.conf.php file to get it to work.
My values:
[Auth]
container = Matrix
expire = 7200
idle = 1800
%AUTH_IDLED% = Your session has been idle for too long. Please sign in again.
%AUTH_EXPIRED% = Your session has expired. Please sign in again.
%AUTH_WRONG_LOGIN% = You provided an incorrect username or password. Please try again.
# DB container options for Auth
[Auth_Matrix]
dsn = mysql://readonly:readonlypass@localhost/matrix
usernamecol = username
passwordcol = password(Note that the Auth container only needs read only access to the database, so I have set up a user with SELECT only privilege on the matrix tables).
I can't seem to get YaWiki to install properly – I just get a blank page when I try and load it. This is not with a Matrix Auth container … just the default install.
Edited to add - D'oh! Its working now.