I have got this error statement for update in trigger- Same update statement outside trigger is working as expected.

Setup Schema - B

Table - B.Tab1 B_Write_Role is having privileges to update table in B.Tab1

Schema - A

A schema has B_Write_Role

For A schema there is no direct role, it's through _Write_Role.

When executing the updating statement for Table - B.Tab1 is working as expected. However, when the same statement is getting executed through a trigger it throws an error.

Note : When assigned direct update privileges to schema A for B.Tab1 trigger is working fine.

Why this behavior ? Is there any way to handle this without assigning direct privileges to schema and handle through role ?

CodePudding user response：

That's expected. Privileges acquired via roles won't work in named PL/SQL programs (such as functions, procedures, or triggers (which is your case)).

What can you do? Nothing; grant privileges directly.