Посмотреть пользователей oracle sql
Обновлено: 07.07.2024
Добрый день! Сегодняшняя статья посвящена аудиту пользователей в БД. Все примеры работают на БД Oracle, но используемые алгоритмы и срезы проверок, вероятно, есть во всех распространенных базах, просто немного иначе реализуются.
Перед нами была поставлена задача оценки активности пользователей БД с точки зрения информационной безопасности, а именно проверки ролей, привилегий, периода доступа к БД и иных метрик, которыми наделяются пользователи БД при предоставлении доступа.
Итак, какие задачи мы поставили перед собой (сразу отмечу, что в качестве уникального ключа мы выбрали логин пользователя в Oracle, и далее обогащали логины различными системными атрибутами, при этом сохраняя его уникальность):
- Установить связь «пользователь Oracle – ПК», с которых производилась авторизация в Oracle конкретными пользователями;
- Обозначить период активности пользователя в БД, в т.ч. флаг активности в текущем году;
- Проверить привилегии пользователя – в Oracle это звучит как Role privileges и System privileges.
Также была задача выявить все возможные действия для каждого объекта БД каждым пользователем, но сохранить уникальность пользователя не удавалось: оборотной стороной медали была потеря наглядности данных, поэтому мы решили создать отдельное представление.
Итак, начну с последнего и наиболее простого с точки зрения реализации пункта – мониторинга привилегий пользователей на каждый объект БД. Для создания этого представления хватило системной таблицы dba_tab_privs, в которой содержатся все пользователи и их полномочия, а также те, кто выдал эти полномочия. Итак, запустим следующий код:
Результат этого кода показывает, к каким объектам БД (OWNER, TABLE_NAME, TYPE) имеет доступ конкретный пользователь (GRANTEE), и какие полномочия у него есть для работы с каждым объектом БД (PRIVS_TO_OBJECT_BD):
Иными словами, суть представления – показать доступные действия пользователя для каждого объекта БД (в данном случае связка Пользователь БД + Объект БД – уникальна).
Отлично, двигаемся дальше. Теперь рассмотрим код для сбора различных системных атрибутов по каждому пользователю БД. Ввиду сложности кода разобьём его на составные части (для удобства выделим связанные атрибуты в SQL-блоки “with”). Так, сначала определимся с перечнем пользователей, по которым будем собирать аналитику. В нашем случае мы исключили пользователей, которые в качестве default_tablespace используют системные пространства БД:
Данный код использует системное представление dba_users и собирает некоторые атрибуты, например, текущий статус пользователя (открыт/заблокирован/…), дату блокировки пользователя, а также профайл пользователя (администратор/пользователь/технологическая учетная запись):
Так, список ключей уникален, теперь будем обогащать эту таблицу атрибутами из других системных таблиц, исходя из задачи. О них далее.
Добавим данные по авторизациям пользователей в БД. Для этого немного преобразуем системное представление dba_audit_session. Ввиду того, что изначально в представлении перечень пользователей БД неуникален, воспользуемся функцией listagg для сбора однотипных метрик пользователя в один кортеж. Для справки: функция listagg таблицу вида:
Преобразует в вид:
С помощью этой функции получаем уникальность пользователей БД, а значит готовы обогащать этими данными первоначальный список пользователей. Исходный код по авторизациям пользователей в БД выглядит так:
Результат исполнения кода:
Обратите внимание, дополнительно оконная sql-функция считает количество ПК, с которых осуществлялся вход под выбранным пользователем (атрибут PC_CNT).
Результат исполнения запроса:
Далее, выделим права каждого пользователя, для этого используем 2 таблицы Oracle DB: dba_role_privs и dba_tab_privs:
И второй код, для dba_tab_privs:
В столбце CNT_ROLE_PRIVELEGES – количество ролей каждого пользователя. Здесь можно отследить критичные. Например, для нас возможность пользователя просматривать содержимое всех таблиц на сервере – недопустима, поэтому все роли “select any table” были заменены на “select table”, что позволяет выводить содержимое только созданных пользователем таблиц. В принципе готово, осталось собрать все блоки “with” в одно представление с помощью конструкции JOIN:
Результат итогового представления (для наглядности показан в виде single record view):
Таким образом созданы 2 представления, которые показывают наглядную картину по аудиту пользователей, а также доступы к критичным объектам БД. В дальнейшем можно настроить систему алертов, которая бы при наступлении определённого события оповещала заинтересованных сотрудников любым доступным в вашей организации способом, например:
Да и в целом, инструменты, представленные в статье, частично готовы решать задачу оптимизации места на сервере за счет оценки активности пользователей. То есть, если у вас есть логины, которые последний раз заходили в БД несколько лет назад – то, вероятно, можно очищать их персональное пространство. Тема оценки активности использования объектов БД – уже совсем другая задача.
"Пользователь пользователю рознь" — я всегда это говорю, когда меня спрашивают:
"Как посмотреть список пользователей?"
Собственно, вас какие именно пользователи интересуют?
Есть те, которые работают — так сказать, трудятся в поле лица. А есть "мёртвые души" — те, кто просто числится.
Начнём с тружеников.
Если речь идёт о получении списка работающих пользователей , то надо смотреть список текущих подключений к базе. Делается это под администратором:
Запрос выводит список текущих сессий. Имена работающих пользователей будут выведены в колонке "USERNAME", время подключения в "LOGON_TIME".
Следует заметить, что:
-
Не все подключения инициированы пользователями.
Есть, так называемые, серверные процессы, которые тоже могут инициировать коннект к базе. Чтобы отличить пользовательские сессии от служебных, следует смотреть на значение в колонке "TYPE".
Типичная ситуация, когда пользователь, усевшись утром в рабочее кресло, запускает приложение, подключается к базе и затем сворачивает окошко на панель задач, чтобы развернуть его только вечером ради клика по крестику в правом верхнем углу. Весь день в базе будет висеть его сессия (вроде работает), но по факту активной работы в этой сессии не было.
Колонка "STATUS" показывает, кто работает именно в данный момент . Значение для таких сессий будет 'ACTIVE'. И надо понимать, что выполнение обычного запроса происходит быстро, как мимолётное мгновение. Хорошо видны только долгоиграющие запросы. Выполните запрос подряд несколько раз — может, что и заметите.
Иногда, смотря на список сессий, у вас может появиться сильное и непреодолимое желание кое-что сделать — не сдерживайте себя. Вот команда для отключения:
Конечно, команду надо выполнять с правами администратора.
Внимание! Удаляйте только пользовательские сессии.
Теперь разберёмся со списочным составом пользователей в базе .
Делается это тоже просто. Вводим следующий запрос:
Как пополнить этот список новыми членами и как удалить неугодных пользователей, я рассказывал в этом посте —
P.S. И не забывайте про команду desc в SQL*Plus. В v$session и dba_users есть много интересных колонок.
Похожие статьи:
Знаете что? Никуда не годится под пользователем SYSTEM выполнять упражнения из моего курса. Честно говоря, работать под ним тоже надо поменьше. Лучше создайте в базе ещё одного пользователя. Для этого проделайте следующее: Подключитесь к базе под пользователем SYSTEM.
Долго не мог понять, почему люди не любят пользоваться SQL*Plus. Оказывается: интерфейс убогий и бестолковый. Словом, не графический – мышкой ткнуть не куда (значит интуитивно не понятный). Мда. ..редко встретишь кодера, умеющего мышкой воять SELECT’ы.
Умеете делать резервную копию оракловой базы? Вопрос далеко-далеко не праздный (если вы уже знаете, как делать копию, то, наверное, догадываетесь, о чём пойдёт речь, правильно — о времени). Тема резервного копирования для администраторов оракла — одна из ключевых.
Я уверен, что это было задано раньше, но я не могу найти соответствующие данные для следующего.
Есть ли какая-то подготовленная таблица, которая может сделать следующее (я использовал dba_tab_privs, но он ограничен и не отвечает всем моим потребностям), если у кого-нибудь нет некоторых запросов для ответа на следующее?
- Список всех пользователей, которым была назначена определенная роль?
- Список всех ролей, предоставленных пользователю?
- Список всех привилегий, предоставленных пользователю?
- Укажите, какие таблицы предоставляют определенную роль для доступа к SELECT?
- Список всех таблиц, из которых пользователь может выбрать SELECT?
- Список всех пользователей, которые могут выбрать SELECT на определенной таблице (либо через соответствующую роль, либо через прямой грант (т. е. выбрать выделение по возможности для joe))? Результат этого запроса также должен показать, через какую роль пользователь имеет этот доступ или был ли он прямым грантом.
2 ответа
Список всех пользователей, которым была назначена определенная роль
Список всех ролей, предоставленных пользователю
Список всех привилегий, предоставляемых пользователю
Укажите, какие таблицы определенную роль предоставляет доступ к SELECT?
Список всех таблиц, из которых пользователь может выбрать:
Список всех пользователей, которые могут выбрать SELECT в определенной таблице (либо через соответствующую роль, либо через прямой грант (т. е. выбрать выделение по возможности для joe))? Результат этого запроса также должен показать, через какую роль пользователь имеет этот доступ или был ли он прямым грантом.
Существует множество способов получить нужную информацию:
присутствует в оракуле.
Вы можете просто запросить представления и получить детали: Например:
выберите * из DBA_COL_PRIVS;
выберите * из ALL_COL_PRIVS;
выберите * из USER_COL_PRIVS;
Это говорит вам:
DBA-представление описывает все гранты объектов столбца в базе данных. ВСЕ просмотр описывает все гранты объекта столбца, для которых текущий пользователь или PUBLIC является владельцем объекта, лицом, предоставляющим право, или грантополучателем. Представление USER описывает столбцы, для которых текущий пользователь является владельцем объекта, лицо, предоставляющее право, или грантополучатель.
При установке Oracle по умолчанию создаются два пользователя/схемы - SYS и SYSTEM. Я написал "пользователя/схемы" потому, что при создании нового пользователя для него создается одноименная схема. Не сразу понятно чем понятие "пользователь" отличается от понятия "схема". Чтобы понять представьте пользователя Windows (Unix). Пользователь имеет имя ИмяПользователя и принадлежащую ему папку - C:\Users\ИмяПользователя ( /home/ИмяПользователя ). Так вот пользователь Oracle аналогичен пользователю Windows, а схема - аналогична папке пользователя. Точно так же как у пользователя Windows, у пользователя Oracle есть набор прав. Так же как папка пользователя Windows содержит различные файлы, также и схема Oracle содержит различные объекты - таблицы, последовательности, триггеры и др. Если продолжать аналогию, то пользователей SYS и SYSTEM можно считать Администратором Windows или root-пользователем Unix. Они имеют неограниченные права. И работать под ними не рекомендуется. По-этому сначала нужно создать еще одного пользователя.
1. Создание пользователя и предоставление ему прав
Создадим пользователя, например fiftin :
Мы создали пользователя fiftin с паролем 123456 . Он не имеет абсолютно никаких прав. Вы даже не сможете под ним зайти:
Для наделения пользователя правами существует команда GRANT . Например дадим права пользователю fiftin на вход:
Если теперь вы попробуете подключиться как пользователь fiftin у вас это получится. Но это все что разрешено пользователю fiftin. Наделим пользователя правами администратора:
Теперь вы можете подцепиться к БД под fiftin'ом как админ:
Создадим таблицу:
Вставим данные:
2. Права на создание таблиц
Создадим еще одного пользователя - test:
Дадим ему права:
Теперь пользователь test может подключаться и создавать таблицы. Попробуем создать таблицу (не забудьте зайти под test'ом):
Получаем ошибку:
Почему так? Оказывается для того чтобы обычный пользователь (не админ) мог что-либо создать в БД, ему нужно выделить для этого место. Зайдем снова под fiftin'ом и выполним команду:
Этой командой мы выделяем пользователю test 50Мб под его нужды. Попробуйте теперь зайти под пользователем test и создать таблицу и у вас получится.
Читайте также: