October 29, 2011 at 9:01 am
I have a problem with a data correction procedure. The username / password policy of company is changed. They have introduced a minimum length and maximum length for username and passwords.
tblUserAccounts table holds all username, password, email , etc. schema of which is given below.
UserName varchar(35) unique,
Password varchar(15),
email varchar(20),
custnum varchar(10)
The stored procedure has the following input parameters
1. @MinUserNameLen Int
2. @MaxUserNameLen Int
3. @MinPassLen Int
4. @MaxPassLen Int
5. @AdjustmentChar char(1)
The rule is like :
1. If the existing user name length is less than @MinUserNameLen, then we will append the adustment char that many times
eg: @MinUserNameLen = 6 , @AdjustmentChar = '!'
The user 'Kiran' will change to Kiran!
2. If the username exceeds the @MaxUserNameLen, then the user name will get truncated
eg : @MaxUserNameLen = 5, User Name = 'KiranKumar'
The user name will change to Kiran
3. It should not create duplicate records in the tblUserAccounts table. If the new name already exists in the tblUserAccounts table, it will renamed by appending 1,2,3... at the end.
Eg: tblUserAccounts has the following data before SP updation
User NamePassword email cust num
VivekSahnis#5sfsfvivek.sahni@etc.in2323
Praveen sw@#$praveen@etc.in 124
Vivek P@ss vivek@etc.in 4214
VivekPatilKis34#vivek.patil@etc.in 43432
Input parameters are :
1. @MinUserNameLen =3
2. @MaxUserNameLen =5
3. @MinPassLen = 3
4. @MaxPassLen =5
5. @AdjustmentChar ='!'
Output would be like :
User NamePassword email cust num
Vive1 s#5sf vivek.sahni@etc.in2323
Prave sw@#$praveen@etc.in 124
Vivek P@ss!vivek@etc.in 4214
Vive2 Kis34 vivek.patil@etc.in 43432
You can see VivekSahni is truncated to Vivek. As it is already there in the table, it is appended with a number. To fit in the max length, it is renamed to vive1. Same for VivekPatil as well.
We need to keep the log of the records changed in another table called tblUserPassChangeLog
This will have the following fields
OldUserName varchar(35)
NewUserName varchar(35)
OldPassword varchar(15)
NewPassword varchar(15)
I have created a procedure which is working fine in most of the conditions. But failing in where username comes like
AAAAAAAAAAAAAAAAA
AAAAAAAAAA
AAAAAA
Min len 4 and max len 16 kind of conditions.
As this table has close to 8 lakh records in which more than 3 lakh falls into the invalid category.
ie. We need to update almost 3 lakh records.
Hope the scenario is clear. Could any one help ?
November 11, 2011 at 2:35 am
Most people would shun having application-level passwords within a table (bad, bad, bad). Once a client app has succeeded in connecting to SQL (whether as SQL login or Windows-integrated login), that should be it and TSQL can discover username from user_name() or suser_sname() built-in functions.
Assuming you have some some domain authentication (e.g. Active Directory), and this is likely to hold information about individuals (DN, OU etc) including email. And you could extend the schema to include other info (e.g. custnum) if you _really_ wanted.
So my recommendation is to let your infrastructure/HR/security look after AD entries, but that your SQL should get a feed from that authoritative source. Having multiple "versions of the truth" is crazy, and would mean making the "same" change to many data repositories (hence deviation).
There remains the question of mapping that username onto email and custnum fields (i.e. lookup). My suggestion is that you change the PK from being UserName to being email column, remembering that AD is providing {email, username=loginname}. You will have to populate the custnum field if you don't store this in AD.
So don't just look on problem for technical username transform, but look at the real underlying problem !
November 26, 2011 at 5:32 pm
Hi
CAn you try to update the table using a SP handling diff scenarios. E.g. First Scenario check if the Username Length is less than minlength and write an update to increase length and so on...
Take a back up of the table before and then work on the copy version...
Just a thought
Gud luck
Vani
November 26, 2011 at 6:11 pm
First order of business. Why is the T-SQL statement passing the following parameters
The stored procedure has the following input parameters
--Your parameters My comments
1. @MinUserNameLen Int --Must be greater than 0
2. @MaxUserNameLen Int --Can not be larger than 35
3. @MinPassLen Int -- Must be greater than 0
4. @MaxPassLen Int -- Can not be greater than 15
5. @AdjustmentChar char(1)
Considering the -- notes I have added, the first item would be to have your SP check the passed parameters, and if they do not meet the minimum requierements to do nothing to the data, but return an explanatory error message to the originator.
Now most people who want to help you expect you to help them by posting your table definition, and sample data in an easily consumable format. I have done that for you as:
CREATE TABLE #UserAccounts(UserName vARCHAR(35), Password VARCHAR(15), email VARCHAR(20), custnum VARCHAR(10))
INSERT INTO #UserAccounts
SELECT 'VivekSahni', 's#5sfsf', 'vivek.sahni@etc.in', '2323' UNION ALL
SELECT 'Praveen', 'sw@#$', 'praveen@etc.in', '124' UNION ALL
SELECT 'Vivek', 'P@ss', 'vivek@etc.in', '4214' UNION ALL
SELECT 'VivekPatil', 'Kis34#', 'vivek.patil@etc.in','43432'
I have created a procedure which is working fine in most of the conditions. But failing in where username comes like
AAAAAAAAAAAAAAAAA
Please post that procedure.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply