I am designing a database for a document management app for my office. I want to get the backend right so I don’t have issues in the future. Basically all users will be categorized into roles e.g, super admin, admin, staff and interns. All documents will have access restrictions e.g public(all user roles can view the document), private (only user who created it can view), role-based(this means only users with the same roles can view these documents). However, Admins and super admins can view all documents. One user can have many documents. Also user roles can be updated e.g from staff to admin etc. I will be using Postgres with Sequelize as my ORM for this project. Based on the description above, is this a good design? What can be improved? Any inputs will be appreciated.
-
Probably you need Groups too, e.g. all members of a team have access to a document. N.B. there are plenty of DMS already available if this is for real-world and not some kind of exercise– SimonCommented Jun 10, 2017 at 20:10
3 Answers
One problem that I see with
role-based (this means only users with the same roles can view these documents)
is that if I create a document with this type as an intern, and later get promoted to a staff position, will that document be visible to the interns or to the staff?
I'd rather set the access_type field on the Document itself, and use the DocumentPermission entity to specify which roles have access to a certain Document (if the access type is role-based).
Also (but that is my opinion, I've seen more implementations like yours), I'd put the roles (superadmin, admin, staff, intern) into a separate entity, and have the UserRole entity link to that entity instead of the role_name column. Your design might save you a join, but in the end it's less flexible.
-
Thanks for the shrewd observation. For this point:
"use the DocumentPermission entity to specify which roles have access to a certain Document (if the access type is role-based)", do you mean just adding a field likerole_permissionon the DocumentPermissions entity that specifies which role has access to the document based on the document id? And will this apply to all access types (Public, Private and Role based)? Commented Jun 11, 2017 at 15:36 -
This would only apply to Role-based access, and one DocumentPermission record would specify that (one) role X has access to (one) document Y. A document can have multiple DocumentPermission records. Commented Jun 11, 2017 at 16:29
-
Please if it's not too much to ask, can you show me an ER diagram to illustrate this? I can't picture what you're saying. Thanks.. Commented Jun 12, 2017 at 10:57
-
This design is a sound start:
- you have
users userscan have severalrolesdocumentsare related to users (I assume that a user is the owner of his documents whatever his role are or will be)documentscan have severalaccess types
But I think it is incomplete. These two things are missing:
- for sure: you said you wanted the possibility of having role based accesses, i.e. give permissions on a document to specific roles. This means that you should have an optional relation between
DocumentPermissionandUserRolesas well. - may be: unless you want to handle "public" and "private" access and their exceptions for administrators and super-users (and may be later for auditors) in a hard coded way (how are these special roles identified, by the way ?), you'd need a
AccessPolicytable that makes the relation between an access-type and the roles which have special privileges for the related documents.
-
Thanks. and sorry for the late acknowledgement. I understand the second point perfectly. But for the first point, how do i establish that relationship between
DocumentPermissionandUserRoles? I'm not sure how to implement that. Commented Jun 11, 2017 at 15:16 -
Suppose you don't implement the second point. You'll have role such as "staff" and "intern". You'll have permissions such as "public", "private", but also role based permission such as "staff only" and "intern only". So you must have an optional relation between
DocumentPermissionandUserRoles. You could implement this using an nullable column inDocumentPermissioncontaining the id of the related role (implies that one such permission gives access to one role). If you implement the second point, you'll not need this, because the many to many relation allows to define it also. Commented Jun 11, 2017 at 15:45
As per my knowledge we should give permission to roles not to documents or users .As per ur requirement in future if you add any new role and tag it to users base u have it should work. RBAC algorithm google it u can find n no.of docs on it
