ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Помогите распутать иерархию
8 сообщений из 8, страница 1 из 1
Помогите распутать иерархию
    #40047313
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Есть не очень удобная иерархическая схема, с которой я весь день не могу разобраться.

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

Есть таблица принадлежности пользователей к ролям, которая строится не сверху-вниз (id - parent_id), а снизу-вниз (id - child_id).
В этой таблице указываются связи "пользователь-роль" и "роль-роль" (роли могут быть вложенными).

Мне нужно для каждого пользователя и роли построить дерево и получить идентификаторы корневой и последней роли.

Макет с данными примерно такой:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
with u as
(
  select 0 user_id, 0 is_role, '' name from dual where 0=1
  union all select 1, 0, 'usr1' from dual
  union all select 2, 0, 'usr2' from dual
  union all select 3, 0, 'usr3' from dual
  union all select 4, 1, 'role1' from dual
  union all select 5, 1, 'role2' from dual
  union all select 6, 1, 'role3' from dual
  union all select 7, 1, 'role4' from dual
  union all select 8, 1, 'grp1' from dual
  union all select 9, 1, 'grp2' from dual
)
, l as
(
  select 0 user_id, 0 role_id from dual where 0=1
  union all select 1, 8 from dual
  union all select 2, 8 from dual
  union all select 2, 9 from dual
  union all select 3, 9 from dual
  union all select 8, 4 from dual
  union all select 8, 5 from dual
  union all select 9, 4 from dual
  union all select 9, 6 from dual
  union all select 1, 6 from dual
)
select u.user_id, u.is_role, u.name u, r.user_id role_id, r.name r
from u
left join l on (l.user_id=u.user_id)
left join u r on (r.user_id=l.role_id and r.is_role=1)
order by u.user_id, r.user_id



А нужная мне структура выглядит примерно так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
path             user_id  root_id  last_id
----             -------  -------  -------

usr1             1
  + grp1         8        8        8
    + role1      4        8        4
    + role2      5        8        5
  + role3        6        6        6

usr2             2
  + grp1         8        8        8
    + role1      4        8        4
    + role2      5        8        5
  + grp2         9        9        9
    + role1      4        9        4
    + role3      6        9        6

usr3             3
  + grp2         9        9        9
    + role1      4        9        4
    + role3      6        9        6

grp1             8        8        8
  + role1        4        8        4
  + role2        5        8        5

grp2             9        9        9
  + role1        4        9        4
  + role3        6        9        6

role1            4        4        4

role2            5        5        5

role3            6        6        6

role4            7        7        7

Но дальше не могу сообразить, как мне построить дерево, чтобы во-первых оно могло начинаться с пользователя или роли, а во-вторых чтобы в нем были и те роли, которые никому не назначены.
Сделал так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select u.user_id, u.is_role, u.name u, r.user_id role_id, r.name r
, u.name||sys_connect_by_path(r.name, '/') path
, connect_by_root r.user_id root_id
, r.user_id last_id
from u
left join l on (l.user_id=u.user_id)
left join u r on (r.user_id=l.role_id and r.is_role=1)
connect by l.user_id = prior l.role_id


Но здесь не решен п.2 и нет уверенности, что п.1 корректен.
...
Рейтинг: 0 / 0
19.02.2021, 20:01
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047315
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Если же говорить о задаче в целом, то есть еще таблица прав доступа, привязанная к user_id.
Права могут быть привязаны как непосредственно к пользователю, так и к роли (причем на любом уровне вложенности).
Мне нужно удобно определить:
1. Какие права дает конкретная роль (или роли).
2. Какие пользователи или роли имеют доступ к конкретному праву (хоть непосредственно, хоть косвенно).
...
Рейтинг: 0 / 0
19.02.2021, 20:11
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047366
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
, t as
  ( select 0 as user_id, user_id as role_id from u
    union all
    select user_id, role_id from l
  )
select sys_connect_by_path((select u.name from u where u.user_id = t.role_id), '\') as path
  from t
  start with user_id = 0
  connect by user_id = prior role_id
;

PATH
--------------------
\usr1
\usr1\role3
\usr1\grp1
\usr1\grp1\role1
\usr1\grp1\role2
\usr2
\usr2\grp1
\usr2\grp1\role1
\usr2\grp1\role2
\usr2\grp2
\usr2\grp2\role1
\usr2\grp2\role3
\usr3
\usr3\grp2
\usr3\grp2\role1
\usr3\grp2\role3
\role1
\role2
\role3
\role4
\grp1
\grp1\role1
\grp1\role2
\grp2
\grp2\role1
\grp2\role3

26 rows selected.
...
Рейтинг: 0 / 0
19.02.2021, 22:19
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047390
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
with u as (
           select 0 user_id, 0 is_role, '' name from dual where 0=1 union all
           select 1, 0, 'usr1' from dual union all
           select 2, 0, 'usr2' from dual union all
           select 3, 0, 'usr3' from dual union all
           select 4, 1, 'role1' from dual union all
           select 5, 1, 'role2' from dual union all
           select 6, 1, 'role3' from dual union all
           select 7, 1, 'role4' from dual union all
           select 8, 1, 'grp1' from dual union all
           select 9, 1, 'grp2' from dual
          ),
     l as (
           select 0 user_id, 0 role_id from dual where 0=1 union all
           select 1, 8 from dual union all
           select 2, 8 from dual union all
           select 2, 9 from dual union all
           select 3, 9 from dual union all
           select 8, 4 from dual union all
           select 8, 5 from dual union all
           select 9, 4 from dual union all
           select 9, 6 from dual union all
           select 1, 6 from dual
          ),
    t0 as (
            select  l.*,
                    null is_role
              from  l
           union all
            select  null,
                    user_id,
                    is_role
              from  u
          ),
    t1 as (
           select  level lvl,
                   t0.user_id,
                   t0.role_id,
                   rownum rn,
                   t0.is_role
             from  t0
             start with t0.user_id is null
             connect by t0.user_id = prior t0.role_id
          )
select  nvl2(
             t1.user_id,
             lpad(' ',t1.lvl * 2) || '+ ' || u.name,
             u.name
            ) path,
        t1.role_id user_id,
        case t1.is_role
          when 0 then null
          else t1.role_id
        end root_id,
        case t1.is_role
          when 0 then null
          else t1.role_id
        end last_id
  from  t1,
        u
  where u.user_id = t1.role_id
  order by t1.rn
/

PATH                    USER_ID    ROOT_ID    LAST_ID
-------------------- ---------- ---------- ----------
usr1                          1
    + role3                   6          6          6
    + grp1                    8          8          8
      + role1                 4          4          4
      + role2                 5          5          5
usr2                          2
    + grp1                    8          8          8
      + role1                 4          4          4
      + role2                 5          5          5
    + grp2                    9          9          9
      + role1                 4          4          4
      + role3                 6          6          6
usr3                          3
    + grp2                    9          9          9
      + role1                 4          4          4
      + role3                 6          6          6
role1                         4          4          4
role2                         5          5          5
role3                         6          6          6
role4                         7          7          7
grp1                          8          8          8
    + role1                   4          4          4
    + role2                   5          5          5
grp2                          9          9          9
    + role1                   4          4          4
    + role3                   6          6          6

26 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
20.02.2021, 01:38
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047421
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
О, большое спасибо.
Вчера весь день голову ломал. В принципе я сообразил, что нужно "перевернуть" иерархию в id - parent_id и добавить корневые элементы, но не придумал, как это сделать, чтобы не добавлять элементы, для которых уже есть корень.
...
Рейтинг: 0 / 0
20.02.2021, 08:37
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047516
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Адаптирую реальный запрос.
Выполняю в SQL*Plus такой фрагмент:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
  select rownum as RN--, level as LVL
  , U.STAFF_ID as USER_ID
  , T.STAFF_ID as PARENT_ID
  , U.IS_ADMIN
  , U.IS_ROLE
  , U.LOGIN
  from
  (
    select STAFF_ID, ROLE_ID from BM_STAFF_ROLE
    union all
    select null, STAFF_ID from BM_STAFF
  ) T join BM_STAFF U on (U.STAFF_ID = T.ROLE_ID)
  --start with T.STAFF_ID is null
  --connect by T.STAFF_ID = prior T.ROLE_ID


Все хорошо, получаю список пользователей/ролей с назначенными ролями.
Но как только раскомментирую строки, относящиеся к иерархии, получаю ошибку:
Код: plaintext
1.
2.
ошибка в строке 1:
ORA-00600: код внутр. ошибки, аргументы: [sorsikbeg_1], [5], [0], [], [], [], [], []
Что это может быть?
...
Рейтинг: 0 / 0
20.02.2021, 14:11
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047518
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
А если вложенный запрос перенести в with — выполняется успешно.
Это баг?
...
Рейтинг: 0 / 0
20.02.2021, 14:24
| Ответить | Цитировать | Написать  
Помогите распутать иерархию
    #40047529
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Alibek B.
Это баг?
Ну ответ же очевиден, не правда ли?
...
Рейтинг: 0 / 0
20.02.2021, 15:17
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Помогите распутать иерархию
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=40047390&tid=1880419]:
 0ms
get settings:
 8ms
get forum list:
 10ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 23ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 46ms
get tp. blocked users:
 2ms
others: 12ms
total:  117ms
созд. / загр.: 117ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]