Mysql procedure and freeradius
Hi All. I have the trouble with mysql & freeradius.I begin to write own authorization using mysql procedures.In sql.conf i have this --- authorize_check_query = "CALL DB.auth_check('%{SQL-User-Name}');" -- And in DB have procedure auth_check with this code: --- CREATE TEMPORARY TABLE DB.make_auth (id int,username VARCHAR(40),attribute VARCHAR(30),op VARCHAR(3),value VARCHAR(40)) ENGINE MEMORY; ....certain checks... INSERT INTO DB.make_auth (id,username,attribute,op,value) VALUES (dbUID,username,"User-Password",":=",dbPassword); SELECT id,username,attribute,op,value FROM DB.make_auth; -- So..this procedure when i call her from CLI return table like this +------+----------+---------------+------+--------+ | id | username | attribute | op | value | +------+----------+---------------+------+--------+ | 1 | username | User-Password | := | password | +------+----------+---------------+------+--------+ Look good..but when I'm trying to use radtest and radius -X i'm getting this: ---- radius_xlat: 'username' rlm_sql (sqlauth): sql_set_user escaped user --> 'username' radius_xlat: 'CALL DB.auth_check('username');' rlm_sql (sqlauth): Reserving sql socket id: 28 rlm_sql_mysql: query: CALL DB.auth_check('username'); rlm_sql_mysql: MYSQL check_error: 1312 received rlm_sql_getvpdata: database query error rlm_sql (sqlauth): SQL query error; rejecting user rlm_sql (sqlauth): Released sql socket id: 28 --- What's wrong??? P.S.Sorry for my english)))
ensure the user has Process_priv. Also there is the security_type flag in the mysql.proc table which defines who the proceedure runs as (invoker or definer) Its all quite well documented on the mysql website.
Hi All. I have the trouble with mysql & freeradius.I begin to write own authorization using mysql procedures.In sql.conf i have this --- authorize_check_query = "CALL DB.auth_check('%{SQL-User-Name}');" -- And in DB have procedure auth_check with this code: --- CREATE TEMPORARY TABLE DB.make_auth (id int,username VARCHAR(40),attribute VARCHAR(30),op VARCHAR(3),value VARCHAR(40)) ENGINE MEMORY; ....certain checks... INSERT INTO DB.make_auth (id,username,attribute,op,value) VALUES (dbUID,username,"User-Password",":=",dbPassword); SELECT id,username,attribute,op,value FROM DB.make_auth; -- So..this procedure when i call her from CLI return table like this +------+----------+---------------+------+--------+ | id | username | attribute | op | value | +------+----------+---------------+------+--------+ | 1 | username | User-Password | := | password | +------+----------+---------------+------+--------+ Look good..but when I'm trying to use radtest and radius -X i'm getting this: ---- radius_xlat: 'username' rlm_sql (sqlauth): sql_set_user escaped user --> 'username' radius_xlat: 'CALL DB.auth_check('username');' rlm_sql (sqlauth): Reserving sql socket id: 28 rlm_sql_mysql: query: CALL DB.auth_check('username'); rlm_sql_mysql: MYSQL check_error: 1312 received rlm_sql_getvpdata: database query error rlm_sql (sqlauth): SQL query error; rejecting user rlm_sql (sqlauth): Released sql socket id: 28 --- What's wrong??? P.S.Sorry for my english)))
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
ensure the user has Process_priv.
Also there is the security_type flag in the mysql.proc table which defines who the proceedure runs as (invoker or definer)
Its all quite well documented on the mysql website.
On mysql website i found that for executing stored procedures need a special flag for connection(CLIENT_MULTI_STATEMENTS).So..i need to rewrite freeradius source code to enabling mysql procedures.This is very bad news for me..because i know C/C++ not very good.Maybe better to include special option for rlm_sql_mysql to enable SP in freeradius?
Hi, That cant be the case, im using mysql 5 and a SP with freeradius 1.1.2 without any problems or recompiling. It must be an other issue. Maybe the sql user you let freeradius connect with has no rights to start the SP ? Regards, Jurgen -----Oorspronkelijk bericht----- Van: freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org [mailto:freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org] Namens Alesha Verzonden: woensdag 9 augustus 2006 18:57 Aan: FreeRadius users mailing list Onderwerp: Re: Mysql procedure and freeradius
ensure the user has Process_priv.
Also there is the security_type flag in the mysql.proc table which defines who the proceedure runs as (invoker or definer)
Its all quite well documented on the mysql website.
On mysql website i found that for executing stored procedures need a special flag for connection(CLIENT_MULTI_STATEMENTS).So..i need to rewrite freeradius source code to enabling mysql procedures.This is very bad news for me..because i know C/C++ not very good.Maybe better to include special option for rlm_sql_mysql to enable SP in freeradius? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I think maybe it could be a problem if the version of mysql.h that was during compilation used predates stored proceedures?
Hi,
That cant be the case, im using mysql 5 and a SP with freeradius 1.1.2 without any problems or recompiling. It must be an other issue. Maybe the sql user you let freeradius connect with has no rights to start the SP ?
Regards,
Jurgen -----Oorspronkelijk bericht----- Van: freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org [mailto:freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org] Namens Alesha Verzonden: woensdag 9 augustus 2006 18:57 Aan: FreeRadius users mailing list Onderwerp: Re: Mysql procedure and freeradius
ensure the user has Process_priv.
Also there is the security_type flag in the mysql.proc table which defines who the proceedure runs as (invoker or definer)
Its all quite well documented on the mysql website.
On mysql website i found that for executing stored procedures need a special flag for connection(CLIENT_MULTI_STATEMENTS).So..i need to rewrite freeradius source code to enabling mysql procedures.This is very bad news for me..because i know C/C++ not very good.Maybe better to include special option for rlm_sql_mysql to enable SP in freeradius? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I think maybe it could be a problem if the version of mysql.h that was during compilation used predates stored proceedures?
Hi Alesha, I had SP running with 1.1.1 before I upgraded, should work too. Regards, Jurgen -----Oorspronkelijk bericht----- Van: freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org [mailto:freeradius-users-bounces+jurgen=wanbound.com@lists.freeradius.org] Namens Alesha Verzonden: vrijdag 11 augustus 2006 5:50 Aan: FreeRadius users mailing list Onderwerp: Re: Mysql procedure and freeradius I used mysql 5 headers.Maybe SP were fixed onlu in freeradius 1.1.2?I'll try to update freeradius..and then write a letter. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (3)
-
Alan Lumb -
Alesha -
Jurgen van Vliet