Designing a database structure for a permissions system

Question

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?

• Lennart - Slava Ukraini: 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.
• Vérace: I'm with @Lennart on this - MySQL is inferior in virtually every way to PostgreSQL - which also has RBAC!
• schacker22: @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.
• Vérace: 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: 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!

Answer 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.

• schacker22: My scheme is based on Apple's CNContacts class: link
• Rick James: Hmmm... That seems more complex than anything I have seem before. Pare it down to what you really need.
• schacker22: 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.