It is currently Thu Jul 31, 2014 5:43 pm




Post new topic Reply to topic  [ 2 posts ] 
 Make your logons case sensitive 
Author Message
General
General

Joined: Fri Aug 03, 2007 11:39 am
Posts: 626
Location: Devon, England
Post Make your logons case sensitive
In contrast to an Internal Authentication Profile (which is case sensitive), if you use an Authentication Profile which stores its usernames and passwords in a MySQL database, by default both the username and password are case insensitive e.g. if the user's logon name is 'Joe Bloggs', they could enter it as 'jOe BlOgGs' and it would still work.

This doesn't really matter except when you use the [System]Client/User/Username tag to display the user's name on a system screen, where you will find it is displayed as 'jOe BlOgGs'.

To get around this problem, you enable the 'Show advanced properties' checkbox at the foot of the Edit Authentication Profile screen on the gateway and enter your own SQL queries to interrogate your MySQL database. This sounds daunting, but if you've been using the automatically generated tables up till now, just copy in the example query for each of the 3 boxes i.e.
Code:
SELECT Username FROM USERS WHERE Username = '$username$' AND Password = MD5($password$)
Code:
SELECT Rolename FROM ROLES
and
Code:
SELECT Rolename FROM USER_ROLE_MAPPING WHERE Username = '$username$'

Note that if you haven't been using the MD5 hash function to obscure your passwords, the first query will have to be changed to
Code:
SELECT Username FROM USERS WHERE Username = '$username$' AND Password = '$password$'

To make the Username part of the query case sensitive, you will have to use the MySQL COLLATE function with a case sensitive collation. Again this is easier to do than to say. All you have to do is update the first query as follows:
Code:
SELECT Username FROM USERS WHERE Username = '$username$' COLLATE latin1_general_cs AND Password = '$password$'
If you wanted your password case sensitive as well the query would become
Code:
SELECT Username FROM USERS WHERE Username = '$username$' COLLATE latin1_general_cs AND Password = '$password$' COLLATE latin1_general_cs

_________________
Al
DataCapture Ltd.

www.datacap.co.uk


Thu Mar 11, 2010 7:42 am
Profile
Moderator
Moderator

Joined: Sun Apr 02, 2006 2:46 pm
Posts: 4046
Location: Sacramento, CA
Post Re: Make your logons case sensitive
Thanks for adding this - great tip!

_________________
Carl Gould
Co-Director, Software Engineering
Inductive Automation


Thu Mar 11, 2010 8:07 am
Profile WWW
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: