SQL help from someone who groks c, please?

[Dan Mahoney, System Admin]

On Thu, 8 Feb 2007, ChristosH wrote:


No, wrong.

You can include any %{check-item} in your query.  I didn't have to modify 
the code at all, but my queries are PERVERSE.  Yours will probably be 
simpler.

If you want to give me your proposed database setup and schema, and what 
you need to auth against, I might be able to offer you a quick answer.  If 
it's a longer and more involved thing, contact me off-list and I might be 
able to work something for you.

This is my auth query (which is actually two) (beware, it's for our site 
database which polls a 
LOT of info from different tables you won't need -- however I find this to 
be a far more real-world example than dedicated radius tables) -- You can 
see that in this case I manually insert the Password Attribute, and 
Operator by using string literals.

authorize_check_query="\
SELECT `adm_permissions`.`admPermitID` AS `id`, 
`adm_permissions`.`admp_username` AS `UserName`, 'Password' as Attribute, 
\
`adm_permissions`.`admp_password` AS `Value`, '==' as Op FROM 
`adm_permissions` , `switches` Inner Join `interface_ip` ON \
`switches`.`id` = `interface_ip`.`deviceid` WHERE admp_username = 
'%{SQL-User-Name}' AND \
`interface_ip`.`interface_is_primary` = '1' AND \
interface_address = '%{NAS-IP-Address}'

This above gets permissions for any staff user, and checks our one-to-many 
interface table to find out what device they're actually logging into.

UNION SELECT IPCustomerID as id, 
`ip_customer`.`ipc_rmtusername`, 'Password' as \
Attribute,\
`ip_customer`.`ipc_rmtpassword` as Value, '==' as Op FROM `ip_customer` 
Inner Join `interface_ip` ON \
`ip_customer`.`ipc_rmtip` = `interface_ip`.`interface_address` Inner Join 
`switches` ON `switches`.`id` = \
`interface_ip`.`deviceid` WHERE interface_address = '%{NAS-IP-Address}' 
AND ipc_rmtusername = '%{SQL-User-Name}' \
GROUP BY `ip_customer`.`ipc_rmtusername`,\
`interface_ip`.`interface_address`"

This does the same for any customer user.

Then my reply-items

authorize_reply_query = "SELECT `ip_customer`.`ipCustomerID` AS `id`, 
`ip_customer`.`ipc_rmtusername` AS UserName,\
`rad_reply`.`Attribute`,`rad_reply`.`Value`, `rad_reply`.`Op` FROM 
`ip_customer` Left Join `interface_ip` ON \
`ip_customer`.`ipc_rmtip` =`interface_ip`.`interface_address` Inner Join 
`switches` ON `switches`.`id` = \
`interface_ip`.`deviceid` Inner Join`rad_reply` ON `switches`.`role` = 
`rad_reply`.`devicetype` WHERE \
`rad_reply`.`Usertype` = '2' AND ipc_rmtusername ='%{SQL-User-Name}' and 
interface_address = '%{NAS-IP-Address}' group by \
ipc_rmtusername, interface_address


This only lets a customer in if it has a devicetype of 2 (which is a 
remote reboot unit AND if they are listed as having a device on that unit. 
We have a table that specifies if you are a customer user then your reply 
is "Outlet"  If you're staff then it's Admin-User.

UNION 
SELECT`adm_permissions`.`admPermitID` AS `id`, 
`adm_permissions`.`admp_username` \
AS `UserName`, `rad_reply`.`Attribute`,`rad_reply`.`Value`, 
`rad_reply`.`Op` FROM `adm_permissions` , `switches` Inner \
Join `interface_ip` ON `switches`.`id` =`interface_ip`.`deviceid` Inner 
Join `rad_reply` ON `switches`.`role` = \
`rad_reply`.`devicetype` WHERE`rad_reply`.`Usertype` = '1' AND 
admp_username = '%{SQL-User-Name}' and interface_address = \
'%{NAS-IP-Address}'

Do the same as above with staff.

UNION SELECT `remote`.`port`as id, 
`ip_customer`.`ipc_rmtusername` as UserName, _latin1 \
'APC-Outlets' as Attribute,group_concat(remote.port order by remote.port 
asc separator ',') as Value, _latin1 ':=' as \
Op FROM `remote` Inner Join`ip_customer` ON `remote`.`suite` = 
`ip_customer`.`ipc_suite` AND `remote`.`row` = \
`ip_customer`.`ipc_row` AND`remote`.`rack` = `ip_customer`.`ipc_rack` AND 
`remote`.`server` = `ip_customer`.`ipc_server` \
Inner Join `interface_ip` ON`remote`.`deviceid` = 
`interface_ip`.`deviceid` Inner Join `switches` ON remote.deviceid = \
switches.id WHERE`ip_customer`.`ipc_rmtreboot` = 'y' AND 
ip_customer.ipc_rmtusername = '%{SQL-User-Name}' AND ipc_rmtip = \
'%{NAS-IP-Address}' AND switches.role = '4' GROUP BY interface_address, 
`ip_customer`.`ipc_rmtusername`"

If they are a customer, return a comma-separated list of which outlets 
they 
are authorized for. (See the APC radius spec).



>
>
>
> Phil Mayers wrote:
>>
>> Dan Mahoney, System Admin wrote:
>>
>> My suggestion is that you use a custom schema and queries for your
>> database - probably a stored procedure. Pass the NAS-IP-Address into
>> these queries, and return different values based on the nas. Effectively
>> you move the code that walks over the request and chooses the right
>> values into the SQL server.
>>
>
> So if I was looking to select a different response based on NAS what I
> should be doing is creating a stored procedure that ends up authenticating
> for me? I don't quite see where this would fit in with the rlm_sql logic.
> Would that go in the sql.conf file? For using a new schema, would that mean
> instead adding an extra column in the radcheck table and the response table
> to associate with the NAS IP?
>
> Would it be easier to create a function that inserts a prefex to the user
> name then processes the SQL as normal? The only issue I see with this is
> doubling the amount of users and user responses in the database .
>
> Either way, I think i'm going to have to modify the rlm_sql.c file and then
> having to recompile FreeRadius after I'm done editing it?
>

--

"A single death is a tragedy.  A million deaths is a statistic."

-Josef Stalin, As quoted on the cover to Savatage's "Dead Winter Dead"

--------Dan Mahoney--------
Techie,  Sysadmin,  WebGeek
Gushi on efnet/undernet IRC
ICQ: 13735144   AIM: LarpGM
Site:  http://www.gushi.org
---------------------------