题:
我是MysqL的新手,我甚至不知道从哪里开始.我想知道如何编写一个简单的函数,它根据分布在许多表中的值返回true或false.
细节:
这是我的表的相关部分(所有引擎都是innodb).在阅读时,您将看到数据库只是为这些用户/组存储用户,组,文件以及这些文件的权限.
USER表:
CREATE TABLE IF NOT EXISTS USER
(
ID INT NOT NULL auto_increment,PRIMARY KEY(ID)
)
GROUP表:
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)
)
文件表:(R,W,X,用于其他)
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,READ BOOLEAN DEFAULT FALSE,WRITE BOOLEAN DEFAULT FALSE,EXECUTE BOOLEAN DEFAULT FALSE,UNIQUE (FILEID,USERID),FOREIGN KEY (FILEID) REFERENCES FILE(ID),FOREIGN KEY (USERID) REFERENCES USER(ID)
)
文件组许可表:
CREATE TABLE IF NOT EXISTS FILEGROUPPERMISSIONS
(
ID INT NOT NULL AUTO_INCREMENT,GROUPID),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)
它执行以下操作:
>检查文件123是否可被其他人读取
>检查用户456是否具有文件123的读取权限
>检查用户456是否在某个组中,该组对文件123具有读取权限
第一个是非常微不足道的三个中最微不足道的.第二个让我难过.第三个对我来说是一个概念障碍.除此之外,我显然必须检查用户和/或文件是否存在.
请善待,因为我是MysqL的新手.
先谢谢你
最佳答案
检查文件的权限.我可能会这样做:
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
但是,如果你真的想要重视你的功能.我建议使用表函数,只提供用户ID.这样您就可以获得用户有权访问的所有文件.这看起来像这样:
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
顺便说一下.问题是大坝好.这应该是如何提出问题的一个例子