0

Scenario

I would now like to implement a permission system so that a user is able to restrict access on his contact properties to his connections, e.g.:

  • A wants to allow B to access his name, birthday and work mail;

  • all other properties are private to B; and

  • A wants to allow C to access his name, phone number and private mail, etc.

Current considerations

I have a database scheme as shown in the picture below for an app that manages contact information for users:

enter image description here

The red table people contains basic information of the user, the yellow tables are arrays so that every user can have multiple email addresses, phone numbers etc. You may expose your contact data to other people by connecting to them as managed by the connection table.

The question

So my question now is, how can I design an efficient permission system that achieves the described goal?

Improve this question
7
  • 1
    You can google for RBAC (or LBAC but that's probably overkill). Some DBMS have built-in support for this, but I don't think MySQL is one of them.
    – Lennart - Slava Ukraini
    Commented Sep 16, 2017 at 16:54
  • I'm with @Lennart on this - MySQL is inferior in virtually every way to PostgreSQL - which also has RBAC!
    – Vérace
    Commented Sep 16, 2017 at 17:24
  • @Vérace When I understood RBAC right then for each entry in the connections table I would have to create a new role, because the permissions are mostly different for each person I'm sharing my data with. I don't think that's what I want.
    – schacker22
    Commented Sep 16, 2017 at 22:00
  • Normally you have groups of users - those who work at a basic level in HR - they may be able to see name, address, even salary, but not reviews! So, you set up a role hr_clerical and you give that role the authority to access name... &c, but not reviews! That way, you have a role per group and this takes a lot of the admin headache out of assigning these permissions to various people.You can further add permissions on a individual basis - say to people who are about to be promoted and need training. You, I presume, will have close friends, friends and acquaintances (that's what I have anyway!)
    – Vérace
    Commented Sep 17, 2017 at 8:20
  • Basically, what I'm saying is, you can set up a default for a category and then individually add (or remove) other permissions as you see fit. Having a well-thought out default will reduce work in the future!
    – Vérace
    Commented Sep 17, 2017 at 8:21

1 Answer 1

Reset to default
1

"... the yellow tables are arrays so that every user can have multiple ..." -- Good.

Restricted access should be in the application layer. No user should be able to write arbitrary SQL.

Dates should not be separated -- it will make inefficient to filter on a range of dates. Anyway, where do you need "multiple dates"?

That's a lot of sensitive data; rethink storing all of it.

Improve this answer
3
  • My scheme is based on Apple's CNContacts class: link
    – schacker22
    Commented Sep 16, 2017 at 18:21
  • 1
    Hmmm... That seems more complex than anything I have seem before. Pare it down to what you really need.
    – Rick James
    Commented Sep 16, 2017 at 18:24
  • The user should decide what he really needs. But my IOS app has to support all possible fields to be scalable and flexible. Anyway that's not the point, my question is about the permission system.
    – schacker22
    Commented Sep 16, 2017 at 20:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.