Validating multiple tables in mySQL

Question

Question:

I am new to mySQL and I don’t even know where to start. I want to know how to write a simple function that returns true or false based on the values ​​that are distributed in many tables.

Details:

Here are the relevant parts of my tables (all innodb engines). When you start reading, you will see that the database simply stores users, groups, files and permissions for these files for these 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 members 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, 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) ) 

USER PERMISSION 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 PERMISSION 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:

 DELIMITER $$ DROP FUNCTION IF EXISTS hasPermission$$ CREATE FUNCTION hasPermission(fileID INT, userID INT) RETURNS BOOLEAN BEGIN ??? END$$ DELIMITER ; 

How would I go, or at least where to start writing the hasPermission function hasPermission that when prompted like this:

 SELECT hasPermission( 123, 456) 

It does the following:

• Checks if file 123 is readable by other
• Checks if user 456 has read permissions on file 123
• Checks if user 456 is in any group that has read permissions in file 123

The first of these is the trivial of the three. The second - I'm at a standstill. The third is a conceptual barrier for me. In addition to all this, I obviously have to check if the user and / or the file exists.

Please be kind because I am new to MySQL.

Thanks in advanced

Answer 1

To check the correctness of the file. I would 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 evaluate your function. I would suggest using a table function and just put userid . So that you can get all the files that the user has access to. It 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 bad. This should be an example of how to ask a question.

Answer 2

• Check if file 123 is readable with other

  You said that this is pretty trivial, but for completeness:

   SELECT READ FROM FILE WHERE ID = 123; 

• Check if user 456 has read permissions on file 123

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

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

• Check if user 456 is in any group that has read permissions in file 123

  To do this, you need the JOIN tables of GROUP MEMBERSHIP and FILE GROUP PERMISSIONS :

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

---

To combine them, you can 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

For completeness, I also post my solution as I solved this problem. My initial motivation was to create a function to see if one linux user (i.e. user1 ) has access to the home folder of another linux user (i.e. user2 ) (i.e. does user1 have user1 permissions on /home/user2 ). Then I would use this function when building the VIEW for each mysql user using the user() function (assuming the mysql usernames and Linux names are one to one). Thanks to Arion’s suggestion, I now instead create a view using joins.

Note that in this example, the home folder is stored in the FILE table.

USER_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 ( FR = TRUE ) OR ( U.HOME = F.PATH AND PR = 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 PR = 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 just can do

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

This is useful if you want to copy 10+ columns.