Sure no problem...I have made it so it is very easy to see who's mac belongs to who..
-- -- Table structure for table `MACAUTH` -- CREATE TABLE IF NOT EXISTS `MACAUTH` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `macaddress` varchar(64) NOT NULL, `SSID` varchar(255) NOT NULL, `CLIENTSNAME` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; On Mon, May 2, 2011 at 3:43 PM, Arran Cudbard-Bell <[email protected]> wrote: > 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 >> <[email protected]> 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 >>>> <[email protected]> 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 >>>>>> <[email protected]> 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 > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
