Multiple MACs per Network
Arran Cudbard-Bell
a.cudbardb at gmail.com
Mon May 2 21:43:41 CEST 2011
Yeah I missed out a bunch of things, well done for figuring it out .
Would you mind dumping out the schema of your table, and I can add it and the below snippet to the wiki for future users?
Thanks,
Arran
On May 2, 2011, at 6:51 AM, John Corps wrote:
> 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
>>
>
> -
> 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
More information about the Freeradius-Users
mailing list