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,

- area_id
- content_id
- user_id
- group_id

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,

Pablo

Hi,

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:
id
id_type
content_id
deallocation_time
allocation_time

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.