Database design for file upload solution

[Kapalua]

Hi,

I have tried several solutions for my application which lets several users upload files to a database.
The users belong to groups and should only be allowed access to their files or the groups files(if 'sharing' is on).

I have tried to give every group their own database, but it gets 'messy' with all the dynamic sql, since everybody logs on ,via a webservice, using the same database account and execute the same stored procedure to first find the database and then find the files in the table.

What i'm thinking about now is a similar solution, but when a group is created this group gets a unique database account which they share in that group to log on to their database.
That way i could set the default database (on the groups account) to the groups database and i don't have to use dynamic sql to find the database etc.

What i find 'messy' with this solution is that there could potentially be very many server accounts; is that a technical problem?

What could be a better solution?

Thanks.
// Martin

[dickersonka]

You don't necessarily need 'permission accounts'. Lets say for instance you have a user account id, group account id they are part of.

You can create your files table something like this

file_id
user_id
group_id
file_name
file_path

then when you need to show the user/group their files

select * from files where group_id = ?

select * from files where user_id = ?

you can create separate file paths based upon the group id as well, once you get a descent amount of groups, it can be much harder and complicated to manage

[Kapalua]

Hi,
Wouldn't that be somewhat insecure?
All the users ,from all the different groups, saves their data in the same table?

I know it should work, but it "feels" a little wrong.

[dickersonka]

I wouldn't say that is insecure at all. You should rely up on your code to enforce the security, not rely up on security to enforce your code.

If you want an additional level of security, create a specific user for selecting from this table only. You will always know the group/user id from the current user's login information.

If you really think it will be an issue, take a look at row level security, depending upon what database you are using.

[Kapalua]

I guess you'r right.

One thing that might be a problem when using only 1 table is the max size of the table(which i have not been able to find an answer to), as far as i know the max databasize size in MS SQL 2008 is 524,272 terabytes(http://msdn.microsoft.com/en-us/library/ms143432.aspx) which is way more than this solution will ever need.

Microsoft also says that the max size per table row is 8,060 bytes, which seems a little strange to me as i have successfully uploaded byte[] that have been much larger than that.
How does that fit?

[jpcaparas]

Thanks. The group ID was something I missed out thinking. Permissions are a big, big issue when it comes to the back-end.