• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 885
  • Last Modified:

user password database

Hi,

Please kindly guide me on desining a username/password table.

what are the typical fields needed? we need a multiuser login. using asp.net

presently columns are:
Date
Username
Password
workstation ip address ?

Thanks.
0
EdwardPeter
Asked:
EdwardPeter
  • 4
  • 4
  • 2
  • +3
4 Solutions
 
Thandava VallepalliCommented:
emailid
phone
address
register_date
0
 
rafranciscoCommented:
You can also add some user information if you want such as:

First Name
Last Name
LastLoginTime (datetime)
Email Address
0
 
adatheladCommented:
It all depends on what you need for your specific environment.
i.e.
I usually start with the following:

UserId (INT IDENTITY)
UserName
ForeName
Surname
Password (encrypted)
Active

Other common columns include:
LastLoggedIn
CreationDate

But to be honest, the only fields you should include are the ones that you think you need, which depends on the functionality that you need - there's no point adding in some of these fields if you will have no use for them. In other words, only you know what fields you will need ;)

Hope this helps
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
EdwardPeterAuthor Commented:
adathelad,

Your the man!

Can you kindly assist me how to encrypt ?

Thanks.

0
 
Thandava VallepalliCommented:
SQL Server's Undocumented Password Encryption Functions

http://www.windowsitpro.com/Article/ArticleID/9809/9809.html
0
 
Thandava VallepalliCommented:
Here is the code for encrypting passwords

DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'itsvtk'
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)
0
 
EdwardPeterAuthor Commented:
itsvtk,

Is it okay if you could kindly guide me how to encrypt and decrypt?

Thanks so much.
0
 
adatheladCommented:
As the pwdencrypt() function is undocumented, I'd personally stay away from it - there's no guarantees on it's use/functionality in future versions.

Instead, I prefer to have my front-end do the encryption - especially as .NET has its own Cryptography classes to make it easier. (e.g. http://www.dart.com/samples/cryptdecryptdotnet.asp )

there's also a lot of other 3rd party encryption tools out there.
0
 
Thandava VallepalliCommented:
Eg:

WHILE INSERTING USER INFO INTO TABLE


INSERT INTO <TABLENAME> ( UNAME, PWD, EMAIL )
SELECT 'ITSVTK',   pwdencrypt( 'EGYPT' ), 'VTK@DOMAIN.COM'

the above statement will insert encrypted password in table

while checking the password entered by user with the  pwd field in table....


DECLARE @PWD VARCHAR(255)

SELECT @PWD = PWD
FROM <TABLENAME>
WHERE UNAME = 'ITSVTK'

select pwdcompare('EGYPT', @PWD)   -- If it returns 1, password entered by the user is correct... otherwise its wrong..

itsvtk




0
 
Eugene ZCommented:
0
 
EdwardPeterAuthor Commented:
itsvtk,

Please kindly assist if it is like this.

________________________________________________________________procedure encrypt

exec dbo.encrypt @username='my username here', @password='my password here'

create proc dbo.encrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'itsvtk'                                            <----------------------------what is this ?
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)
INSERT INTO <TABLENAME> ( UNAME, PWD,)
SELECT 'ITSVTK',   pwdencrypt( @mypassword )
GO


________________________________________________________________procedure decrypt
exec dbo.decrypt @username='my username here', @password='my password here'

create proc dbo.decrypt
@myUsername as varchar(20)
@myPassword as varchar(10)
as
DECLARE @ClearPIN varchar(255)
DECLARE @EncryptedPIN varbinary(255)
SELECT @ClearPIN = 'itsvtk'                                            <----------------------------what is this ?
SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))
SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

SELECT @PWD = PWD
FROM <TABLENAME>
WHERE UNAME = @myusername
select pwdcompare(@mypassword, @PWD)  <---------hope this is correct?
Go
0
 
Greg RowlandSoftware Designer, SysDBA, WebMaster OwnerCommented:
Excerpt from DotNetNuke.com Open Source Initiative.

CREATE TABLE [Users] (
      [UserID] [int] IDENTITY (1, 1) NOT NULL ,
      [FirstName] [nvarchar] (50) NOT NULL ,
      [LastName] [nvarchar] (50) NOT NULL ,
      [Street] [nvarchar] (50) NULL ,
      [City] [nvarchar] (50) NULL ,
      [Region] [nvarchar] (50) NULL ,
      [PostalCode] [nvarchar] (50) NULL ,
      [Country] [nvarchar] (50) NULL ,
      [Password] [nvarchar] (50) NOT NULL ,
      [Email] [nvarchar] (100) NOT NULL ,
      [Unit] [nvarchar] (50) NULL ,
      [IsSuperUser] [bit] NOT NULL CONSTRAINT [DF_Users_IsSuperUser] DEFAULT (0),
      [Telephone] [nvarchar] (50) NULL ,
      [Username] [nvarchar] (100) NOT NULL ,
      CONSTRAINT [PK_Users] PRIMARY KEY  NONCLUSTERED
      (
            [UserID]
      )  ON [PRIMARY] ,
      CONSTRAINT [IX_Users] UNIQUE  NONCLUSTERED
      (
            [Username]
      )  ON [PRIMARY]
) ON [PRIMARY]
GO


0
 
EdwardPeterAuthor Commented:
Hi,

I'll repost and hopefully other experts will join.

Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now