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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-30 : 10:59:05
|
Michael writes "I'm not sure if this is "guru level," but I am trying not to re-invent the wheel here. I am building an authentication scheme for a client extranet in ASP using an ISAPI filter to control access to files - the filter uses a stored procedure call to a SQL 7 DB to see if the user has the priveleges necessary to see the file. I am now building the data model to represent the directories and files, and the users and groups. I know this has been done before - frequently I'm sure!
The following list gives the basic entities and their relationships:
- Clients have Projects
- Projects contain Milestones
- Directories are associated with Milestones
- Directories contain other (Sub)Directories and Files
- Users belong to Groups
- Groups have access to Directories and Files
There is more to the DB than this, but that is the relevant part.
So here's the questions:
- What's the best way to represent a tree in SQL? Should the leaf nodes of the tree live in the same table?
- If the leaf nodes (ie files) live in the same table, should I use a separate table to store attributes about files that aren't relevant to directories?
- If I want to assign permissions, at what level(s) do I represent the permissions in the data model - I may want to allow user (a) to read a file, and user (b) to edit that file.
- The above seems straightforward, but what about user (b) being able to add a file to a directory (which belongs to a milestone, which is a part of a project for a client. That would imply priveleges at a higher level - how do I represent these levels?
- A million other details I can't think of right now.
Thanks in advance. I have looked around a while for info on the general issues involved with representing users/groups/permissions in an RDBMS, so, as they say in the Holy Grail, "any help you can give would be most helpful." |
|
|
|
|
|
Data Model for a 'roll your own' authentication scheme