J
J
jtag2019-07-23 11:39:35
SQL
jtag, 2019-07-23 11:39:35

How optimally to make a selection of allowed data for the user?

There is a permissions table.
5d36c5a10af52882377632.png
After a user enters the site, rows are read from this table in accordance with id_person, i.e. those 3 lines.
They contain the name of the table and the row number in the table, this information is available to the user. Is it possible to do it this way? Is it correct to store the table name in varchar? What is the correct way to provide the user with the information he needs?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
E
Eugene Wolf, 2019-07-23
@Wolfnsex

It seems to me that you either somehow overdid it with logic, or approached the issue from the wrong side. In addition, you did not indicate which particular database this question is about.
For example, in MySQL (at the moment it will be tested on MariaDB, but I think the original MySQL works the same way), if you need to restrict access, you can do it at the level of users of the database itself, and set separate restrictions for each: Base -> table -> separate column.
If you need to restrict the data even more strictly, I would recommend adding access data to the data itself directly.
For example, if we were using PostgreSQL: in the "news" table, for each news, I would add a list of users who can read this particular news, in a separate column of the "array" type with a list of user IDs, in order to be able to make such a selection at the level of "pure queries" or simply I would use the row level security mechanism (I have not yet had to use this, but I think this is exactly what you are talking about).
What if MySQL: would create for each such table would create a satellite table in which the rights to each individual record would be registered, according to the same principle as described above. Or I would try to implement a similar scheme (as described in the variant for PosgtreSQL), only the JSON field would play the role of an array field (how much it would work and how fast it would work if it worked - I can’t say for sure, in MySQL I I haven't tried it yet, but in theory it can be done.
In your version, I strongly dislike the fact that you store namestables in another table. Thus, these names are more likely to be impossible to use inside a SQL query, which in turn creates serious specifics when working with such a database, which I would strongly recommend avoiding, in particular, in order to guarantee the correctness of rights between queries - each query will be needed on SELECT to wrap in transaction, at least...
PS Still here describe some implementation of row level security for MySQL'a.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question