SQL, Анализ данных

АУДИТ ПОЛЬЗОВАТЕЛЕЙ ORACLE БАЗЫ ДАННЫХ ШТАТНЫМИ СРЕДСТВАМИ

Время прочтения: 8 мин.

Добрый день! Сегодняшняя статья посвящена аудиту пользователей в БД. Все примеры работают на БД Oracle, но используемые алгоритмы и срезы проверок, вероятно, есть во всех распространенных базах, просто немного иначе реализуются.

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

Из желаемых ограничений задачи было решение опираться на готовые системные представления БД Oracle, чтобы максимально избавить себя от работы по обновлению данных. Здесь и далее постараюсь избегать сложных терминов администрирования — для максимального понимания происходящего. Детали можно почитать в официальной документации к Oracle.

Итак, какие задачи мы поставили перед собой (сразу отмечу, что в качестве уникального ключа мы выбрали логин пользователя в Oracle, и далее обогащали логины различными системными атрибутами, при этом сохраняя его уникальность):

  1. Установить связь «пользователь Oracle – ПК», с которых производилась авторизация в Oracle конкретными пользователями;
  2. Обозначить период активности пользователя в БД, в т.ч. флаг активности в текущем году;
  3. Проверить привилегии пользователя – в Oracle это звучит как Role privileges и System privileges.

Также была задача выявить все возможные действия для каждого объекта БД каждым пользователем, но сохранить уникальность пользователя не удавалось: оборотной стороной медали была потеря наглядности данных, поэтому мы решили создать отдельное представление.

Итак, начну с последнего и наиболее простого с точки зрения реализации пункта – мониторинга привилегий пользователей на каждый объект БД. Для создания этого представления хватило системной таблицы dba_tab_privs, в которой содержатся все пользователи и их полномочия, а также те, кто выдал эти полномочия. Итак, запустим следующий код:

    select
        GRANTEE
        , OWNER
        , TABLE_NAME
        , TYPE
        , listagg(PRIVILEGE, ',') within group (order by PRIVILEGE) as PRIVS_TO_OBJECT_BD
    from
    (
        select
            GRANTEE
            , OWNER
            , TABLE_NAME
            , PRIVILEGE
            , TYPE
        from dba_tab_privs
    )
    group by
        GRANTEE
        , OWNER
        , TABLE_NAME
        , TYPE

Результат этого кода показывает, к каким объектам БД (OWNER, TABLE_NAME, TYPE) имеет доступ конкретный пользователь (GRANTEE), и какие полномочия у него есть для работы с каждым объектом БД (PRIVS_TO_OBJECT_BD):

Иными словами, суть представления – показать доступные действия пользователя для каждого объекта БД (в данном случае связка Пользователь БД + Объект БД – уникальна).

Отлично, двигаемся дальше. Теперь рассмотрим код для сбора различных системных атрибутов по каждому пользователю БД. Ввиду сложности кода разобьём его на составные части (для удобства выделим связанные атрибуты в SQL-блоки “with”). Так, сначала определимся с перечнем пользователей, по которым будем собирать аналитику. В нашем случае мы исключили пользователей, которые в качестве default_tablespace используют системные пространства БД:

with main_users as (
select
    USERNAME
    , ACCOUNT_STATUS
    , to_char(LOCK_DATE, 'dd.mm.yyyy')        LOCK_DATE
    , to_char(EXPIRY_DATE, 'dd.mm.yyyy')      EXPIRY_DATE
    , DEFAULT_TABLESPACE
    , to_char(CREATED, 'dd.mm.yyyy')          CREATED
    , PROFILE
from dba_users
where DEFAULT_TABLESPACE NOT in ('SYSAUX', 'SYSTEM'))

Данный код использует системное представление dba_users и собирает некоторые атрибуты, например, текущий статус пользователя (открыт/заблокирован/…), дату блокировки пользователя, а также профайл пользователя (администратор/пользователь/технологическая учетная запись):

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

Добавим данные по авторизациям пользователей в БД. Для этого немного преобразуем системное представление dba_audit_session. Ввиду того, что изначально в представлении перечень пользователей БД неуникален, воспользуемся функцией listagg для сбора однотипных метрик пользователя в один кортеж. Для справки: функция listagg таблицу вида:

Преобразует в вид:

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

select
    USERNAME
    , listagg(OS_USERNAME, ', ') within group (order by OS_USERNAME) OS_USERS
    , PC_CNT
from (
    select
        OS_USERNAME
        , USERNAME
        , count(OS_USERNAME) over (partition by USERNAME) PC_CNT
    from (
        select
            upper(OS_USERNAME) OS_USERNAME
            , upper(USERNAME) USERNAME, row_number() over (partition by upper(OS_USERNAME), upper(USERNAME) order by 0) RN
        from dba_audit_session
    )
    where RN = 1
)
group by
    USERNAME
    , PC_CNT

Результат исполнения кода:

Обратите внимание, дополнительно оконная sql-функция считает количество ПК, с которых осуществлялся вход под выбранным пользователем (атрибут PC_CNT).

Далее — в рамках оценки активности пользователей необходимо понимать их период активности, актуальность «учетки» в текущем году, а также количество успешных авторизаций на сервере. Эти задачи исполняет следующий код:

select
    USERNAME
    , LOGONS_RANGE
    , max(ACTIVE_IN_CURRENT_YEAR) ACTIVE_IN_CURRENT_YEAR
    , LOGONS_CNT
from (
    select
        USERNAME
        , to_char(min(TIMESTAMP) over (partition by USERNAME), 'dd.mm.yyyy') || ' - ' || to_char(max(TIMESTAMP) over (partition by USERNAME), 'dd.mm.yyyy') LOGONS_RANGE
        , count(USERNAME) over(partition by USERNAME) LOGONS_CNT
        , row_number() over (partition by USERNAME order by 1) RN
        , case when to_char(TIMESTAMP, 'YYYY') = to_char(SYSDATE, 'YYYY') then 1 else 0 end ACTIVE_IN_CURRENT_YEAR
    from dba_audit_session
    where action_name in ('LOGON')
    )
group by
    USERNAME
    , LOGONS_RANGE
    , LOGONS_CNT)

Результат исполнения запроса:

Обратите внимание, атрибут ACTIVE_IN_CURRENT_YEAR использует не статичный год, а год из системной даты (to_char(TIMESTAMP, ‘YYYY’)). Столбец LOGONS_CNT – количество успешных авторизаций пользователя на сервере.

Далее, выделим права каждого пользователя, для этого используем 2 таблицы Oracle DB: dba_role_privs и dba_tab_privs:

select
    GRANTEE
    , CNT_ROLE_PRIVELEGES
    , listagg(GRANTED_ROLE, ', ') within group (order by GRANTED_ROLE) ROLE_PRIVELEGES
from (
    select
        GRANTEE
        , GRANTED_ROLE
        , count(GRANTED_ROLE) over (partition by GRANTEE) CNT_ROLE_PRIVELEGES
    from dba_role_privs
    )
group by
    GRANTEE
    , CNT_ROLE_PRIVELEGES

И второй код, для dba_tab_privs:

select
    GRANTEE
    , listagg(PRIVILEGE, ',') within group (order by PRIVILEGE)                                        PRIVS_TO_OBJECT_BD
from (
select GRANTEE, PRIVILEGE, row_number() over (partition by GRANTEE, PRIVILEGE order by 1) RN from dba_tab_privs
)
where RN = 1
group by GRANTEE

Результат:

В столбце CNT_ROLE_PRIVELEGES – количество ролей каждого пользователя. Здесь можно отследить критичные. Например, для нас возможность пользователя просматривать содержимое всех таблиц на сервере – недопустима, поэтому все роли “select any table” были заменены на “select table”, что позволяет выводить содержимое только созданных пользователем таблиц. В принципе готово, осталось собрать все блоки “with” в одно представление с помощью конструкции JOIN:

select 
    main_users.USERNAME                                 --ПОЛЬЗОВАТЕЛЬ ОРАКЛ
    , main_users.CREATED                                --ДАТА СОЗДАНИЯ ПОЛЬЗОВАТЕЛЯ
    , main_users.PROFILE                                --ПРОФАЙЛ ПОЛЬЗОВАТЕЛЯ
    , main_users.DEFAULT_TABLESPACE                     --TABLESPACE ПОЛЬЗОВАТЕЛЯ
    , main_users.ACCOUNT_STATUS                         --СТАТУС УЧЕТКИ ПОЛЬЗОВАТЕЛЯ (ОТКРЫТ, ЗАБЛОКИРОВАН, ИСТЕК)
    , main_users.LOCK_DATE                              --ДАТА БЛОКИРОВКИ ПОЛЬЗОВАТЕЛЯ
    , main_users.EXPIRY_DATE                            --ДАТА ИСТЕЧЕНИЯ АКТИВНОСТИ
    , oracle_users_logons.LOGONS_RANGE                  --ПЕРИОД ВХОДОВ ПОЛЬЗОВАТЕЛЯ В БД (МИН - МАКС)
    , oracle_users_logons.ACTIVE_IN_CURRENT_YEAR        --ФЛАГ ВХОДА В БД В ТЕКУЩЕМ ГОДУ
    , oracle_users_logons.LOGONS_CNT                    --КОЛ-ВО ВХОДОВ В БД ПОЛЬЗОВАТЕЛЯ
    , cnt_os_users.OS_USERS                             --УЧЕТКА ОПЕРАЦИОННОЙ СИСТЕМЫ, ПОД КОТОРОЙ ОСУЩЕСТВЛЯЛСЯ ВХОД ДАННОГО ОРАКЛ-ПОЛЬЗОВАТЕЛЯ В БД
    , cnt_os_users.PC_CNT                               --КОЛ-ВО УНИКАЛЬНЫХ УЧЕТОК ОПЕРАЦИОННОЙ СИСТЕМЫ
    , role_priveleges.CNT_ROLE_PRIVELEGES               --КОЛ-ВО ROLE PRIVELEGES (CONNECT, SEMIDBA И ТД)
    , role_priveleges.ROLE_PRIVELEGES                   --ПЕРЕЧЕНЬ ROLE PRIVELEGES (CONNECT, SEMIDBA И ТД)
    , system_privileges.PRIVS_TO_OBJECT_BD              --ГРАНТЫ НА ОБЪЕКТЫ БД
from main_users left join oracle_users_logons on main_users.USERNAME = oracle_users_logons.USERNAME
                left join cnt_os_users        on main_users.USERNAME = cnt_os_users.USERNAME
                left join role_priveleges     on main_users.USERNAME = role_priveleges.GRANTEE
                left join system_privileges   on main_users.USERNAME = system_privileges.GRANTEE

Результат итогового представления (для наглядности показан в виде single record view):

Таким образом созданы 2 представления, которые показывают наглядную картину по аудиту пользователей, а также доступы к критичным объектам БД. В дальнейшем можно настроить систему алертов, которая бы при наступлении определённого события оповещала заинтересованных сотрудников любым доступным в вашей организации способом, например:

  1. Вход пользователя с нового ПК;
  2. Попытка получить доступ ко всем объектам БД;
  3. Различные манипуляции с ролями/привилегиями БД;
  4. Несанкционированный перевод пользователя в несоответствующий ролевой модели профайл пользователя с целью получения расширенных прав;
  5. И т.д.

Да и в целом, инструменты, представленные в статье, частично готовы решать задачу оптимизации места на сервере за счет оценки активности пользователей. То есть, если у вас есть логины, которые последний раз заходили в БД несколько лет назад – то, вероятно, можно очищать их персональное пространство. Тема оценки активности использования объектов БД – уже совсем другая задача.

Советуем почитать