2

What is the best way to store permissions in a database that involve more than 2 levels? Currently there's only 2 levels of permissions and they are stored in a bridge entity table like this:

UID | Buildings | Floor
------------------------
 1  |  A, B, C  |  F9

There's also another table called Exceptions that has the same structure which indicates that users should NOT have permissions for those:

UID | Buildings | Floor
------------------------
 1  |    null   |  C4

So in my example above, user 1 would have permissions to floor 9 in building F, all the rooms in buildings A, B and C, but not floor 4 in C.

The new requirement is to add an even more granular level of permissions, rooms on floors. So the code would look something like A1A, A1B, etc. The buildings and rooms are always alphabetic and the floor number is always numeric. I cannot change the format of these codes as they are coming from an external source.

What is the best way to handle a situation like this? I know Linux handles file permissions with group and user with a bit string, but with thousands of rooms in my case that doesn't sound feasible, and also the user/group thing is only two levels.

I've considered just adding a new column called Rooms and continuing with the comma-separated string but is there a better way? If it matters, I'm using MySQL.

rink.attendant.6
  • 44,500
  • 61
  • 101
  • 156
  • Have you looked at externalizing authorization and using a framework like XACML? – David Brossard Sep 21 '14 at 14:00
  • [Path enumeration and Closure patterns](http://stackoverflow.com/questions/23223333/data-structure-for-many-to-many-hierarchies-in-sql-server/23224653#23224653) can help you. – Mohsen Heydari Sep 21 '14 at 16:35
  • @DavidBrossard I just briefly researched XACML and it seems that 1) it is overkill for the scope of the project and 2) the development team only consists of a few people and without anyone having experience with it, it's something we have to take time to learn. – rink.attendant.6 Sep 22 '14 at 02:50
  • If it's for a one-off app, then it may be overkill, true. Just take the best parts i.e. the architecture and implement the decoupling from your business app. Good luck! – David Brossard Sep 22 '14 at 07:37

1 Answers1

0

If I had to implement this I would use a single table with three columns - uid, type and element. Uid references the user, type (building,floor,room) and element the code of building or floor or room. I would normally insert one row for each person for each access element.

I could also become more bold and if I was sure that the format of the elements follows strict patterns. For example

Buildings will have codes only letter

Floors will have codes like letter.number

Rooms will have codes like letter.number.letter

In the above case you could use one table only with columns uid and element because from the format of the data you can extract the type of the element(if it's a building, floor or room). You can also go further and store access with wildcards:

Access to all floor and rooms of building C: C*

Access to all rooms of floor 6 of building B: B6*

or even

Access all C rooms in all floor and buildings : **C

Access to all second floors : *2*

Hope it helps

geoandri
  • 2,360
  • 2
  • 15
  • 28
  • Thanks for the response. This is also an interesting solution, but for now we decided to normalize the data and go with 6 tables with two columns each. – rink.attendant.6 Oct 23 '14 at 12:54