programing

1:1 연결 테이블 및 CTE(예: 사용자 및 그룹에 대한 권한)를 사용하여 다른 행과 연결된 SQL 행 찾기

telebox 2023. 7. 31. 21:19
반응형

1:1 연결 테이블 및 CTE(예: 사용자 및 그룹에 대한 권한)를 사용하여 다른 행과 연결된 SQL 행 찾기

그 문제는

사용자가 사용 중인지 여부를 확인해야 합니다.U엔티티에 액세스할 수 있습니다.EMySQL 테이블(MariaDB 10.2의 경우)에 저장된 사용 권한에 따라 다음 규칙이 적용됩니다.

  • 사용자 및 그룹에 권한을 부여할 수 있습니다.
  • 사용 권한만 허용됩니다. 엔티티와 사용자/그룹 간의 관계가 존재하면 엔티티(사용자 또는 그룹)에 액세스할 수 있습니다.
  • 그룹 구성원 자격: 사용자는 하나 이상의 그룹에 속할 수 있습니다.
  • 그룹 상속: 그룹에 상위 그룹이 있을 수 있으며, 상위 그룹의 권한이 하위 그룹에 적용됩니다.

테이블은 이미 준비되어 있으며 문제를 이해하는 방법은 다음과 같습니다. "사용자 행 사이에 링크가 있습니까?U그리고 실체의 행E(가능한 경로를 통해)?"

테이블이 그래프가 아니기 때문에(아래 세부 정보 참조), 그룹 상속 때문에(내가 잘 모르는) 여기서 공통 테이블 식을 재귀와 함께 사용할 수 있을 것 같습니다(나에게는 더 좋지 않습니다).

나의 시도

그래서 직접 엔티티 사용자 허용을 지원하기 위해 다음 SQL 쿼리를 구축했습니다(첫 번째 CTE:cte_entities_allowed_to_users)

SET @TEST_EID = 302;
SET @TEST_UID = 103;

WITH
cte_entities_allowed_to_users AS (
    SELECT
        DISTINCT
        entities.eid,
        entity_user_access.uid
    FROM
        entity_user_access
        JOIN entities
            ON entity_user_access.eid = entities.eid
)
SELECT
    COUNT(*)
FROM
    cte_entities_allowed_to_users
WHERE
    cte_entities_allowed_to_users.eid = @TEST_EID
    AND cte_entities_allowed_to_users.uid = @TEST_UID

또한 다음과 같은 다른 CTE도 구축했습니다.

cte_groups_of_users AS (
    SELECT
        DISTINCT
        users.uid,
        groups.gid
    FROM
        groups
        JOIN group_members
            ON group_members.gid = groups.gid
        JOIN users
            ON users.uid = group_members.uid
)
,
cte_entities_allowed_to_groups AS (
    SELECT
        DISTINCT
        entities.eid,
        entity_group_access.gid
    FROM
        entity_group_access
        JOIN entities
            ON entity_group_access.eid = entities.eid
)

내가 고군분투하는 곳

하지만 확신할 수 없습니다.

  • 그것들을 함께 사용하는 방법.
  • 또는 다른 CTE를 구축하는 방법(대부분 사용하는 CTE).group_of_groups표)
  • CTE가 이런 종류의 문제를 해결하는 방법이 아니라면요.

내가 기대하는 것("진실표")

다음의 경우@TEST_EID그리고.@TEST_UID여기서 쌍은 SELECT에 대한 예상 반환 값입니다.COUNT(*)필드):

@TEST_EID @TEST_UID 돌아가다 왜죠
301 101 ≥1 entity_user_access을 가지고 있습니다.(301,101)배를 젓다
301 102 ≥1 entity_user_access을 가지고 있습니다.(301,102)배를 젓다
301 103 =0
302 101 =0
302 102 ≥1 entity_group_access을 가지고 있습니다.(302,201)행과group_members을 가지고 있습니다.(201,102)배를 젓다
302 103 ≥1 entity_user_access을 가지고 있습니다.(302,103)배를 젓다
303 101 =0
303 102 ≥1 (303,201)줄을 지어 들어오다entity_group_access그리고.group_members을 가지고 있습니다.(201,102)배를 젓다
303 103 ≥1 entity_user_access을 가지고 있습니다.(303,103)배를 젓다
304 101 ≥1 (304,205)줄을 지어 들어오다entity_group_access그리고.group_of_groups을 가지고 있습니다.(204,205)행과group_members을 가지고 있습니다.(204,101)배를 젓다
304 102 ≥1 (304,201)줄을 지어 들어오다entity_group_access그리고.group_members을 가지고 있습니다.(201,102)배를 젓다
304 103 ≥1 (304,205)줄을 지어 들어오다entity_group_access그리고.group_of_groups을 가지고 있습니다.(202,205)행과group_members을 가지고 있습니다.(202,103)배를 젓다

부록 1/1: 표 세부 정보

관계:


      [users]───────────────[group_members]─────[groups]──┐
         │                       │                 │      │
         │                       │                 └──[group_of_groups]
[entity_user_access]   [entity_group_access]
         │                       │
         └────────┐    ┌─────────┘
                  │    │
                [entities]

일부 데이터 집합:

users표:

유이드 이름 없는
101 앨리스야.
102 밥.
103 찰리.

groups표:

현기증이 gname
201 관리자
202 사용자
203 연산자
204 손님들
205 크리스마스 이벤트

entities표:

이드 이름을 대다
301 푸
302 바
303 쿼스
304 눈

entity_user_access표:

이드 유이드
301 101
301 102
302 103
303 103

entity_group_access표:

이드 현기증이
301 201
302 201
303 201
304 201
302 203
304 205

group_members표:

현기증이 유이드 주석(데이터의 일부가 아님)
201 102 밥은 관리자입니다.
203 102 Bob 또한 운영자입니다.
202 103 Charlie는 사용자입니다.
204 101 앨리스는 손님입니다.

group_of_groups표:

현기증이 부모_학생 주석(데이터의 일부가 아님)
201 203 관리자(201명)는 운영자(203명)입니다.
202 205 사용자(202)가 크리스마스 이벤트 그룹(205)에 속합니다.
204 205 게스트(204명)는 크리스마스 이벤트 그룹(205명)에 속합니다.

전체 SQL:

-- Structure
CREATE TABLE `users` (
    `uid` INT(11) NOT NULL,
    `uname` TINYTEXT NOT NULL,
    PRIMARY KEY (`uid`)
);
CREATE TABLE `groups` (
    `gid` INT(11) NOT NULL,
    `gname` TINYTEXT NOT NULL,
    PRIMARY KEY (`gid`)
);
CREATE TABLE `entities` (
    `eid` INT(11) NOT NULL,
    `ename` TINYTEXT NOT NULL,
    PRIMARY KEY (`eid`)
);
CREATE TABLE `entity_user_access` (
    `eid` INT(11) NOT NULL,
    `uid` INT(11) NOT NULL,
    PRIMARY KEY (`eid`, `uid`),
    CONSTRAINT `FK_eua_entity` FOREIGN KEY (`eid`) REFERENCES `entities` (`eid`),
    CONSTRAINT `FK_eua_user` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
);
CREATE TABLE `entity_group_access` (
    `eid` INT(11) NOT NULL,
    `gid` INT(11) NOT NULL,
    PRIMARY KEY (`eid`, `gid`),
    CONSTRAINT `FK_ega_entity` FOREIGN KEY (`eid`) REFERENCES `entities` (`eid`),
    CONSTRAINT `FK_ega_group` FOREIGN KEY (`gid`) REFERENCES `groups` (`gid`)
);
CREATE TABLE `group_members` (
    `gid` INT(11) NOT NULL,
    `uid` INT(11) NOT NULL,
    PRIMARY KEY (`gid`, `uid`),
    CONSTRAINT `FK_gm_group` FOREIGN KEY (`gid`) REFERENCES `groups` (`gid`),
    CONSTRAINT `FK_gm_user` FOREIGN KEY (`uid`) REFERENCES `users` (`uid`)
);
CREATE TABLE `group_of_groups` (
    `gid` INT(11) NOT NULL,
    `parent_gid` INT(11) NOT NULL,
    PRIMARY KEY (`gid`, `parent_gid`),
    CONSTRAINT `FK_gog_group` FOREIGN KEY (`gid`) REFERENCES `groups` (`gid`),
    CONSTRAINT `FK_gog_parent_group` FOREIGN KEY (`parent_gid`) REFERENCES `groups` (`gid`)
);

-- Data
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
INSERT INTO `users` VALUES
    (101, 'Alice'),
    (102, 'Bob'),
    (103, 'Charlie');
INSERT INTO `groups` VALUES
    (201, 'Administrators'),
    (202, 'Users'),
    (203, 'Operators'),
    (204, 'Guests'),
    (205, 'X-Mas event');
INSERT INTO `entities` VALUES
    (301, 'Foo'),
    (302, 'Bar'),
    (303, 'Qux'),
    (304, 'Snow');
INSERT INTO `entity_user_access` VALUES
    (301, 101),
    (301, 102),
    (302, 103),
    (303, 103);
INSERT INTO `entity_group_access` VALUES
    (301, 201),
    (302, 201),
    (302, 203),
    (303, 201),
    (304, 201),
    (304, 205);
INSERT INTO `group_members` VALUES
    (201, 102),
    (202, 103),
    (203, 102),
    (204, 101);
INSERT INTO `group_of_groups` VALUES
    (201, 203),
    (202, 205),
    (204, 205);
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;

CTE는 하위 쿼리에 대한 대체일 뿐입니다.

하지만 당신의 생각은 좀 틀렸습니다.

그룹 테이블은 첫 번째 테이블과 동일한 구조여야 하므로 다른 테이블에도 가입해야 id 및 uid를 얻을 수 있습니다.

다음 단계는 다음과 같습니다.UNION할 수

그 연합에서 당신은 당신의 카운트 쿼리를 실행합니다.

예제 참조

SET @TEST_EID = 302;
SET @TEST_UID = 103;

SELECT COUNT(*)
FROM
(    SELECT
        DISTINCT
        entities.eid,
        entity_user_access.uid
    FROM
        entity_user_access
        JOIN entities
            ON entity_user_access.eid = entities.eid
UNION
    SELECT
        DISTINCT
        entities.eid,
        users.uid
    FROM
        `groups`
        JOIN group_members
            ON group_members.gid = groups.gid
        JOIN users
            ON users.uid = group_members.uid
         JOIN 
            entity_group_access
            ON groups.gid = entity_group_access.gid
         JOIN entities
            ON entity_group_access.eid = entities.eid) t1
WHERE eid = @TEST_EID and uid = @TEST_UID

카운트(*) || -------: ||        1 |

db<>여기로 이동

언급URL : https://stackoverflow.com/questions/71267240/find-sql-rows-associated-to-other-using-11-linking-tables-and-cte-eg-permissi

반응형

'programing' 카테고리의 다른 글

@Inspectable with enum?  (0) 2023.07.31
하나의 데이터베이스에만 파일 허용  (0) 2023.07.31
Intellij Idea 경고 - "반환된 약속은 무시됩니다" 및 aysnc/wait  (0) 2023.07.31
Java의 mariadb 커넥터가 다른 언어의 커넥터보다 mariadb에 더 빨리 연결되는 이유는 무엇입니까?  (0) 2023.07.31
Angular CLI 프로젝트를 업그레이드하는 방법은 무엇입니까?  (0) 2023.07.31

'programing'의 다른글

  • 현재글1:1 연결 테이블 및 CTE(예: 사용자 및 그룹에 대한 권한)를 사용하여 다른 행과 연결된 SQL 행 찾기

관련글

댓글

티스토리툴바