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.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-02-25 : 17:48:11
|
| I have a website that is currently general access for everyone. We have a few features that are just using way too much server resources and we are contemplating the idea of making certain parts require a specific access level.My goal is a very scalable solution. I need to be able to set accounts to have different access levels, so that they are allowed or not allowed access to certain parts. To start is going to be a simple. For example: "level 1 is allowed on this page and level 0 is not". However I want to be able to add more levels and I do not necassarily want a higher level meaning that it can acess all functions below it and more. I want to be able to control all pages/functions that each level can access on a individual basis. I also want to be able to setup access levels to last a certain amount of time, and then revert back to a certain level. I need to log each membership upgrade (purchase)All this and I need it to be a very scalable solution. Altho not all pages will even require any form of access level check, quite a few will. I really dont want to go to the database on this, so I was thinking of either using asp.net's dataset caching, or session variables on the webserver, so ideally the cached data is small.I think that pretty much covers all situations that I might face. Hopefully it all makes sense.Has anybody done something like this before? I've been contemplating doing this for a few months now, but haven't had the confidence in my design abilities to follow thru with it. I'm hoping one of the many SQL guru's here can toss in some experience and knowledge and help me out.Thanks alot!mike123 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-25 : 21:12:38
|
I've done something like this before and it was a quite simple but effective solution. It was made in asp for an intranet I created some years ago. The design is soo simple, and table-structure is like this:users------------------------------------------------------------------UserID |Name(varchar(100)) |Access(varchar(2000)) |------------------------------------------------------------------1 |Frank |Sec1, Sec6, Sec15 | 2 |Paul |Sec3, Sec4, Sec9, Sec22 | ------------------------------------------------------------------ Then in the app where I had the protected stuff I did something like this<%Section = "Sec15"IF InStr(Access, Section) = 0 THEN Response.Write("Piss off, no access")ELSE 'Show me the money ...END IF%>I know it looks totally lame and I did hardcode the name of the sections in the files but it actually worked like a charm. I'll be happy to send you the code if you want it...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
 |
|
|
tribune
Posting Yak Master
105 Posts |
Posted - 2004-02-26 : 00:46:04
|
| Lumbargo that is lame! I dont want to rain on your parade but that designed violates 1NF: atomicity.Mike123,Create two new tables in addition to Users:UsersPermissions-------------------------| UserID | PermissionID |-------------------------Permissions-------------------------| PermissionID | Action |-------------------------ie, for "User 1"UserPermissions:1 | 10001 | 10011 | 1007Permissions:1000 | Allow Eat cake1001 | Allow Eat Quizno subs1007 | Say we like the MOON:)When you do a permission lookup, you can determine if the user has the permission for the page by seeing if the user has permission PK XXXX in UserPermissions.Now, if you want to do some tricky inherited permissions, there is a good article somewhere on this site about heirarchal data. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-26 : 08:44:30
|
| As I said it was a plain and simple solution and terrible when it comes to DB-design, but the...umh..."genious"...(?)...part of it was that "Access" was a plain single attribute to the user and that meant no additional overhead whatsoever in the login-sequence. On every page I needed to do a lookup in the database for a few attributes to the user anyhow so adding an extra attribute didn't add any strain whatsoever. And the HTML to do the user-admin was even simpler:<INPUT TYPE="checkbox" NAME="Access" VALUE="Sec1"<%IF InStr(Access, "Sec1") THEN Response.Write(" CHECKED")%>><INPUT TYPE="checkbox" NAME="Access" VALUE="Sec2"<%IF InStr(Access, "Sec2") THEN Response.Write(" CHECKED")%>><INPUT TYPE="checkbox" NAME="Access" VALUE="Sec3"<%IF InStr(Access, "Sec3") THEN Response.Write(" CHECKED")%>>and then the asp/sql was like this:Access = TRIM(Request.Form("Access"))...SQL = UPDATE users SET Access = '" & Access & "' WHERE UserID = " & UserIDIt looks and really is totally lame but for my purpose it was great and I'll defend it with my life!! So there, hehehe... :)--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-02-27 : 05:32:24
|
| Lumbago thanks but that solution isnt scalable enough for me.tribune, thanks for the help. I agree with what you've put down, but where I really start to get unsure of things is with the "memberships"Each time a membership is credited, I need to insert a row into the DB that has start date, duration, access level, userID etc. And then I need to revert back to the old level.I will have 100k members to start, with possible multiple access levels. Ideally I dont want to be hitting the database for each pageview.What I'm having problems visualizing is what data I bring back. Ideally I would like to have so little that I can keep it in a session variable, can you see this happening? Or maybe I can keep a asp.net cached dataset in memory to keep things fast. Any ideas of some sample data I would bring back?Hope this makes sense its almost 3am here :)Thanks alot for anyhelpmike123 |
|
|
|
uberman
Posting Yak Master
159 Posts |
Posted - 2004-02-27 : 08:03:55
|
| I use set of bit flags, stored as a number0000 = no access = 00001 = view only = 10011 = view and add = 3... etcso my administrator has the number 127You could then store this into session variable and do bit checks whenever you need(The actual implementation is somewhat more complex, in that the application has areas, so I store rights for user and area...Also to make my life easier on some pages where I am doing complex stuff I set flags blnView, blnAdd, blnDelete etc. based on their rights so I can do a quick "if blnAdd " rather then bit checking all the time.Hope this helps..... |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-27 : 08:44:24
|
| Hmm, I must have been asleep or something when I frist read this post and now (!!) after a few days I realized that this actually is on a quite different scale than what I initially thought, so I almost feel I need to apologize for wasting your time with such useless posts. The only thing I really have to add to this discussion is that I belive using sessions for 100k users will be quite a challenge for your server. I have never worked with a website this large but I would assume that 100k sessions would consume quite a bit of memory and slow things down considerably. i would probably go down the lane of MD5-hashing the accesslevel string and put in it a cookie instead. Just my 2 cents I guess...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
|
|
Creating Access Levels Solution for website