Web Authentication System - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Web Authentication System

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-02-22 : 09:55:40

Steve writes "I have a need to restrict users based upon their job definition at company A. Certain users will be allowed to access program B with read-only permissions, others with write permissions, and others with out access at all. Repeat for about 15 to 20 programs, and in the future there is a large possibility of anywhere up to in the hundreds of programs, and multiple permission levels.

I would like to setup groups also, and set members into these groups, (this is for inheritance).

What I am looking for is how to solve this many-to-many relationship. I do not require code."

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-02-22 : 11:16:26

well if you want to do this in a database I use this structure often and it works well


Systems
 - SystemID
 - Name
 
Permissions
 - PermissionID
 - SystemID
 - Name
 
Users
 - UserID
 - LoginName
 - Password
 
UserGroups
 - UserGroupID
 - Name

UserGroupMembers
 - UserGroupID
 - UserID
 
UserPermissions
 - UserID
 - PermissionID
 
UserGrouPermissions
 - UserGroupID
 - PermissionID

something like this seems to work very well and the security guys like it because it makes their job a lot easier administrating privilages between applications ... but of course this is web based...

- Onamuji

uberbloke
Yak Posting Veteran

67 Posts

Posted - 2002-02-22 : 11:50:21

For security permission I use a bit mask, so for each area your just store userid and a binary number

admin, delete, add, update, view
16,8,4,2,1

So for area one you might have 7 (view, update, add)
Or
31, which is everything.

Then if you need to add on anther permission level, its easy, just use the next binary number (in this example) 32.

If you do all of your comparisions using OR then all the old priviledges will be retained, have a look at

[url]http://www.aspmessageboard.com/forum/databases.asp?M=293983&F=21&P=1[/url] which is where I started.

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-02-22 : 12:07:07

that's a good idea too... I used something similar to identify the different permissions


Permissions
 - PermissionID
 - SystemID
 - Name
 - Description
 - Property
 
 CHECK (Property > 0 AND FLOOR(LOG(Property)/LOG(2)) = (LOG(Property)/LOG(2)))
 PRIMARY KEY (PermissionID)
 UNIQUE (SystemID, Name, Property)
 UNIQUE (SystemID, Name)

That allows me to specify a single bit for each permission and reference those bits to do lookups rather than by name or by id (which could change) ... also for granting or revoking permissions is easy .. just or all the properties together ...

- Onamuji

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources