Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Russia
    Posts
    2

    store user permissions in 1 or 2 tables?

    I need to store two types of users' permissions:
    1) general permissions (like "modify system settings")
    2) permissions for specific objects (like "user can modify this object")

    if i use the only table to store these both types of permissions:
    create table userpermission (
    userid INTEGER not null,
    serviceid INTEGER,
    permission VARCHAR2(300) not null,
    constraint FK_USERPERM_REFERENCE_SERVICE foreign key (serviceid)
    references action (id),
    constraint FK_USERPERM_REFERENCE_USER foreign key (userid)
    references smsuser (id)
    );

    then I can't create UNIQUE index for all three columns because "serviceid" column is nullable. ("serviceid" column stores references to specific objects).
    the question is - what is a general approach to store these two types of users' permissions? do I need to have two separate tables here - one for userid-serviceid-permission and one for userid-permission?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: store user permissions in 1 or 2 tables?

    Originally posted by alskor
    I need to store two types of users' permissions:
    1) general permissions (like "modify system settings")
    2) permissions for specific objects (like "user can modify this object")

    if i use the only table to store these both types of permissions:
    create table userpermission (
    userid INTEGER not null,
    serviceid INTEGER,
    permission VARCHAR2(300) not null,
    constraint FK_USERPERM_REFERENCE_SERVICE foreign key (serviceid)
    references action (id),
    constraint FK_USERPERM_REFERENCE_USER foreign key (userid)
    references smsuser (id)
    );

    then I can't create UNIQUE index for all three columns because "serviceid" column is nullable. ("serviceid" column stores references to specific objects).
    the question is - what is a general approach to store these two types of users' permissions? do I need to have two separate tables here - one for userid-serviceid-permission and one for userid-permission?
    Perhaps the answer id DBMS-dependent. For example, in Oracle you can create a unique (not primary key) constraint that includes a nullable column - so having both types of permission in a single table is possible. This has the advantage that to query a user's permissions you only need to access one table.

    On the other hand, it may make more sense to store these different kinds of permission in different tables.

    This is a design decision and there is no single answer that is always right. You have to consider the pros and cons, and choose accordingly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •