locked
Need help trying to put my security model idea to a schema RRS feed

  • Question

  • User-1644933537 posted

    I am designing a centralized management GUI to manage all manner of things in my companies intranet. What I am stuck on is part of the security I am trying to design. I can't get the normalization of the tables correct.

    essentially my concept is as follows:

    • A USER is member of a ROLE which is assigned TRANSACTIONS which allows access to WEBS and/or features in the WEB

    So I start out with 4 tables:

    • USERS (all users in organization)
      • UserID
    • ROLES (all roles related to webs in the organization)
      • RoleID
    • WEBS (list of web in the organization)
      • WebID
    • TRANSACTIONS (list of transactions (permissions for the webs)
      • TransactionID

    Now clearly I need a few more tables that link the IDs together. Initially I had FK_RoleID in the USERS table, but when the scope expanded to include multiple webs, I had to account for a user having different roles in different webs.

    So a USER can have one ROLE per WEB, but can be in multiple ROLES across multiple WEBS.

    Additional "linking" tables

    • USERS_ROLES_WEBS
      • UserRoleID
      • FK_UserID
      • FK_RoleID
      • FK_WebID

    Then I began thinking how that would work in a GUI

    User is selected from a list ............. for editing ............. to add USER to the 'someweb.com' WEB with 'admin' ROLE

    • I would envision two (2) DropDownList's, one listing WEBS and the other dynamically populated with ROLES once a WEB is selected)

    There would have to be a table that contains a list of ROLES for each WEB so that would be i also need:

    • ROLES_WEBS
      • RoleWebID
      • FK_RoleID
      • FK_WebID

    Also since there can be the same ROLE used in multiple WEBS that the TRANSACTIONS assigned also could be different

    • ROLES_TRANSACTIONS
      • RoleTransactionID
      • FK_RoleID
      • FK_TransactionID

    You can see how complex this is getting and I am kind of starting to lose my place already.

    So to reiterate

    • A USER is member of a ROLE which is assigned TRANSACTIONS which allows access to WEBS and/or features in the WEB
    • Each WEBS may not have the same TRANSACTIONS but may have the same ROLES

    Is there another way to think about this?

    Thanks for you time and insight.

    Monday, September 22, 2014 2:07 PM

Answers

  • User269602965 posted

    You are getting into MANY to MANY relationships which requires a JOINING table.

    Example

    One patient can have many doctors

    One doctor can have many patients

    Then throw in VISITS to a health care facility

    Ah but the patient can visit many facilities

    and the doctor can work in many facilities

    Now you can see how you are getting blurry vision real fast right?

    So then you use LINKING table to resolve many to many relationships back into one to many relationships to maintain referential integrity

    PATIENT                    LINKPATIENTDOCTOR               DOCTOR

    PK PATIENTID           PK LINKID                                  PK DOCTORID

                                     FK PATIENT

                                     FK DOCTOR

    So now if I want to know which doctors a patient has seen I go to the many to many resolver, the linking table

    Now apply that to USERS, ROLES, APPLICATIONS

    Users can have many Roles

    Roles can have many Users

    You need a linking table for Users and Roles

    in fact you will need many linking tables since Users, Roles, Applications is kind of a TRIANGLE of many to many relationships.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 27, 2014 1:30 PM
  • User-1644933537 posted

    Thank-you for you reply, I wondered if anyone ever would.

    I had a moment of clarity a couple days ago and came up with the following as the "linking" table structure.

    Its still early in  testing to say this is it, but so far it has been working.

    USERS_ROLES

    • USERROLE_ID (PK)
    • FK_USER_ID
    • FK_ROLETRANSACTION_ID

    ROLES_TRANSACTIONS

    • ROLETRANSACTION_ID (PK)
    • FK_ROLE_ID
    • FK_WEBTRANSACTION_ID

    WEBS_TRANSACTIONS

    • WEBTRANSACTION_ID (PK)
    • FK_WEB_ID
    • FK_TRANSACTION_ID

    Here is the accompanying SQL that resulted from the schema.

    SELECT COUNT(*) FROM RolesTrans 
    RIGHT OUTER JOIN Trans 
    LEFT OUTER JOIN WebsTrans ON Trans.TransId = WebsTrans.FK_TransId ON RolesTrans.FK_WebTranId = WebsTrans.WebTransId 
    LEFT OUTER JOIN UsersRoles ON RolesTrans.RoleTransId = UsersRoles.FK_RoleTransId 
    WHERE (WebsTrans.FK_WebId = @webId ) 
    AND (UsersRoles.FK_UserId = @userId ) 
    AND (Trans.StaticId IN ( @values ))
    • @webId is the ID from a table that contains the root web uri (which is looked up when a user loads the page)
    • @userId is the ID of the user (retrieved from windows authentication)
    • @values are the comma delimited "roles" retrieved from the sitemap for the current page 
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 27, 2014 4:36 PM

All replies

  • User269602965 posted

    You are getting into MANY to MANY relationships which requires a JOINING table.

    Example

    One patient can have many doctors

    One doctor can have many patients

    Then throw in VISITS to a health care facility

    Ah but the patient can visit many facilities

    and the doctor can work in many facilities

    Now you can see how you are getting blurry vision real fast right?

    So then you use LINKING table to resolve many to many relationships back into one to many relationships to maintain referential integrity

    PATIENT                    LINKPATIENTDOCTOR               DOCTOR

    PK PATIENTID           PK LINKID                                  PK DOCTORID

                                     FK PATIENT

                                     FK DOCTOR

    So now if I want to know which doctors a patient has seen I go to the many to many resolver, the linking table

    Now apply that to USERS, ROLES, APPLICATIONS

    Users can have many Roles

    Roles can have many Users

    You need a linking table for Users and Roles

    in fact you will need many linking tables since Users, Roles, Applications is kind of a TRIANGLE of many to many relationships.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 27, 2014 1:30 PM
  • User-1644933537 posted

    Thank-you for you reply, I wondered if anyone ever would.

    I had a moment of clarity a couple days ago and came up with the following as the "linking" table structure.

    Its still early in  testing to say this is it, but so far it has been working.

    USERS_ROLES

    • USERROLE_ID (PK)
    • FK_USER_ID
    • FK_ROLETRANSACTION_ID

    ROLES_TRANSACTIONS

    • ROLETRANSACTION_ID (PK)
    • FK_ROLE_ID
    • FK_WEBTRANSACTION_ID

    WEBS_TRANSACTIONS

    • WEBTRANSACTION_ID (PK)
    • FK_WEB_ID
    • FK_TRANSACTION_ID

    Here is the accompanying SQL that resulted from the schema.

    SELECT COUNT(*) FROM RolesTrans 
    RIGHT OUTER JOIN Trans 
    LEFT OUTER JOIN WebsTrans ON Trans.TransId = WebsTrans.FK_TransId ON RolesTrans.FK_WebTranId = WebsTrans.WebTransId 
    LEFT OUTER JOIN UsersRoles ON RolesTrans.RoleTransId = UsersRoles.FK_RoleTransId 
    WHERE (WebsTrans.FK_WebId = @webId ) 
    AND (UsersRoles.FK_UserId = @userId ) 
    AND (Trans.StaticId IN ( @values ))
    • @webId is the ID from a table that contains the root web uri (which is looked up when a user loads the page)
    • @userId is the ID of the user (retrieved from windows authentication)
    • @values are the comma delimited "roles" retrieved from the sitemap for the current page 
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, September 27, 2014 4:36 PM
  • User269602965 posted

    Exactly moving in the right direction.  But it sure can get complex at times!!

    Saturday, September 27, 2014 7:22 PM