where is the TRIGGER ANY TABLE privilege?
May 13th, 2010
1 comment
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.