Checking across multiple tables in mySQL

mysql

Solution

To check the right for a file. I would probably do it like this:

SELECT
    (
        CASE WHEN EXISTS
            (
                SELECT
                    NULL
                FROM
                    FILE
                WHERE EXISTS
                    (
                        SELECT
                            NULL
                        FROM
                            USER
                            JOIN GROUPMEMBERSHIP
                                ON GROUPMEMBERSHIP.USERID=USER.ID
                            JOIN FILEUSERPERMISSIONS
                                ON FILEUSERPERMISSIONS.USERID=GROUPMEMBERSHIP.USERID
                            JOIN FILEGROUPPERMISSIONS
                                ON FILEGROUPPERMISSIONS.GROUPID=GROUPMEMBERSHIP.GROUPID
                        WHERE 
                            FILEUSERPERMISSIONS.FILEID=FILE.ID
                            AND FILEGROUPPERMISSIONS.FILEID=FILE.ID
                            AND FILEUSERPERMISSIONS.READ=FILE.READ
                            AND FILEGROUPPERMISSIONS.READ=FILE.READ
                            AND USER.ID=userID
                    )
                WHERE
                    FILE.READ=1
                    AND FILE.ID=fileID
            )
        THEN CAST(1 AS BIT)
        ELSE CAST(0 AS BIT)
        END
    ) AS hasRights

But if you really want to value of your function. I would suggest using a table function and just supply the `userid`. So that you can get all the files that the user has access to. This will look something like this:

SELECT
    ID
FROM
    FILE
WHERE EXISTS
    (
        SELECT
            NULL
        FROM
            USER
            JOIN GROUPMEMBERSHIP
                ON GROUPMEMBERSHIP.USERID=USER.ID
            JOIN FILEUSERPERMISSIONS
                ON FILEUSERPERMISSIONS.USERID=GROUPMEMBERSHIP.USERID
            JOIN FILEGROUPPERMISSIONS
                ON FILEGROUPPERMISSIONS.GROUPID=GROUPMEMBERSHIP.GROUPID
        WHERE 
            FILEUSERPERMISSIONS.FILEID=FILE.ID
            AND FILEGROUPPERMISSIONS.FILEID=FILE.ID
            AND FILEUSERPERMISSIONS.READ=FILE.READ
            AND FILEGROUPPERMISSIONS.READ=FILE.READ
            AND USER.ID=userID
    )
WHERE
    FILE.READ=1

And by the way. The question was dam good. This should be an example of how to ask a question

Problem

Question: I am fairly new to mySQL and I don't even know where to begin with this. I want to know how to write a simple function which returns true or false based on values which are spread out across many tables. Details: Here are the relevant parts of my tables (all engines are `innodb`). As you read on, you will see that the database is just storing users, groups, files, and the permissions for those files for those users/groups. USER table: ``` CREATE TABLE IF NOT EXISTS USER ( ID INT NOT NULL auto_increment, PRIMARY KEY(ID) ) ``` GROUP table: ``` CREATE TABLE IF NOT EXISTS GROUP ( ID INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID) ) ``` GROUP MEMBERSHIP table: ``` CREATE TABLE IF NOT EXISTS GROUPMEMBERSHIP ( ID INT NOT NULL AUTO_INCREMENT, USERID INT NOT NULL, GROUPID INT NOT NULL, UNIQUE ( USERID, GROUPID ), PRIMARY KEY(ID), FOREIGN KEY (USERID) REFERENCES USER(ID), FOREIGN KEY (GROUPID) REFERENCES GROUP(ID) ) ``` FILE table: (R, W, X, is for `other`) ``` CREATE TABLE IF NOT EXISTS FILE ( ID INT NOT NULL AUTO_INCREMENT, READ BOOLEAN DEFAULT FALSE, WRITE BOOLEAN DEFAULT FALSE, EXECUTE BOOLEAN DEFAULT FALSE, PRIMARY KEY(ID) ) ``` FILE USER PERMISSIONS table: ``` CREATE TABLE IF NOT EXISTS FILEUSERPERMISSIONS ( ID INT NOT NULL AUTO_INCREMENT, FILEID INT NOT NULL, USERID INT NOT NULL, READ BOOLEAN DEFAULT FALSE, WRITE BOOLEAN DEFAULT FALSE, EXECUTE BOOLEAN DEFAULT FALSE, UNIQUE (FILEID, USERID), PRIMARY KEY(ID), FOREIGN KEY (FILEID) REFERENCES FILE(ID), FOREIGN KEY (USERID) REFERENCES USER(ID) ) ``` FILE GROUP PERMISSIONS table: ``` CREATE TABLE IF NOT EXISTS FILEGROUPPERMISSIONS ( ID INT NOT NULL AUTO_INCREMENT, FILEID INT NOT NULL, GROUPID INT NOT NULL, READ BOOLEAN DEFAULT FALSE, WRITE BOOLEAN DEFAULT FALSE, EXECUTE BOOLEAN DEFAULT FALSE, UNIQUE (FILEID, GROUPID), PRIMARY KEY(ID), FOREIGN KEY (FILEID) REFERENCES FILE(ID), FOREIGN KEY (GROUPID) REFERENCES GROUP(ID) ) ``` hasPermission function: ``` DELIMITER $$ DROP FUNCTION IF EXISTS hasPermission$$ CREATE FUNCTION hasPermission(fileID INT, userID INT) RETURNS BOOLEAN BEGIN ??? END$$ DELIMITER ; ``` How would I go about, or at least where do I start writing the `hasPermission` function such that when queried like so: ``` SELECT hasPermission( 123, 456) ``` It carries out the below: - Checks to see if file 123 is readable by `other` - Checks to see if user 456 has read permissions on file 123 - Checks to see if user 456 is in some group that has read permissions on file 123 The first one is pretty trivial the most trivial of the three. The second one has me stumped. The third one presents a conceptual barrier for me. In addition to all this, I obviously have to check to see if the user and/or the file exists. Please be kind as I am a newbie wrt MySQL. Thank you in advanced

Original source