Re: sql group checks

[Alexander Serkin]

did you mean to answer something, Kostas?
Kostas Kalevras wrote:
On Fri, 15 Oct 2004, Alexander Serkin wrote:

Hi.
could anybody explain me what exactly FR does with group checks working with SQL
(Oracle in my case) ?
I see group_membership_query in sql.conf, but i do not see that FR uses it in debug:
rad_recv: Access-Request packet from host 127.0.0.1:50893, id=174, length=78
        User-Name = "[EMAIL PROTECTED]"
        User-Password = "blahblah"
        Calling-Station-Id = "250097000002749"
        Framed-Protocol = PPP
        Service-Type = Framed-User
        NAS-IP-Address = 212.119.97.86
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 29
  modcall[authorize]: module "preprocess" returns ok for request 29
  modcall[authorize]: module "chap" returns noop for request 29
    rlm_realm: Looking up realm "c" for User-Name = "[EMAIL PROTECTED]"
    rlm_realm: Found realm "c"
    rlm_realm: Proxying request from user a to realm c
    rlm_realm: Adding Realm = "c"
    rlm_realm: Authentication realm is LOCAL.
  modcall[authorize]: module "suffix" returns noop for request 29
    users: Matched DEFAULT at 73
  modcall[authorize]: module "files" returns ok for request 29
WARNING: Attempt to use unknown xlat function, or non-existent attribute in
string %{DEFAULT}
radius_xlat:  '[EMAIL PROTECTED]'
rlm_sql (sql): sql_set_user escaped user --> '[EMAIL PROTECTED]'
radius_xlat:  'SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE
Username = '[EMAIL PROTECTED]' ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 0
radius_xlat:  'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op
 FROM radgroupcheck,usergroup WHERE (usergroup.Username = '[EMAIL PROTECTED]' or
usergroup.CLID = '250097000002749') AND usergroup.GroupName =
radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id,UserName,Attribute,Value,op FROM radreply WHERE
Username = '[EMAIL PROTECTED]' ORDER BY id'
radius_xlat:  'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
 FROM radgroupreply,usergroup WHERE (usergroup.Username = '[EMAIL PROTECTED]' OR
usergroup.CLID = '250097000002749') AND usergroup.GroupName =
radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): No matching entry in the database for request from user [EMAIL 
PROTECTED]
rlm_sql (sql): Released sql socket id: 0
  modcall[authorize]: module "sql" returns notfound for request 29
  modcall[authorize]: module "mschap" returns noop for request 29
modcall: group authorize returns ok for request 29
  rad_check_password:  Found Auth-Type Accept
  rad_check_password: Auth-Type = Accept, accepting the user
Second - what exactly will FR do if authorize_group_check_query returns several
groups' membership for the user (i've slightly modified query and usergroup
table to check CLID also):
SQL> SELECT radgroupcheck.id, radgroupcheck.GroupName, radgroupcheck.Attribute,
radgroupcheck.Value, radgroupcheck.op  FROM radgroupcheck, usergroup WHERE
(usergroup.Username = '[EMAIL PROTECTED]' or usergroup.CLID = '250097000002749') AND
usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id;
ID GROUPNAME    ATTRIBUTE       VALUE             OP
10 carta        Realm           c                 ==
11 carta        NAS-IP-Address  212.119.117.1     ==
19 blackholed   Auth-Type       Reject            :=
In my case user is accepted though he is a member of blackholed group with
Auth-Type - Reject.

--
Alexander
- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html