This system also has the ability to disable a given VLAN (in the event of catastrophic system/network issues) by the inclusion of a new table called "radhashgroup" which looks like:
mysql> select * from radhashgroup +----+-----------+---------------+---------+ | id | groupname | chain | status | +----+-----------+---------------+---------+ | 1 | vlan1296 | authenticated | ACTIVE | | 2 | vlan1296 | stateful | STANDBY | | 3 | vlan0316 | authenticated | STANDBY | | 4 | vlan0316 | stateful | STANDBY | | 6 | vlan0808 | stateful | ACTIVE | | 7 | vlan1312 | stateful | ACTIVE | +----+-----------+---------------+---------+In the above scheme, we have two kinds of networks for WPA, one with stateful packet inspection (no inbound without an outbound request) called "stateful" and the other which has the inbound firewall turned off, historically called "authenticated".
We also have a new table that allows people to request to be put into one group or the other (in case there are more than one "authenticated" vlans in the future) with the table "user_prefs":
+----+--------------+-------------------+---------------+ | id | username | mac_address | chain | +----+--------------+-------------------+---------------+ | 3 | aa66617 | 58:b0:35:67:55:9b | authenticated | | 6 | rdearux3 | 00:aa:5e:38:4b:6e | authenticated | | 11 | frapp66 | 00:1f:ff:d8:bc:ff | authenticated | | 8 | snark340 | 00:03:cc:12:92:54 | authenticated | The system has the following features:1) VLAN based steering based on username only, mac address only, username and mac address pairing (this is based on "priority" which I believe is explained in my SQL logic better) 2) VLAN steering based on type of connection (inbound security on or off) that still utilizes the MAC based vlan hashing 2) The ability to turn off a given "group" or in our case "vlan" if something really really bad happens. Granted, this will require the user to re-authenticate to get the new VLAN, but it's better than a longer term system outage. I have NEVER had to use this (knock on wood) yet. :)
As you can see, we have two "stateful" networks and one "authenticated" network. The above table allows us to add new networks in as needed and as we migrate our captive portal IP ranges to GTwpa as we encourage it's use over the old way.
I did have to modify the radusergroup table slightly so that it looks like: +-----+-------------------+-------------------+-----------+-----------+----------+--------------------------+| id | username | mac_address | source_ap | groupname | priority | comment |
+-----+-------------------+-------------------+-----------+-----------+----------+--------------------------+| 123 | equevedo3 | 00:14:d1:c8:02:ec | | vlan0316 | 100 | block_id:3258 | | 253 | inorris3 | | | vlan0316 | 100 | block_id:3697 | | 223 | | 00:aa:c6:d0:bf:ff | | vlan1987 | 200 | tablet 1 |
We have two radius servers and I use the id to keep a central table in sync with the outlying tables on either radius server. The priority comes in when we talk about which VLAN preference takes priority.
The MySQL functions that I designed look as follows: # Separate file of the functions we use for WPA # # Instantiate by: # # mysql -u radius -p radius_wpa < wpa-db-functions.sql # ### # We need to use the radius database :) # use radius_wpa; ### # Add our custom MySQL stored procedures # #### function: simpleHash(string, number of buckets) returns <0 - (number of buckets - 1)>
#
DROP FUNCTION IF EXISTS simpleHash;
DELIMITER |
CREATE FUNCTION simpleHash(hashthis VARCHAR(30), hashsize INT) RETURNS INT
DETERMINISTIC
BEGIN
DECLARE hashval INT;
DECLARE hashme VARCHAR(30);
SET hashme = UPPER(hashthis);
SET hashval = CONV(SUBSTR(md5(hashme),-8),16,10) % hashsize;
RETURN hashval;
END|
DELIMITER ;
###
# function: determineGroupByHash(string) returns groupname defined in
table radhashgroup which corresponds to radgroupreply table entries
# DROP FUNCTION IF EXISTS determineGroupByHash; DELIMITER |CREATE FUNCTION determineGroupByHash(client_mac VARCHAR(17), client_username VARCHAR(64)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE hashval INT;
DECLARE hashsize INT;
DECLARE chain_pref VARCHAR(32);
DECLARE returngroup VARCHAR(64);
DECLARE rownum INT;
SET @rownum = -1;
SET chain_pref = determinePreferredChain(client_mac,
client_username);
SELECT count(*) INTO hashsize FROM radhashgroup WHERE status =
'ACTIVE' AND chain = chain_pref;
SET hashval = simpleHash(client_mac, hashsize);
SELECT r1.groupname INTO returngroup FROM (SELECT
@rownum:=@rownum+1 AS hash_value, groupname FROM radhashgroup WHERE
status = 'ACTIVE' AND
chain = chain_pref ORDER BY groupname ASC) as r1 WHERE
hash_value = hashval;
RETURN returngroup;
END|
DELIMITER ;
###
# function: determinePreferredChain(mac, user) returns preferred chain
defined in table user_prefs
# DROP FUNCTION IF EXISTS determinePreferredChain; DELIMITER |CREATE FUNCTION determinePreferredChain(client_mac VARCHAR(17), client_username VARCHAR(64)) RETURNS VARCHAR(64)
DETERMINISTIC
BEGIN
DECLARE returnchain VARCHAR(64);
IF EXISTS(SELECT chain FROM user_prefs WHERE (mac_address =
client_mac AND username = client_username) LIMIT 1)
THEN
SELECT chain INTO returnchain FROM user_prefs WHERE
(mac_address = client_mac AND username = client_username) LIMIT 1;
ELSE
SET returnchain = 'stateful';
END IF;
RETURN returnchain;
END|
DELIMITER ;
###
# function
#
# | Criteria | Priority |
# +-----------------------------+----------+
# | User or MAC Blocks | 100 | -> blocks should direct
user to one of the linux FWs captive portal
# | MAC assignments | 200 | -> should be used SPARINGLY
and only by admins
# | Username + MAC assignments | 300 | -> user preferences for a
specific device (this device when used by this user)
# | Username assignments | 400 | -> user preference for
(this user, all devices)
# +-----------------------------+----------+ DROP FUNCTION IF EXISTS determineGroup; delimiter |CREATE FUNCTION determineGroup(client_mac VARCHAR(17), client_username VARCHAR(64)) RETURNS VARCHAR(64)
BEGIN
DECLARE returngroup VARCHAR(64);
DECLARE clean_mac VARCHAR(17);
SET clean_mac = REPLACE(LOWER(client_mac),'-',':');
IF EXISTS(SELECT groupname FROM radusergroup WHERE (mac_address
= clean_mac OR username = client_username) ORDER BY priority LIMIT 1)
THEN
SELECT groupname INTO returngroup FROM radusergroup \
WHERE ((username = client_username OR mac_address =
clean_mac) AND priority = 100) \
OR (mac_address = clean_mac AND priority = 200) \
OR (username = client_username AND mac_address = clean_mac
AND priority = 300) \
OR (username = client_username AND priority = 400) \
OR (username = 'DEFAULT') \
ORDER BY priority ASC LIMIT 1;
IF returngroup IS NULL
THEN
SELECT determineGroupByHash(clean_mac, client_username)
INTO returngroup;
END IF;
ELSE
SELECT determineGroupByHash(clean_mac, client_username) INTO
returngroup;
END IF;
RETURN returngroup;
END|
delimiter ;
What we have found is that the basic hash of the MAC address for MOST
general users, results in a practically 50/50 split between the two
"stateful" networks. (For our networks these are the 1312 and the 808
networks for the average users).
My Freeradius sql configuration uses these procedures for assignments as such:
group_membership_query = "SELECT determineGroup('%{Calling-Station-Id}','%{SQL-User-Name}') as groupname";
Again, perhaps this isn't ideal, and I wish my knowledge of ulang was better. :) But...it's been fantastically successful, modular, and allowed us to do some very...interesting things with our networks. ;)
Thanks to all you guys on the list and especially Alan DeKok who's posts are most informative and often entertaining :)
Attached is an example image of our VLAN distribution graph. it should show that the WPA users are pretty much distributed evenly between the two stateful networks given the simpleHash algorithm.
Just my $0.02 of systems design. But it works :) Enjoy the day! On 02/18/2011 11:55 AM, Alexander Clouter wrote:
Phil Mayers<p.may...@imperial.ac.uk> wrote:How do you deal with excessive broadcast protocols?We do nothing. We used to be very worried about this, but in practice we've found it's a non-existent problem. The world isn't 10Mbit/half-duplex ethernet any more ;o)...it supposedly nukes the ability for workstations to do power saving (broadcast packets are always processed kernel land) and as broadcast/multicast is limited to the lowest common denominator speed for wireless network's, it can have a pained effect (no doubt most places are still set at 1Mbps). 500 Windows workstations on my local LAN would annoy my NO_HZ laptop :) Cheers
<<attachment: lawn-status-distribution.png>>
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
