Mysql

SQL - 瞬態（無狀態）使用者的受限視圖

  • January 18, 2017

幾天來我一直在尋找解決方案，但沒有找到具體的解決方案。我不是 DBA，如果這是一個微不足道的問題，請原諒我。簡而言之：我需要創建一個受限制的視圖，它按使用者過濾行，而無需維護唯一的使用者數據庫連接。

我正在使用 JWT 令牌身份驗證建構一個無狀態 Web 應用程序（因此我可以在令牌中儲存某種 DB 會話 ID，如果有幫助的話）。允許使用者訪問任何伺服器，並隨機切換伺服器（通過自動負載平衡）。由於網路伺服器不維護任何會話狀態（瀏覽器在每個請求上上傳令牌），我無法為給定使用者維護唯一的數據庫連接。我也在經歷 JPA/Hibernate，所以這可能也會限制我的能力；儘管如有必要，Hibernate 確實允許原生 SQL。

我需要在數據庫中添加額外的數據安全層，用於使用者帳戶的行級訪問限制。我打算在我的所有表中添加一個包含 UUID accountID 的列。然後，使用者創建的任何記錄都將其 accountID 與數據一起儲存在該行中。因此，他們在所有表中的所有行都將由他們的 accountID 標記。然後創建一個按此 accountID 列過濾的受限視圖，然後所有使用者將只能看到他們自己創建的行。（最終，我還將有 2+ 個不同的應用程序（每個應用程序具有多個伺服器）通過這些受限視圖的不同變體訪問相同的表；不確定這是否重要）。

是否可以創建一個接受參數的受限視圖？而且，這可以用 JPA/Hibernate 完成嗎？

我發現了這個問題：https://stackoverflow.com/questions/2281890/can-i-create-view-with-parameter-in-mysql，它使用看起來像黑客的東西來獲取視圖以獲取參數，但我擔心視圖的並發訪問和參數重疊。即，如果多個使用者同時點擊同一個視圖，每個使用者都傳遞自己的參數，是否存在相互覆蓋並流血的危險？

而這個：帶參數的視圖 IN，它使用儲存過程來生成動態 SQL 查詢。

我也看到了這個：https ://stackoverflow.com/questions/4498364/create-parameterized-view-in-sql-server-2008 ，它談到了使用“內聯表值函式”，但我沒有非常了解他的兩個解決方案範例之間的區別，它專門針對 SQL Server，我不確定 MySQL 是否支持，或者我什至如何開始使用 JPA 訪問它。

僅僅在我的所有查詢中添加 where 子句是不夠的，因為這會產生非常脆弱的限制。如果我的任何查詢忘記添加此限制，他們將能夠看到所有內容。但是，通過強制查看受限視圖，它會強制數據庫始終檢查該列。如果任何查詢忘記添加該參數，則數據庫應該爆炸並拋出錯誤，或者將該參數視為 null 並使用 null 進行列檢查。由於所有表中的 accountID 列都不允許 NULL 值，這將導致簡單地返回零匹配的空結果的安全失敗。

我研究過使用儲存過程，但如果這是唯一的解決方案，那將意味著我的應用程序中的所有內容都將通過儲存過程（所有表上的選擇、更新、刪除等），這將大大增加程式碼量有必要寫。至少在我看來是這樣，但我不是儲存過程方面的專家（我從未使用過它們）。

現在，我致力於 MySql Cluster (NDB)，但如果絕對必要，我可以考慮切換到另一個供應商以完成此安全措施。

範例表

使用者被分組到帳戶組中。在下面的範例中，有 4 個使用者分為 2 個帳戶組（2 和 2）。每個使用者應該能夠看到其帳戶組中的任何使用者創建的所有記錄，並且不能看到其他帳戶組中的使用者創建的任何記錄。在視圖中，我將 accountGroupId 硬編碼為 101，但這是我要傳入的參數（101、202 等）。所有使用者共享數據庫連接；數據庫連接沒有特定於使用者的內容，因為伺服器是無狀態的（即沒有會話）。

我需要做的是使用通用的共享數據庫連接查詢受限視圖，並傳入 accountGroupId 進行過濾。

CREATE TABLE user (
 id int(11) NOT NULL,
 username varchar(50) NOT NULL,
 passwordHash varchar(150) NOT NULL,
 accountGroupId int(11) NOT NULL
);

INSERT INTO user (id, username, passwordHash, accountGroupId) VALUES
(1, 'John',   '<A_HASHED_PASSWORD>', 101),
(2, 'George', '<A_HASHED_PASSWORD>', 101),
(3, 'Sarah',  '<A_HASHED_PASSWORD>', 202),
(4, 'Mary',   '<A_HASHED_PASSWORD>', 202);

-- --------------------------------------------------------

CREATE TABLE privateNotes (
 id int(11) NOT NULL,
 userId int(11) NOT NULL,
 text varchar(500) NOT NULL,
 accountGroupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, accountGroupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(31, 1, '[John] An Embarassing Message',   101),
(32, 2, '[George] A Secret Message',       101),
(33, 2, '[George] An Embarassing Message', 101),
(34, 3, '[Sarah] A Secret Message',        202),
(35, 3, '[Sarah] An Embarassing Message',  202),
(36, 4, '[Mary] A Secret Message',         202),
(37, 4, '[Mary] An Embarassing Message',   202);

-- --------------------------------------------------------
-- Views

-- My Users
CREATE VIEW myUsers AS
SELECT
 user.id AS id,
 user.username AS username,
 user.passwordHash AS passwordHash
FROM
 user
WHERE
 user.accountGroupId = 101; /* 101, 202, etc. */

-- My Private Notes
CREATE VIEW myPrivateNotes AS
SELECT
 privateNotes.id AS id,
 privateNotes.userId AS userId,
 privateNotes.text AS text
FROM
 privateNotes
WHERE
 privateNotes.accountGroupId = 101; /* 101, 202, etc. */

-- --------------------------------------------------------
-- ------- --
--  TESTS  --
-- ------- --

-- View Select Test
select * from myUsers;
select * from myPrivateNotes;

好的，所以，感謝@joanolo，我有一個很好的解決方案。

  1. **創建一個保存 accountGroupId 的會話變數。**Spring @Transactional 和 Hibernate 在每個請求/執行緒的基礎上隔離數據庫會話（在 Spring 請求和執行緒中是一對一的；即每個請求 1 個隔離執行緒），因此不存在並發參數洩露的風險。
  2. 創建數據庫儲存函式，該函式接受一個值並將其儲存在會話變數中。雖然，如果你願意，你可以只執行一個更新查詢SET @mySessionVar = 101;而不是創建一個 set() 函式，但你必須有一個 get 函式。MySQL 不允許您將會話變數放在視圖定義中。
  3. **創建與 where 子句中的會話變數進行比較的受限視圖。**由於此變數是帳戶範圍的單個值（並且不會在同一使用者中從選擇更改為選擇），因此可以正常工作。每個請求/使用者都將擁有自己的數據庫會話和自己的會話變數實例；彼此完全隔離。他們只是在執行他們的查詢/插入之前呼叫 set() 函式來初始化他們的會話變數，並且一切正常。

**現在，說到插入：**這是它變得粘稠的地方。有一個簡單的解決方案（非常簡單的觸發器），問題是數據庫支持。MySQL v5.6 (& in MariaDB) 中有一個錯誤，他們在執行觸發器之前檢查 NULL 約束。這阻止了使用觸發器來填充這些欄位。但是，此錯誤已在 MySQL v5.7 (& MariaDB v10.1.21 & v10.2.4) 中修復。如果您使用 MySQL v5.7+ (MariaDB v10.1.21+ / v10.2.4+) 或任何其他符合 SQL 標準的數據庫，並在檢查 NULL 約束之前執行觸發器，那麼您會沒事的。

這是一個實現修復的簡單程式碼範例，我還製作了一個SQL Fiddle：

CREATE TABLE privateNotes (
 id int(11) NOT NULL,
 userId int(11) NOT NULL,
 text varchar(500) NOT NULL,
 groupId int(11) NOT NULL
);

INSERT INTO privateNotes (id, userId, text, groupId) VALUES
(30, 1, '[John] A Secret Message',         101),
(32, 2, '[George] A Secret Message',       101),
(34, 3, '[Sarah] A Secret Message',        202),
(36, 4, '[Mary] A Secret Message',         202);

-- --------------------------------------------------------

-- Get
CREATE FUNCTION getCurrentGroupId()
   RETURNS int(11)
   NO SQL
   BEGIN
     RETURN @currentGroupId;
   END //

-- Set
CREATE FUNCTION setCurrentGroupId(groupId_in int(11))
   RETURNS int(11)
   NO SQL
   BEGIN
     SET @currentGroupId = groupId_in;
     RETURN @currentGroupId;
   END //

-- --------------------------------------------------------

CREATE VIEW myPrivateNotes AS
SELECT
 privateNotes.id AS id,
 privateNotes.userId AS userId,
 privateNotes.text AS text
FROM
 privateNotes
WHERE
 privateNotes.groupId = getCurrentGroupId(); /* 101, 202, etc. */

-- --------------------------------------------------------
-- Trigger

CREATE TRIGGER before_insert_privateNotes
   BEFORE INSERT ON privateNotes
   FOR EACH ROW BEGIN
       IF NEW.groupId IS NULL THEN
           SET NEW.groupId = getCurrentGroupId();
       END IF;
   END //

-- --------------------------------------------------------
-- ------- --
--  TESTS  -- To see resultSets, run selects in the SQL box over there ->
-- ------- -- Can't run inserts over there ->

-- View Insert Test
SELECT setCurrentGroupId(999);
//
-- Bug in MySQL fixed in v5.7 & in MariaDB v10.1.21 / v10.2.4
-- Bug causes -> Error: Field 'groupId' doesn't have a default value
INSERT INTO privateNotes (id, userId, text) VALUES (50, 51, 'a cool message');
//

-- View Select Tests
-- 
-- Copy the full chunk bellow to the Sql box over there ->
-- Don't forget to change the delimeter to: [//]
SELECT setCurrentGroupId(NULL);
//
SELECT * from myPrivateNotes; -- Returns Zero Results [Correct]
//

SELECT setCurrentGroupId(101);
//
SELECT * from myPrivateNotes; -- Returns Valid Results [Correct]
//

我不確定 MySQL 能否以簡單的方式處理此問題。至少，它不是 MySQL 和 MariaDB 標準實現的一部分。您可以查看保護您的數據：MariaDB 10.0 中的行級安全性以獲取涉及儲存過程的解決方案。我想它不僅可以應用於 MariaDB，還可以應用於 MySQL。

如果您可以選擇，您可以查看 PostgreSQL 以及如何定義Row Level Policies。這些政策非常符合您的需求；並且可能提供更好的匹配。

您可以在此部分文件中看到一個與您匹配的案例：

要允許所有使用者訪問使用者表中他們自己的行，可以使用一個簡單的策略：

CREATE POLICY user_policy ON users
   USING (user_name = current_user);

（user_name將相當於你的accountID）。

雖然這不是一個完整的答案，但我希望它有所幫助。

引用自：https://dba.stackexchange.com/questions/159103

相關問答

Mysql

MariaDB 和 MySQL - 觸發器未觸發：“欄位 ‘__’ 沒有預設值”（舊錯誤未真正修復？）

  • January 18, 2017
檢視問答
Mysql

MariaDB 欄位列表中的未知列

  • January 11, 2022
檢視問答
Mysql

MySQL 在儲存過程中將所有視圖從一個數據庫複製到另一個數據庫

  • December 18, 2021
檢視問答
Mysql

MySQL 創建全域常式（儲存過程和/或函式）的可能性

  • September 1, 2021
檢視問答
Mysql

帶參數的視圖，可以嗎？

  • May 19, 2019
檢視問答
Mysql

MySQL - 從另一個函式創建一個函式

  • July 11, 2016
檢視問答