mysql - 基于子行的行权限

Question

假设您有以下表格:

CREATE TABLE users 
(
    id INT(11) NOT NULL,
    username VARCHAR(45) NOT NULL
)

CREATE TABLE accounts
(
    id INT(11) NOT NULL,
    accountname VARCHAR(45) NOT NULL
)

CREATE TABLE users_accounts
(
    user_id INT(11) NOT NULL,
    account_id INT(11) NOT NULL
)

CREATE TABLE events
(
    id INT(11) NOT NULL,
    eventdata VARCHAR(45) NOT NULL,
    eventtype VARCHAR(45) NOT NULL,
    date DATE NOT NULL
)

create table events_accounts
(
   event_id INT(11),
   account_id INT(11)
)

现在，我希望用户能够查询事件表，但我想限制他们仅在有权访问与事件关联的所有帐户时才能查看事件。

我想出了几种执行查询的方法，但它们似乎都效率低下。示例:

' View shows number of accounts associated with each event
CREATE VIEW event_account_count AS
SELECT e.id AS event_id, count(1) AS account_count
FROM events e JOIN event_accounts ea ON e.id = ea.event_id
GROUP BY e.id

其次

` View shows the number of accounts each user can see of each event
CREATE VIEW event_account_user_count AS
SELECT e.id AS event_id, ua.user_id, count(1) AS account_count
FROM events e 
    JOIN event_accounts ea ON e.id = ea.event_id 
    JOIN users_accounts ua ON ea.account_id = ua.account_id
GROUP BY e.id, ua.user_id

最后:

' Select all the events that this user can see where the number 
' of accounts of the event they can see is equal to the number of accounts in the event
SELECT e.*
FROM events e 
    JOIN event_account_count eac ON e.id = eac.event_id
    JOIN event_account_user_count eaus ON e.id = eaus.event_id
WHERE eac.account_count = eaus.account_count AND
eaus.user_id = @user_id

我知道随着事件数量变大，这将变得非常低效。我考虑过具体化第一个 View ，即将 account_count 添加到事件表中，这会删除其中一个查询，但我不确定如何绕过第二个 View 。

我倾向于放弃允许通过帐户权限访问事件的功能，因为我知道这可能成为针对事件表的任何查询的瓶颈，特别是如果查询过滤事件表中的字段。一旦您过滤了事件表，我认为我无法使用 View ，因为谓词不会被推送。

关于实现这一点的其他方法有什么想法吗？

Answer 1

尝试双重否定 - 不要找到任何你不想找到的东西;)

首先，选择所有非该用户的帐户，然后选择不在这些帐户上的所有事件。

SELECT * FROM events where id NOT IN(
  SELECT event_id FROM events_accounts WHERE account_id NOT IN(
    SELECT account_id FROM users_accounts WHERE user_id = @user_id
))

我认为您不需要更多时间来附上表格并检查状况，但请自行检查。 (未测试代码)