Oracle кто создал объект

Обновлено: 29.06.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 представления, которые показывают наглядную картину по аудиту пользователей, а также доступы к критичным объектам БД. В дальнейшем можно настроить систему алертов, которая бы при наступлении определённого события оповещала заинтересованных сотрудников любым доступным в вашей организации способом, например:

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

К слову сказать, в чем мы далее и убедимся. Для того, чтобы запросы пользователей могли создавать временные сегменты в табличном пространстве TEMP, им не нужны квоты на дисковое пространство. Попробуем создать пользователя! Запускайте SQL*Plus с пользователем SYS или SYSTEM пароли администраторов смотрите в шаге 5! Из всего выше сказанного, запишем вот такую конструкцию:

Здесь мы создаем пользователя (схему) DUMMY с паролем DUMB и позволяем ему резвится на 100 Мб пространства USERS и еще немного выделяем из пространства TEMP. Получаем в результате:

Ок! Пользователь (схема) создан. Наверное, можно уже подключится и начать создавать объекты! Пробуем!

Именное такой синтаксис подключения можно использовать, он еще называется строка коннекта и расписывается вот так:

Даем пользователю право создавать сессию с сервером:

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

Дадим такой запрос к представлению DBA_USERS:

Вот теперь он может не только создавать эти объекты, но и изменять их! А, что если пользователю необходимо будет удалить какой-либо объект или удалить записи из таблиц? Тогда нужно добавить права на удаление объектов БД вот так:

Уфф! Ну вот теперь кажется все! Пользователь действительно полноценный и может работать! Помните в шаге 6 мы с вами это уже проделывали, но тогда я не вдавался в подробности, так как было не до того! А, вот теперь давайте разберемся более детально и продолжим далее.

По умолчанию аккаунт не имеет никаких прав в БД Oracle. Невозможно даже создать подключения без назначенных прав. И даже после получения прав на подключения, аккаунт не может сделать ничего полезного (или опасного) без получения соответсвующих прав. Права назначаются с помощью команды GRANT и убираются с помощью команды REVOKE. Дополнительные директивы команды используются для разрешения аккаунта делится правами которые у него есть с другими пользователями. По умолчанию только аккаунта администратора (SYS и SYSTEM) владеют правами назначения прав. Пользователь который назначает права другому пользователю называется grantor когда получатель прав – grantee. Права разбиты на две группы: системные права, которые грубо говоря позволяют пользователю совершать действия влияющие на словарь данных, и права над объектами, которые позволяют пользователю совершать действия влияющие на данные.

Системные права

Всего доступно около двух сотен системных прав. Большинство из них влияет на действия затрагивающие словать данных (такие как создание таблиц или пользователей). Остальные влияют на экземпляр или БД (создание табличны пространств, изменение параметров БД и создание сессий). Наиболее часто используемые права это

CREATESESSION – права на подключения. Без этих прав вы даже не сможете подключиться к БД
RESTRICTEDSESSION – Если БД запущена с директивой STARTUPRESTRICT или применялась команда ALTERSYSTEMENABLERESTRICTEDSESSION, то только пользователи с этими правами смогут подключаться к БД
ALTERDATABASE – разрешает выполнять команды влияющие на физические структуры
ALTERSYSTEM – разрешает изменять параметры экземпляра и структуры памяти
CREATETABLESPACE – вместе с ALTERTABLESPACE и DROPTABLESPACE позволяют пользователю управлять табличными пространтсвами
CREATETABLE – позволяет gratee создавать таблицы в своей схеме; включает возможность создавать, изменять и удалять таблицы, выполнять команды DML и select и управлять индексами
GRANTANYOBJECTPRIVILEGE – позволяет grantee управлять правами объектов которые ему не принаджлежат, но не даёт прав ему самому
CREATEANYTABLE – grantee может создавать таблицы которые принадлежат другим аккаунтам
DROPANYTABLE – grantee позволяется удалять таблицы которые принадлежат другим аккаунтам
INSERTANYTABLE, UPDATEANYTABLE, DELETEANYTABLE – даёт grantee право выполнять DML команды над объектами которые ему не принадлежат
SELECTANYTABLE – Даёт право grantee выполнять SELECT к любмы таблицам.

Синтаксис для назначения прав

GRANT privilege [,privilege…] TO username;

После создания аккаунта, обычно назначаются права часто используемые пользователями кто вовлечён в разработку приложения

grant create session, alter session,

create table, create view, create synonym, create cluster,

create database link, create sequence,

create trigger, create type, create procedure, create operator

Эти права позволяют подключаться и настраивать сессию, создавать объекты и хранить PL/SQL объекты. Объекты могут быть созданы только в схеме аккаунта; нет прав к схемам других аккаунтов. Также создание объектов ограничивается лмимитами табличных пространств.

Другим вариантом назначения прав будет назначение grantee доступа для переназначения прав другим аккаунтам. Например

grant create table to scott with admin option;

grant create table to jon;

Выполнение этих команд позволит SCOTT создавать таблицы в совей схеме, и выполнять команду GRANT. SCOTT даёт права пользователю JON создавать таблицы – но JON сможет создавать таблицы только в схеме JON. На рисунке 6-5 показаны права пользователя в Database Control; ту же информацию можно получить выполнив запрос к представлению DBA_SYS_PRIVS.

Если системные разрешения были отозваны, все действия которые вы выполнили пока у вас были права остаются в силе. Если у вас были права с ADMIN OPTION то у всех пользователей которым вы назначили права – права остаются, несмотря на то что у вас права отозвали. Не остаётся записей кто именно назначил системные привилегии, таким образом невозможно забрать права CASCADE как показано на рисунке 6-6

Revocation of a system privilege will not cascade (unlike

revocation of an object privilege).

Права ANY дают доступ ко всем объектам в БД. Таким образом

grant select any table to scott

позволить аккаунту SCOTT выполнять запрос SELECT ко всем таблицам во всех схемах БД. Такое назначение прав считается дурным тоном и ANY права назначаются только DBA.

In fact, ANY is not as dangerous now as with earlier releases. It no longer

includes tables in the SYS schema, so the data dictionary is still protected. But

ANY should still be used with extreme caution, as it removes all protection

from user tables.

Объектные права

Объектные права дают доступ к выполнению команд DML и SELECT к соответствующим объектам и выполнению PL/SQL объектов. Эти права не существуют для объектов в схеме аккаунта; если у пользователя есть системные права CREATE TABLE – это значит что он может выполнять SELECT и DML запросы к таблицам которые он создал без дополнительных прав.

The ANY privileges, that grant permissions against objects in

every user account in the database, are not object privileges—they are

Объектные права применяются к разным группам объектов

GRANT privilege ON [schema.]object TO username [WITH GRANT OPTION];

grant select on store.customers to scott;

Можно использовать ALL чтобы применить права для всех операций, или использовать конкретное указание столбца таблицы или представления.

grant select on store.orders to scott;

grant update (order_status) on store.orders to scott;

grant all on store.regions to scott;

Эти команды позволят аккаунту SCOTT выполнять запрос SELECT ко всем столбцам таблицы ORDERS в схеме STORE но обновлять данные только в одном столбце. Также у аккаунта SCOTT есть доступ ко всем операциям к таблице REGIONS. На рисунке 6-7 отображается результат назначения прав при просмотре в Database Control

Granting privileges at the column level is often said to be bad practice

because of the massive workload involved. If it is necessary to restrict peoples’

access to certain columns, creating a view that shows only those columns will

often be a better alternative.

Использование директивы WITH GRANT OPTION позволит пользователю передавать свои права другим аккаунта. Оракл хранит информацию о том кто и кому дал доступ на объектном уровне; это позволяет отзывать права учитывая эту информацию. Рассмотрим пример

grant select on customers to sales with grant option;

grant select on store.customers to webapp with grant option;

grant select on store.customers to scott;

revoke select on customers from sales;

После выполнения этих команд, ни у пользователя SALES ни у пользователя WEBAPP ни у пользователя SCOTT нет прав на выполнение команд SELECT к таблице STORE.CUSTOMERS.

Revocation of an object privilege will cascade (unlike revocation of

Полномочия – это право на выполнение конкретного типа SQL-оператора или на доступ к объекту базы данных, принадлежащему другому пользователю. В базе данных Oracle необходимо явно предоставить пользователю полномочия для выполнения любых действий, включая подключение к базе данных или выборку, изменение и обновление данных в любой таблице, кроме собственной.

Существуют два основных типа полномочий Oracle: системные полномочия и объектные полномочия. Для предоставления пользователям как системных, так и объектынх полномочий служит оператор GRANT.

Системные полномочия:

Системные полномочия позволяют пользователю выполнить конкретное действие в базе данных либо действие с любым объектом схемы, конкретного типа. Хороший пример первого типа системных полномочий – полномочия, которые позволяют подключаться к базе данных, носящие название полномочий CONNECT. Другими полномочиями этого типа являются полномоичия CREATE TABLESPACE, CREATE USER, DROP USER и ALTER USER.

Второй класс системных полномоичий предоставляет пользователям право на выполнение операций, которыевлияют на объекты в любой схеме. Примерами этого типа системных полномочий служат ANALYZE ANY TABLE, GRANT ANY PRIVILEGE, INSERT ANY TABLE, DELETE ANY TABLE и т.п. Системные полномочия являются очень мощным средством и выдача их не тому пользователю может оказать разруши тельное влияние на базу данных.

Ниже перечислены некоторые наиболее часто используемые полномочия базы данных Oracle:

ADVISOR
ALTER DATABASE
ALTER SYSTEM
AUDIT SYSTEM
CREATE DATABASE LINK
CREATE TABLE
CREATE ANY INDEX
CREATE SESSION
CREATE TABLESPACE
CREATE USER
DROP USER
INSERT ANY TABLE

Пример:

Объектыные полномочия:

Объектыне полномочия – это полномочия по отношению к различным типам объектов базы данных. Объектыные полномочия дают пользователю возможность выполнять действия с конкретной таблицей, представлением, материализованным представлением, последовательностью, процедурой, функцией или пакетом. Следовательно, всем пользователям базы данных нужны объектные полномочия.

Для выдачи объектных полномочий можно использовать следующие SQL-операторы.

В базе данных Oracle схема (schema) определяется как коллекция логических структур данных, или объектов схемы, хотя в основном используется в качестве синонима пользователя базы данных (в частности, владельца приложения), который владеет схемой, относящейся к определенному приложению. Таким образом, схема accounting (бухгалтерия) внутри базы данных компании должна владеть всеми таблицами и кодом, относящимся к бухгалтерскому отделу. В дополнение к таблицам, схема включает в себя и другие объекты базы, такие как процедуры и функции PL/SQL, представления, последовательности, синонимы и кластеры. Подобное логическое отделение объектов внутри базы данных позволяет существенно облегчить задачу управления и защиты базы данных Oracle Database.

Хотя администратор баз данных может использовать оператор CREATE SCHEMA для заполнения схемы объектами базы данных, наподобие таблиц и представлений, гораздо чаще владелец приложения создает объекты базы, и его называют владельцем схемы . Пользователь, который создает объекты, владеет такими объектами, как таблицы, представления, процедуры, функции и триггеры. Владелец объекта должен явно выдать права доступа другим пользователям, наподобие прав на SELECT или UPDATE, чтобы пользователи могли работать с этими объектами.

Определяемые пользователем типы объектов

Oracle Database 11g и 12c — объектно-реляционная база данных, и как таковая, позволяет пользователям определять несколько типов объектов данных, которых нет среди стандартных реляционных типов данных. Эти определяемые пользователем объекты включают перечисленные ниже.

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

В приложении приведены примеры создания различного рода пользовательских объектных типов.

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

Существуют два основных способа создания схемы в базе данных Oracle. Более распространенный способ состоит в подключении от имени владельца схемы и создании всех таблиц, индексов и прочих объектов, которые планируется включить в схему. Поскольку все объекты создаются владельцем схемы, они автоматически становятся частью этой схемы.

Второй способ создания схемы предусматривает ее явное создание с помощью оператора CREATE SCHEMA. Этот оператор позволяет создать множество таблиц и представлений, а также выдать пользователям привилегии доступа к ним, и все это в единственном операторе SQL.

Читайте также: