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 |
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
 Posted - 2005-05-10 : 15:24:04
|
| Hey gurus.I have a sort of web version of file mgmt.Files/foldersusersgroupspermissionsIn an effort to keep the database code compact, I took out some of the basic rules of normalization. It was intentional for version 1.0 of the product.Anywho, A user or group can have permission (view,create,modify,etc)on any file, and any of the folders that contain it. In addition, the user/group can have global permissions (not specific to any item).The Files table holds folders and files (since they have the same attributes.)FilesID,FileName,Folder[0 or 1 to tell if its a file or folder],parentfolderid [points to the filesID] and heirarchy[a mimic of the lineage string in the RobVolk article...works great]the permissions table hasfilesID,UserID,Groupid,permisssionfields....if the permission record is just for a user the filesid = -1,and the groupid = -1(and other combinations for group perm on a file,etc)Anyway, I have a nice sproc that creates a temp table, iterates through the permissions, and climbs the tree looking for explicit permissions on the object, or any of its containers for the user, or any groups they are a member of. The recursion is done using a while loop. Since I only do this when they request the file, it isnt too bad.the user now wants a full view of all the files, and the files filtered out if the user doesn't have permissions. The app runs on an asp frontend, that just reads all the files and does some fancy printing to make the heirarchial view currently. I don't want to have a loop N number of files and then call a heavy sproc for each file to get the permissions from within the loop in ASPIS a view with all the combinations of user and file the way to go? It could be big, but probably in the order of 50 users x 300 files.That would be better than a heavy request for the main page of the app to load the visual GUI for the application.Sorry, this is a load, but theres alot to it. any thoughts?________________________________________________Bier is a privelege of the working man (or woman). |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2005-05-10 : 15:53:04
|
as it relates to trees, I would bet you don't neeed to loop through each file... but ultimately some kind of view or mapping of all files to allowed users would be the best way to go.50x300 is only 15k, which really isn't that bad.Corey Secret Service Agent: Mr. President, you're urinating on me.President Lyndon Johnson: I know I am. It's my prerogative. |
 |
|
|
Vivaldi
Constraint Violating Yak Guru
298 Posts |
Posted - 2005-05-10 : 16:16:20
|
I would tend to agree, but I only get to be 1/2 involved with sql server, therefor my coding prowess isn't as good as I would like. a while loop is functional, easy to work with, and I know how to do it.. but I would definitely prefer to do it completely in set theory.quote:
Originally posted by Seventhnight
as it relates to trees, I would bet you don't neeed to loop through each file... but ultimately some kind of view or mapping of all files to allowed users would be the best way to go.50x300 is only 15k, which really isn't that bad.CoreySecret Service Agent: Mr. President, you're urinating on me.President Lyndon Johnson: I know I am. It's my prerogative.
________________________________________________Bier is a privelege of the working man (or woman). |
|
|
|
|
|
|
|
|
recursion, and the performance problem