locked
Creating per item permissions RRS feed

  • Question

  • I am creating a site where users will be able to upload content and set permissions on a per-item basis. They may choose to have the some content should be private, public, or shared with certain users or groups. Most, if not all, of the content will be shared as read only.

    I'm sure this has been done a 100 times before and would like to use better DB design skills than my own. Perhaps there is a best practice for this? Does any one know where I could find this info or know how to effectively handle this scenario.

    Thanks.

    Tuesday, March 16, 2010 12:21 AM

Answers

  • That's one way. It depends how group is defined. If view is the only right, right can be left out completely, as it would be implicit in the relationship.

    Though,  no reason to query it each time. CREATE a User_Right VIEW that defines what all users and their rights. Querying that will be easy, and will guarantee the correct rules are used each time.
    Tuesday, March 16, 2010 1:21 PM
    Answerer
  • A VIEW is a hard coded query, so it can be treated like a TABLE.

    Here's an example:

    CREATE VIEW User_Right AS
     SELECT User, Right FROM User_Right
     UNION ALL
     SELECT Group_User.User, Group_Right.Right
     FROM Group_Right, Group_User
     WHERE Group_User.Group = Group_Right.Group;
     
    Thursday, March 18, 2010 12:53 PM
    Answerer

All replies

  • What is the exact list of rights? Are they mutually exclusive?

    Normally, rights is a simple TABLE listing rights:

    Right
    --------
    Code(PK)
    Description

    User
    -----
    Id
    Name

    Object
    -------
    Id
    Name

    Object_User_Right
    ----------------
    Object(FK)
    User(FK)
    Right(FK)

    Tuesday, March 16, 2010 2:14 AM
    Answerer
  • So view is typically the only right. But I want to have users and groups, not just users.

    So would I have an Object_Group_Right and a Object_User_Right and then I would have to query both tables for a match?

    Thanks.
    Tuesday, March 16, 2010 4:24 AM
  • That's one way. It depends how group is defined. If view is the only right, right can be left out completely, as it would be implicit in the relationship.

    Though,  no reason to query it each time. CREATE a User_Right VIEW that defines what all users and their rights. Querying that will be easy, and will guarantee the correct rules are used each time.
    Tuesday, March 16, 2010 1:21 PM
    Answerer
  • Agreed.  You need to be careful with modification rights using views like this, but it is definitely the manner I would suggest too.
    Louis

    Thursday, March 18, 2010 4:59 AM
  • I've never used views. How would I set up the view to do this?

    Thursday, March 18, 2010 5:48 AM
  • A VIEW is a hard coded query, so it can be treated like a TABLE.

    Here's an example:

    CREATE VIEW User_Right AS
     SELECT User, Right FROM User_Right
     UNION ALL
     SELECT Group_User.User, Group_Right.Right
     FROM Group_Right, Group_User
     WHERE Group_User.Group = Group_Right.Group;
     
    Thursday, March 18, 2010 12:53 PM
    Answerer