CODEWITHSUNDEEP
mysqlsqlgroup-by
bpeikes
bpeikes

Reputation: 3695

Row permissions based on child rows

Let's say you have the following tables:

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)
)

Now I want a user to be able to query the events table, but I want to restrict them to view events only if they also have access to all of the accounts associated with the event.

I've come up with a couple of ways of doing the query, but they all seem inefficient. Example:

' 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

secondly

` 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

and then finally:

' 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

I know that as the number of events gets large, this is going to become very inefficient. I've thought about materializing the first view, i.e. add an account_count to the event table, which would cut out one of the queries, but I'm not sure how to get around the second view.

I'm leaning towards dropping a feature to allow access to events by account permissions because I know that this could become a bottleneck for any query against the events table, particularly if the queries filter on fields in the events table. Once you're filtering on the events table, I don't think I can use views because predicates won't get pushed.

Any ideas on other ways to implement this?

Upvotes: 0

Views: 126

Answers (3)

Andrzej Reduta
Andrzej Reduta

Reputation: 767

Now I want a user to be able to query the events table, but I want to restrict them to view events only if they also have access to all of the accounts associated with the event.

So You want to get all events associated with accounts which all of this accounts are associated with user. So if at least one of this accounts is not associated with user it will not be returned.

So here is solution:

SELECT * 
FROM events e 
WHERE NOT EXISTS (
      SELECT * 
      FROM events_accounts ea 
      WHERE ea.event_id = e.id 
      AND ea.account_id NOT IN (
           SELECT ua.account_id
           FROM users_accounts 
           WHERE ua.user_id = @user_id)

)
;

PS Using Views it's not a good solution.

Upvotes: 0

dened
dened

Reputation: 4310

The solution of @bato3 is very nice, but the following reusable views may be helpful also:

/* View shows events inaccessible to users */
CREATE VIEW users_inaccessible_events AS
SELECT DISTINCT u.id AS user_id, event_id
FROM users u
    JOIN events_accounts ea ON 1
    LEFT JOIN users_accounts ua
        ON u.id = ua.user_id AND ea.account_id = ua.account_id
WHERE ua.account_id IS NULL

/* View shows events accessible to users */
CREATE VIEW users_accessible_events AS
SELECT u.id AS user_id, e.id AS event_id
FROM users u
    JOIN events e ON 1
    LEFT JOIN users_inaccessible_events AS uie
        ON u.id = uie.user_id AND e.id = uie.event_id
WHERE uie.event_id IS NULL

So, the query which gives the results you need, could be as simple as:

SELECT e.*
FROM users_accessible_events, events e
WHERE user_id = @user_id AND event_id = e.id

And if you need, for example, the list of users which can access a particular event, or the list of events which can't be accessed by a specific user, etc., an applicable query will be also very simple and straightforward.

Upvotes: 1

bato3
bato3

Reputation: 2815

Try double negation - don't find nothing who youw don't want find ;)

First, select all the accounts that are not the user, and then select all events not on these accounts.

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
))

I do not think you need more time to attach the tables and check the condition, but check for yourself. (Not tested code)

Upvotes: 1

Related Questions