using unlang to call a stored procedure
Hi, I've written a mysql stored procedure that accepts 2 arguments, the nas-ip address of one of our (HP) switches and the calling station Id of a network client ( it's a MAC auth so the User-Name=Calling-Station-Id below). The procedure then queries various back end database tables to figure out which vlan to drop the client into based upon where it is on the network and the type of client it is. Once I've got the vlan back I can decide whether to use RFC 3580 or RFC 4675 when creating the attributes to pass back in the access-accept packet. Only problem is figuring out how to format the unlang statement. Elsewhere in my sites-enable/default file I've got if ( "%{sql:SELECT count(*) from banned_macs where mac_address=UPPER(TRIM('%{Calling-Station-Id}'))}" > "0" ) { update control { Auth-Type := Reject } update reply { Reply-Message := "quarantined, contact ITSO" } } which works just fine and I can block specific mac addresses from connecting to our wired network. In this case I've got Tmp-String-0 := "%{sql:call get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}" get_vlan_id accepts two varchar arguments. Which, when I run radiusd -X -d /etc/freeradius gives me /etc/freeradius/sites-enabled/default[248]: Unknown action '%{sql:CALL get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}'. I found a message on the list that says ………. call a stored procedure by using "%{<call the stored proc in here>}" Well, from a mysql cli I'd type call get_vlan_id(…….) to run the stored procedure. Rgds Alex
On 20/05/13 16:55, Alex Sharaz wrote:
In this case I've got
Tmp-String-0 := "%{sql:call get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}"
get_vlan_id accepts two varchar arguments.
Which, when I run radiusd -X -d /etc/freeradius gives me
/etc/freeradius/sites-enabled/default[248]: Unknown action '%{sql:CALL get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}'.
Which version of FreeRADIUS is this? From the source, the error "Unknown action" suggests you've got a syntax error. Remember you need to wrap this in an "update" block, like so: authorize { ... update control { Tmp-String-0 := "%{sql:....}" } if (control:Tmp-String-0 =~ /.../) { } ... }
On 20 May 2013, at 17:16, Phil Mayers wrote:
On 20/05/13 16:55, Alex Sharaz wrote:
In this case I've got
Tmp-String-0 := "%{sql:call get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}"
get_vlan_id accepts two varchar arguments.
Which, when I run radiusd -X -d /etc/freeradius gives me
/etc/freeradius/sites-enabled/default[248]: Unknown action '%{sql:CALL get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}'.
Which version of FreeRADIUS is this? 2.2.0 source
From the source, the error "Unknown action" suggests you've got a syntax error. Remember you need to wrap this in an "update" block, like so:
authorize { ... update control { Tmp-String-0 := "%{sql:....}" } if (control:Tmp-String-0 =~ /.../) { } ... } - Ah! o.k. fair enough Rgds Alex
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Many thanks Phil, all sorted. Wrapping the sql:" statement with an update control fixed the Unknown Action error. Haven't checked that I'm returning the correct stuff yet, but I'm past this particular problem Rgds Alex On 20 May 2013, at 17:16, Phil Mayers wrote:
On 20/05/13 16:55, Alex Sharaz wrote:
In this case I've got
Tmp-String-0 := "%{sql:call get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}"
get_vlan_id accepts two varchar arguments.
Which, when I run radiusd -X -d /etc/freeradius gives me
/etc/freeradius/sites-enabled/default[248]: Unknown action '%{sql:CALL get_vlan_id('%{NAS-IP-Address}','%{User-Name}')}'.
Which version of FreeRADIUS is this?
From the source, the error "Unknown action" suggests you've got a syntax error. Remember you need to wrap this in an "update" block, like so:
authorize { ... update control { Tmp-String-0 := "%{sql:....}" } if (control:Tmp-String-0 =~ /.../) { } ... } - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (2)
-
Alex Sharaz -
Phil Mayers