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@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@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@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