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.cudba...@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.cudba...@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.cudba...@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
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
