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

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

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

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

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

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

/* получить по имени пользователя (процедуры, триггера) все его права доступа
и права доступа всех связанных процедур и триггеров по цепочке. Позволяет
избежать бесконечной рекурсии в ситуации, когда процедуры имеют право на вызов
друг друга, в том числе по цепочке из нескольких объектов

Входной параметр:
  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

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

/* получить по имени пользователя (процедуры, триггера) все его права доступа
и права доступа всех связанных процедур и триггеров по цепочке. Позволяет
избежать бесконечной рекурсии в ситуации, когда процедуры имеют право на вызов
друг друга, в том числе по цепочке из нескольких объектов

Входной параметр:
  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