Ora 04088 ошибка во время выполнения триггера

While I was testing something on a 12.1 test database got this below error whenever I’m trying to execute specific admin commands:

SQL> drop user xx;
drop user xx
*
ERROR at line 1:
ORA-04088: error during execution of trigger ‘SYS.XDB_PI_TRIG’
ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 3, column 13:
PLS-00302: component ‘IS_VPD_ENABLED’ must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored

The above was just a sample but the error with showing up with lots of admin commands!

I checked the trigger SYS.XDB_PI_TRIG which causing this error and it was already valid, so I decided to DISABLE it, and then admin commands ran as usual:

Above failing admin commands have run smoothly:

Hello, we used exceltable package a few week ago and everything was fine. During this period, our oracle admins applied some patch or etc. and now we get an oracle error ORA-04088 which is connected with trigger checking grants to PUBLIC. I don’t know why something should grant privileges to PUBLIC, but it seems that error emerges on this row — «open l_rc for l_query using p_file, p_method, p_password;» in first getCursor function in EXCELTABLE package. We use ORACLE DB 12.2. Do you have any idea where is granting privileges used and why emerges this error? Thank you in advance.

1. This is part of our SQL code:
   declare
   piv_excel_name ext_ds_upt_kalendar_pro_kl.nazev_souboru%TYPE := ‘Kalendar.xlsx’;
   piv_nazev_listu ext_ds_upt_kalendar_pro_kl.nazev_listu%TYPE := ‘Plan’;
   cv_db_adresar CONSTANT VARCHAR2(16) := ‘DB_DIR’;
   lvr_data SYS_REFCURSOR;

begin
lvr_data :=
ExcelTable.getCursor(
p_file => ExcelTable.getFile(cv_db_adresar, piv_excel_name)
, p_sheet => piv_nazev_listu
, p_cols => ‘»A2″ VARCHAR2(100) column »A»’
, p_range => ‘A2:A2’
);
end;

1. This is whole error message:
   Error report —
   ORA-04088: error during execution of trigger ‘APPLDBA_P.BEFORE_GRANT_PUBLIC’
   ORA-00604: error occurred at recursive SQL level 3
   ORA-20997: Public grants on data schema objects not allowed
   ORA-06512: on line 23
   ORA-06512: on «EXT_STAGE.EXCELTABLE», line 4087
   ORA-06512: on line 11

1. 00000 — «error during execution of trigger ‘%s.%s'»
   *Cause: A runtime error occurred during execution of a trigger.
   *Action: Check the triggers which were involved in the operation.

1. This is the trigger mentioned above:
   create or replace TRIGGER appldba_p.before_grant_public BEFORE GRANT ON DATABASE
   declare
   vLst ora_name_list_t;
   vCnt int;

function is_authorized(p_owner varchar2,p_grantor varchar2) return varchar2 is
vRet varchar2(1);
begin
select decode(max(profile),’DAT_USER_PROFILE’,’F’,’T’) into vRet from dba_users where username=p_owner;
if vRet=’F’ then
select decode(count(*),0,’F’,’T’) into vRet from dba_role_privs where granted_role=’DBA’ and grantee=p_grantor;
end if;
return vRet;
end;

begin
if ora_dict_obj_name is null then
return;
end if;

vCnt:=ora_grantee(vLst);
for i in 1..nvl(vCnt,0) loop
if vLst(i)=’PUBLIC’ and is_authorized(ora_dict_obj_owner,ora_login_user)=’F’ then
raise_application_error(-20997,’Public grants on data schema objects not allowed’);
end if;
end loop;
end;

СУБД Oracle внутри себя достаточно давно поддерживает автономные транзак­ции. Мы видим их в форме рекурсивного SQL. Например, рекурсивная транзак­ция может выполняться при выборе значения из последовательности, чтобы не­медленно увеличить значение счетчика последовательности в таблице SYS.SEQ$. Обновление таблицы SYS.SEQ$ для поддержки вашей последовательности немед­ленно фиксируется и становится видимым другим транзакциям, хотя ваша тран­закция еще и не зафиксирована. Вдобавок, если вы откатите свою транзакцию, то увеличение значения счетчика последовательности останется в силе; оно не будет откачено вместе с вашей транзакцией, поскольку уже было зафиксировано. Управление пространством, аудит и другие внутренние операции выполняются в аналогичной рекурсивной манере. 

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

таблицы является попытка читать таблицу в то время, когда инициирован ее триг­гер. Используя автономную транзакцию, вы можете запросить таблицу, но при этом не сможете увидеть изменения (для чего в первую очередь и предназначено ограничение мутирующей таблицы; таблица находится в процессе модификации, поэтому результаты запроса должны быть несогласованными). Любые решения, принятые на основе запроса из триггера, сомнительны — в этот момент времени вы читаете “старые” данные. 

Потенциально корректное применение автономной транзакции — в специализи­рованном аудите, но я подчеркиваю слова “потенциально корректное”. Существуют более эффективные пути аудита информации базы данных, нежели посредством написания специального триггера. Например, вы можете использовать пакет DBMS_FGA или саму команду AUDIT. 

Часто задаваемый разработчиками приложений вопрос звучит так: “Как я могу выполнять аудит каждой попытки модификации секретной информации и запи­сывать значения, которые они пытаются модифицировать?” Они хотят не только предотвращать попытки модификации, но также сохранять записи о таких по­пытках. До появления автономных транзакций многие разработчики пытались (и безуспешно) делать это с помощью стандартных триггеров без автономных тран­закций. Триггер должен обнаруживать UPDATE, и, увидев, что пользователь моди­фицирует данные, которых он модифицировать не должен, создавать запись ауди­та и отказывать UPDATE. К сожалению, когда триггер отвергает UPDATE, он также выполняет откат записи аудита — это типичная ситуация “все или ничего”. С ав­тономными транзакциями стало возможным безопасно выполнять аудит попыток выполнения запретных операций, одновременно откатывая эти операции. В про­цессе мы можем информировать конечного пользователя о том, что он пытается модифицировать данные, которые не имеет права модифицировать, а также о том, что его попытка зафиксирована в записи аудита. 

Интересно отметить, что родная команда Oracle AUDIT уже в течение многих лет предоставляет возможность фиксации безуспешных попыток модификации информации, используя автономные транзакции. Предоставление этого средства в распоряжение разработчиков Oracle позволяет нам выполнять собственный, более гибкий специализированный аудит. 

Рассмотрим небольшой пример. Давайте добавим триггер автономной транзак­ции к таблице, которая фиксирует след аудита, детализируя, кто пытался обно­вить таблицу, и когда это произошло, наряду с информативным сообщением о том, какие именно данные этот пользователь пытался модифицировать. Логика такого триггера должна предотвращать любые попытки обновления записи сотрудника, который (прямо или опосредованно) не подчиняется вам. 

Во-первых, создадим копию таблицы EMP из схемы SCOTT, чтобы использовать в качестве таблицы примеров: 

ops$tkyte@ORA10G> create table emp

2 as

 3 select * from scott.emp;

Table created. 

Таблица создана. 

ops$tkyte@ORA10G> grant all on emp to scott;Grant succeeded. 

Полномочия унаследованы. 

Мы также создадим таблицу AUDIT_TAB, в которой будем размещать информа­цию аудита. Обратите внимание, что мы используем атрибут DEFAULT в столбцах, которые должны хранить имя текущего зарегистрированного пользователя и теку­щее значение даты/времени в наш “след” аудита. 

ops$tkyte@ORA10G> create table audit_tab2 ( username varchar2(30) default user,3 timestamp date default sysdate,4 msg varchar2(4000)5 )6 /

Table created. 

Таблица создана. 

Далее создадим триггер EMP_AUDIT для выполнения аудита действия UPDATE над таблицей EMP: 

ops$tkyte@ORA10G> create or replace trigger EMP_AUDIT2 before update on emp3 for each row 4 declare

 5 pragma autonomous_transaction;

6 l_cnt number;7 begin

26 end;

27 /Trigger created.Триггер создан. 

Обратите внимание на применение запроса с CONNECT BY. Это позволяет нам развернуть всю иерархию, начиная с текущего пользователя, чтобы проверить, что запись, попытка обновления которой предпринимается, относится к кому-то, кто подчиняется нам на некотором уровне. 

Ниже перечислены главные моменты, которые нужно отметить относительно этого триггера. 

PRAGMA AUTONOMOUS_TRANSACTION применяется к определению триггера. Весь триггер представляет собой “автономную транзакцию”, и потому независим от родительской транзакции, пытающейся выполнить обновление. 

Триггер пытается выполнять чтение таблицы, которую он защищает, а имен­но — таблицу EMP. Само по себе это может привести к ошибке “мутирующей” таблицы во время выполнения, но это не касается автономной транзакции. Автономная транзакция обходит эту проблему — она позволяет читать табли­цу, но с условием, что мы не увидим тех изменений, которые проводятся в таблице вышестоящей транзакцией. В таком случае следует проявлять ис­ключительную осторожность. Подобная логика должна быть тщательно про­думана. Что если текущая транзакция пытается обновить саму иерархию сотрудников? Мы не увидим этих изменений в триггере, и это следует при­нимать во внимание, проверяя корректность кода триггера. 

Этот триггер выполняет фиксацию. Раньше такое было невозможно — триг­геры никогда не фиксировали работу. Данный триггер не фиксирует ту рабо­ту, которая была причиной его вызова; вместо этого он фиксирует только ту работу, которую выполняет он сам (запись аудита). 

Итак, мы настроили таблицу EMP, имеющую замечательную иерархическую структуру (рекурсивное отношение EMPNO — MGR). Также у нас есть таблица AUDIT_TABLE, в которую мы хотим вносить записи о безуспешных попытках мо­дифицировать информацию. У нас имеется триггер, обеспечивающий выполнение нашего правила — что только наш руководитель или руководитель нашего руково­дителя (и так далее) может модифицировать нашу запись. 

Давайте посмотрим, как это работает, попытавшись обновить запись в таблице EMP: 

ops$tkyte@ORA10G> update emp set sal = sal*10;

update emp set sal = sal*10

* 

ERROR at line 1: 

ORA-20001: Access Denied 

ORA-06512: at «OPS$TKYTE.EMP_AUDIT», line 21

ORA-04088: error during execution of trigger ‘OPS$TKYTE.EMP_AUDIT’

ОШИБКА в строке 1: 

ORA-20001: Доступ запрещен 

ORA-06512: в «OPS$TKYTE.EMP_AUDIT», строка 21 

ORA-04088: ошибка во время выполнения триггера ‘OPS$TKYTE.EMP_AUDIT’ 

ops$tkyte@ORA10G> select * from audit_tab; 

USERNAME TIMESTAMP MSG 

OPS$TKYTE 27-APR-05 Attempt to update 7369 

Вызов триггера предотвращает операцию UPDATE, в то же время создавая по­стоянную запись о такой попытке (обратите внимание, как используется ключевое слово DEFAULT в операторе CREATE TABLE таблицы AUDIT_TAB, чтобы автоматиче­ски вносить значения USER и SYSDATE). Далее давайте зарегистрируемся как поль­зователь, который может выполнить UPDATE, и попытаемся сделать кое-что: 

ops$tkyte@ORA10G> connect scott/tiger

Connected. 

Подключено. 

scott@ORA10G> set echo on

scott@ORA10G> update ops$tkyte.emp set sal = sal*1.05 where ename = ‘ADAMS’;

1 row updated.

1 строка обновлена. 

scott@ORA10G> update ops$tkyte.emp set sal = sal*1.05 where ename = ‘SCOTT’;

update ops$tkyte.emp set sal = sal*1.05 where ename = ‘SCOTT’

* 

ERROR at line 1: 

ORA-20001: Access Denied 

ORA-06512: at «OPS$TKYTE.EMP_AUDIT», line 21

ORA-04088: error during execution of trigger ‘OPS$TKYTE.EMP_AUDIT’

ОШИБКА в строке 1: 

ORA-20001: Доступ запрещен 

ORA-06512: в «OPS$TKYTE.EMP_AUDIT», строка 21 

ORA-04088: ошибка во время выполнения триггера ‘OPS$TKYTE.EMP_AUDIT’ 

В инсталляции по умолчанию демонстрационной таблицы EMP сотрудник ADAMS подчиняется SCOTT, поэтому первый оператор UPDATE выполнился успешно. Второй UPDATE, где SCOTT пытается дать самому себе прибавку, терпит провал, посколь­ку SCOTT не подчиняется SCOTT. Зарегистрируемся в схеме, содержащей таблицу AUDIT_TAB, и увидим следующее: 

scott@ORA10G> connect /

Connected. 

Подключено. 

ops$tkyte@ORA10G> set echo on

ops$tkyte@ORA10G> select * from audit_tab; 

USERNAME TIMESTAMP MSG 

OPS$TKYTE 27-APR-05 Attempt to update 7369

SCOTT 27-APR-05 Attempt to update 7788 

Как видим, попытка SCOTT выполнить оператор UPDATE была зафиксирована. 

Problem Description
-------------------------------
You are creating a trigger that includes an exception handling block.  You wish to raise a user defined error when a certain condition is met within the trigger body using keyword RAISE.  
Inside your error handling block you also include a call to RAISE_APPLICATION_ERROR.


Consider this code example --
 create table tmp (col1 char(40));
create table violations (col1 varchar2(30));
 
CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
  RULE_001 EXCEPTION;
BEGIN
  IF :NEW.col1 = 'mike' THEN
  dbms_output.put_line(:new.col1);
  INSERT INTO VIOLATIONS values ('violation logged');
  -- Raise rule
  RAISE RULE_001;
  END IF;
EXCEPTION
WHEN RULE_001 THEN  
  RAISE_APPLICATION_ERROR (-20001,'Guideline Violation, Rule-001.');
END; 
 
When this trigger is executed, you receive the ora-4088 and ora-6512 errors.
ORA-04088: error during execution of trigger 'SCOTT.DEMO_RULE_001'
 
 Solution Description
-------------------------------
 You cannot use both RAISE, within the execution block of a trigger, and RAISE_APPLICATION_ERROR, within the exception block.  
 Explanation
------------------------
 RAISE forces execution to move into the exception block.RAISE_APPLICATION_ERROR, within the exception block, terminates the program.If the trigger body does not complete, the triggering SQL statement and any 
SQL statements within the trigger body are rolled back. Thus, execution completes unsuccessfully with a runtime error and it appears as if none of the code within the trigger body gets executed.
 
Consider this corrected code --
CREATE OR REPLACE TRIGGER DEMO_RULE_001
BEFORE INSERT OR UPDATE ON TMP
FOR EACH ROW
DECLARE
  RULE_001 EXCEPTION;
BEGIN
  IF :NEW.col1 = 'mike' THEN
  dbms_output.put_line(:new.col1);
  INSERT INTO VIOLATIONS values ('violation logged');
  -- Raise rule
  RAISE RULE_001;
  END IF;
EXCEPTION
WHEN RULE_001 THEN  
  --raise_application_error(-20001, 'Guideline Violation, Rule-001.');
  dbms_output.put_line('Guideline Violation, Rule-001.');
END;
 
Oracle Support Doc ID 103293.1