Multiple users mysql backend

[Collen Blijenberg]

>
> G'day Collen,
>
> Can you post any and all SQL queries you see in the output of radiusd -X?
>
> Cheers, 

Sure, here they come:
i just posted a view, coz it goes on and on,
basicly it it saids "No matching entry in the database for request from 
user [gebruiker]"
if i change the "==" to ":= " in "Calling-Station-Id" it will pass, and 
i get in.
but then the Calling-Station-Id/mac address will not be checked!!!!!!
-------------- 1) startup
Module: Loaded SQL
 sql: driver = "rlm_sql_mysql"
 sql: server = "127.0.0.1"
 sql: port = ""
 sql: login = "radius"
 sql: password = "radius"
 sql: radius_db = "radius"
 sql: nas_table = "nas"
 sql: sqltrace = yes
 sql: sqltracefile = "/usr/local/freeradius/var/log/radius/sqltrace.sql"
 sql: readclients = yes
 sql: deletestalesessions = yes
 sql: num_sql_socks = 5
 sql: sql_user_name = "%{User-Name}"
 sql: default_user_profile = ""
 sql: query_on_not_found = no
--------------
> rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, 
> op           FROM radcheck           WHERE Username = 
> 'gebruiker'           ORDER BY id
> radius_xlat:  'SELECT 
> radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
> FROM radgroupcheck,usergroup
>
> WHERE usergroup.Username = 'gebruiker' AND usergroup.GroupName = 
> radgroupcheck.GroupName ORDER BY radgroupcheck.id'
> rlm_sql_mysql: query:  SELECT 
> radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
> FROM
>
> radgroupcheck,usergroup WHERE usergroup.Username = 'gebruiker' AND 
> usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op           
> FROM radreply           WHERE Username = 'gebruiker'           ORDER 
> BY id'
> rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, 
> op           FROM radreply           WHERE Username = 
> 'gebruiker'           ORDER BY id
> radius_xlat:  'SELECT 
> radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
> FROM radgroupreply,usergroup
>
> WHERE usergroup.Username = 'gebruiker' AND usergroup.GroupName = 
> radgroupreply.GroupName ORDER BY radgroupreply.id'
> rlm_sql_mysql: query:  SELECT 
> radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
> FROM
>
> radgroupreply,usergroup WHERE usergroup.Username = 'gebruiker' AND 
> usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
> rlm_sql (sql): Released sql socket id: 3
> rlm_sql (sql): No matching entry in the database for request from user 
> [gebruiker]
>   modcall[authorize]: module "sql" returns notfound for request 0
---------------
> radius_xlat:  'gebruiker'
> rlm_sql (sql): sql_set_user escaped user --> 'gebruiker'
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op           
> FROM radcheck           WHERE Username = 'gebruiker'           ORDER 
> BY id'
> rlm_sql (sql): Reserving sql socket id: 2
> rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, 
> op           FROM radcheck           WHERE Username = 
> 'gebruiker'           ORDER BY id
> radius_xlat:  'SELECT 
> radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
> FROM radgroupcheck,usergroup
>
> WHERE usergroup.Username = 'gebruiker' AND usergroup.GroupName = 
> radgroupcheck.GroupName ORDER BY radgroupcheck.id'
> rlm_sql_mysql: query:  SELECT 
> radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
> FROM
>
> radgroupcheck,usergroup WHERE usergroup.Username = 'gebruiker' AND 
> usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op           
> FROM radreply           WHERE Username = 'gebruiker'           ORDER 
> BY id'
> rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, 
> op           FROM radreply           WHERE Username = 
> 'gebruiker'           ORDER BY id
> radius_xlat:  'SELECT 
> radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
> FROM radgroupreply,usergroup
>
> WHERE usergroup.Username = 'gebruiker' AND usergroup.GroupName = 
> radgroupreply.GroupName ORDER BY radgroupreply.id'
> rlm_sql_mysql: query:  SELECT 
> radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
> FROM
>
> radgroupreply,usergroup WHERE usergroup.Username = 'gebruiker' AND 
> usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
> rlm_sql (sql): Released sql socket id: 2
> rlm_sql (sql): No matching entry in the database for request from user 
> [gebruiker]
>   modcall[authorize]: module "sql" returns notfound for request 1
--------------
> radius_xlat:  'gebruiker'
> rlm_sql (sql): sql_set_user escaped user --> 'gebruiker'
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op           
> FROM radcheck           WHERE Username = 'gebruiker'           ORDER 
> BY id'
> rlm_sql (sql): Reserving sql socket id: 1
> rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, 
> op           FROM radcheck           WHERE Username = 
> 'gebruiker'           ORDER BY id
> radius_xlat:  'SELECT 
> radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
> FROM radgroupcheck,usergroup
>
> WHERE usergroup.Username = 'gebruiker' AND usergroup.GroupName = 
> radgroupcheck.GroupName ORDER BY radgroupcheck.id'
> rlm_sql_mysql: query:  SELECT 
> radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  
> FROM
>
> radgroupcheck,usergroup WHERE usergroup.Username = 'gebruiker' AND 
> usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id
> radius_xlat:  'SELECT id, UserName, Attribute, Value, op           
> FROM radreply           WHERE Username = 'gebruiker'           ORDER 
> BY id'
> rlm_sql_mysql: query:  SELECT id, UserName, Attribute, Value, 
> op           FROM radreply           WHERE Username = 
> 'gebruiker'           ORDER BY id
> radius_xlat:  'SELECT 
> radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
> FROM radgroupreply,usergroup
>
> WHERE usergroup.Username = 'gebruiker' AND usergroup.GroupName = 
> radgroupreply.GroupName ORDER BY radgroupreply.id'
> rlm_sql_mysql: query:  SELECT 
> radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  
> FROM
>
> radgroupreply,usergroup WHERE usergroup.Username = 'gebruiker' AND 
> usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id
> rlm_sql (sql): Released sql socket id: 1
> rlm_sql (sql): No matching entry in the database for request from user 
> [gebruiker]
>   modcall[authorize]: module "sql" returns notfound for request 2
--------------

--------------
James Wakefield wrote:
> Collen Blijenberg wrote:
>> I'm still looking for a good solution, authenticating multiple users 
>> with the
>> same login name and with mysql as backend.
>>
>> in the mysql table 'radcheck' i have entered the following:
>>
>> UserName      Attribute                      op      Value
>> gebruiker         Calling-Station-Id         ==      00166f980e78
>> gebruiker         NT-Password               :=      <NT-HASH>
>> gebruiker         Calling-Station-Id         ==      00166f97d99d
>> gebruiker         NT-Password               :=      <NT-HASH>
>>
>> it does work with the 'Users' plain text file.
>> but as soon as i turn to mysql, it failles... ?!
>> dunno is there some bug, or incomplete code, for the mysql backend ?
>