Working with linked tables. How to properly organize user access rights to modules on the site?

MySQL has tables:

users  
modules  
rights  

The rightstable contains two fields: uid(user id from the users table, foreign key) and mid(module id, everything is the same) .

During the authentication process, the following request occurs:

SELECT modules.mod_name 
FROM rights 
INNER JOIN modules ON rights.mod_id = modules.mod_id 
WHERE rights.uid = 1

Where 1 – can be any number (user id), and in the field mod_name– the technical name of the module. The result of the request is stored in an array, and later, when building pages, we check with this array in order to know where the user can and cannot.

Who has experienced this before? Am I doing everything right?

PS: And yet, I want to come up with a separate method for working with link tables, because there will be more of them in the future. Which algorithm, in this case, will look better and smarter?


Answer 1, authority 100%

um… And what do we write on, actually? PHP? You can take as a basis the query I proposed to the database and…

function GetRelatedFields($tfrom, $frfield, $fcfield, $tcross, $ccfield, $crfield, $crvalue)
{
    $sql = 'SELECT ' . $tfrom . '.' . $frfield . ' FROM ' . $tfrom . ' INNER JOIN (SELECT ' . $ccfield . ' FROM ' . $tcross . ' WHERE ' . $crfield . ' = ' . $crvalue . ') AS __temptable ON ' . $fcfield . ' = ' . $ccfield . ';';
$result = false;
$res = mysqli_query($link, $sql);
if(!$res)
{
    $err = mysqli_error($link));
    // ...
}
else
{
    // fill $result array with results
    $result = array();
    while($row = mysqli_fetch_array($res))
        $result[] = $row;
}
    return $result;     
}
  • The code is written right here, without validation, so copy-paste is not welcome. $link is a global object for accessing the database. The extension is mysqli. In general, I think the idea is clear. And – unlimited possibilities for expanding the functionality…

Answer 2, authority 100%

It seems to me that everything is fine with you, the only thing I would change is the request itself.

SELECT modules.mod_name 
FROM modules 
INNER JOIN (SELECT mid FROM rights WHERE uid = 1) AS am ON mod_id=mid;

Answer 3

To be honest, I didn’t understand the essence of the question.
But I’ll try to answer.
You want to allow/prohibit visiting different pages for different users.
There is a group system for this. Those. each user is in a certain group, admins in admins mods in mods spammers in banned.
Well, that’s the gist of it. You create a table of groups, id, name, homepage, and other common things for groups, and assign a group id to each user.
Then, you simply enter the checkPrivileges function, into which you pass an array of id-checks that can visit this page, or use this function inside the script to restrict page viewing (do not show buttons that delete, etc.). In general, this standard practice.