Suggestions for database setup

M

Mark

Hi - I want to provide a secure (ASP) based file browser - based on a
parent/child table of files/images etc - stored in a directory on a
server.

I want to be able to have individuals, who belong to groups, to be able
to access various folders - no problem -assign a group to the folder,
and check that that person belongs to one of the groups of the folder
before displaying it.

However, I want to go one further - rather than just 'can view/can't
view' for each individual/group for each folder, I'd like to grant
additional rights to the person to be able to modify/add/delete from
folders - but for those privileges to be set for individual folders, not
for the user as a whole. Similar to the way in which Windows Explorer
secures files and directories.

Can anyone suggest a database schema which could tackle this?

Thanks for any pointers,
 
B

Blair Bonnett

I've done something similar (not quite as complex though) with the Admin
section of my site. My solution would rely on the following database
tables:

tblUsers - provides information about the users on your system
UserID (autoincrementing primary key) - an individual User ID for each
user on your system
GroupID (one-to-many relationship from tblGroups.GroupID) - the ID of the
group the user belongs to
Username, password etc... - as neccessary for the rest of the site

tblGroups - provides information about the groups on your system
GroupID (autoincrementing primary key) - an individual Group ID for each
group you create
Group Name etc... - as neccessary for the rest of the site

tblFolders - provides information about the folders on your site
FolderID (autoincrementing primary key) - an individual Folder ID for each
folder on your system
Foldername etc... - as neccessary for the rest of the site

tblPermissions
GroupID (one-to-many relationship from tblGroups.GroupID) - the ID of the
group the permission is for
FolderID (one-to-many relationship from tblFolders.FolderID) - the ID of
the folder the permission is for
Read (Boolean) - if the group has read permission for this folder
Modify (Boolean) - if the group has modify permission for this folder
Add (Boolean) - if the group has add permission for this folder
Delete (Boolean) - if the group has delete permission for this folder

When you add a group, you'd add all neccessary information in tblGroups,
and specify the permissions for each existing folder in the tblFolders
table. If you added a new folder, then I guess you'd set a generic set of
permissions for all existing groups, and then modify them on a case-by-case
basis.

Then as an ASP file included on each page:
Since you'd have the UserID passed to each page as part of the login, you'd
be able to retrieve the ID number of the group they belong to from
tblGroups. You'd also have the ID number of the current folder. You'd then
request the Read, Modify, Add and Delete permissions from tblPermissions,
and set them to boolean variables, which you could then use to evaluate
whether or not the selected action is allowed.

If you wanted, you could also set default permissions in tblFolders. Then,
if there is no entry in tblPermissions, use the default folder permissions.

Hope this helps,
Blair
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top