Блог

Предположим, что система, в которой People есть доступ к Buildings зданиям и в зданиях, имеет доступ к определенным Rooms . Доступ определяется в соответствии со связанными Permissions таблицами или, в случае пользователей с полным доступом, полным доступом.

Таблицы БД определяются следующим образом:

 buildings (id INT)
rooms (id INT, building_id INT)
people (id INT, has_full_access BIT)
building_permissions (building_id INT, person_id INT)
room_permissions (room_id INT, person_id INT)
  

В настоящее время у меня есть функции с табличным значением, которые возвращают таблицы с авторизованными идентификаторами зданий и комнат на основе идентификатора пользователя и наличия у них полного доступа.

 CREATE FUNCTION fn_get_authorised_buildings (@person_id INT, @has_full_access BIT)
RETURNS TABLE AS
RETURN
(
    SELECT b.id
    FROM buildings b
    WHERE @has_full_access = 1

    UNION

    SELECT b.id
    FROM buildings b
        INNER JOIN building_permissions bp ON bp.building_id = b.id
    WHERE bp.person_id = @person_id 
);

CREATE FUNCTION fn_get_authorised_rooms (@person_id INT, @has_full_access BIT)
RETURNS TABLE AS
RETURN
(
    SELECT r.id
    FROM rooms r
    WHERE @has_full_access = 1

    UNION

    SELECT r.id
    FROM rooms r
        INNER JOIN room_permissions rp ON rp.room_id = r.id
    WHERE rp.person_id = @person_id 
);
  

Во всей системе, когда задействованы здания и помещения, мне нужно фильтровать эти таблицы на основе разрешений пользователя. Если пользователь имеет полный доступ, то должны быть включены все строки, в противном случае только те, на которые у них есть разрешение.

Мои запросы (упрощенные) выглядят примерно так:

 DECLARE @person_id INT = 123
DECLARE @has_full_access BIT = 1

DECLARE @authorised_buildings TABLE (id INT) 
INSERT INTO @authorised_buildings SELECT id FROM fn_get_authorised_buildings(@person_id , @has_full_access)

DECLARE @authorised_rooms TABLE (id INT) 
INSERT INTO @authorised_rooms SELECT id FROM fn_get_authorised_rooms(@person_id, @has_full_access)

--Example A
SELECT * 
FROM buildings b
    INNER JOIN rooms r ON r.building_id = b.id
WHERE 1 = 1
    AND b.id IN (SELECT id FROM @authorised_buildings)
    AND r.id IN (SELECT id FROM @authorised_rooms)

--Example B
SELECT * 
FROM floors f -- or other tables that are related to rooms
    INNER JOIN rooms r ON r.floor_id = f.id
WHERE 1 = 1
    AND r.id IN (SELECT id FROM @authorised_rooms)
  

Есть ли лучший способ сделать это?

Здесь
РЕДАКТИРОВАТЬ: это скрипка с настройкой

Вы можете создать подобное представление, которое содержит всю логику:

 CREATE VIEW bulding_rooms_per_person AS
SELECT p.id AS person_id, b.id as building_id, r.id AS room_id
FROM people p 
LEFT JOIN building_permissions bp ON bp.person_id = p.id
LEFT JOIN room_permissions rp ON rp.person_id = p.id
INNER JOIN buildings b ON b.id = bp.building_id OR p.has_full_access = 1
INNER JOIN rooms r ON r.building_id = b.id AND (r.id = rp.room_id OR p.has_full_access = 1)
  

А затем просто выполните запрос по нему:

 SELECT * from bulding_rooms_per_person WHERE person_id = @person_id