[radius] Freeradius/MySql problem

[Paul Hampson]

On Sat, Jul 02, 2005 at 10:42:44AM -0700, Radius wrote:
> OK, let me try this way, when our wholesale provider receives a realm, 
> they know where
> to send the request.
> 
> If the user sends RADIUS at kingmanaz.net or radius at kingmanaz.net
> 
> our radius regardless if I have lower_user before/after/no
> 
> They will be authenticated either way.

> If we force it lower on our end, does not force lower on their end.

> It's a mess. They said only this month they were going to issue credits
> and that I needed to get my end to deny UPPER case logins.

> I set the lower_user lower and lower_pass to no and a user will
> all RADOUS at kingmanaz.net will be authenticated. I guess mysql
> doesn't care if it's upper or lower.

For what you want to do, you need to set lower_user to 'no',
and check your authorize_check_query to be sure you're using
the one that has "STRCMP(Username, '%{SQL-User-Name}')" and not the
one that has "Username = '%{SQL-User-Name}'".

ie (this is in 1.0.4, and doesn't work with mysql 4 onwards.)

	# Use these for case sensitive usernames. WARNING: Slower queries!
	authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"
	authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"

#	authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
#	authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"

rather than the default.

	# Use these for case sensitive usernames. WARNING: Slower queries!
#	authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"
#	authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"

	authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"
	authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE Username = '%{SQL-User-Name}' ORDER BY id"


(That's only moving the #s, not changing the query itself.)

This is the joy of mySQL, it's not case-sensitive for string
comparisons by default. ^_^

Alternatively, change the radcheck table's UserName column to be 'BINARY', see
http://dev.mysql.com/doc/mysql/en/case-sensitivity.html for details. (Although
that's mySQL 4.1. If you're using a packaged mySQL from a distribution, check
A.5.1 in the included manual for more specific details.)

In fact, I'd be interested to know if
	authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM ${authcheck_table} WHERE BINARY Username = '%{SQL-User-Name}' ORDER BY id"
	authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM ${authreply_table} WHERE BINARY Username = '%{SQL-User-Name}' ORDER BY id"
fixes it, and if it works for mySQL < 4, because it's more future-proofed
than STRCMP, which has already changed semantics.

-- 
Paul "TBBle" Hampson, on an alternate email client.