mysql - 检查 mySQL 中的多个表

Question

问题:

我是 mySQL 的新手，我什至不知道从哪里开始。我想知道如何编写一个简单的函数，该函数根据分布在多个表中的值返回 true 或 false。

详情:

这是我的表的相关部分(所有引擎都是 innodb)。当您继续阅读时，您会看到数据库只存储用户、组、文件以及这些用户/组对这些文件的权限。

用户表:

CREATE TABLE IF NOT EXISTS USER
(
   ID                 INT NOT NULL auto_increment,

   PRIMARY KEY(ID)
)

组表:

CREATE TABLE IF NOT EXISTS GROUP
(
   ID           INT NOT NULL AUTO_INCREMENT,

   PRIMARY KEY(ID)
)

GROUP MEMBERSHIP 表:

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 表:(R、W、X，用于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)
)

文件用户权限表:

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 表:

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 ;

我将如何着手，或者至少我应该从哪里开始编写 hasPermission 函数，以便在这样查询时:

SELECT hasPermission( 123, 456)

它执行以下操作:

1. 检查文件 123 是否可被 other 读取
2. 检查用户 456 是否对文件 123 具有读取权限
3. 检查用户 456 是否在对文件 123 具有读取权限的某个组中

第一个是相当琐碎三个中最琐碎的。第二个让我难住了。第三个对我来说是一个概念障碍。除了所有这些之外，我显然必须检查用户和/或文件是否存在。

请善待我，我是 MySQL 的新手。

先谢谢你

Answer 1

检查文件的权限。我可能会这样做:

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

但是如果你真的想重视你的功能。我建议使用表函数并只提供 userid。这样就可以获得用户有权访问的所有文件。这看起来像这样:

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

顺便说一句。这个问题很好。这应该是一个如何提问的例子