Multiple MACs per Network

[John Corps]

wow i totally overlooked that, many thanks Aaran! I have it setup and
working perfectly! Many many thanks again. The sql was wrong in your
post, missing some quotes or something so the working code was, my
complete authorize section:

authorize {
	preprocess
	rewrite_calling_station_id
	rewrite_called_station_id
	if("%{sql:SELECT COUNT(*) FROM `SSIDMACAUTH` WHERE macaddress =
'%{Calling-Station-ID}' AND SSID = '%{Called-Station-SSID}'}" >= 1){
		ok
		update control {
			Auth-Type := Accept
		}
	}
	else{
		reject
	}
}

Obviously this can be optimized, the sql line, so that the update
control section doesn't need to be referenced, it can be pulled from
the table but the original sql i have is just counting the amount of
rows returned and if its more then or equal to 1, it accepts the user.

Thanks again.


On Fri, Apr 29, 2011 at 2:48 PM, Arran Cudbard-Bell
<a.cudbardb at gmail.com> wrote:
> John,
>
> To be honest its probably easier to use SQL xlat then calling the SQL module if you're just trying to determine whether a mac address is allowed to access an SSID. SQL module is meant for more complex configurations.
>
> Create a new table with two fields 'ssid' and 'macaddress'
>
> authorize {
>        preprocess
>        if(%{sql:SELECT COUNT(*) FROM `my_mac_table` WHERE macaddress = '%{Calling-Station-ID}' AND ssid = '%{Called-Station-SSID}'} >= 1}{
>                ok
>        }
>        else{
>                reject
>        }
>        rewrite_calling_station_id
>        rewrite_called_station_id
> }
>
> FYI in your example you listed sql and sql.authorize, in the authorize section they do the same thing. Modules generally perform different actions depending in the section from which they're called adding a suffix of .<section_name> overrides this and explicitly sets a section name.
>
> -Arran
>
> On Apr 29, 2011, at 11:24 AM, John Corps wrote:
>
>> Do you have an example of how to accomplish this? I have tried a lot
>> of things but can't seem to get it to work. I have this in my
>> authorize section:
>> authorize {
>>        preprocess
>>        rewrite_calling_station_id
>>        rewrite_called_station_id
>>        sql
>>        sql.authorize
>>        if(notfound){
>>                reject
>>        }
>>        else{
>>                ok
>>        }
>> }
>> Do i have to add anything else here or where do I do the check
>> attribute? I have created a new table in my db called just macauth
>> that has the same structure as the radacct table except for the
>> exception of adding an SSID field. I have tried to modify the original
>> sql for checking the radacct table to reflect the ssid table, so check
>> ssid table where macaddress is the macaddress and ssid is the ssid. I
>> am stuck here as when connecting it just shows up in debug as the user
>> was not found...
>>
>> [sql]         expand: SELECT id, macaddress, attribute, value, op
>> FROM SSIDMACAUTH           WHERE SSID = '%{Called-Station-SSID}'
>>    AND macaddress ='%{Calling-Station-ID}'           ORDER BY id ->
>> SELECT id, macaddress, attribute, value, op           FROM SSIDMACAUTH
>>          WHERE SSID = 'SSID'           AND macaddress
>> ='00-11-22-33-44-55'           ORDER BY id
>> rlm_sql_mysql: query:  SELECT id, macaddress, attribute, value, op
>>      FROM SSIDMACAUTH           WHERE SSID = 'RADIUSTEST'
>> AND macaddress ='00-11-22-33-44-55'           ORDER BY id
>> [sql]         expand: SELECT groupname           FROM radusergroup
>> WHERE username = '%{SQL-User-Name}'           ORDER BY priority ->
>> SELECT groupname           FROM radusergroup           WHERE username
>> = '00-11-22-33-44-55'           ORDER BY priority
>> rlm_sql_mysql: query:  SELECT groupname           FROM radusergroup
>>       WHERE username = '00-11-22-33-44-55'           ORDER BY
>> priority
>> rlm_sql (sql): Released sql socket id: 3
>> [sql] User 00-11-22-33-44-55 not found
>>
>> I think I am missing something here as the user is found in the db,
>> but i think it is trying to read the results from like username and
>> not macaddress. Any insight would be great, thanks.
>>
>>
>> On Thu, Apr 28, 2011 at 4:29 PM, Arran Cudbard-Bell
>> <a.cudbardb at gmail.com> wrote:
>>>
>>> On Apr 28, 2011, at 1:13 PM, John Corps wrote:
>>>
>>>> Thank you Aaran. It does indeed work. Is there an easy way of
>>>> implementing the same functionality to work with calling the
>>>> SSID.00-11-22-33-44-55 pulling from the radcheck sql table?
>>>
>>> Sure, you can use Calling-Station-SSID as a check attribute for both users and groups
>>>
>>> -Arran
>>>
>>>>
>>>> On Thu, Apr 28, 2011 at 3:27 PM, Arran Cudbard-Bell
>>>> <a.cudbardb at gmail.com> wrote:
>>>>>
>>>>> On Apr 28, 2011, at 11:54 AM, John Corps wrote:
>>>>>
>>>>>> I have done a testing environment with the Mac-Auth section from the
>>>>>> Wiki. http://wiki.freeradius.org/Mac-Auth
>>>>>>
>>>>>> Not to sure what module you would be referring to...only thing I could
>>>>>> think of is the files module?
>>>>>
>>>>> Updated the wiki page with an example, let me know if it works for you.
>>>>>
>>>>> -Arran
>>>>>
>>>>> Arran Cudbard-Bell
>>>>> RM-RF Limited - Security consultation and contracting
>>>>> VoIP: +1 916-436-1352 Cell: +44 7854041841
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> -
>>>>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>>>>>
>>>> -
>>>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>>>
>>> Arran Cudbard-Bell
>>> RM-RF Limited - Security consultation and contracting
>>> VoIP: +1 916-436-1352 Cell: +44 7854041841
>>>
>>>
>>>
>>>
>>>
>>> -
>>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>>>
>> -
>> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
> Arran Cudbard-Bell
> RM-RF Limited - Security consultation and contracting
> VoIP: +1 916-436-1352 Cell: +44 7854041841
>
>
>
>
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>