Document level read permissions junction table?

Question

I need to control at the document level what the user type of SubClient sees in a list when they log in.

I have tables userTable and docTable. The app is just a team document app. Manager logs in and sets up a Client and under that client are associated documents and subclients of any number but generally both are around 4 or 5. Clients, subClients and docs comprise a group. One to Many for clients=>subclients and clients=>documents. Unidirectional.

I have this UI to control what PDFs a 'SubClient' sees when they log in. From a database standpoint the best way to handle document level read permissions is a simple userDocJunctionTablewith userID, DocID, AccessRightCodefields.

I feel like I'm not thinking of something and every example I find is overly complicated for this app, like databaseanswers.org/.../document_management_for_security.

JIC it's a Yii2 app with DB based RBAC auth.

You need to decide what has a junction table between *it* and a document. It may be a user table, it may be a client group, a client subgroup. Or you may want more than one junction table that hooks into a document for added flexibility. Then you build those relationships. See my general answer here on [Junction Tables](http://stackoverflow.com/a/32620163) if it is of any benefit. Sometimes the best start is a fresh piece of paper, and a crayon. — Drew, Jun 26 '16 at 22:48
The need for a client causes everything. A client won't always have subclients associated with he/she/it but a client **will** always have documents. The **only** user type (they all have logins so every people-object in my app is in the `userTable`) that I need to toggle whether the document shows up in the doc index function is a subclient. "I'm Mr. Client. I want FredSubClient to see this document but I do not want SallySubClient to even know this one exists so I will uncheck this box and she will not see it." — charlyRoot, Jun 27 '16 at 15:35
I can't paste my SQL based on your link above so here is a [link to a png.](http://screencast.com/t/Nncf6lK6) — charlyRoot, Jun 27 '16 at 15:42
I emailed it to you too. I need to read up on formatting. I couldn't get the code to look like yours. — charlyRoot, Jun 27 '16 at 16:04
[http://pastebin.com/8FuYsVff](http://pastebin.com/8FuYsVff) — charlyRoot, Jun 27 '16 at 16:07

score 1 · Accepted Answer · edited May 23 '17 at 12:33

1

CREATE TABLE userTable
(
    UserID int PRIMARY KEY,
    UserEmail varchar(50) NOT NULL,
    UserPassword varchar(50) NOT NULL
);

CREATE TABLE documentTable 
(
    DocumentID int PRIMARY KEY,
    DocumentDescription varchar(500) NOT NULL
);

CREATE TABLE UDJunction
(
    -- User/Document Junction table
    id int auto_increment primary key,
    userId int not null,
    documentId int not null,

    -- charlyRoot (OP) had this:
    readaccess tinyint not null, -- 1 = yes, 0 no

    -- Drew recommends this (extensible to dozens of permissions per file):
    docPermissions int not null, -- or just jam a bitmask in here (bit OR)

    unique key(userId,documentId), -- WHAT IS THIS USED FOR?
    key (documentId,userId),
    CONSTRAINT fk_ud_user FOREIGN KEY (userId) REFERENCES userTable(userId),
    CONSTRAINT fk_ud_documents FOREIGN KEY (documentId) REFERENCES documentTable(documentId)
);

Drew's reference from here:

unique key(studentId,courseId,term), -- no duplicates allowed for the combo (note student can re-take it next term)
key (courseId,studentId),

edited May 23 '17 at 12:33

Community

1
1

answered Jun 27 '16 at 16:01

charlyRoot

161
1
9

Note that from the *reference*, I had a unique key (a composite key which means 2 or more columns). It enforced no duplicate entries allowed. And the key that followed it was a helper index for queries going *the other way*. That is, queries that banked on caring about `courseId` primarily. – Drew Jun 27 '16 at 16:09
The `term` like Spring or Fall for courses added a twist to it. But normally, one could get away with `unique key(col13,col14), key(col14,col13);` ... so just a pair flipped. – Drew Jun 27 '16 at 16:12
Every implementation is going to be different. So, yours will undoubtedly have its own twist. – Drew Jun 27 '16 at 16:13
The `readaccess` is more human-understandable. The Drew `docPermissions` is more propeller-head and the way I would do it (cuts down on columns needed but involves a bitmask). – Drew Jun 27 '16 at 16:19
1

The only thing you need to understand about `unique key(userId,documentId), -- WHAT IS THIS USED FOR?` is only one row can be in that table with that combo. That is it. Period. Otherwise, you have too much debris in your table and your data gets messed up. – Drew Jun 27 '16 at 16:24
The unique key is so I could exist test before INSERT by just checking one column, thus being a really fast and light query? Like checking that usernames don't already exist when someone registers on a site? – charlyRoot Jun 27 '16 at 16:50
For the purposes of this table (which remember, is an intersect for user and a document) .... to discuss `checking that usernames don't already exist when someone registers on a site` is not really on topic. That unique key is to elimate clutter and data errors, that could happen well after user registration (at a time when provisioning a user to doc permissions occur). – Drew Jun 27 '16 at 16:52
You could have C D CDJunction, and U D UDJunction where C=clients, D=Documents, U=Users, where there are two junction tables. It all depends on how you want to provision document rights. It has intermediate level complexity imo. – Drew Jun 27 '16 at 17:00
I guess I'll have to create records in junction table for all SubClients when a document is uploaded AND for all documents when SubClient is added. That or remove docPermissions field and just exist check. IF EXIST THEN SUBCLIENT CAN READ ELSE... – charlyRoot Jun 27 '16 at 17:05
Yes, the higher level you can do permissions the better. The lower level (user if you call it that) to a document is real granular. It is powerful, but not easy to pull off. So you might say everyone in the client or subclient group gets this document, but Kurt can also see DocumentX. – Drew Jun 27 '16 at 17:06
Strange. I answered my own question and contributed nothing towards the answer. I just dug this question up because I was trying to remember the stuff @drew told me. Anyway, Thanks for the info Drew. I don't see where I thanked you a year ago. – charlyRoot Aug 31 '17 at 01:23

Document level read permissions junction table?

1 Answers1