#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2002
    Posts
    155
    Rep Power
    13

    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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    55
    Rep Power
    13
    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.

IMN logo majestic logo threadwatch logo seochat tools logo