Hash username or mac address to assign user to different vlan
John Douglass
john.douglass at oit.gatech.edu
Thu Mar 3 18:38:38 CET 2011
Here at Georgia Tech, I had to design a system to do VLAN steering based
on a number of criteria (including hashing based on MAC). Because I know
MySQL and the like MUCH better than freeradius configuration, that's
where we moved the logic to by using stored functions.
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.mayers at 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
>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: lawn-status-distribution.png
Type: image/png
Size: 31596 bytes
Desc: not available
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20110303/2b444143/attachment.png>
More information about the Freeradius-Users
mailing list