where is the TRIGGER ANY TABLE privilege?

You have your table data in one schema and your procedures in another one. But can you have triggers and tables in different schemas?

SYS@lsc01> create user u1 identified by u1;


User created.
SYS@lsc01> create user u2 identified by u2;
User created.
SYS@lsc01> grant create table, unlimited tablespace to u1;
Grant succeeded.
SYS@lsc01> grant create session, create trigger to u2;
Grant succeeded.
SYS@lsc01> create table u1.t(x number);
Table created.
SYS@lsc01> grant select on u1.t to u2;
Grant succeeded.

SYS@lsc01> connect u2/u2 Connected. U2@lsc01> create trigger u2.tr after insert on u1.t for each row 2 begin 3 null; 4 end; 5 / create trigger u2.tr after insert on u1.t for each row * ERROR at line 1: ORA-01031: insufficient privileges

What’s the missing privilege? To create a trigger on another schema, you need the CREATE ANY TRIGGER privilege.

U2@lsc01> connect / as sysdba Connected. SYS@lsc01> grant CREATE ANY TRIGGER to u2;


Grant succeeded.
SYS@lsc01>

SYS@lsc01> connect u2/u2

Connected.

U2@lsc01> create trigger u2.tr after insert on u1.t for each row

  2  begin

  3  null;

  4  end;

  5  /

Trigger created.

1 thought on “where is the TRIGGER ANY TABLE privilege?”