rlm_sql - Different table for User authentication

[Andrew Olson]

I was trying to do something just like this.  I have an existing user
database that is already in production.

My user table looks something like:
user_name, status, etc(no Attribute, Value, op columns)

My SQL query ended up looking something like:
authorize_check_query = "SELECT 1 as id, user_name as UserName,
'Auth-Type' as Attribute, 'EAP' as Value, ':=' as op FROM
${authcheck_table} WHERE user_name = '%{SQL-User-Name}'"

FR is particular about the order of the columns(i.e. it expects id at
index 0, username at index 1, and so on).

Eventually the plan is to create a view that FR is happy with.

-andrew


2008/4/17 rsg <ranil.santhish at gmail.com>:
> 2nd option is chosen.
>
>  Many thanks.
>
>  rg
>
>  2008/4/17 Ivan Kalik <tnt at kalik.net>:
>
>
> > Database is used to store attributes. It doesn't authenticate users.
>  >  >From data you have stored in authcheck table no attribute can be created
>  >  with chack query so that's a likely reason for segfault.
>  >
>  >  1. Use another module (perl, exec) that can do authentication with data
>  >  from authcheck table.
>  >
>  >  or
>  >
>  >  2. Add attribute name and op fields (back) to your table and fix them in
>  >  your modified query to Cleartext-Password (or whatever password
>  >  attribute you are using) and :=.
>  >
>  >  Ivan Kalik
>  >  Kalik Informatika ISP
>  >
>  >
>  >  Dana 17/4/2008, "rsg" <ranil.santhish at gmail.com> piše:
>  >
>  >
>  >
>  >  >Hi,
>  >  >
>  >  >I need to use a different auth table for Username/Password check.
>  >  >
>  >  >
>  >  >Here are the modifications I made into config.
>  >  >
>  >  >#raddb/sql.conf
>  >  >
>  >  >:
>  >  >  authcheck_table = "authcheck"
>  >  >:
>  >  >..
>  >  >
>  >  >#raddb/sql/mysql/dialup.conf
>  >  >:
>  >  >..
>  >  >## +++++++++ Here i retain the order of return data but omit some
>  >  >Attributes (2. Attr Name and 4.Attr Operation as described in
>  >  >Authorization Queries) +++++
>  >  >
>  >  >       authorize_check_query = "SELECT id, userid, passwd \
>  >  >          FROM ${authcheck_table} \
>  >  >          WHERE userid = '%{SQL-User-Name}' \
>  >  >          ORDER BY id"
>  >  >
>  >  >
>  >  >## +++++++++++++I DID NOT TOUCH THE REPLY QUERY
>  >  >++++++++++++++++++++++++++++++++++++++
>  >  >
>  >  >        authorize_reply_query = "SELECT id, username, attribute, value, op \
>  >  >          FROM ${authreply_table} \
>  >  >          WHERE username = '%{SQL-User-Name}' \
>  >  >          ORDER BY id"
>  >  >:
>  >  >..
>  >  >
>  >  >When I tried to authenticate a user I get a segmentation fault as follows;
>  >  >
>  >  >------
>  >  >rlm_sql (sql): sql_set_user escaped user --> 'test'
>  >  >rlm_sql (sql): Reserving sql socket id: 2
>  >  >   expand: SELECT id, userid , password  FROM authcheck  WHERE userid
>  >  >= '%{SQL-User-Name}'  ORDER BY id
>  >  >-> SELECT id, userid, password  FROM authcheck  WHERE login = 'test' ORDER BY id
>  >  >
>  >  >Segmentation fault
>  >  >------
>  >  >
>  >  >Are there any particular parameters/settings that i've missed here?
>  >  >
>  >  >
>  >  >Thanks for the suggestions.
>  >  >
>  >  >rg
>  >  >-
>  >  >List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>  >  >
>  >  >
>  >
>  >  -
>  >  List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>  >
>
>  -
>  List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>