December 3rd, 2002, 05:11 PM
table design question
Hi all. Here's the situation. I'm building a CMS which will be serving content from a database, and I'm trying to figure out the best way to restrict user access to specific content in the database.
Here's the tables which need to be considered here:
cms_groups (holds group_id and name)
cms_users (holds user_id, group_id, and other info)
cms_areas (holds area_id and name)
cms_content (holds content_id, area_id, and other info)
Now, I was thinking that I would create another table, cms_access, which would have four columns,
Then for each page or directory which I wanted to protect, I would just have a record added with an associated user and/or group id.
From here when a user was logged in and needed a listing of all content which they can access, I would hit a view which pulled the content and areas for each specific user, based on what they were given access to in the cms_access table.
Does this seem to be a feasible solution? Can anyone offer any advice as to how it could be improved?
Thanks in advance,
December 9th, 2002, 03:23 PM
Yeah, that is a pretty good way of restricting access.
You might also want to record a history of assignments and auditing information as well.
I would change your cms_access table slightly, making it an assign table. This way you have a history of when something was accessible.
Possible table structure of cms_access_assign:
All fields by allocation_time are part of the primary key. allocation_time is the time that the allocation starts being active. deallocation_time is the time it stops working. For assignments that are always on, you can just choose an arbitrarily large future date, like 1/1/4000.
so the way that you would work out what privileges were granted to 'bob' directly is:
select * from cms_access_assign
where id = 13
and id_type = 'USER'
and now() between date_allocated and date_deallocated;
I removed area_id from the privilege because if you moved the content to a new area then the assignment would become 'broken' under the way you were suggesting.
If you bung this into a view the information on current user access should become quite tidy in the application layer.