September 2, 2003 at 4:35 pm
I am using a database to manage access rights to various virtual folders. Each folder has permissions associated with it indicating whether it is a personal folder, public folder or a group folder.
I am having trouble coming up with a query that returns all folders for a given person or group that takes into account the permissions of the folders above it. For example, there are 2 tables:
tbl_Folder
tbl_FolderAccess
tbl_Folder
Id, Title, ParentId, Lineage
1, Main, 0, \Main
2, Client1, 1, \Main\Client1
3, Client2, 1, \Main\Client2
4, Audit, 3, \Main\Client2\Audit
tbl_FolderAccess
FolderId, AccessType, GroupId
1, Public, 0
2, Group, Grp1
3, Group, Grp2
4, Group, Grp3
Where Grp1=Client1, Grp2=Client2 and Grp3=Admin.
A person must belong to the Grp2(Client2) and Grp3(Admin) to be able to access the Audit folder (id=4). If a person only belongs to Grp3, they should not be able to access the Audit folder because they do not belong to the Client2 group.
How can I create a query to accomplish this?
Any help would be appreciated.
Thanks,
Steve
September 3, 2003 at 8:40 am
OK, not to sound pessimistic, but this kind of thing is generally better left to the filesystem. That is specifically what it is designed for. But, that said, I guess the answer depends to your table schema. You've posted the fields, but not the keys, so I'm not sure how to interpret your FolderAccess table. Could you identify the PK on the table?
Edited by - jpipes on 09/03/2003 08:40:58 AM
September 3, 2003 at 12:34 pm
There is another thread around just dealing with this kind of problem.
To go short. You will need the maximum depth of the tree and build some dynamic query.
Or you can use a cursor to run through the tree.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply