Админ

» Firebird » Firebird SQL

суббота, 21 апреля 2018 г.

Firebird SQL: получение списка прав доступа связанных процедур и триггеров

| Автор
В правах доступа к объектам Firebird довольно легко запутаться: например, пользователь входит под определённой ролью, запускает хранимую процедуру, которая вносит изменения в таблицу, при этом вызывается триггер на изменение, и этот триггер пишет что-то в другую таблицу, или вызывает другую процедуру и т. д. Каждый пользователь, роль, процедура или триггер должен иметь соответствующие права, в противном случае пользователь получит сообщение об ошибке. А ошибка эта может быть из-за того, что какому-то триггеру не разрешено работать с какой-то таблицей в длинной цепочке связей.

Interbase Expert умеет показывать только права доступа «первого уровня», то есть можно узнать например, с какими объектами базы может работать пользователь. Для получения прав «второго уровня» нужно извлечь список подпрограмм, проверить все триггеры ко всем таблицам… в общем, если таблиц больше десятка, то это тяжёлая работа.

В очередной раз столкнувшись с необходимостью отслеживать права доступа к объектам, я написал рекурсивный скрипт. Скрипт получает на вход имя пользователя, роли, процедуры или триггера и выводит список объектов базы данных, к которым этот пользователь (роль и т. д.) имеет доступ, а также объектов, которые участвуют в описанной выше цепочке. Здесь представлены два варианта скрипта: один просто выводит список объектов и прав доступа, второй ещё и показывает саму цепочку связей.

Поскольку скрипт рекурсивный, на него распространяется традиционное для Firebird ограничение: не более 1024 звеньев в цепочке ссылок. Впрочем, если ваша база данных содержит такое количество взаимосвязей, то вы наверняка используете какие-нибудь специализированные программы, и вам такой скрипт не нужен.

Простой список

?
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
/* получить по имени пользователя (процедуры, триггера) все его права доступа
и права доступа всех связанных процедур и триггеров по цепочке. Позволяет
избежать бесконечной рекурсии в ситуации, когда процедуры имеют право на вызов
друг друга, в том числе по цепочке из нескольких объектов
 
Входной параметр:
  USER_NAME — имя пользователя, роли, процедуры или триггера, чьи права
    проверяются
 
Выходные параметры:
  OBJECT_TYPE — тип объекта (процедура или таблица, триггеры не перечисляются)
  OBJECT_NAME — имя объекта
  PRIVILEGE — список непосредственно полученных прав через запятую:
    S — чтение,
    R — ссылка,
    I — вставка,
    D — удаление,
    U — изменение данных,
    X — выполнение (только для процедур),
    M — членство (для пользователя по отношению к роли)
  PRIVILEGE_EX — список косвенных (по цепочке) прав, расшифровывается так же
 
*/
execute block (
  USER_NAME varchar(31) = :USER_NAME)
returns (
  OBJECT_TYPE varchar(9),
  OBJECT_NAME varchar(31),
  PRIVILEGE varchar(9),
  PRIVILEGE_EX varchar(9))
as
declare variable MAX_LEVEL smallint;
declare variable RECORD_CNT integer;
declare variable RECORD_CNT_OLD integer;
begin
  MAX_LEVEL = 2;
  RECORD_CNT_OLD = -1;
  RECORD_CNT = 0;
       
  -- получение максимального уровня вложенности
  while (RECORD_CNT > RECORD_CNT_OLD) do
    for with recursive data
        as (select
              1 as LVL,
              RDB$OBJECT_TYPE as OBJ_TYPE,
              RDB$RELATION_NAME as OBJECT_NAME,
              RDB$OBJECT_TYPE || trim(RDB$PRIVILEGE) || RDB$RELATION_NAME as OBJECT_DATA
            from
              RDB$USER_PRIVILEGES
            where
              RDB$USER = :USER_NAME
            union all
            select
              data.LVL + 1 as LVL,
              R.RDB$OBJECT_TYPE as OBJ_TYPE,
              R.RDB$RELATION_NAME as OBJECT_NAME,
              R.RDB$OBJECT_TYPE || trim(R.RDB$PRIVILEGE) || R.RDB$RELATION_NAME as OBJECT_DATA
            from
              RDB$USER_PRIVILEGES R
            inner join
              data on data.LVL < :MAX_LEVEL and data.OBJECT_NAME = R.RDB$USER and data.OBJECT_NAME <> R.RDB$RELATION_NAME
            where
              RDB$USER <> :USER_NAME and R.RDB$RELATION_NAME <> :USER_NAME
            union all
            select
              data.LVL + 1 as LVL,
              2 as OBJ_TYPE,
              T.RDB$TRIGGER_NAME as OBJECT_NAME,
              '2X' || T.RDB$TRIGGER_NAME as OBJECT_DATA
            from
              RDB$TRIGGERS T
            inner join
              data on data.OBJ_TYPE = 0 and data.OBJECT_NAME = T.RDB$RELATION_NAME
            where
            T.RDB$TRIGGER_INACTIVE = 0)
        select
          count(distinct OBJECT_DATA)
        from
          data
        into
          :RECORD_CNT
    do
    begin
      RECORD_CNT_OLD = RECORD_CNT;
      MAX_LEVEL = MAX_LEVEL + 1;
    end
 
  -- извлечение прав доступа
  for with recursive data
      as (-- права, явно заданные через GRANT
          select
            1 as LVL,
            RDB$OBJECT_TYPE as OBJ_TYPE,
            decode(RDB$OBJECT_TYPE, 1, 'представление', 5, 'процедура', 13, 'роль', 'таблица') as OBJECT_TYPE,
            RDB$RELATION_NAME as OBJECT_NAME,
            trim(RDB$PRIVILEGE) as PRIVILEGE,
            null as PRIVILEGE_EX
          from
            RDB$USER_PRIVILEGES
          where
            RDB$USER = :USER_NAME
          union all
          -- права, косвенно полученные по ссылке из процедуры или триггера
          select
            data.LVL + 1 as LVL,
            R.RDB$OBJECT_TYPE as OBJ_TYPE,
            decode(R.RDB$OBJECT_TYPE, 1, 'представление', 5, 'процедура', 13, 'роль', 'таблица') as OBJECT_TYPE,
            R.RDB$RELATION_NAME as OBJECT_NAME,
            null as PRIVILEGE,
            trim(R.RDB$PRIVILEGE) as PRIVILEGE_EX
          from
            RDB$USER_PRIVILEGES R
          inner join
            data on data.LVL < :MAX_LEVEL and data.OBJECT_NAME = R.RDB$USER and data.OBJECT_NAME <> R.RDB$RELATION_NAME
          where
            RDB$USER <> :USER_NAME and R.RDB$RELATION_NAME <> :USER_NAME
          union all
          -- триггеры — на их запуск не нужны отдельные права, но сами они могут иметь право на работу с другими объектами БД
          select
            data.LVL + 1 as LVL,
            2 as OBJ_TYPE,
            null as OBJECT_TYPE,
            T.RDB$TRIGGER_NAME as OBJECT_NAME,
            null as PRIVILEGE,
            null as PRIVILEGE_EX
          from
            RDB$TRIGGERS T
          inner join
            data on data.OBJ_TYPE = 0 and data.OBJECT_NAME = T.RDB$RELATION_NAME
          where
            T.RDB$TRIGGER_INACTIVE = 0
          order by
            2, 4, 5)
      select
        OBJECT_TYPE,
        OBJECT_NAME,
        list(distinct PRIVILEGE) as PRIVILEGE,
        list(distinct PRIVILEGE_EX) as PRIVILEGE_EX
      from
        data
      where
        OBJ_TYPE <> 2
      group by
        1, 2
      order by
        1 desc, 2
      into
        :OBJECT_TYPE,
        :OBJECT_NAME,
        :PRIVILEGE,
        :PRIVILEGE_EX
  do
    suspend;
end

Список с указанием вызывающих подпрограмм

?
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
/* получить по имени пользователя (процедуры, триггера) все его права доступа
и права доступа всех связанных процедур и триггеров по цепочке. Позволяет
избежать бесконечной рекурсии в ситуации, когда процедуры имеют право на вызов
друг друга, в том числе по цепочке из нескольких объектов
 
Входной параметр:
  USER_NAME — имя пользователя, роли, процедуры или триггера, чьи права
    проверяются
 
Выходные параметры:
  OBJECT_TYPE — тип объекта (процедура, таблица и т.п. триггеры не перечисляются)
  OBJECT_NAME — имя объекта
  PRIVILEGE — список непосредственно полученных прав через запятую:
    S — чтение,
    R — ссылка,
    I — вставка,
    D — удаление,
    U — изменение данных,
    X — выполнение (только для процедур),
    M — членство (для пользователя по отношению к роли)
  CALLER — имя объекта, который имеет права на данный
  CALLER_TYPE — тип объекта CALLER (процедура, триггер)
  PRIVILEGE_EX — список косвенных (по цепочке) прав, расшифровывается так же
  LVL — уровень вложенности (1 — непосредсвенный доступ, 2 — по цепочке через
    одну подпрограмму и пр.)
 
*/
execute block (
  USER_NAME varchar(31) = :USER_NAME)
returns (
  OBJECT_TYPE varchar(13),
  OBJECT_NAME varchar(31),
  PRIVILEGE varchar(9),
  CALLER_TYPE varchar(13),
  CALLER varchar(31),
  LVL smallint)
as
declare variable MAX_LEVEL smallint;
declare variable RECORD_CNT integer;
declare variable RECORD_CNT_OLD integer;
begin
  MAX_LEVEL = 2;
  RECORD_CNT_OLD = -1;
  RECORD_CNT = 0;
       
  -- получение максимального уровня вложенности
  while (RECORD_CNT > RECORD_CNT_OLD) do
    for with recursive data
        as (select
              1 as LVL,
              RDB$OBJECT_TYPE as OBJ_TYPE,
              RDB$RELATION_NAME as OBJECT_NAME,
              RDB$OBJECT_TYPE || trim(RDB$PRIVILEGE) || RDB$RELATION_NAME as OBJECT_DATA
            from
              RDB$USER_PRIVILEGES
            where
              RDB$USER = :USER_NAME
            union all
            select
              data.LVL + 1 as LVL,
              R.RDB$OBJECT_TYPE as OBJ_TYPE,
              R.RDB$RELATION_NAME as OBJECT_NAME,
              R.RDB$OBJECT_TYPE || trim(R.RDB$PRIVILEGE) || R.RDB$RELATION_NAME as OBJECT_DATA
            from
              RDB$USER_PRIVILEGES R
            inner join
              data on data.LVL < :MAX_LEVEL and data.OBJECT_NAME = R.RDB$USER and data.OBJECT_NAME <> R.RDB$RELATION_NAME
            where
              RDB$USER <> :USER_NAME and R.RDB$RELATION_NAME <> :USER_NAME
            union all
            select
              data.LVL + 1 as LVL,
              2 as OBJ_TYPE,
              T.RDB$TRIGGER_NAME as OBJECT_NAME,
              '2X' || T.RDB$TRIGGER_NAME as OBJECT_DATA
            from
              RDB$TRIGGERS T
            inner join
              data on data.OBJ_TYPE = 0 and data.OBJECT_NAME = T.RDB$RELATION_NAME
            where
            T.RDB$TRIGGER_INACTIVE = 0)
        select
          count(distinct OBJECT_DATA)
        from
          data
        into
          :RECORD_CNT
    do
    begin
      RECORD_CNT_OLD = RECORD_CNT;
      MAX_LEVEL = MAX_LEVEL + 1;
    end
 
  -- извлечение прав доступа
  for with recursive data
      as (-- права, явно заданные через GRANT
          select
            1 as LVL,
            null as CALLER,
            null as CALLER_TYPE,
            RDB$OBJECT_TYPE as OBJ_TYPE,
            decode(RDB$OBJECT_TYPE, 1, 'представление', 5, 'процедура', 13, 'роль', 'таблица') as OBJECT_TYPE,
            RDB$RELATION_NAME as OBJECT_NAME,
            trim(RDB$PRIVILEGE) as PRIVILEGE
          from
            RDB$USER_PRIVILEGES
          where
            RDB$USER = :USER_NAME
          union all
          -- права, косвенно полученные по ссылке из процедуры или триггера
          select
            data.LVL + 1 as LVL,
            R.RDB$USER as CALLER,
            data.OBJECT_TYPE as CALLER_TYPE,
            R.RDB$OBJECT_TYPE as OBJ_TYPE,
            decode(R.RDB$OBJECT_TYPE, 1, 'представление', 5, 'процедура', 13, 'роль', 'таблица') as OBJECT_TYPE,
            R.RDB$RELATION_NAME as OBJECT_NAME,
            trim(R.RDB$PRIVILEGE) as PRIVILEGE
          from
            RDB$USER_PRIVILEGES R
          inner join
            data on data.LVL < :MAX_LEVEL and data.OBJECT_NAME = R.RDB$USER and data.OBJECT_NAME <> R.RDB$RELATION_NAME
          where
            RDB$USER <> :USER_NAME and R.RDB$RELATION_NAME <> :USER_NAME
          union all
          -- триггеры — на их запуск не нужны отдельные права, но сами они могут иметь право на работу с другими объектами БД
          select
            data.LVL + 1 as LVL,
            T.RDB$RELATION_NAME as CALLER,
            data.OBJECT_TYPE as CALLER_TYPE,
            2 as OBJ_TYPE,
            'триггер' as OBJECT_TYPE,
            T.RDB$TRIGGER_NAME as OBJECT_NAME,
            null as PRIVILEGE
          from
            RDB$TRIGGERS T
          inner join
            data on data.OBJ_TYPE = 0 and data.OBJECT_NAME = T.RDB$RELATION_NAME
          where
            T.RDB$TRIGGER_INACTIVE = 0
          order by
            2, 4, 5)
      select
        OBJECT_TYPE,
        OBJECT_NAME,
        list(distinct PRIVILEGE) as PRIVILEGE,
        CALLER,
        CALLER_TYPE,
        min(LVL)
      from
        data
      where
        OBJ_TYPE <> 2
      group by
        1, 2, 4, 5
      order by
        1 desc, 2, 4, 5, 6
      into
        :OBJECT_TYPE,
        :OBJECT_NAME,
        :PRIVILEGE,
        :CALLER,
        :CALLER_TYPE,
        :LVL
  do
    suspend;
end
,

Комментариев нет:

Отправить комментарий

Пожалуйста, не используйте в сообщениях ненормативную лексику и нарушающие закон темы

К началу