new query verification in sql.conf

[Alexander Serkin]

we did this that way:

1. modified usergroup table to (it's oracle):
  Name       Null?    Type
  ---------- -------- ----------------------------
  ID                  NOT NULL NUMBER(38)
  USERNAME            VARCHAR2(128)
  CLID                VARCHAR2(15)
  GROUPNAME           VARCHAR2(30)
  PRIORITY            NOT NULL NUMBER(38)

2. modified auth sql queries:

         authorize_group_check_query = "SELECT 
${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attribute,${groupcheck_table}.Value,${groupcheck_table}.op 
  FROM ${groupcheck_table},${usergroup_table} WHERE 
(${usergroup_table}.Username = '%{SQL-User-Name}' or 
${usergroup_table}.CLID = '%{Calling-Station-Id}') AND 
${usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY 
${usergroup_table}.PRIORITY,${groupcheck_table}.id"
         authorize_group_reply_query = "SELECT 
${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attribute,${groupreply_table}.Value,${groupreply_table}.op 
  FROM ${groupreply_table},${usergroup_table} WHERE 
(${usergroup_table}.Username = '%{SQL-User-Name}' OR 
${usergroup_table}.CLID = '%{Calling-Station-Id}') AND 
${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY 
${groupreply_table}.id"
         group_membership_query = "SELECT GroupName FROM 
${usergroup_table} WHERE UserName='%{SQL-User-Name}' OR 
CLID='%{Calling-Station-Id}' order by priority"

3. created group profile:
insert into RADGROUPCHECK values('','blackholed','Auth-Type',':=','Reject');
insert into RADGROUPCHECK values('','blackholed','Fall-Through','=','No');
insert into RADGROUPREPLY 
values('','blackholed','Reply-Message','=','Access denied due to 
agreement violation');

4. to blacklist client just add the MAC to "blackholed" group:
insert into USERGROUP values('','','<blaclisted MAC>','blackholed','10');

Jean Carlos Oliveira Guandalini wrote:
> I use freeradius for authentication of pppoe wifi.
> 
> I need to make new sql query in a table with a list of mac-address, if
> the CallingStationId will be equal to the some mac-adress of the table
> then will not have to be connected.
> A system of mac-adress blacklist.
> I tried to make adding one query in sql.conf but it does not function.
> 
> I find that it would have to add a new function in rlm_sql.c, but am not
> habil C programmer.
> 
> Somebody can help me?
> 
> Sorry for my english
> 
> Thanks
> 
> Jean
> - 
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html


-- 
Sincerely Yours,
Alexander