Extranet - ASP, SQL Server authentication

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Extranet - ASP, SQL Server authentication

Author

Topic

bjh
Starting Member

14 Posts

Posted - 2004-06-08 : 09:03:57

Hi,

I'm new here and would appreciate any help and advice you can give me.

I've been given the task of creating an extranet application using ASP and SQL Server and am at the moment considering how I can make it secure. My manager has suggested using SQL logins and authenticating them straight onto SQL server. For example, a user will come to the login page, enter their username and password, which will then be used to go straight through authenticate them on SQL Server. Anonymous access to IIS would be used.

I'm not sure how successful this would be, as I can't see a way of giving a meaningful message when and if the login credentials are incorrect (wouldn't SQL Server just spit out an error message about permissions?). I can see it working if they enter the correct details, and then I suppose I could set a session variable set them as being authenticated.

The bit that I'm stuck on is the need for each authenticated user to have access only to details relevant to them. I suppose the use of views would do this (i.e. "view1_[username]") where the username is used to make each view of a certain type unique from one another, and the user has acces to only their views.....

I was trying to figure out a way of using Windows user accounts by somehow assigning them to a relevant account once authenticated against a username and passowrd stored in a table. I could then set permissions on views/stored procs based on user account.

I really am in need of some guidance as to which would be the best way to go on this, so please, if you have done this before, or have any better suggestions, let me know!!

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-06-08 : 14:33:50

Sql server is not the ONLY to secure web pages.
You could easily use session variables like you suggested to authenticate users and redirect them to the pages you want them to have access to based on their login credentials.

so the way I would go is have a table on your sql server db with something like:
firstname
lastname
username
password
access_level
So now, a user could have the following credentials
First name John
Last name Doe
username john
password doe
access_level Admin

then I would have an input form that asks for username and password
and posts to another page, let's call it verify.asp.

Verify.asp would then determine if user's login credential is correct.
If it is, then what access rights does the user have?
if Session.Contents("Access_Level") = "Admin" then
response.redirect "some page" ' this page would contain details relevant to the user
else
'get back to login page
response.redirect "login.asp"
Something like this should work

If I misunderstand you or you need further help, ask

bjh
Starting Member

14 Posts

Posted - 2004-06-09 : 03:56:04

Hi,

I've had a bit of time to develop my thoughts on this now - apologies if I'm repeating myself........

I want to let each user view their own information, but not that of other users. All the information is stored in one database, so I was thinking of using views in SQL Server 2000 to ensure a user only see what they are allowed to.

I envisage writing it as follows...

1) User navigates to our extranet login page.
2) User enters username/password
3) User details checked against SQL Server 2000 "logon" table, which holds, for each user:

a. ASPUserName - the username the user enters into the logon page.
b. ASPPassword - the password the user enters into the logon page.
c. DBUserName - the username the ASP page supplies as part of the database connection string.
d. DBPassword - the password the ASP page supplies as part of the database connection string.

If the details entered by the user are matched to a record on the logon table, I will set the following session variables:

a. bAuthenticated - a boolean value = true
b. sUserName = DBUserName (from the logon table)
b. sPassword = DBPassword (from the logon table)

4) For all subsequent page requests a check is made to see if the user has been authenticated (the bAuthenticated session variable will be true if they have been authenticated). If authenticated, the session variables sUserName and sPassword will be supplied in the connection string for database access. If not authenticated, then the user will be redirected to the logon page.

As far as I can see, this will work quite well. The benefits will be that we do not have to hand over the actual username and password for the database, and the use of stored procs and views can be tied down exactly how we like by setting permissions on the SQL Server user accounts.

A disadvantage may be that I have to use session variables, but this application will never have enough concurrent users for this to become an issue. Another is that I have to maintain two sets of usernames and passwords for each user.

Can you lot see if I've not though of anything glaringly obvious, or can you suggest a better approach?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-06-09 : 08:42:20

Well, if your approach works for you, then go for it.

My approach, if I were to handle this task, will be different.
As I indicated, I would create a login table with username, password, access_level and a field called assignedTo.

Then on the details table, would be among other things, another field called assignedTo that will relate to the assignedTo in the login table.

Then on the front end, I would have one asp page for user input that requests user's login and password.
Once that info is supplied, the info will be posted to another page that verifies information entered - I called that page verify.asp.

The page will determine if login info is correct and the access_level assigned to the person logging in.

Then on every page on your extranet, I would put this code at the top:

<%
Response.Buffer = True
If Session.Contents("Access_Level") <> "ViewOnly" AND _
Session.Contents("Access_Level") <> "ReadWrite" AND _
Session.Contents("Access_Level") <> "FullControl" Then
Response.Redirect "login.asp"
End If
%>
Access level on above code is just an example. You can put whatever you want in their stead.

This way, the user logging in will be redirected to one central location, maybe main page.

Then on the main page, I would put this sql statement:

SQL = "SELECT * FROM detailsTable WHERE assignedTo = '" & Session("assignedTo") & "' "

What this does then is that on the main page, there will be a link that says:
My Information.
The user who logged in whose assignedTo matches the name on the assignedTo field on your database, will be able to see his/her information as soon as he/she clicks that link.
If he/her assignedTo doesn't match the info on the database, you can put in a code that says: No information available for your viewing.

This is simple straight forward approach with asp.
It provides three layers of security.
First by making sure that you can ONLY login in if you have login credentials.
Second, by making sure that you are logging in with proper access level and last but not least, you can only view information that belongs to you.
But again as I said, if you are confident that your approach will work, then go for it.
Good luck

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2004-06-09 : 08:44:26

I wish this forum has the ability to allow you to edit your information.

I forgot to mention that your assignedTo field will house the name of the user who will be allowed to view his/her own information only.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources