Security on a file hierarchy - 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)

Security on a file hierarchy

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-15 : 13:12:03

Hi,

I have a file hierarchy implemented using the adjacency list model.

The hierarchy is comprised of folders and files. Folders can have permissions set on them for a user group. Files do not have permissions set on them. They inherit their permissions from the first parent folder which has permissions set on them. Similarly, folders without permissions on them also inherit their permissions.

So given a request for a file, I need to determine if the user's group has access to the file.

What I want to avoid, is recursing up the tree to look for the parent folder with permissions defined on it - there's just too many times that this'll need to be done.

So what I was thinking of doing is storing the ID of the 'permissons parent folder' for each file and folder in the row of that file or folder.

This is a bit like this... http://www.sqlteam.com/item.asp?ItemID=8866 except that I'm not storing the entire lineage in row, just the parent with the relevant permissions set on it.

Does anyone think this a good or bad idea?

Apart from the redundancy, which I think is justified given the avoidance of the recursions, the only issues I see are in..

Firstly making sure that if a file is moved, it needs to have its permissions folder updated. This can just be set to that of its new parent.

Secondly, if a folder is moved, it also needs its permissions folder updated but also all of its children need their permissions folder updated. So this would lead to more recursion.

Any comment?

Cheers, XF.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 14:04:47

How deep is your hierarchy, (how many levels will there be?) I'm thinking there would be a cut line where beyond a certain nesting level, the maintainance of a permissionid is worth avoiding the recursion. I'm not sure where that line would be.

Since you're only interested in parents rather than all children, I would think that the recursion would be your better bet. Unless your hierarchy went to more than, I don't know...maybe 20 levels before reaching a permission folder. I guess the other factor is how often are you just getting permissions compared to moving/deleting/creating folders?

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 14:34:03

btw, I'm not sure what you had in mind for "recursion" but I wouldn't actually have a recursive function or SP. I would just loop through the levels until you hit a permission folder with something like this:


set nocount on
create table #hierarchy ([id] int, parentid int, permisionid int)

insert #hierarchy ([id],parentid,permisionid)
select 1,null,1 union all
select 2,1,null union all
select 3,1,null union all
select 4,2,2 union all
select 5,4,null union all
select 6,5,null union all
select 7,6,null

go

--SP input
declare	@current int
set @current = 7


declare @permisionid int

select	@permisionid = permisionid 
	,@current = parentid
from	#hierarchy 
where	[id] = @current

while @@rowcount > 0 and @permisionid is null
begin
	select	@permisionid = permisionid
		,@current = parentid
	from	#hierarchy
	where	[id] = @current
end

select @permisionid

go

drop table #hierarchy

Be One with the Optimizer
TG

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-15 : 14:39:40

Hi there.

I honestly don't know how deep the hierarchy is. I'll have to wait until the site is in use and its the same with the other CRUD operations.

But I know that I'll need a permission every time a user:

- looks at a file's properties
- downloads a file
- chooses a location to upload a file
- browses a folder's contents
- renames a file or folder
- deletes a file or folder
- moves a file or folder
- creates a folder

And the real killer is when a user does a file search because every file that matches the search will have to be checked!

Please tell me if you disagree, but the only real draw back to storing the permission folder in each row is the overhead of the recursive updates when moving a folder?

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-15 : 14:41:28

Yes I agree. Just looping instead of recursive function call.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-15 : 15:33:00

>>And the real killer is when a user does a file search because every file that matches the search will have to be checked!

This may make me change my answer to storing the permissionid with each file

>>Please tell me if you disagree, but the only real draw back to storing the permission folder in each row is the overhead of the recursive updates when moving a folder?

Well it's not just the overhead in maintaining it as permissions change (for any reason). But also the complications of concurrency. You could have either excessive blocking or incorrect results (for dirty reads). May not be much of an issue if you're not moveing stuff around too much.

Be One with the Optimizer
TG

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-15 : 17:53:23

Thanks for your replies.

>>May not be much of an issue if you're not moveing stuff around too much.

Yes well something I didn't mention is that, apart from one exception, the current design only has permissions set on top level folders and you can only browse one top level folder at a time. So moving a folder would never result in a recursive update of the children.

Surely this problem must come up every now and again. Does anyone know of any articles on it? How, for example, does Windows structure permissions set on files and folders?

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2005-12-30 : 10:04:39

quote:
>>And the real killer is when a user does a file search because every file that matches the search will have to be checked!

This may make me change my answer to storing the permissionid with each file

Well I have changed my mind. I have realised that if I created a new permission folder and wanted to inherit all of the permissions of the parent permission folder then a copy of all those permissions would have to be made. There's just too much maintenance of alot redundant data.

So for operations of files and folders I'm going to step up the tree looking for permission and when a value is returned stick it in the application cache with its own key. So for group = 2, folder = 5, permission = 9 (..rename) and where the action is allowed I'd do the following..

Cache[string.Concat("Perm_", 2, "_", 5, "_", 9)] = true;

If everything gets its own key then the cache is able to prune away values that aren't needed without being stuck with a great monolithic lump in memory.

For the file search I'm planning to have a table in the database which holds all of the folders that all the groups can access which is generated on demand when a search is first performed. If folders are added or removed or permissions changed the the table can be re-generated. So this table is like a cache at the database.

The search query would just join to this table to evaluate whether a user's group can see a file.

Any thoughts?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources