SQL structure

В этой статье собраны и структурированы минимальные основы SQL Oracle из обширной документации базы данных Oracle 12c. Для тех, кто начинает изучение базы, эта информация поможет обрисовать общую картину. Для разработчиков и тестировщиков базы данных эти таблицы служат удобными помощниками в повседневной работе.
Расшифровки языковых аббревиатур в DB Oracle

SQL	Structured Query Language
DDL	Data Definition Language (DDL) statements manage schema objects in the database. These statements create new, drop old, and establish schema objects. They also control access to schema objects.
DML	Data Manipulation Language (DML) statements can change data in database tables. For example, DML statements insert new rows into a table, update column values in existing rows, delete rows from a table, lock a table in the database, and explain the execution plan for a SQL statement.
TCL	Transaction control language
PL	Procedural Language

Структуризация

SQL
Static PL/SQL			DDL		Dynamic PL/SQL		SQL*Plus
query	DML	TCL	manage	control	native	DBMS_SQL package
SELECT	DELETE	COMMIT	ALTER	ADMINISTER	CLOSE	DBMS_SQL.RETURN_RESULT Procedure	@
	EXPLAIN	ROLLBACK	CREATE	ANALYZE	EXECUTE IMMEDIATE	DBMS_SQL.GET_NEXT_RESULT Procedure	@@
	INSERT	SAVEPOINT	DROP	ASSOCIATE	FETCH	DBMS_SQL.TO_REFCURSOR Function	/
	LOCK TABLE	SET TRANSACTION	FLASHBACK	AUDIT	OPEN FOR	DBMS_SQL.TO_CURSOR_NUMBER Function	ACCEPT, APPEND, ARCHIVE, ATTRIBUTE
	MERGE		GRANT	CALL			BREAK, BTITLE
	UPDATE		PURGE	COMMENT			CHANGE, CLEAR, COLUMN, COMPUTE, CONNECT, COPY
			RENAME	DISASSOCIATE			DEFINE, DEL, DESCRIBE, DISCONNECT
			REVOKE	NOAUDIT			EDIT, EXECUTE, EXIT
			TRUNCATE	SET (constraint, role)			GET
							HELP, HOST
							INPUT
							LIST
							PASSWORD, PAUSE, PRINT, PROMPT
							QUIT
							RECOVER, REMARK, REPFOOTER, REPHEADER, RUN
							SAVE, SET, SHOW, SHUTDOWN, SPOOL, START, STARTUP, STORE
							TIMING, TTITLE
							UNDEFINE
							VARIABLE
							WHENEVER
							XQUERY

Нижеследующие списки касаются PL (Procedural Language)

Stored PL/SQL Units
Anonymous Block - часть хранимых подпрограмм, самостоятельный блок в скрипте или теле джоба, триггера
FUNCTION
LIBRARY
PACKAGE
PACKAGE BODY
PROCEDURE
TRIGGER
TYPE
TYPE BODY

PL/SQL Data Types
SQL	BOOLEAN	INTEGER	REF CURSOR	User-Defined Subtypes	Predefined
BINARY_FLOAT		SIMPLE_INTEGER		Unconstrained	BFILE
BINARY_DOUBLE		PLS_INTEGER		Constrained	BLOB
CHAR		BINARY_INTEGER		with Base Types	BOOLEAN
VARCHAR2					CHAR
LONG					CLOB
LONG RAW					DATE
ROWID					NUMBER
UROWID

PL/SQL Collections and Records						Collection Methods
Associative Arrays	Collection Types	Multidimensional Collections	Nested Tables	Varrays (Variable-Size Arrays)	Record	COUNT
					%ROWTYPE Attribute	DELETE
					Constants	EXISTS
					Types	EXTEND
					Variables	FIRST
						LAST
						LIMIT
						NEXT
						PRIOR
						TRIM

PL/SQL Language Elements
Statement	Pragma	Constuction	Declaration	Attributes	Others
Assignment	AUTONOMOUS_TRANSACTION	Block	Collection Variable	Implicit Cursor	Collection Method Invocation
Basic LOOP	EXCEPTION_INIT	Comment	Constant	Named Cursor	RETURNING INTO Clause
CASE	INLINE	Exception Handler	Cursor Variable	%ROWTYPE	SQLCODE Function
CLOSE	RESTRICT_REFERENCES	Expression	Exception	%TYPE	SQLERRM Function
CONTINUE	SERIALLY_REUSABLE		Explicit Cursor
Cursor FOR LOOP	UDF		Formal Parameter
DELETE			Function
EXECUTE IMMEDIATE			Procedure
EXIT			Record Variable
FETCH			Scalar Variable
FOR LOOP
FORALL
GOTO
IF
INSERT
NULL
OPEN
OPEN FOR
PIPE ROW
RAISE
RETURN
SELECT INTO
UPDATE
WHILE LOOP

PL/SQL Control Statements
Conditional Selection	LOOP	Sequential Control
IF THEN	LOOP	GOTO
IF THEN ELSE	EXIT	NULL
IF THEN ELSIF	EXIT WHEN
Simple CASE	CONTINUE
Searched CASE	CONTINUE WHEN
	FOR LOOP
	WHILE LOOP

SQL Statements for Stored PL/SQL Units
Изменение свойств*	Создание	Удаление
ALTER FUNCTION	CREATE FUNCTION	DROP FUNCTION
ALTER LIBRARY	CREATE LIBRARY	DROP LIBRARY
ALTER PACKAGE	CREATE PACKAGE	DROP PACKAGE
	CREATE PACKAGE BODY	DROP PACKAGE BODY**
ALTER PROCEDURE	CREATE PROCEDURE	DROP PROCEDURE
ALTER TRIGGER	CREATE TRIGGER	DROP TRIGGER
ALTER TYPE	CREATE TYPE	DROP TYPE
	CREATE TYPE BODY	DROP TYPE BODY

* - вместо внесения изменений через ALTER-команду можно пересоздать объект командой CREATE OR REPLACE. При этом объект сохранит статус, например, включенность триггера. ** - в документации Oracle пропущена команда, но удалить тело пакета с сохранением его спецификации позволительно

PL/SQL Reserved Words
ALL, ALTER, AND, ANY, AS, ASC, AT
BEGIN, BETWEEN, BY
CASE, CHECK, CLUSTERS, CLUSTER, COLAUTH, COLUMNS, COMPRESS, CONNECT, CRASH, CREATE, CURSOR
DECLARE, DEFAULT, DESC, DISTINCT, DROP
ELSE, END, EXCEPTION, EXCLUSIVE
FETCH, FOR, FROM, FUNCTION
GOTO, GRANT, GROUP
HAVING
IDENTIFIED, IF, IN, INDEX, INDEXES, INSERT, INTERSECT, INTO, IS
LIKE, LOCK
MINUS, MODE
NOCOMPRESS, NOT, NOWAIT, NULL
OF, ON, OPTION, OR, ORDER, OVERLAPS
PROCEDURE, PUBLIC
RESOURCE, REVOKE
SELECT, SHARE, SIZE, SQL, START, SUBTYPE
TABAUTH, TABLE, THEN, TO, TYPE
UNION, UNIQUE, UPDATE
VALUES, VIEW, VIEWS
WHEN, WHERE, WITH

PL/SQL Keywords
A, ADD, ACCESSIBLE, AGENT, AGGREGATE, ARRAY, ATTRIBUTE, AUTHID, AVG
BFILE_BASE, BINARY, BLOB_BASE, BLOCK, BODY, BOTH, BOUND, BULK, BYTE
C, CALL, CALLING, CASCADE, CHAR, CHAR_BASE, CHARACTER, CHARSET, CHARSETFORM, CHARSETID, CLOB_BASE, CLONE, CLOSE, COLLECT, COMMENT, COMMIT, COMMITTED, COMPILED, CONSTANT, CONSTRUCTOR, CONTEXT, CONTINUE, CONVERT, COUNT, CREDENTIAL, CURRENT, CUSTOMDATUM
DANGLING, DATA, DATE, DATE_BASE, DAY, DEFINE, DELETE, DETERMINISTIC, DIRECTORY, DOUBLE, DURATION
ELEMENT, ELSIF, EMPTY, ESCAPE, EXCEPT, EXCEPTIONS, EXECUTE, EXISTS, EXIT, EXTERNAL
FINAL, FIRST, FIXED, FLOAT, FORALL, FORCE
GENERAL
HASH, HEAP, HIDDEN, HOUR
IMMEDIATE, INCLUDING, INDICATOR, INDICES, INFINITE, INSTANTIABLE, INT, INTERFACE, INTERVAL, INVALIDATE, ISOLATION
JAVA
LANGUAGE, LARGE, LEADING, LENGTH, LEVEL, LIBRARY, LIKE2, LIKE4, LIKEC, LIMIT, LIMITED, LOCAL, LONG, LOOP
MAP, MAX, MAXLEN, MEMBER, MERGE, MIN, MINUTE, MOD, MODIFY, MONTH, MULTISET
NAME, NAN, NATIONAL, NATIVE, NCHAR, NEW, NOCOPY, NUMBER_BASE
OBJECT, OCICOLL, OCIDATE, OCIDATETIME, OCIDURATION, OCIINTERVAL, OCILOBLOCATOR, OCINUMBER, OCIRAW, OCIREF, OCIREFCURSOR, OCIROWID, OCISTRING, OCITYPE, OLD, ONLY, OPAQUE, OPEN, OPERATOR, ORACLE, ORADATA, ORGANIZATION, ORLANY, ORLVARY, OTHERS, OUT, OVERRIDING
PACKAGE, PARALLEL_ENABLE, PARAMETER, PARAMETERS, PARENT, PARTITION, PASCAL, PIPE, PIPELINED, PLUGGABLE, PRAGMA, PRECISION, PRIOR, PRIVATE
RAISE, RANGE, RAW, READ, RECORD, REF, REFERENCE, RELIES_ON, REM, REMAINDER, RENAME, RESULT, RESULT_CACHE, RETURN, RETURNING, REVERSE, ROLLBACK, ROW
SAMPLE, SAVE, SAVEPOINT, SB1, SB2, SB4, SECOND, SEGMENT, SELF, SEPARATE, SEQUENCE, SERIALIZABLE, SET, SHORT, SIZE_T, SOME, SPARSE, SQLCODE, SQLDATA, SQLNAME, SQLSTATE, STANDARD, STATIC, STDDEV, STORED, STRING, STRUCT, STYLE, SUBMULTISET, SUBPARTITION, SUBSTITUTABLE, SUM, SYNONYM
TDO, THE, TIME, TIMESTAMP, TIMEZONE_ABBR, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TRAILING, TRANSACTION, TRANSACTIONAL, TRUSTED
UB1, UB2, UB4, UNDER, UNPLUG, UNSIGNED, UNTRUSTED, USE, USING
VALIST, VALUE, VARIABLE, VARIANCE, VARRAY, VARYING, VOID
WHILE, WORK, WRAPPED, WRITE
YEAR
ZONE

Триггер всемогущий

Именно так, и не иначе, могу назвать объект БД Oracle, который может автоматизировать любой процесс с данными. Когда в далёких 1990-х мне приходилось налаживать заводской документооборот, то частенько сотрудницы ОТиЗ, ФинО, да и всей прочей бухгалтерии высказывали свои пожелания к ПО словами: "Вот бы одну кнопочку нажать, а данные сами заполнились, рассчитались и распечатались!". Но, к сожалению, тогда в нашем распоряжении были только Clipper и FoxPro, поэтому клиентскую часть приходилось расписывать досконально и многократно дублировать, а потом ещё и перепроверять интеграцию модулей между отделами. Когда автоматизация распространилась и на цеха, то табельщицам всё равно приходилось информировать по телефону общезаводские отделы о завершении ввода первичных данных, чтобы верхи у себя запускали общий расчёт. Как же нам, программистам, не хватало тогда такого умного объекта базы данных, как триггер. Он мог в разы сократить время на обработку данных, в том числе и на проверку корректности ввода. Разработчики на Oracle, гордитесь тем, что у вас есть такой страж порядка, исполняющий свою миссию столь безукоснительно и незаметно. Точно также и тестировщик в группе разработки выполняет роль смотрителя за качеством.

Внушительность триггеров заключена в их возможности управлять системой. Кроме DML триггеров для таблиц и вьюверов, Oracle предоставляет администраторам БД триггеры типа system - на схему и всю базу.

Рис. 1 - Мастер Триггера для таблицы в SQLDEVELOPER

Рис. 2 - Мастер Триггера для вьювера в SQLDEVELOPER

Рис. 3 - Мастер Триггера для схемы в SQLDEVELOPER

Рис. 4 - Мастер Триггера для базы в SQLDEVELOPER

Рис. 5 - Мастер Триггера в SQLDetective

Больше всего в триггерах мне нравится их автоматичность срабатывания при наступлении выбранных предопределённых системой условий. А невозможность их запуска извне, поскольку это по сути обычная хранимая подпрограмма, максимально защищает данные от несанкционированного распространения. Только стандартное событие базы выполнит PL/SQL блок или хранимую подпрограмму (процедура, функция, в том числе и пакетные) на языке PL/SQL, С или Java. Получается, что система контролирует саму себя. Но при написании тела триггера стоит внимательно относится к исполняемым командам, которые не должны воздействовать на исходную транзакцию.
Поскольку триггер - это специальная исполняемая программа, то и параметры у него свои, специальные (correlation names) - :NEW, :OLD, :PARENT. Они автоматически связаны с данными, которые изменяются в процессе срабатывания триггера, поэтому удобны в использовании в рамках исполняемого PL/SQL блока, условий WHEN самого триггера или в качестве передаваемых параметров в вызываемую процедуру.
Пример из документации Oracle про самостоятельную процедуру и её вызов из тела триггера с учётом специфических параметров триггера:
CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)
IS LANGUAGE Java
name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';
CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab
FOR EACH ROW
CALL Before_delete (:OLD.Id, :OLD.Ename)
/
Программисты - люди увлекающиеся, и, в надежде автоматизировать всё и вся, могут написать слишком большой PL/SQL блок в теле одного триггера. Поэтому документация их сразу предупреждает - не превышать 32Kb или 60 строк, либо обходить лимит за счёт использования самостоятельных хранимых подпрограмм, чтобы не получать ошибку "PLS-00123: program too large (Diana nodes)" при компиляции триггера в базу или постоянные задержки на срабатывании этого триггера.
Ещё одним ограничением - по-умолчанию 32 штуки - является набор единовременно исполняемых триггеров, например, все они во включенном состоянии для одной таблицы. Но если сервер базы достаточно мощный, то ограничение меняется системным параметром OPEN_CURSORS. Триггеры могут заменить внешнее приложение, поскольку они сами себя упорядочивают, выполняясь автоматически по строгой схеме: предшествие выражения, предшествие строки, последствие строки, последствие выражения. При этом временная точка ВМЕСТО никогда не пересекается с ДО, ПОСЛЕ точками, так как они предназначены для разных типов объектов (ВМЕСТО - вьювер, ДО или ПОСЛЕ - таблицы, схемы или база). Исключением являются материализованные представления, у которых не случается запуска триггера при обновлении (refresh), но лог материализованного представления есть не что иное как AFTER ROW триггер этого вьювера, собирающий данные о командах  INSERT, UPDATE или DELETE вьювера в отдельную техническую таблицу.
Отличным помощником для тестировщика приложения на основе множества авто-подпрограмм могла бы быть визуализация этих двигателей. К сожалению, на страницах Triggers модуля DB Examiner в SQLDetective, весьма подешевевшем этой весной IDE, есть только два списка триггеров - на схемы и базу.

Рис. 6 - SQLDetective - DB Examiner - Triggers - DB

Рис. 7 - SQLDetective - DB Examiner - Triggers - Schemas

А ведь как было бы удобно иметь дерево зависимостей dml-триггеров и их calling/called объектов, или хотя бы фильтры системных триггеров по типу применения. Кстати, в SmartDataset (модуль в SQLDetective) можно настроить фильтры и сортировки, а затем самостоятельно или в рамках встроенного Repository использовать вьювер sys.all_triggers по различным условиям, например: OWNER LIKE '%MY_USER%' or TABLE_NAME IS NULL or TRIGGERING_EVENT LIKE '%DROP%'. Отследить же системные события в любом IDE на сегодняшний день возможно только вручную из системных представлений. 
По-моему, разработчики избегают такой объект, как триггер, потому что его воздействие сложно отследить. Ведь обладая системной привилегией CREATE ANY TRIGGER можно настроить обработку чужих данных через свои объекты, которые владелец никогда не найдёт. Триггер на объект одной схемы может находиться как в этой самой схеме, так и в другой. Аналогично триггер на одну схему может создаваться и храниться в соседней схеме. Для создания и редактирования триггеров для чужих схем администраторские права не нужны юзеру-исполнителю. Например, USER1 создаёт ограничение (dml trigger) на добавление записей в таблицу схемы USER2 и запрещает удалять объекты (schema trigger) из схемы USER3. Оба триггера хранятся в схеме USER1, но срабатывают только на события в определённых схемах и объектах, отличных от держателя объектов. Эти срабатывания сложно отыскать в деревьях объектов, которые показывают всевозможные приложения для разработчиков (IDE) и даже Call Tree диаграммы или CRUD матрицы могут оказаться бесполезными. Частично помогают диаграммы References&Dependencies, но только для dml-триггеров, поскольку триггеры на схемы и базу в такой диаграмме не учитываются. Здесь нужны специальные визуализаторы вызовов, заточенные на структуру именно триггеров, где раздельно упоминаются владельцы объектов хранения и воздействия. Пользуясь этим фактом можно вести мониторинг работы незаметно для рядовых пользователей, выполняющих логгируемые шаги или иные значимые действия.
Пример.
-- создаём таблицу в одной схеме у простого юзера USER1
CREATE TABLE USER1.US1_TB1 (US1TB1COL1 VARCHAR2(10), US1TB1COL2 NUMBER(5,0), US1TB1COL3 DATE)
/
-- подключаемся к базе каким-нибудь системным юзером SYS с привилегией CREATE ANY TRIGGER  и создаём триггер USER2.NO_MY_TR на таблицу первого юзера USER1.US1_TB1, но в другой схеме  такого же простого юзера USER2
connect as db_admin
/
CREATE OR REPLACE TRIGGER USER2.NO_MY_TR
  BEFORE  INSERT OR UPDATE  ON USER1.US1_TB1  FOR EACH ROW
BEGIN
:new.US1TB1COL1 := 'my value ' || to_char(:old.US1TB1COL2);
END;
/
-- теперь обычный юзер при вставке или изменении значений второй колонки не сможет понять почему в первой колонке сами появляются значения, несмотря на то, что таблица не имеет значений по-умолчанию и своих триггеров
connect as user1
/
INSERT INTO USER1.US1_TB1(US1TB1COL2)  VALUES(1)
/
INSERT INTO USER1.US1_TB1(US1TB1COL2)  VALUES(2)
/
INSERT INTO USER1.US1_TB1(US1TB1COL2)  VALUES(3)
/
UPDATE USER1.US1_TB1 SET US1TB1COL2 = :US1TB1COL2
/
Триггеры, созданные в любых схемах юзером с системными привилегиями CREATE ANY TRIGGER и  ADMINISTER DATABASE TRIGGER, срабатывают для обозначенных схем, но остаются невидимыми им. Однако, перед созданием триггеров в разных editions нужны права на сами editions (наследование, текущее и активное пространство), а для корректного срабатывания вызываемых процедур необходимо позаботиться о доступе юзера к самим исполняемым элементам (достаточно привилегии EXECUTE на спецификацию пакета). Отслеживая права, не забывайте о посредниках - системных ролях.
Универсальность триггера заключена ещё в частичной замене свойств (переключение активности, компиляция, переименование) через команду ALTER и глобальную замену через выражение OR REPLACE в команде создания, позволяющее кардинальные перемены без удаления объекта. Также, триггер универсален тем, что создаётся в базе даже с ошибками компиляции, не заставляя создателя отлаживать его на первом же этапе. Но сильно беспокоиться не стоит, поскольку срабатывать инвалидный объект не будет, не смотря на его включенность. И это на мой взгляд - хороший плюс.
Ещё полезная фича есть в базах с EDITION (Oracle после 10 версии), где триггеры по-умолчанию создаются в режиме EDITIONABLE и это позволяет создавать различное содержимое для одного и того же объекта. Версионность кода, то есть смена EDITIONs, переключается в одну команду (ALTER DATABASE DEFAULT EDITION = ORA$BASE) администратором базы, либо обычный юзер выбирает доступный текущий EDITION. К сожалению, не всякое IDE позволяет параллельную работу с кодом в разных EDITION: мастер триггера не имеет опции EDITIONABLE | NONEDITIONABLE, диалог коннекта к базе не позволяет перевыбрать current EDITION, например SQLDetective и ClearSQL. Порядок исполнения триггеров одного и того же объекта в одном и том же edition контролируется параметром FOLLOWS | PRECEDES. Полезным параметром для дерева объектов в IDE могла бы быть сортировка триггеров по параметру следования. Да, версионность кода усложняет работу с триггерами, но версионное наследование CROSSEDITION можно определять параметром FORWARD | REVERSE. В БД Oracle продумана даже такая запутанность, но "увидеть" эту логику можно только в системных вьюверах, IDE не поспевают за внутренними удобствами, хоть и пытаются рапортовать о "полной поддержке новой версии базы". Так что, пока единственный выход - это собственноручно настроенные запросы из вьюверов SYS.ALL_TRIGGERS, SYS.ALL_TRIGGER_COLS SYS.ALL_TRIGGER_ORDERING и им подобных.
БД Oracle, введя dml-команду MERGE, не забыла про триггеры и разрешила создавать составные триггеры, назвав их COMPOUND, позволяющие обработать до четырёх событий ДО-ПОСЛЕ одновременно и избежать переизбытка однотипных объектов, их насильного упорядочивания. Такие триггеры результативно предотвращают мутацию данных и способствуют эффективной передаче данных в другие таблицы. И те, и другие одновременно разрешают обработку DELETE, INSERT и UPDATE команд, что значительно упрощает разработку. А конкретизация и лимитирование списка полей для срабатывания UPDATE триггера уберегает базу от излишней загруженности. CRUD матрицы объединяют обращения к объектам в единый список и своеобразно визуализируют код, но только тот, что заключён в теле триггера. Поэтому структуры и зависимости самих триггеров и их связных объектов нуждаются в самостоятельной диаграмме, нечто среднее между Ref&Dep и Call Tree диаграммами на уровне всей базы (см. ClearDB Sample Docu).
Благодаря DML триггеру можно автоматически заполнять и отслеживать внешние ключи, последовательность уникальных значений, каскадность зависимых величин и связанных объектов, соблюдать сложные и комплексные ограничения, генерить новые значения и логгировать изменения данных. Поскольку триггер исполняется на серверной стороне, то большую половину приложения, которое обычно является полностью клиентской частью, можно вынести в тело триггеров. Например, вся наша Bug Tracking System состояла из десятка таблиц и по полдюжины триггеров к ним. Никакого дополнительного интерфейса уже не требовалось, автоматизация закрытия и передачи задач была максимальная.
Триггером схемы или базы можно отслеживать создание, удаление и изменение объектов: кто, что и когда сделал или с какой ошибкой базы столкнулся, когда и кто подключился к базе или база стартанула без предшествующей остановки. Это вам не голый список запросов из Top SQL (SQLDetective - DB Examiner), а полноценная отчётность о важных событиях БД. Контроль и мониторинг базы или отдельной схемы возможен при исполнении команд: AFTER CLONE (только для PLUGGABLE DATABASE, срабатывает и самостоятельно удаляется после копирования/клонирования базы), AFTER DB_ROLE_CHANGE (актуально только для триггера базы), AFTER LOGON, AFTER SERVERERROR (кроме ошибок ORA-01403: no data found, ORA-01422: exact fetch returns more than requested number of rows, ORA-01423: error encountered while checking for extra rows in exact fetch, ORA-01034: ORACLE not available, ORA-04030: out of process memory when trying to allocate string bytes), [AFTER | BEFORE] SET CONTAINER (при исполнении ALTER SESSION SET CONTAINER команды), AFTER STARTUP (актуально только для триггера базы), ALTER (кроме ALTER DATABASE), ANALYZE, ASSOCIATE STATISTICS, AUDIT, BEFORE LOGOFF, BEFORE SHUTDOWN (актуально только для триггера базы), BEFORE UNPLUG (только для PLUGGABLE DATABASE, срабатывает и самостоятельно удаляется после успешного исполнения), COMMENT, CREATE (кроме CREATE DATABASE или CREATE CONTROLFILE), DDL, DISASSOCIATE STATISTICS, DROP (кроме удаления самого триггера), GRANT, NOAUDIT, RENAME, REVOKE, SUSPEND, TRUNCATE. Исполненные события администратор базы может логгировать в особый список (таблица в этой же или соседней базе, сообщения по электронной почте или СМС), "опасные" события запрещать (не выполнить, показав пользовательское сообщение об ошибке), не заменяя их на иные. Некоторые IDE, например DB Monitor в SQLDetective, отслеживают некоторые события базы и оповещают о критичных изменениях, но для этого необходимо постоянно держать приложение и модуль в работающем состоянии, что само по себе нагружает сервер и клиентскую машину. А если написать системные триггеры и включать их только в периоды предположительных срабатываний, то такая настройка базы не только минимизирует нагрузку на сервер, но и конкретизирует столь легко варьируемое логгирование. Это вполне логично, так как для выполнения триггера база открывает автономную транзакцию, исполняет тело триггера и закрывает (сохраняет изменения) транзакцию, не пересекаясь с пользовательскими транзакциями. Поэтому триггер лучше джоба: вполне можно триггер всегда держать включенным (он не будет отнимать ресурсы без толку, только при определённом событии), а регулярно срабатывающий джоб только излишне нагрузит систему и мало-что важного успеет отследить.
IDE предназначены для упрощения работы программистам или даже в качестве обучающего инструмента элементам языка базы. Но мастер триггера в SQLDEVELOPER предлагает список событий вне зависимости от выбранного порядка исполнения, а редактировать скомпиллированный объект предлагает только через его DDL, без визуализации интерфейсом, как это доступно в SQLDetective через мастер объекта. Но и в SQLDetective мастер триггера становится опасным, если он в чужой схеме - подмена на текущего владельца проходит незаметно, особенно в схемных.

Pис. 8 - в схеме USER2 триггер запрещает удалять объекты из схемы USER1, но при малейшей корректировке через интерфейс триггер меняет владельца незаметно для редактирующего

Так что воздержитесь от правки объектов в интерфейсе, на сегодняшний день более надёжно править код в простом SQL редакторе, как это предлагает SQLDEVELOPER. К сожалению, полноценной поддержки триггеров нет ни в одном доступном IDE. К примеру, для отладки триггера Stored Program Editor в SQLDetective автоматически генерит DML выражение в анонимном PL/SQL блоке, но не учитывает ограничения списка полей UPDATE OF, а для схемных или триггеров на базу генерит лишь пустой Begin-End блок, хотя вполне мог показать примеры из Code Assistant. Подсказки подобного рода уничижают ученическую лицензию SQLDetective. Вероятно, это и есть причина того, что в разрабатываемых приложениях так мало полезных объектов. Хотя допускаю, что кому-то не нравятся некоторые ограничения: триггеры в схеме SYS посторонним создавать запрещено; имена триггеров не могут повторяться в одной схеме, но можно одноимённо называть таблицу и один из её триггеров; по-умолчанию триггер создаётся включенным, то есть может невовремя сработать; анонимный блок тела триггера не позволяет объявлять переменные типа LONG или LONG RAW; триггер не срабатывает для LOB колонки, изменяемой через OCI функции или DBMS_LOB пакет, но, надеюсь, Oracle со временем эту проблему разрешит. А пока, у продавцов IDE есть шанс предложить уникальную утилиту, где бы можно было отследить триггеры на схемы и базу. Например, распутать клубок: USER1 не может создавать и удалять собственные объекты при достаточном наличии системных привилегий:
-- подключаемся к базе системным юзером и создаём трёх пользователей с минимальным, но достаточным набором прав
connect db_admin
/
CREATE USER USER1
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER1
/
GRANT RESOURCE TO USER1
/
GRANT CREATE PROCEDURE TO USER1
/
GRANT UNLIMITED TABLESPACE TO USER1
/
CREATE USER USER2
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER2/
GRANT RESOURCE TO USER2
/
GRANT CREATE PROCEDURE TO USER2
/
GRANT UNLIMITED TABLESPACE TO USER2
/
CREATE USER USER3
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER3
/
GRANT RESOURCE TO USER3
/
GRANT CREATE PROCEDURE TO USER3
/
GRANT UNLIMITED TABLESPACE TO USER3
/
-- подключаемся к базе первым пользователем и проверяем возможность создания и удаления объектов
connect user1
/CREATE OR REPLACE PROCEDURE USER1.NEW_PROCEDURE  AS
BEGIN    NULL;    END;
/
drop procedure NEW_PROCEDURE 
/
-- возвращаемся к коннекту админом и создаём схемные триггеры во второй и третьей схемах
connect db_admin
/
CREATE OR REPLACE TRIGGER USER2.US2DRUS1
   BEFORE   DROP   ON USER1.SCHEMA
   BEGIN
     RAISE_APPLICATION_ERROR (num => -20000,  msg => 'User2: Do not drop an object!');
   END;
/
CREATE OR REPLACE TRIGGER USER3.US3CRUS1
   BEFORE   CREATE   ON USER1.SCHEMA
   BEGIN
      RAISE_APPLICATION_ERROR (num => -20000,  msg => 'User3 does not allow to create an object');
   END;
/-- возвращаемся к обычному юзеру и вновь выполняем создание и удаление объекта
connect user1
/
CREATE OR REPLACE PROCEDURE USER1.NEW_PROCEDURE  AS
BEGIN    NULL;    END;
/
drop procedure NEW_PROCEDURE 
/
Результат отрицательный:

Рис. 9 - Исполнение схемных триггеров чужого владельца

Рис. 10 - Исполнение схемных триггеров чужого владельца

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

Рис. 11 - Диаграмма связей схемных триггеров

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

Рис. 12 - Значение колонки по-умолчанию не может быть составным

Рис. 13 - Автоматическое заполнение колонок посредством триггеров

Да, часть операций могут произвести констрейнты и специализированные колонки (unique ID, default column value), но им не под силу отследить сложные или композитные условия (specific logon requirements, firewall, work hours, custom rules). В нашей самописной BTS именно триггер автоматически отправлял письма-нотификации высшему руководству об успешной публикации очередного билда: публикатор проставлял в одной из таблиц BTS признак завершения работ, когда чек-лист публикации весь был с датами  окончания и номер опубликованного билда с опцией "Выложен", и UPDATE OF триггер рассылал e-mail сообщения на предопределённые адреса с полной и точной информацией: наименование продукта, номер версии, дата-время публикации, имя ответственного.
-- триггер OUR_DB.VERSION_BU_TRG контролировал публикацию билда по чек-листу
CREATE OR REPLACE TRIGGER OUR_DB.VERSION_BU_TRG
  BEFORE
  UPDATE
  ON OUR_DB.VERSION
  FOR EACH ROW
  WHEN ( NEW.PUBLISHED = 'Y' )
DECLARE
  I INTEGER;
BEGIN
  SELECT COUNT(*)
    INTO I
    FROM RELEASE_CHECK_LIST
    WHERE VER_ID = :NEW.ID AND
          CHECK_DATE IS NULL;
  IF ( I > 0 ) THEN
    RAISE_APPLICATION_ERROR('-20001', 'There are still '||TO_CHAR(I)||' not checked items for this version in the Release Check List. Publishing is forbidden!');
  END IF;
END;
/-- триггер OUR_DB.VER_PUBLISHED_TRG рассылал нотификации стейкхолдерам об окончании публикации билда
CREATE OR REPLACE TRIGGER OUR_DB.VER_PUBLISHED_TRG
  AFTER
  INSERT OR UPDATE OF PUBLISHED
  ON OUR_DB.VERSION
  FOR EACH ROW
DECLARE
 prod_name VARCHAR2(30);
 mail_body VARCHAR2(300);
BEGIN
  If :new.published = 'Y' then
     Select pr.Name into prod_name from our_db.products pr Where pr.id = :new.prd_id;
     mail_body := prod_name||' '||:new.version||'.'||TO_CHAR(:new.release)||'.'||TO_CHAR(:new.Build)||' '
               ||' has been published on '||TO_CHAR(:new.BUILD_DATE, 'yyyy.mm.dd hh24:mi:ss');
-- пример пакетной процедуры mailer.SEND_SMTP_MAIL можно найти на форумах Oracle
     our_db.mailer.SEND_SMTP_MAIL(
          'ProductPublisher@OurCompany.org'
        , 'Tester-Publisher'
        , 'ProductOwner@OurCompany.org'
        , 'Product Owner'
        , 'Published Build - '||prod_name
        , mail_body);
  End If;
END;
/Такая автоматизация значительно сокращала моё рабочее время и отлично исключала "человеческий фактор": усталость, опечатки, забывчивость. Иной пример - от администратора базы (вернее множества баз на множестве серверов в различных местах): случайные и непредвиденные падения базы или сервера отслеживаются маленьким триггером на каждую инсталляцию, сигнализирующим админу о включении (startup) той или иной базы без предварительного стандартного гашения (stop), поскольку ей необходим дополнительный аудит.
Триггер - это вам не прыщик-выскочка, он удалец!
Сам по строкам бродит, рыщет, проверяет, генерит.
Он в плаще, он невидимка, словно сыщик за углом.
У него заданий тыщщи, а успевает всё кругом.
Он пинка даст всей системе и напомнит каждой соне,
Что и где случилось - вскоре разведёт проблему в корне.
Вы его лишь снарядите целью и шагов порядком,
А в бою он вас заменит целиком и без оглядки.
Вот сколько плюсов у этого маленького объекта базы данных Oracle, которые полноценно могут заменить целое внешнее приложение. А вашим программистам нравится писать триггеры и использовать их минимализм и приближённость к данным вместо "лапши" многократных проверок в рамках клиентского приложения? Вы всё-ещё вручную проверяете ввод первичных данных или уже доверились автомату, который всё делает сам и за вас - программисты, и за вас - тестировщики, и за вас - операторы данных?

Access control 2

Продолжим постигать волшебство грантования.
Прежде чем создавать пользователей и выдавать им поочерёдно гранты из предыдущего примера надо иметь пользователя с админскими правами. Сразу после создания базы вход осуществлялся юзером "SYS", под правами которого глобально одним махом в ConquestSS создавался пользователь с постфиксом "_DBA" и полнейшим набором системных привилегий и ролей. Мастер юзера в SQLDetective имеет кнопки в тулбаре весьма опасных экшенов: "Grant all roles to user", "Grant all system privileges to user" и аналогичный функционал для параметра "WITH ADMIN OPTION". Получался эдакий дубликат системного пользователя "SYS". А затем уже этот "WSL_DBA" или "TESTER_DBA" с менее сложным паролем, чем у  "SYS", создавал иных простых пользователей и системные объекты, поскольку все роли и системные привилегии выданы были с опцией "Admin", то есть с возможностью раздавать другим. Таким глупым шагом упрощалась работа тестировщика в последствии: продукты проверялись на двух видах пользователей - с админскими привилегиями и без них. То есть тестились только крайние значения из списка грантов: либо всё, либо ничего. Как следствие, от конечных пользователей продуктов приходили баги об ограниченности доступа к функционалу продукта, и техподдержка выявляла эти узкие необработанные места в каждом конкретном случае. К сожалению, руководитель проекта никак не способствовал автоматизации тестирования, аргументируя дороговизной приложений для тестировщиков, вынужденных ежемесячно терять время на переустановку триала TestComplete вместо написания новых полезных авто-тестов. В продуктах от ConquestSS, работающих с базой Oracle, очень много разнообразных вариантов и мест доступа к базе, поэтому невозможно получить точный список комбинаций из выданных ролей и выполняемых запросов от имени простого юзера и с DBA правами. Да, если бы эти вариации можно было скомпоновать в матрицу, то отпала бы необходимость в единственном TestComplete из линейки приложений для тестировщиков, распознающего Delphi интерфейс. И поскольку матрицу команд могли составить только разработчики выборкой из кода, то данный объём работ также отрицался PM-ом (Project Manager), как  избыточная нагрузка на программистов. Хотя правильнее всего было выбрать из кода все запросы при помощи аналогичных утилит, создаваемых командой ConquestSS, и проверить их выполнимость пользователями базы со всевозможным набором грантов.
Итак, некий админский юзер, в нашем случае это "ADMIN_GRANTS" с некоторыми системными привилегиями и ролями, но опцией "Admin", создаёт пользователей (USER1, USER2, USER3) с минимальным набором системных привилегий (UNLIMITED TABLESPACE, CREATE TABLE, CREATE PROCEDURE, CREATE SYNONYM, CREATE PUBLIC SYNONYM) и обязательными ролями (CONNECT, RESOURCE) для подключения к базе. Следует напомнить, что в ранних версиях Oracle не было особой необходимости выдавать некоторые системные привилегии, так как они входили в стандартные роли для подключения.
Объектные привилегии раздаются самими простыми пользователями после создания этих объектов. Напомню, что синонимы можно создать на не существующие пока ещё объекты, но объектные привилегии (разные для типов объектов: например, "Execute" для хранимых программ и "Select" для набора данных) можно выдать на синоним только после создания самого "родительского" объекта (таблица или пакет в нашем случае).
После того, как мы выдали объектные привилегии, начинаем проверять их достаточность: через продуктовый интерфейс (например, Oracle Forms) или в IDE (Integrated Development Environment) разработчика (например, Oacle SQL Developer) выполняем команды по считыванию и редактированию данных, в том числе и через выполнение хранимых подпрограмм, подконнектившись к базе разными пользователями (USER1, USER2, USER3). Когда для выполнения команды не хватает прав, то возможны ошибки: "ORA-00942: table or view does not exist", "PLS-00904: insufficient privilege to access object", "ORA-00904: : invalid identifier", "PLS-00201: identifier must be declared" и иные. Если по первым двум сообщениям понятно, что к объекту недостаточен доступ и настройка ролей может поправить в корне ситуацию, то по описанию вторых двух совершенно не понятно, что причина таже самая. Поэтому для исследования критичных моментов быстрая помощь может быть оказана диаграммами "Call Tree", "ER" (Entity Relationships), "Ref&Dep" (References, Dependencies), "Grants Access" с возможностью поиска объектов по имени (обычно поиск реализуется в SVG формате, чего к сожалению не имеет ClearSQL).
Как всегда, особого внимания заслуживают синонимы. Объектные привилегии, выданные на синоним, автоматически распространяются на сам объект, то есть после применения грантов можно обращаться как к синониму по упрощённому имени, так и к "родительскому" объекту.
Если гранты выданы так называемому юзеру "Public", то доступ к объекту возможен для всех пользователей базы. Создать Public-синоним (или обычный синоним) не достаточно для доступа к объекту, поэтому продумывайте минимальный объём объектных привилегий и выдавайте их на синоним сторонним юзерам сразу после создания синонима. В нашем примере после создания объектов "SYN_P_US3_TB1" и "SYN_P_US3PKG1" их могут увидеть в списке доступных синонимов пользователи USER1, USER2, но выбрать данные или выполнить пакетную функцию может только владелец "родительских" объектов, то есть USER3. Но, как только объектные привилегии выданы на синонимы юзеру PUBLIC, а значит и на исходные объекты, юзера всей базы начнут их использовать. Хотя есть вариант выдать объектные привилегии на public-синоним только отдельным пользователям, например, один будет только править (UPDATE) таблицу, а другой только выбирать (SELECT) из неё данные. Такое распределение обычно делают для справочников.
Когда синоним создан через "DBLink", то доступ к объекту можно получить и из другой базы, указанной в DBLink, но только тем пользователем (один или все) другой базы, которому обозначен доступ в настройках DBLink. В диаграмме "Grants Access" тоже полезно иметь узлы из числа объектов "DBLink".
Дабы упростить выдачу системных и объектных привилегий, их можно объединить в роль пользовательского плана. Создать, пополнить и грантовать такую роль может только юзер с системными привилегиями "CREATE ROLE" и "GRANT ANY ROLE". В нашем примере это пользователь "ADMIN_GRANTS" и созданные им роли "ROLE_TBL", "ROLE_ROLE_TBL", "ROLE_PRC_TBL". Поскольку в Oracle можно выдать роль на роль, то диаграмма "Grants Access" через уровень вложенности поможет выявить цикличность выданных привилегий и расшифрует избыток прав. У нас объектные привилегии на "USER1.US1_TB1" содержатся в роли "ROLE_TBL", а эта роль в свою очередь является частью роли "ROLE_ROLE_TBL". Поэтому, выдавая роль "ROLE_ROLE_TBL" юзеру "USER3", мы автоматически даём ему доступ к таблице "USER1.US1_TB1". Отслеживайте по ролям излишнюю выдачу грантов на свои же объекты.

Access control

На днях возникла проблема отслеживания прав на объекты. Привилегии в базе Oracle делятся на системные, объектные, ролевые. Отдельно заслуживают внимания так называемый пользователь Public и синонимы.
Для примера создадим трёх пользователей с таблицами и пакетами. Выдадим объектные привилегии юзерам на таблицу, колонку в таблице, на спецификации пакетов. Какие-то права определим конкретно (только просмотр или только редактирование). В число примеров включим грантование юзеру Public и на синонимы без прав на сами объекты.
-- создаём юзеров с системными привилегиями
CREATE USER USER1
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER1
/
GRANT RESOURCE TO USER1
/
GRANT CREATE PROCEDURE TO USER1
/
GRANT CREATE SYNONYM TO USER1
/
GRANT CREATE TABLE TO USER1
/
GRANT UNLIMITED TABLESPACE TO USER1
/
CREATE USER USER2
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER2
/
GRANT RESOURCE TO USER2
/
GRANT CREATE PROCEDURE TO USER2
/
GRANT CREATE SYNONYM TO USER2
/
GRANT CREATE TABLE TO USER2
/
GRANT UNLIMITED TABLESPACE TO USER2
/
CREATE USER USER3
  IDENTIFIED BY **** --Password hidden
/
GRANT CONNECT TO USER3
/
GRANT RESOURCE TO USER3
/
GRANT CREATE PROCEDURE TO USER3
/
GRANT CREATE PUBLIC SYNONYM TO USER3
/
GRANT CREATE SYNONYM TO USER3
/
GRANT CREATE TABLE TO USER3
/
GRANT UNLIMITED TABLESPACE TO USER3
/
--создаём роли и выдаём юзерам
CREATE ROLE ROLE_PRC_TBL
/
GRANT ROLE_PRC_TBL TO USER1
/
CREATE ROLE ROLE_ROLE_TBL
/
GRANT ROLE_ROLE_TBL TO USER3
/
CREATE ROLE ROLE_TBL
/
GRANT ROLE_TBL TO ROLE_ROLE_TBL
/
GRANT ROLE_TBL TO USER2
/
--создаём объекты в схемах, синонимы на некоторые и выдаём объектные привилегии
connect user1
/
CREATE TABLE USER1.US1_TB1
  (
    US1TB1COL1                        VARCHAR2(10 BYTE)     NULL,
    US1TB1COL2                        NUMBER(5,0)           NULL,
    US1TB1COL3                        DATE                  NULL
  )
/
GRANT DELETE, INSERT, SELECT, UPDATE ON USER1.US1_TB1 TO ROLE_TBL
/
CREATE OR REPLACE PACKAGE USER1.us1pkg1
AS
  PROCEDURE us1pkg1prc1;
  FUNCTION us1pkg1fun1 RETURN VARCHAR2;
END;
/
GRANT DEBUG, EXECUTE ON USER1.US1PKG1 TO USER3
/
CREATE OR REPLACE PACKAGE BODY USER1.us1pkg1
AS
  PROCEDURE us1pkg1prc1 IS
  BEGIN
    dbms_output.put_line('us1pkg1prc1');  
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  
  END;
  FUNCTION us1pkg1fun1 RETURN VARCHAR2 IS
  BEGIN
    return 'yes us1pkg1fun1';  
  END;
END;
/
CREATE OR REPLACE SYNONYM USER1.SYN_US1PKG1 FOR USER1.US1PKG1
/
CREATE TABLE USER2.US2_TB1
  (
    US2TB1COL1                        VARCHAR2(10 BYTE)     NULL,
    US2TB1COL2                        NUMBER(5,0)           NULL,
    US2TB1COL3                        DATE                  NULL
  )
/
GRANT SELECT ON USER2.US2_TB1 TO ROLE_PRC_TBL
/
CREATE TABLE USER2.US2_TB2
  (
    US2TB2COL1                        VARCHAR2(10 BYTE)     NULL,
    US2TB2COL2                        NUMBER(5,0)           NULL,
    US2TB2COL3                        DATE                  NULL
  )
/
CREATE OR REPLACE SYNONYM USER2.SYN_US2_TB2 FOR USER2.US2_TB2
/
CREATE OR REPLACE PACKAGE USER2.us2pkg1
AS
  PROCEDURE us2pkg1prc1 (prm1 in number);
  FUNCTION us2pkg1fun1 (prm2 in number) RETURN VARCHAR2;
END;
/
GRANT EXECUTE ON USER2.US2PKG1 TO ROLE_PRC_TBL
/
CREATE OR REPLACE PACKAGE BODY USER2.us2pkg1
AS
  PROCEDURE us2pkg1prc1 (prm1 in number) IS
  BEGIN
    dbms_output.put_line('us2pkg1prc1 '||to_char(prm1));  
  EXCEPTION
    WHEN OTHERS THEN
      NULL;  
  END;
  FUNCTION us2pkg1fun1 (prm2 in number) RETURN VARCHAR2 IS
  BEGIN
    return 'us2pkg1fun1'||to_char(prm2);     
  END;
END;
/
CREATE OR REPLACE PACKAGE USER2.us2pkg2
AS
  PROCEDURE us2pkg2prc1 (prm3 in number, prm4 in date);
  FUNCTION us2pkg2fun1 (prm5 in number, prm6 in number) RETURN VARCHAR2;
END;
/
GRANT DEBUG ON USER2.US2PKG2 TO USER1
/
GRANT DEBUG ON USER2.US2PKG2 TO USER3
/
CREATE OR REPLACE PACKAGE BODY USER2.us2pkg2
AS
  PROCEDURE us2pkg2prc1 (prm3 in number, prm4 in date) IS
  BEGIN
    dbms_output.put_line('us2pkg2prc1 '||to_char(prm3));     
  END;
  FUNCTION us2pkg2fun1 (prm5 in number, prm6 in number) RETURN VARCHAR2 IS
  BEGIN
   return 'us2pkg2fun1'||to_char(prm5); 
  END;
END;
/
CREATE TABLE USER3.US3_TB1
  (
    US3TB1COL1                        VARCHAR2(10 BYTE)     NULL,
    US3TB1COL2                        NUMBER(5,0)           NULL,
    US3TB1COL3                        DATE                  NULL
  )
/
CREATE TABLE USER3.US3_TB2
  (
    US3TB2COL1                        VARCHAR2(10 BYTE)     NULL,
    US3TB2COL2                        NUMBER(5,0)           NULL,
    US3TB2COL3                        DATE                  NULL
  )
/
GRANT SELECT ON USER3.US3_TB2 TO USER1
/
CREATE TABLE USER3.US3_TB3
  (
    US3TB3COL1                        VARCHAR2(10 BYTE)     NULL,
    US3TB3COL2                        NUMBER(5,0)           NULL,
    US3TB3COL3                        DATE                  NULL
  )
/
CREATE OR REPLACE PACKAGE USER3.us3pkg1
AS
  PROCEDURE us3pkg1prc1;
  FUNCTION us3pkg1fun1 RETURN VARCHAR2;
END;
/
CREATE OR REPLACE PACKAGE BODY USER3.us3pkg1
AS
  PROCEDURE us3pkg1prc1 IS
  BEGIN
    dbms_output.put_line('us3pkg1prc1');     
  END;
  FUNCTION us3pkg1fun1 RETURN VARCHAR2 IS
  BEGIN
    return 'us3pkg1fun1';     
  END;
END;
/
CREATE OR REPLACE PACKAGE USER3.us3pkg2
AS
  PROCEDURE us3pkg2prc1 (prm7 in number);
  FUNCTION us3pkg2fun1 (prm8 in date) RETURN VARCHAR2;
END;
/
GRANT EXECUTE ON USER3.US3PKG2 TO USER1
/
CREATE OR REPLACE PACKAGE BODY USER3.us3pkg2
AS
  PROCEDURE us3pkg2prc1 (prm7 in number) IS
  BEGIN
    dbms_output.put_line('us3pkg2prc1 '||to_char(prm7));  
  END;
  FUNCTION us3pkg2fun1 (prm8 in date) RETURN VARCHAR2 IS
  BEGIN
    return 'yes us3pkg2fun1';  
  END;
END;
/
CREATE OR REPLACE PACKAGE USER3.us3pkg3
AS
  PROCEDURE us3pkg3prc1;
  FUNCTION us3pkg3fun1 RETURN VARCHAR2;
  PROCEDURE us3pkg3prc2;
  FUNCTION us3pkg3fun2 RETURN VARCHAR2;
END;
/
GRANT DEBUG, EXECUTE ON USER3.US3PKG3 TO USER1
/
GRANT DEBUG, EXECUTE ON USER3.US3PKG3 TO USER2
/
CREATE OR REPLACE PACKAGE BODY USER3.us3pkg3
AS
  PROCEDURE us3pkg3prc1 IS
  BEGIN
    dbms_output.put_line('us3pkg3prc1');     
  END;
  FUNCTION us3pkg3fun1 RETURN VARCHAR2 IS
  BEGIN
    return 'us3pkg3fun1';  
  END;
    PROCEDURE us3pkg3prc2 IS
  BEGIN
    dbms_output.put_line('us3pkg3prc2');     
  END;
  FUNCTION us3pkg3fun2 RETURN VARCHAR2 IS
  BEGIN
    dbms_output.put_line('yes us3pkg3fun2');  
  EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line('no us3pkg3fun2');  
  END;
END;
/
--создаём public синонимы и позже выдаём права
CREATE OR REPLACE PUBLIC SYNONYM SYN_P_US3_TB1 FOR USER3.US3_TB1
/
CREATE OR REPLACE PUBLIC SYNONYM SYN_P_US3PKG1 FOR USER3.US3PKG1
/
GRANT DELETE, INSERT, SELECT, UPDATE ON SYN_P_US3_TB1 TO PUBLIC
/
GRANT DEBUG, EXECUTE ON SYN_P_US3PKG1 TO PUBLIC
/

К сожалению, моя попытка отследить все зависимости с помощью ClearDB (для доки были взяты все объекты трёх рабочих схем, пользовательские роли и public синонимы, всем доступным диаграммам выставлены уровни DB и третий порядок вложенности, включены все свойства объектов) ограничилась лишь R&D диаграммой, построенной по вьюверам "SYS.ALL(DBA)_REFS" "SYS.ALL(DBA)_DEPENDENCIES".

ER диаграмма и CRUD2 матрица оказались пусты, Call Tree диаграммы - бесполезны. Пришлось рисовать вручную.

 По диаграмме, построенной на данных системных вьюверов ("SYS.ALL(DBA)_TAB_PRIVS", "SYS.TABLE_PRIVILEGES", "SYS.COLUMN_PRIVILEGES", "SYS.DBA_PUBLISHED_COLUMNS", "SYS.DBA_ROLE_PRIVS", "SYS.PUBLICSYN", "SYS.ROLE_ROLE_PRIVS" и других) легче понять, какой юзер сможет воспользоваться данными и в каком объёме. По ней очевидными стали результаты нижеследующих скриптов. 
-----------сессия первого юзера
connect user1
/
BEGIN   USER3.US3PKG3.US3PKG3PRC2; END;
select USER3.US3PKG3.US3PKG3FUN1() from dual;
BEGIN   USER3.US3PKG2.US3PKG2PRC1(:PRM7 /* IN - NUMBER */ ); END;
select USER3.US3PKG2.US3PKG2FUN1(sysdate) from dual;
--ORA-06550: line 2, column 3:
--PLS-00201: identifier 'SYN_P_US3PKG1' must be declared
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3PKG1 TO public
BEGIN   syn_p_us3pkg1.US3PKG1PRC1; END;
--ORA-00904: : invalid identifier
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3PKG1 TO public
select syn_p_us3pkg1.US3PKG1FUN1 from dual;
--редактировать можно только одну колонку, но в CodeAssistant (SQL Editor или Stored Program Editor в SQLDetective) можно просмотреть структуру всей таблицы
insert into USER3.US3_TB3(US3TB3COL1) values (:US3TB3COL1 /* IN - NUMBER */ );
UPDATE USER3.US3_TB3 SET    US3TB3COL1 = :US3TB3COL1  /* IN - NUMBER */ ;
SELECT *  FROM USER3.US3_TB2 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM USER3.US3_TB1 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM SYN_P_US3_TB1;
--PLS-00904: insufficient privilege to access object USER2.US2PKG2
--available DDL viewing
BEGIN   USER2.US2PKG2.US2PKG2PRC1(:PRM3 /* IN - NUMBER */ , :PRM4 /* IN - DATE */ );  END;
--ORA-01031: insufficient privileges
--available DDL viewing
SELECT USER2.US2PKG2.US2PKG2FUN1(:prm5 /* in number */, :prm6 /* in number */ ) FROM dual;
BEGIN   USER2.US2PKG1.US2PKG1PRC1(:PRM1 /* IN - NUMBER */ );  END;
select USER2.US2PKG1.US2PKG1FUN1(:prm2 /* in number */) from dual;
SELECT * FROM USER2.US2_TB1 ;
-------------------сессия второго юзера
connect user2
/
BEGIN   USER3.US3PKG3.US3PKG3PRC2; END;
select USER3.US3PKG3.US3PKG3FUN1() from dual;
--PLS-00201: identifier 'USER3.US3PKG2' must be declared
BEGIN   USER3.US3PKG2.US3PKG2PRC1(:PRM7 /* IN - NUMBER */ );  END;
--ORA-00904: : invalid identifier
select USER3.US3PKG2.US3PKG2FUN1(sysdate) from dual;
--ORA-06550: line 2, column 3:
--PLS-00201: identifier 'SYN_P_US3PKG1' must be declared
----BUT OK after GRANT ALL PRIVILEGES ON SYN_p_US3pkg1 TO public
BEGIN   syn_p_us3pkg1.US3PKG1PRC1; END;
--ORA-00904: : invalid identifier
----BUT OK after GRANT ALL PRIVILEGES ON SYN_p_US3pkg1 TO public
select syn_p_us3pkg1.US3PKG1FUN1 from dual;
--ORA-00942: table or view does not exist
insert into USER3.US3_TB3(US3TB3COL1) values (:US3TB3COL1 /* IN - NUMBER */ );
--ORA-00942: table or view does not exist
UPDATE USER3.US3_TB3 SET    US3TB3COL1 = :US3TB3COL1  /* IN - NUMBER */ ;
--ORA-00942: table or view does not exist
SELECT *  FROM USER3.US3_TB2 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM USER3.US3_TB1 ;
--ORA-00942: table or view does not exist
----BUT OK after GRANT ALL PRIVILEGES ON SYN_P_US3_TB1 TO public
SELECT * FROM SYN_P_US3_TB1;
--PLS-00201: identifier 'USER1.US1PKG1' must be declared
BEGIN   USER1.US1PKG1.US1PKG1PRC1;  END;
--ORA-00904: : invalid identifier
select USER1.US1PKG1.US1PKG1FUN1  from dual;
SELECT A.*    FROM USER1.US1_TB1 A;
INSERT INTO USER1.US1_TB1(US1TB1COL1, US1TB1COL2, US1TB1COL3)   VALUES (:US1TB1COL1, :US1TB1COL2, :US1TB1COL3);
UPDATE USER1.US1_TB1 SET    US1TB1COL1 = :US1TB1COL1,   US1TB1COL2 = :US1TB1COL2,   US1TB1COL3 = :US1TB1COL3;
DELETE FROM USER1.US1_TB1;
--------------------------сессия третьего юзера
connect user 3
/
--PLS-00904: insufficient privilege to access object USER2.US2PKG2
--available DDL viewing
BEGIN   USER2.US2PKG2.US2PKG2PRC1(:PRM3 /* IN - NUMBER */ , :PRM4 /* IN - DATE */ );  END;
--ORA-01031: insufficient privileges
--available DDL viewing
SELECT USER2.US2PKG2.US2PKG2FUN1(:prm5 /* in number */, :prm6 /* in number */ ) FROM dual;
--PLS-00201: identifier 'USER2.US2PKG1' must be declared
--no DDL viewing
BEGIN   USER2.US2PKG1.US2PKG1PRC1(:PRM1 /* IN - NUMBER */ );  END;
--ORA-00904: : invalid identifier
--no DDL viewing
select USER2.US2PKG1.US2PKG1FUN1(:prm2 /* in number */) from dual;
--ORA-00942: table or view does not exist
SELECT * FROM USER2.US2_TB1 ;
BEGIN   USER1.US1PKG1.US1PKG1PRC1;  END;
select USER1.US1PKG1.US1PKG1FUN1  from dual;
INSERT INTO USER1.US1_TB1(US1TB1COL1, US1TB1COL2, US1TB1COL3)   VALUES (:US1TB1COL1, :US1TB1COL2, :US1TB1COL3);
UPDATE USER1.US1_TB1 SET    US1TB1COL1 = :US1TB1COL1,   US1TB1COL2 = :US1TB1COL2,   US1TB1COL3 = :US1TB1COL3;
DELETE FROM USER1.US1_TB1;
----зелёным шрифтом помечены удачные попытки выполнения, а красным - проблемные доступы к объектам

А у вас есть визуализатор привилегий?