locked
ERD for users and roles RRS feed

  • Question

  • hi , could you please tell me if this ERD is right or not , it has two parts ,

    1- i should be able to assign privileges on objects to users .

    2- i should be able to assign privileges on objects to Roles and assign that role to a user or more .

    Saturday, February 20, 2016 9:04 PM

Answers

  • >In reality a user can be part of more than one roles which is why i gave you above model

    No.  Reality is defined by eslam_elbyaly's domain rules.  And additional "flexibility" is additional cost.  You might discover a genuine need for a user to have multiple roles, which can be added in a later version.

    So something like:

    User
    ------
    ID - PK
    Name
    Role_ID - FK to Role(ID)
    
    Role
    ------
    ID - PK
    NAME
    
    
    
    SYS_OBJECTS
    ------------
    ID - PK
    NAME
    TYPE
    
    PRIVILEDGES
    --------------
    ID - PK
    NAME
    
    OBJECT_PRIVILEDGES
    -------------------
    OBJ_ID - FK TO SYS_OBJECTS(ID)
    PRIV_ID - FK TO PRIVILEDGES(ID)
    RoleID - FK TO Role (ID)
    PRIMARY KEY (RoleID,OBJ_ID,PRIV_ID)
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, February 21, 2016 12:37 PM

All replies

  • Do you _really_ need to be able to assign privileges directly to users?  Probably not.  So eliminate OBJ_PRIVS_FOR_USER.

    Then a USER has zero or more roles, so ROLE_ID would no be a column on USERS. And there wouldn't be a FK between OBJ_PRIVS_FOR_ROLE and USERS in any case, since ROLE_ID isn't a key for USERS.

    Generally, write down the rules for your domain, and the model will be obvious.  EG,

    "A user can be a member of 0 or 1 role" (if that's the rule you want)

    "A role has priveleges on objects"

    . . .

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Naomi NEditor Tuesday, February 23, 2016 5:57 PM
    Saturday, February 20, 2016 9:42 PM
  • i think you can go for something like below for flexibility

    Users
    ------
    ID
    Name
    
    
    Roles
    ------
    ID
    NAME
    
    RoleMember
    -------------
    MemberID
    UserID - FK to User(ID)
    RoleID - FK to Role (ID)
    
    
    SYS_OBJECTS
    ------------
    ID
    NAME
    TYPE
    
    PRIVILEDGES
    --------------
    ID
    NAME
    
    OBJECT_PRIVILEDGES
    -------------------
    OBJ_PRIV_ID
    OBJ_ID - FK TO SYS_OBJECTS(ID)
    PRIV_ID - FK TO PRIVILEDGES(ID)
    RoleID - FK TO Role (ID)
    UserID - FK TO USer(ID)

    You would require UserID in OBJECT_PRIVILEDGES  only if you want permissions to be assigned to users directly. Another better alternative is link only to Role and add all users to a default role


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, February 21, 2016 6:13 AM
  • Do you _really_ need to be able to assign privileges directly to users?  Probably not.  So eliminate OBJ_PRIVS_FOR_USER.

    Then a USER has zero or more roles, so ROLE_ID would no be a column on USERS. And there wouldn't be a FK between OBJ_PRIVS_FOR_ROLE and USERS in any case, since ROLE_ID isn't a key for USERS.

    Generally, write down the rules for your domain, and the model will be obvious.  EG,

    "A user can be a member of 0 or 1 role" (if that's the rule you want)

    "A role has priveleges on objects"

    . . .

    David


    David http://blogs.msdn.com/b/dbrowne/

    the rules for my domain are :

    - each users can be assigned one role.

    - each role has privilege or more .

    Sunday, February 21, 2016 11:04 AM
  • i think you can go for something like below for flexibility

    OBJECT_PRIVILEDGES
    -------------------
    OBJ_PRIV_ID
    OBJ_ID - FK TO SYS_OBJECTS(ID)
    PRIV_ID - FK TO PRIVILEDGES(ID)
    RoleID - FK TO Role (ID)
    UserID - FK TO USer(ID)

    You would require UserID in OBJECT_PRIVILEDGES  only if you want permissions to be assigned to users directly. Another better alternative is link only to Role and add all users to a default role

    if this is the structure , then i could assign more than role to the same user , and that's not what i want .

    Sunday, February 21, 2016 11:09 AM
  • i think no need to mention these tables ( users , sys_objs , privileges and roles ) , if they are good .
    Sunday, February 21, 2016 11:10 AM
  • i think you can go for something like below for flexibility

    OBJECT_PRIVILEDGES
    -------------------
    OBJ_PRIV_ID
    OBJ_ID - FK TO SYS_OBJECTS(ID)
    PRIV_ID - FK TO PRIVILEDGES(ID)
    RoleID - FK TO Role (ID)
    UserID - FK TO USer(ID)

    You would require UserID in OBJECT_PRIVILEDGES  only if you want permissions to be assigned to users directly. Another better alternative is link only to Role and add all users to a default role

    if this is the structure , then i could assign more than role to the same user , and that's not what i want .

    In reality a user can be part of more than one roles which is why i gave you above model

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, February 21, 2016 11:57 AM
  • >In reality a user can be part of more than one roles which is why i gave you above model

    No.  Reality is defined by eslam_elbyaly's domain rules.  And additional "flexibility" is additional cost.  You might discover a genuine need for a user to have multiple roles, which can be added in a later version.

    So something like:

    User
    ------
    ID - PK
    Name
    Role_ID - FK to Role(ID)
    
    Role
    ------
    ID - PK
    NAME
    
    
    
    SYS_OBJECTS
    ------------
    ID - PK
    NAME
    TYPE
    
    PRIVILEDGES
    --------------
    ID - PK
    NAME
    
    OBJECT_PRIVILEDGES
    -------------------
    OBJ_ID - FK TO SYS_OBJECTS(ID)
    PRIV_ID - FK TO PRIVILEDGES(ID)
    RoleID - FK TO Role (ID)
    PRIMARY KEY (RoleID,OBJ_ID,PRIV_ID)
    

    David


    David http://blogs.msdn.com/b/dbrowne/

    Sunday, February 21, 2016 12:37 PM

  • User
    ------
    ID - PK
    Name
    Role_ID - FK to Role(ID)
    
    Role
    ------
    ID - PK
    NAME
    
    
    
    SYS_OBJECTS
    ------------
    ID - PK
    NAME
    TYPE
    
    PRIVILEDGES
    --------------
    ID - PK
    NAME
    
    OBJECT_PRIVILEDGES
    -------------------
    OBJ_ID - FK TO SYS_OBJECTS(ID)
    PRIV_ID - FK TO PRIVILEDGES(ID)
    RoleID - FK TO Role (ID)
    PRIMARY KEY (RoleID,OBJ_ID,PRIV_ID)

    i already have mentioned the rules , and i totally agree with this structure , and i think if i need to assign privs directly to users , i should create a table like " OBJECT_PRIVILEGES " you have created , but instead of " RoleId" , i should put " UserId " .

    - the role is created in order to " create once " and assign it many times to many users , then may be i need to assign some privs to a user without the intent to assign same privs to another user , that's why i think i need to assign privs directly to users without including privs into roles .

    - about reality :-

    the reality is , i can assign any number of privs to a role then assign the role to a user , then why the need to assign more than one role to a user ? !!

    • Marked as answer by eslam_elbyaly Tuesday, February 23, 2016 6:25 PM
    • Unmarked as answer by eslam_elbyaly Tuesday, February 23, 2016 6:25 PM
    Sunday, February 21, 2016 7:39 PM
  • what do you think ?
    Tuesday, February 23, 2016 2:34 PM
  • >then why the need to assign more than one role to a user ? !!

    If you have a complicated permissions model, you might end up with a large number of roles.  As you would need a new role for say CLERK_WHO_IS_ALSO_A_MANAGER or TESTER_WHO_IS_ALSO_A_DEVELOPER, etc.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, February 23, 2016 4:24 PM
  • hmmm , it is more organized or detailed .

    good but do not need it at all .

    but the erd we reach to is correct , right ?

    please reply to this

    i already have mentioned the rules , and i totally agree with this structure , and i think if i need to assign privs directly to users , i should create a table like " OBJECT_PRIVILEGES " you have created , but instead of " RoleId" , i should put " UserId " .

    Tuesday, February 23, 2016 5:41 PM
  • You can do that.  Or you can just have roles, and if you need to assign particular permissions to a user, create a one-member role for that user.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, February 23, 2016 5:50 PM