110 lines
2.8 KiB
SQL
110 lines
2.8 KiB
SQL
DELIMITER $$
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` FUNCTION `account`.`user_hasPriv`(vChain VARCHAR(100),
|
|
vPrivilege ENUM('SELECT','INSERT','UPDATE','DELETE'),
|
|
vUserFk INT
|
|
)
|
|
RETURNS tinyint(1)
|
|
NOT DETERMINISTIC
|
|
READS SQL DATA
|
|
BEGIN
|
|
/**
|
|
* Search if the user has privileges on table or column
|
|
*
|
|
* @param vChain string passed with this syntax dbName.tableName[.columnName]
|
|
* @param vPrivilege privileges to check('SELECT','INSERT','UPDATE','DELETE')
|
|
* @param vUserFk user to check
|
|
* @return vHasPrivilege
|
|
*/
|
|
DECLARE vHasPrivilege BOOL DEFAULT FALSE;
|
|
DECLARE vDb VARCHAR(50);
|
|
DECLARE vTableName VARCHAR(50);
|
|
DECLARE vColumnName VARCHAR(50);
|
|
DECLARE vPrivilegeFlag INT;
|
|
DECLARE vChainExists BOOL;
|
|
|
|
SET vDb = SUBSTRING_INDEX(vChain, '.', 1);
|
|
SET vChain = SUBSTRING(vChain, LENGTH(vDb) + 2);
|
|
IF LOCATE('.', vChain) > 0 THEN
|
|
SET vTableName = SUBSTRING_INDEX(vChain, '.', 1);
|
|
SET vColumnName = SUBSTRING(vChain, LENGTH(vTableName) + 2);
|
|
ELSE
|
|
SET vTableName = vChain;
|
|
END IF;
|
|
|
|
SELECT COUNT(*) INTO vChainExists
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = vDb
|
|
AND TABLE_NAME = vTableName
|
|
AND (COLUMN_NAME = vColumnName OR vColumnName IS NULL)
|
|
LIMIT 1;
|
|
|
|
IF NOT vChainExists THEN
|
|
RETURN FALSE;
|
|
END IF;
|
|
|
|
CASE vPrivilege
|
|
WHEN 'Select' THEN SET vPrivilegeFlag = 1;
|
|
WHEN 'Insert' THEN SET vPrivilegeFlag = 2;
|
|
WHEN 'Update' THEN SET vPrivilegeFlag = 4;
|
|
WHEN 'Delete' THEN SET vPrivilegeFlag = 8;
|
|
ELSE BEGIN END;
|
|
END CASE;
|
|
|
|
DROP TEMPORARY TABLE IF EXISTS tRole;
|
|
CREATE TEMPORARY TABLE tRole
|
|
(INDEX (`name`))
|
|
ENGINE = MEMORY
|
|
SELECT r.`name`
|
|
FROM user u
|
|
JOIN roleRole rr ON rr.role = u.role
|
|
JOIN `role` r ON r.id = rr.inheritsFrom
|
|
WHERE u.id = vUserFk;
|
|
|
|
SELECT hasPrivilege INTO vHasPrivilege
|
|
FROM (SELECT JSON_VALUE(Priv, '$.access') & vPrivilegeFlag hasPrivilege
|
|
FROM mysql.global_priv gp
|
|
JOIN tRole tr ON CONCAT(tr.name) = gp.User
|
|
HAVING hasPrivilege
|
|
LIMIT 1)sub;
|
|
|
|
IF NOT vHasPrivilege THEN
|
|
|
|
SELECT sub.privilege = 'Y' INTO vHasPrivilege
|
|
FROM
|
|
(SELECT CASE vPrivilege
|
|
WHEN 'SELECT' THEN Select_priv
|
|
WHEN 'INSERT' THEN Insert_priv
|
|
WHEN 'UPDATE' THEN Update_priv
|
|
WHEN 'DELETE' THEN Delete_priv
|
|
END privilege
|
|
FROM mysql.db db
|
|
JOIN tRole tr ON tr.name = db.User
|
|
WHERE db.Db = vDb)sub;
|
|
|
|
END IF;
|
|
|
|
IF NOT vHasPrivilege THEN
|
|
|
|
SELECT TRUE INTO vHasPrivilege
|
|
FROM tRole tr
|
|
LEFT JOIN (
|
|
SELECT User, Table_priv privilege
|
|
FROM mysql.tables_priv
|
|
WHERE Db = vDb
|
|
AND Table_name = vTableName
|
|
UNION
|
|
SELECT User, Column_priv
|
|
FROM mysql.columns_priv
|
|
WHERE Db = vDb
|
|
AND Table_name = vTableName
|
|
AND Column_name = vColumnName
|
|
)sub ON sub.`User` = tr.name
|
|
WHERE sub.privilege = vPrivilege;
|
|
END IF;
|
|
|
|
DROP TEMPORARY TABLE tRole;
|
|
|
|
RETURN vHasPrivilege;
|
|
END$$
|
|
DELIMITER ;
|