1

My app manages user data that is shared between users, with different permissions such as read-only, edit, superuser, rename, delete etc.

I'm weighing up two approaches to modelling the user permissions, the first is the simpler approach, the second involves more work but is more extensible, refactorable, I think.

(1) quick solution, hard-coding against user permission properties:

-- basic data
CREATE TABLE symbol (
    id INT,
    name VARCHAR(255)
);

CREATE TABLE user (
    id CHAR(10) 
);

CREATE TABLE user_permission (
    symbol_id INT,
    user_id CHAR(10),
    readable BIT,
    writable BIT,
    owner BIT,
    rename BIT,
    deletion BIT
);

(2) complete solution, hard-coding against entitlements:

enter image description here

The areas I'm considering are:

  1. extensibility - need or not to change model & schema
  2. microservices - possibilities to spin off into a separate DB?
  3. performance - filter algos, number of joins in queries
  4. no-sql caching - no idea but denormalising user permissions sounds crazy
  5. admin for users - need good UX
  6. admin for DBAs/Support - don't want complaints and endless support requests
  7. web services API simplicity / complexity using Spring Data REST - HAL

I'd like to go with the more complex solution since it is unlikely to require re-working in the future, but I'm a bit concerned about both performance and the admin tasks involved in the UI to allow users to manage it.

A utopian solution would be a third-party Java-based webapp providing a user interface to allow admin.

EDIT: interesting to see other people tackling the same problem: Authorisation in microservices - how to approach domain object or entity level access control using ACL?

Community
  • 1
  • 1
Adam
  • 5,215
  • 5
  • 51
  • 90
  • Use KISS and YAGNI. Also denormalising is trivial in CQRS. – Constantin Galbenu Apr 11 '17 at 19:38
  • I'm all for KISS but YAGNI is often too subjective, and abused in situations where a small effort now would save a lot of effort later. I would love to know how denormalising can be trivial in CQRS when you have access lists involved. If I denormalise completely, a 50 user access list would cause the data volume to explode. – Adam Apr 12 '17 at 12:07
  • Can you use Postgres, which supports column security and row security? – Neil McGuigan Apr 12 '17 at 18:43

1 Answers1

0

Since it is hard to get security related software right, I would recommend not implementing your own solution but using an existing open source solution. At the time of writing the Hydra project seems most promising. Specifically look at the access control features for your use case.

Oswin Noetzelmann
  • 9,166
  • 1
  • 33
  • 46
  • v. interesting but it has no front-end which pretty much rules it out if I find anything that does already. – Adam Apr 12 '17 at 12:38