Accept message based on (ne type,group)

[Douglas Straub]

Hi,
  Neither a radius nor sql expert, so please be gentle:

I'd like to use mysql based user authentication/authorization, with
multiple NEs and multiple user groups.
Ideally, the radius reply message should only return the VSAs
appropriate for the vendor type x user group combination.

So if user1 with permission of readonly logs into a "typeA" ne, it
supplies the correct readonly VSAs for that box type.

Today, with the standard config, the radgroupreply is including all
vendor VSAs for the readonly group, no matter which vendor type the
box is.

mysql> select * from nas;
+----+---------------+---------------+----------+-------+----------+---------------+--------------------+
| id | nasname       | shortname     | type     | ports | secret   |
community     | description        |
+----+---------------+---------------+----------+-------+----------+---------------+--------------------+
| 10 | 10.0.0.1 |       ne1   |             typeA |     0 |
testpwd1 | commX | this is a alu box  |
| 11 | 10.0.0.2 |       ne2   |             typeB |     0 |
testpwd2 | commY | this is a alu box  |
|  8 | 10.0.0.3  |       ne3   |             typeA  |     0 |
testpwd3 | commX |s is a wwp box  |
|  9 | 10.0.0.4 |        ne4    |            typeC |     0 |
testpwd4 | commY | this is a 3750 box |
+----+---------------+---------------+----------+-------+----------+---------------+--------------------+


mysql> select * from radcheck;
+----+----------+--------------------+----+-----------+
| id | username | attribute          | op | value     |
+----+----------+--------------------+----+-----------+
|  1 | user1      | Password           | == | barf      |
|  2 | user2    | Cleartext-Password | == | barf      |
|  3 | user3    | User-Password      | == | barf      |

mysql> select * from radusergroup;
+----------+-----------+----------+
| username | groupname | priority |
+----------+-----------+----------+
| rouser   | readonly  |        1 |
| techsup1 | admin     |        1 |
+----------+-----------+----------+

mysql> select * from radgroupreply;
+----+-----------+------------------------+----+---------------------+
| id | groupname | attribute              | op | value               |
+----+-----------+------------------------+----+---------------------+
|  9 | readonly  | Service-Type           | := | NAS-Prompt-User     |
| 10 | readonly  | Timetra-Access         | := | console             |
| 11 | readonly  | Timetra-Default-Action | := | none                |
| 12 | readonly  | Timetra-Profile        | := | readonly            |
| 13 | readonly  | Cisco-AVPair           | := | shell:priv-lvl=7    |
| 14 | admin     | Service-Type           | := | Administrative-User |
+----+-----------+------------------------+----+---------------------+


I was wondering if there is a way to have the radgroupreply be vendor
type based.  I was thinking:
1)  the nas table already has a type field.
2)  add a type field to radgroupreply.
3) modify  the the radgroupreply select to use the type.  so something like:

authorize_group_reply_query = "SELECT id, groupname, attribute, \
         value, op \
         FROM ${groupreply_table} \
         WHERE groupname = '%{Sql-Group}'   AND  radgroupreply.type =
%{SELECT type from nas where nas.nasname = ???}
         ORDER BY id"

Questions:
a)  Is this the right appoach?  If so, any hints on the right side of
the AND ?  I was having difficulty understanding the xlat structure,
and which variables are defined/carried through.

b)  Is there a better way?

Appreciate any help, thx Doug