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

CREATE FUNCTION simpleHash(hashthis VARCHAR(30), hashsize INT) RETURNS INT
          DECLARE hashval INT;
          DECLARE hashme VARCHAR(30);
          SET hashme = UPPER(hashthis);
          SET hashval = CONV(SUBSTR(md5(hashme),-8),16,10) % hashsize;
          RETURN hashval;


# function:  determineGroupByHash(string) returns groupname defined in 
table radhashgroup which corresponds to radgroupreply table entries

CREATE FUNCTION determineGroupByHash(client_mac VARCHAR(17), 
client_username VARCHAR(64)) RETURNS VARCHAR(64)
          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, 
          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;

# function:  determinePreferredChain(mac, user) returns preferred chain 
defined in table user_prefs
DROP FUNCTION IF EXISTS determinePreferredChain;

CREATE FUNCTION determinePreferredChain(client_mac VARCHAR(17), 
client_username VARCHAR(64)) RETURNS VARCHAR(64)
          DECLARE returnchain VARCHAR(64);
          IF EXISTS(SELECT chain FROM user_prefs WHERE (mac_address = 
client_mac AND username = client_username) LIMIT 1)
             SELECT chain INTO returnchain FROM user_prefs WHERE 
(mac_address = client_mac AND username = client_username) LIMIT 1;
             SET returnchain = 'stateful';
          END IF;
          RETURN returnchain;

# 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)
# +-----------------------------+----------+


delimiter |
CREATE FUNCTION determineGroup(client_mac VARCHAR(17), client_username 

        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)
           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
              SELECT determineGroupByHash(clean_mac, client_username) 
INTO returngroup;
           END IF;
           SELECT determineGroupByHash(clean_mac, client_username) INTO 
        END IF;
        RETURN returngroup;
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 

          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