Extranet SQL SERVER ASP
- I'm creating an ASP extranet application where different users will have access to details such as work we've done for them, costs, and scheduled work.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 sees 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 = trueb. 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. -
This will work as long as session vars don't kill you.
Asfar as user information, I don't see anything listed to help prevent access there?
We used to oly allow access through views, which joined each row by it's PK to a table that had the PK and the userid. Admin overhead, but worked well for security.
-
Asfar as user information, I don't see anything listed to help prevent access there?
What do you mean by this?
We used to oly allow access through views, which joined each row by it's PK to a table that had the PK and the userid. Admin overhead, but worked well for security.
So how do you do it now?
Do you think that I should do it differently?
BTW - Thanks for your quick response!
-
I created a very similar application not too long ago. To avoid excess administration I did not create database level logins, partly because some users logging into the application had privileges to add additional users to their extranet group. There was one login to the database used and all content was secured by lookup tables that contained access rights. There was actually no content stored on the file system as all sensitive data was stored as blob's or varchars with lookup tables to control additional levels of access and rights. Session variables were used sparingly, and the only information I kept in session was their name for some tailoring, their companyID for group rights and an admin variable for additional privileges. The app was further customized to allow for full administrative and CMS maintenance by marketing and by utilizing my security model, there was full exposure to all logins and associated content. This was especially beneficial considering our distributed administrative model.
Performance was excellent even when there was 100 + concurrent users.
Once the application was created, IT had little involvement with the day to day maintenance. Interestingly enough MS implemented a similar model of content and administrative rights with their new portal products.
-
I say used to because I don't work for that company any more. AFAIK, they still do it that way. I mentioned it because you say you want to use Views, but don't mention details. You oculd do:
create view companya_data
as select * from datatable where company = 'companya'
etc.
which works, but it lacks some flexibility that will come back to bite you.
We did the following:
Datatable
company data1 data2
--------- ------- --------
a sda adas
a asdasd asdsada
b asdsa asdsadas
c adsasd asdads
Then have a join table for security
User company
----- -----------
bob a
bob b
Sam c
Joe b
If you use a view that joins these on company
create myview as select a.*,b.user from datatable a inner join usertable b on a.company=b.company
then you always select from the view qualifying it with the current user, say Bob, "select * from myview where user = 'bob'", you will get company A and B data. Sam only sees company c data, Joe only B.
-
thanx - that's great advice. I think I'll do it you way!
-
just my 2 pennies,
you said you were using asp. Have you thought of asp.net? they have a very easy to implement Forms Authentication that allows your user once logged in to have an Authentication cookie. That means you do not have to chase around the data and pass it from page to page. Also, they have a method for moving your session variables to the sql server so that you can scale more easily if you need to.
tal mcmahon
-
yep - i am going to use (and learn at the same time) .net for subsequent extranet applications. the main reason for sticking with asp is that I'm going to cobble lots of it together from an existing intranet app.
Just me being lazy!!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply