sql: ERROR: rlm_sql_oracle: ORA-01403: no data found
Hi, I am trying to authorize a user through a authorize_check_query on Oracle database. When I test, I get this error on my radius server : (0) sql: Executing select query: SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL (0) sql: ERROR: Error fetching row (0) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found But this can't be true, this query always returns 1 row ! Below is the output of my server in debug mode, starting from database connection, ending to the authorize test : # Instantiating module "sql" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/sql rlm_sql (sql): Attempting to connect to database "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.97.147)(PORT=1533))(CONNECT_DATA=(SID=MEMAD)))" rlm_sql (sql): Initialising connection pool pool { start = 4 min = 3 max = 32 spare = 10 uses = 0 lifetime = 0 cleanup_interval = 30 idle_timeout = 60 retry_delay = 1 spread = no } rlm_sql (sql): Opening additional connection (0), 1 of 32 pending slots used rlm_sql (sql): Opening additional connection (1), 1 of 31 pending slots used rlm_sql (sql): Opening additional connection (2), 1 of 30 pending slots used rlm_sql (sql): Opening additional connection (3), 1 of 29 pending slots used # Instantiating module "linelog" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/linelog # Instantiating module "log_accounting" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/linelog # Instantiating module "attr_filter.post-proxy" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/attr_filter reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/post-proxy # Instantiating module "attr_filter.pre-proxy" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/attr_filter reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/pre-proxy # Instantiating module "attr_filter.access_reject" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/attr_filter reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/access_reject [/USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/access_reject]:11 Check item "FreeRADIUS-Response-Delay" found in filter list for realm "DEFAULT". [/USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/access_reject]:11 Check item "FreeRADIUS-Response-Delay-USec" found in filter list for realm "DEFAULT". # Instantiating module "attr_filter.access_challenge" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/attr_filter reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/access_challenge # Instantiating module "attr_filter.accounting_response" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/attr_filter reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/attr_filter/accounting_response # Instantiating module "logintime" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/logintime # Instantiating module "IPASS" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/realm # Instantiating module "suffix" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/realm # Instantiating module "realmpercent" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/realm # Instantiating module "ntdomain" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/realm # Instantiating module "cache_eap" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/cache_eap rlm_cache (cache_eap): Driver rlm_cache_rbtree (module rlm_cache_rbtree) loaded and linked # Instantiating module "files" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/files reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/files/authorize reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/files/accounting reading pairlist file /USR/radiusrec/radius/etc/raddb/mods-config/files/pre-proxy # Instantiating module "auth_log" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/detail.log rlm_detail (auth_log): 'User-Password' suppressed, will not appear in detail output # Instantiating module "reply_log" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/detail.log # Instantiating module "pre_proxy_log" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/detail.log # Instantiating module "post_proxy_log" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/detail.log # Instantiating module "reject" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "fail" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "ok" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "handled" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "invalid" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "userlock" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "notfound" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "noop" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "updated" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/always # Instantiating module "pap" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/pap # Instantiating module "expiration" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/expiration # Instantiating module "etc_passwd" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/passwd rlm_passwd: nfields: 3 keyfield 0(User-Name) listable: no # Instantiating module "mschap" from file /USR/radiusrec/radius/etc/raddb/mods-enabled/mschap rlm_mschap (mschap): using internal authentication } # modules radiusd: #### Loading Virtual Servers #### server { # from file /USR/radiusrec/radius/etc/raddb/radiusd.conf } # server server default { # from file /USR/radiusrec/radius/etc/raddb/sites-enabled/default # Loading authenticate {...} # Loading authorize {...} # Loading preacct {...} # Loading post-auth {...} } # server default server inner-tunnel { # from file /USR/radiusrec/radius/etc/raddb/sites-enabled/inner-tunnel # Loading authenticate {...} # Loading authorize {...} # Loading session {...} # Loading post-auth {...} } # server inner-tunnel radiusd: #### Opening IP addresses and Ports #### listen { type = "auth" ipaddr = * port = 1912 limit { max_connections = 1024 lifetime = 0 idle_timeout = 30 } } listen { type = "auth" ipaddr = 127.0.0.1 port = 18120 } Listening on auth address * port 1912 bound to server default Listening on auth address 127.0.0.1 port 18120 bound to server inner-tunnel Ready to process requests (0) Received Access-Request Id 21 from 127.0.0.1:51714 to 127.0.0.1:1912 length 74 (0) User-Name = "FT10895748" (0) User-Password = "FT10895748" (0) NAS-IP-Address = 172.22.97.209 (0) NAS-Port = 0 (0) Message-Authenticator = 0x9d34c3f30a9e038d72f149648f828f45 (0) # Executing section authorize from file /USR/radiusrec/radius/etc/raddb/sites-enabled/default (0) authorize { (0) policy filter_username { (0) if (&User-Name) { (0) if (&User-Name) -> TRUE (0) if (&User-Name) { (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@[^@]*@/ ) { (0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # if (&User-Name) = notfound (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) [chap] = noop (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: No '@' in User-Name = "FT10895748", looking up realm NULL (0) suffix: No such realm "NULL" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) [files] = noop rlm_sql (sql): Reserved connection (0) (0) sql: EXPAND SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL (0) sql: --> SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL (0) sql: Executing select query: SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL (0) sql: ERROR: Error fetching row (0) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found (0) sql: User found in radcheck table (0) sql: Conditional check items matched, merging assignment check items (0) sql: User-Password := "FT10895748" rlm_sql (sql): Released connection (0) rlm_sql (sql): Need 6 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (4), 1 of 28 pending slots used (0) [sql] = ok (0) [expiration] = noop (0) [logintime] = noop (0) pap: WARNING: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! (0) pap: WARNING: !!! Ignoring control:User-Password. Update your !!! (0) pap: WARNING: !!! configuration so that the "known good" clear text !!! (0) pap: WARNING: !!! password is in Cleartext-Password and NOT in !!! (0) pap: WARNING: !!! User-Password. !!! (0) pap: WARNING: !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! (0) pap: WARNING: No "known good" password found for the user. Not setting Auth-Type (0) pap: WARNING: Authentication will fail unless a "known good" password is available (0) [pap] = noop (0) } # authorize = ok (0) ERROR: No Auth-Type found: rejecting the user via Post-Auth-Type = Reject (0) Failed to authenticate the user (0) Using Post-Auth-Type Reject (0) Post-Auth-Type sub-section not found. Ignoring. (0) # Executing group from file /USR/radiusrec/radius/etc/raddb/sites-enabled/default (0) Delaying response for 1.000000 seconds Waking up in 0.6 seconds. Waking up in 0.3 seconds. (0) Sending delayed response (0) Sent Access-Reject Id 21 from 127.0.0.1:1912 to 127.0.0.1:51714 length 20 Waking up in 3.9 seconds. (0) Cleaning up request packet ID 21 with timestamp +24 Ready to process requests Any idea of what is wrong ? Best regards, Vincent MARCEL
On Feb 10, 2016, at 10:52 AM, Vincent MARCEL <Vincent.MARCEL@monext.net> wrote:
I am trying to authorize a user through a authorize_check_query on Oracle database. When I test, I get this error on my radius server : (0) sql: Executing select query: SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL
As the later error message says, DON"T have "User-Password" here. Use "Cleartext-Password".
(0) sql: ERROR: Error fetching row (0) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found
But this can't be true, this query always returns 1 row !
That error is returned from Oracle. There's very little we can do to fix Oracle.
Below is the output of my server in debug mode, starting from database connection, ending to the authorize test :
It still seems to work, so I'm not sure what the problem is. Alan DeKok.
Hi, Thank you, my radius server is working fine now, except thoses unjustified ORA-01403 errors each time a user authorization is tested. My authorize_check_query checks password in database for the user. 2 cases : 1/ If password is found, user is accepted (or rejected if password don't match) : Thu Feb 11 17:11:19 2016 : ERROR: (11) sql: ERROR: Error fetching row Thu Feb 11 17:11:19 2016 : ERROR: (11) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found Thu Feb 11 17:11:19 2016 : Auth: (11) Login OK: [FT10895748/FT10895748] (from client localhost port 0) ORA-01403 is not correct because data is found. Injecting the query in sqlplus don't make ORA-01403 error, so I think it is a radius problem. 2/ If password is not found, user is rejected : Thu Feb 11 17:11:06 2016 : ERROR: (8) sql: ERROR: Error fetching row Thu Feb 11 17:11:06 2016 : ERROR: (8) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found Thu Feb 11 17:11:06 2016 : Auth: (8) Login incorrect (sql: Error fetching row): [tutu/tutu] (from client localhost port 0) Yes data is not found but making this case an error is not normal : just the user is not authorized because not known, this is normal. Finally, everything works great except that my log file is contaminated with 2 unjustified ERROR lines each time a user calls the server, instead having just "Login OK" and "Login incorrect" type lines. Is there an explanation/solution for this or does this mean that everybody with radius v3.0.11 has the same problem with authorize_check_query on Oracle, whatever the query actually is ? Best regards, Vincent MARCEL Tél : 04 42 25 92 22 -----Message d'origine----- De : Freeradius-Users [mailto:freeradius-users-bounces+vincent.marcel=monext.net@lists.freeradius.org] De la part de Alan DeKok Envoyé : mercredi 10 février 2016 17:04 À : FreeRadius users mailing list Objet : Re: sql: ERROR: rlm_sql_oracle: ORA-01403: no data found On Feb 10, 2016, at 10:52 AM, Vincent MARCEL <Vincent.MARCEL@monext.net> wrote:
I am trying to authorize a user through a authorize_check_query on Oracle database. When I test, I get this error on my radius server : (0) sql: Executing select query: SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL
As the later error message says, DON"T have "User-Password" here. Use "Cleartext-Password".
(0) sql: ERROR: Error fetching row (0) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found
But this can't be true, this query always returns 1 row !
That error is returned from Oracle. There's very little we can do to fix Oracle.
Below is the output of my server in debug mode, starting from database connection, ending to the authorize test :
It still seems to work, so I'm not sure what the problem is. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Feb 11, 2016, at 11:53 AM, Vincent MARCEL <Vincent.MARCEL@monext.net> wrote:
Thank you, my radius server is working fine now, except thoses unjustified ORA-01403 errors each time a user authorization is tested.
It's an Oracle problem. http://www.orafaq.com/wiki/ORA-01403 Your query doesn't return data from the database, so Oracle claims that it fails. The solution is to use the database as a database.
ORA-01403 is not correct because data is found. Injecting the query in sqlplus don't make ORA-01403 error, so I think it is a radius problem.
Please think about it a little. FreeRADIUS doesn't invent Oracle errors. It can't. FreeRADIUS queries Oracle, which returns an error. It is 100% an Oracle problem.
Finally, everything works great except that my log file is contaminated with 2 unjustified ERROR lines each time a user calls the server, instead having just "Login OK" and "Login incorrect" type lines. Is there an explanation/solution for this or does this mean that everybody with radius v3.0.11 has the same problem with authorize_check_query on Oracle, whatever the query actually is ?
Complain to Oracle about Oracle problems. Alan DeKok.
Your query doesn't return data from the database
False : here is the query (debug mode) for an accepted user : (1) sql: EXPAND SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = '%{User-Name}' (1) sql: --> SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = 'FT10895748' (1) sql: Executing select query: SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = 'FT10895748' (1) sql: ERROR: Error fetching row (1) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found Here is the result of the copy/paste query in sqlplus : SQL> SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = 'FT10895748'; 1 NUM_SERIE 'CLEARTEXT-PASSWOR NUM_SERIE ': ---------- -------------------- ------------------ -------------------- -- 1 FT10895748 Cleartext-Password FT10895748 := This error may occur in PL/SQL blocks. Do Radius create PL/SQL blocks ?? Cordialement, Vincent MARCEL Tél : 04 42 25 92 22 -----Message d'origine----- De : Freeradius-Users [mailto:freeradius-users-bounces+vincent.marcel=monext.net@lists.freeradius.org] De la part de Alan DeKok Envoyé : jeudi 11 février 2016 17:57 À : FreeRadius users mailing list Objet : Re: sql: ERROR: rlm_sql_oracle: ORA-01403: no data found On Feb 11, 2016, at 11:53 AM, Vincent MARCEL <Vincent.MARCEL@monext.net> wrote:
Thank you, my radius server is working fine now, except thoses unjustified ORA-01403 errors each time a user authorization is tested.
It's an Oracle problem. http://www.orafaq.com/wiki/ORA-01403 Your query doesn't return data from the database, so Oracle claims that it fails. The solution is to use the database as a database.
ORA-01403 is not correct because data is found. Injecting the query in sqlplus don't make ORA-01403 error, so I think it is a radius problem.
Please think about it a little. FreeRADIUS doesn't invent Oracle errors. It can't. FreeRADIUS queries Oracle, which returns an error. It is 100% an Oracle problem.
Finally, everything works great except that my log file is contaminated with 2 unjustified ERROR lines each time a user calls the server, instead having just "Login OK" and "Login incorrect" type lines. Is there an explanation/solution for this or does this mean that everybody with radius v3.0.11 has the same problem with authorize_check_query on Oracle, whatever the query actually is ?
Complain to Oracle about Oracle problems. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Thu, Feb 11, 2016 at 05:19:29PM +0000, Vincent MARCEL wrote:
Your query doesn't return data from the database
(1) sql: ERROR: Error fetching row (1) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found
Here is the result of the copy/paste query in sqlplus : SQL> SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = 'FT10895748';
1 NUM_SERIE 'CLEARTEXT-PASSWOR NUM_SERIE ': ---------- -------------------- ------------------ -------------------- -- 1 FT10895748 Cleartext-Password FT10895748 :=
Just a thought... in sqlplus are you using the same user with the same permission to read the database as FreeRADIUS is using? Matthew -- Matthew Newton, Ph.D. <mcn4@le.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
Your query doesn't return data from >>the database
False : here is the query (debug mode) >for an accepted user :
No. Sorry. It's true. Look: (1) sql: ERROR: Error fetching row (1) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found See. Error fetching row and no data found. Ensure your sql config had suitable access user/pass and host has required access alan
participants (4)
-
Alan Buxey -
Alan DeKok -
Matthew Newton -
Vincent MARCEL