Checking across multiple tables in mySQL

Question

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:

1. Checks to see if file 123 is readable by other
2. Checks to see if user 456 has read permissions on file 123
3. 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

Answer 1

For completeness I am also posting my solution to how I solved this problem. My initial motivation was to create a function to see if one linux user (ie. user1) has access to another linux user's (ie. user2) home folder (ie. whether user1 has read permissions on /home/user2). I would then use this function when constructing a VIEW for each mysql user by using the user() function (assuming the mysql usernames and linux usernames are one to one). Thanks to Arion's suggestion, I now, instead, am creating a view using joins instead.

Note, for the purposes of this example, the home folder is stored in the FILE table.

USER_VIEW view:

CREATE VIEW USER_VIEW
AS
SELECT 
   U.ID
FROM 
   USERS AS U
   LEFT JOIN FILES AS F
      ON U.ID = F.USER_ID
   LEFT JOIN FILE_USER_PERMISSIONS AS P
      ON F.ID = P.FILE_ID
   LEFT JOIN USERS AS S
      ON P.USER_ID = S.ID
WHERE
(
   U.USERNAME = SUBSTR( USER(), 1, POSITION( '@' IN USER() ) - 1 )
)
OR
(
   F.R = TRUE
)
OR
(
   U.HOME = F.PATH AND P.R = TRUE AND S.USERNAME = SUBSTR( USER(), 1, POSITION( '@' IN USER() ) - 1 )
)
UNION
SELECT 
   U.ID
FROM 
   USERS AS U
   LEFT JOIN FILES AS F
      ON U.ID = F.USER_ID
   LEFT JOIN FILE_GROUP_PERMISSIONS AS P
      ON F.ID = P.FILE_ID
   LEFT JOIN GROUP_MEMBERSHIP AS G
      ON P.GROUP_ID = G.ID
   LEFT JOIN USERS AS S
      ON G.USER_ID = S.ID
WHERE
(
   U.HOME = F.PATH AND P.R = TRUE AND S.USERNAME = SUBSTR( USER(), 1, POSITION( '@' IN USER() ) - 1 )
)

EDIT: I believe instead of issuing a command like

CREATE VIEW USER_VIEW
AS
SELECT 
   U.ID
FROM 
    ...

I can just do

CREATE VIEW USER_VIEW
AS
SELECT 
   U.*
FROM 
    ...

Which comes in handy if you want to copy 10+ columns

Answer 2

1. Check to see if file 123 is readable by other

   You said this is pretty trivial, but for completeness:

   SELECT READ
   FROM FILE
   WHERE ID = 123;
   

2. Check to see if user 456 has read permissions on file 123

   You can do this with a similar lookup in the FILEUSERPERMISSIONS table:

   SELECT READ
   FROM FILEUSERPERMISSIONS
   WHERE FILEID = 123 AND USERID = 456;
   

3. Check to see if user 456 is in some group that has read permissions on file 123

   To accomplish this, you need to JOIN the GROUP MEMBERSHIP and FILE GROUP PERMISSIONS tables:

   SELECT READ
   FROM FILEGROUPPERMISSIONS JOIN GROUPMEMBERSHIP USING (GROUPID)
   WHERE FILEID = 123 AND USERID = 456;
   

---

To combine these together, you could do:

SELECT
   (SELECT READ FROM FILE WHERE ID = 123)
OR (SELECT READ FROM FILEUSERPERMISSIONS WHERE FILEID = 123 AND USERID = 456)
OR (SELECT READ FROM FILEGROUPPERMISSIONS JOIN GROUPMEMBERSHIP USING (GROUPID)
    WHERE FILEID = 123 AND USERID = 456);

Answer 3

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