从数据库向特定用户显示数据（使用角色？）[关闭]

Question

I want to display some data to specific users. I've already done something similar but the difference is that the role number was static. For example I added a column and gave '1' to specific users, so I wrote if($role == 1) { do something }. That was fine because it's a static number. With this way I'm showing secret information only to users with role 1.

Now I've added a new feature; the admin can upload information for specific users that belong to a group. Info will be different for groups. The data is stored and retrieved just fine. That's not my problem. For example I want to display document A to users 'a1','a2','a3' who are in group A (it's random names, i just want to make it easier for you), document B to users 'a4','a7','a35','a40' (who are in group B) and so on.

I know how to store/retrieve document 'A' and link it with users 'a1','a2','a3' and their group... but I'm not sure how I can show each document to a group. Document A can only be linked with one group (same for B,C,D.. etc).

TABLES
DOCUMENTS
Number - Title - File
1 - Document A - A.doc
2 - Document B - B.doc

DOC_USERS
Doc N - User N
1 - 10
1 - 15
2 - 7

DOC_GROUPS
Doc N - Group N
1 - 1
2 - 2

Instead of saying if($role == 1) as I did before.. I will need something like if ($document == $group). I cannot put 'A', or 'B' or whatever because it's not static though.

Any help?

Answer 1

I would move this logic into the query rather than the view. Pass in the user_id as a parameter and return only the records that the user is allowed to view. It looks like your schema allows you to grant permission to a document based on your userID, or by a user's association with a group (which seems to be missing from the table structure, but you mentioned it in your description)

select documents.*
from documents
     inner join doc_users on documents.number= doc_users.doc
where doc_users.user = ?

Union

select documents.*
from documents
     inner join doc_groups on documents.number= doc_group.doc
     inner join user_groups on user_groups.group = doc_groups.group
where user_groups.user = ?

i'm sure this query could be done without a union, but this is pretty clear to read and understand.