SQL help from someone who groks c, please?

[Dan Mahoney, System Admin]

Hey all...

Disclaimer: If I should ask this on the devel list, let me know and I 
will.  As I'm not qualified in the languages to be a developer, this 
seemed the more logical place.

I'm decent with Radius but have just come to freeradius over Cistron...I'm 
still in the process of assimilating the "op" syntax, but that's not the 
problem, I think.

That said, I figured out two possible ways to handle my previous question 
regarding advanced SQL auth (including multiple occurances of the same 
username with different check-items).  What I need to know is which way it 
was "intended" to work (either one of my two thoughts here, or something 
entirely -- or even "what I want to do isn't possible".)

Here's the thought (apologies if the tabs get messed up).

a) The rows returned are parsed in order, just as if they were a users 
file, and something (perhaps a password entry or an op of ==) triggers the 
system that it's on the "next record".

of

b) The "id" field (which some of the docs say are unused) is used to 
"bind" multiple items having the same ID.

i.e (citing my earlier example)...

Jeremy nas-ip-address="the vpn server" password="x"
Service-Type = "Framed-User"

# Our switches, which we trust jeremy not to mess up
Jeremy password = "y"
Service-Type = "Admin-User"

Jeremy nas-ip-address="a big powerful router"
Service-Type = Reject

If it's (a):

Based on the standard values in the database, would this work:

id	username	attribute	value		op
1	jeremy		password	X		==
2	jeremy		nas-ip-address	"vpnsvr"	:=
3	jeremy		password	y		==
4	jeremy		password	"z"		==
5	jeremy		nas-ip-address	"router"	:=

The problem I see with this one is that a) there's no easy way to keep 
your (presumably auto-increment) id field entries properly ordered.

Also, line 4 above doesn't make sense -- there really ARE no check items 
that would work here.  If line 4 above were deleted, it would instead 
match password=y, nas-ip-address=router (again, I may be missing something 
critical about operators and their signaling here).

Also, based on this, you would have to then find which attributes in the 
reply table to ask, which could *possibly* be based on the id of the 
"password" attribute, but that's hardly normalized.

If it's (b):

id	username	attribute	value		op
1	jeremy		password	X		==
1	jeremy		nas-ip-address	"vpnsvr"	:=
2	jeremy		password	y		==
3	jeremy		password	"z"		==
3	jeremy		nas-ip-address	"router"	:=

This one implies you either have a keyless table (bad), or that there's 
another key value here, which isn't in the included schemas.  It also 
implies that the server acutally LOOK AT the "id" value to know when to 
stop parsing.  In this case your reply items would be pulling based on 
that "id" field, not the username at all (since in this case the username 
is not unique).

/endif

The assumption I worked with is: My knowledge of SQL is far better than my 
knowledge of the inner workings of freeradius-SQL.

What I've done is I've had to hard code the NAS-IP address into my 
queries, coupled with some special tables to return the right values 
expected by certain network devices (for example), if it's a PDU, return 
the admin (or userlevel) service-type for those devices, if it's a switch, 
return the appropriate there -- even allowing our customers access to 
those PDU ports that they control, but this still isn't as flexible as an 
old-fashioned users file.  In reality, I should be able to return ALL 
records related, and radius should pick up the differences in the 
check-items, instead of the query only returning a select few.  This will 
make more sense if we decide we need to start checking more values than 
just the one.

I've also had to UNION my queries together because employees and clients 
are in different tables (and I am only checking on employee access on 
some devices -- but this is also done through the SQL).

I noticed, interestingly enough, that UNION works, but the semicolon does 
not (perhaps this is to prevent SQL injection?).  As I am unable to grok 
the c, I can't be sure.

I.e. at the moment there's about forty users, some of which have differing 
access than others, and there's no real radius "groups", since each 
person's needs are slightly different and vary based on what each person 
has been certified and tested on.

Apologies for seeming long.

If anyone has any ideas, let me know, please.  I may be able to compensate 
someone for an hour or two of consultation time, if it gets the questions 
answered (please contact me off-list regarding this).

-Dan

--

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