I’ve been looking for this but i have no idea about the name of this.
I am creating a data model on MySql and i have a table called users and i want to restrict the queries from each user, for example, allow to read all the rows from the clients table or only allow them to query some selected clients. It’s like a filter but my question is, how can i save the name of the clients for each users or allow them to have access to all the clients?
My first idea was create a Many to Many relationship but when i create a new client i need to update the table for each user that have the options to query all the clients.
My second idea was create a table with a column called selectedClients with array data type and save [“all”] and whith an “if” make the query and if i selected various clients, in that filed will be save the id of the clients such as [“clientID1”, “clientID2”] and after, in my middleware i will query the table only with the id’s of that field. But the problem is that it’s not a good practice i think and when deleted a client, i need to deleted for each field of that id.
How can i implement this?
Advertisement
Answer
Database-wise, what you want is a many-to-many relationship. Many users can access many clients. So you’ll have a users table, a clients table, and a usersclients table (or whatever you want to name it). The usersclients table would map users.id (presuming id is the name of the primary key) to clients.id. There would be 1 row in usersclients for every client that every user can access.
As for users that can view all clients, my initial thought is to have that set as a flag on the user. Then, when you’re building the list of clients, do something like:
(pseudo-code, please excuse any c# influence)
let viewableClients = [];
if( user.canViewAllClients ) {
clients = queryToGetAllClients();
} else {
clients = user.UserClients.forEach(uc => uc.Client);
}