Skip to Main Content
  • Questions

  • Create trigger on different user with different schema

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ronald.

Asked: December 26, 2006 - 12:08 am UTC

Last updated: June 23, 2008 - 4:05 pm UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Please correct me, I create trigger on different user than the owner of the trigger. Is this action possible?

RK@orcl3> create table t (x int);

Table created.

RK@orcl3> grant all on t to bahan;

Grant succeeded.

RK@orcl3> conn bahan
Enter password:
Connected.

BAHAN@orcl3> create or replace trigger t_air
2 after insert on rk.t
3 for each row
4 declare
5 l_int number(2);
6 begin
7 select count(*) into l_int from dual;
8 end;
9 /
after insert on rk.t
*
ERROR at line 2:
ORA-01031: insufficient privileges


BAHAN@orcl3>

Thanks.

and Tom said...

The sql reference guide is great for figuring this stuff out (what privileges you need)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm#sthref7890

for example states:

...
To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.
........


ops$tkyte%ORA10GR2> create user a identified by a default tablespace users quota unlimited on users;

User created.

ops$tkyte%ORA10GR2> create user b identified by b;

User created.

ops$tkyte%ORA10GR2> grant create session, create table to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant create session, create any trigger to b;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create table t (x int);

Table created.

a%ORA10GR2>
a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> create trigger t_trigger
  2  after insert on a.t
  3  begin
  4          null;
  5  end;
  6  /

Trigger created.


Now that said - you DO NOT want to do this!! the "any" privileges should be avoided whenever possible - user B can actually remove pretty much any trigger now!! just be creating an empty one.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks Tom

A reader, December 29, 2006 - 7:06 pm UTC


missing privilege

Georg, June 22, 2007 - 11:02 am UTC

Is there any reason why Oracle does not offer the possibility of
GRANT CREATE TRIGGER ON <table> TO <user>?

Tom Kyte
June 22, 2007 - 5:10 pm UTC

nope, but I personally am thankful it doesn't exist.

i hate triggers. I cannot even begin to imagine the additional level of confusion you would introduce by having someone outside of the schema having a trigger on the table. that would make a bad idea even worse.

Requirement that warrants Triggers

Rajasekar, June 27, 2007 - 8:23 am UTC

As you generally hate triggers. Can you please share with us, any one good requirement that had forced you to create triggers in your application? and any one good requirement that you had avoided trigger(by following other approaches) while most of the other Oracle pros would have coded it using triggers?
Tom Kyte
July 02, 2007 - 10:07 am UTC

I don't have any examples where I was "forced".

I don't like triggers with side effects, I avoid them.

Where is the need for 'CREATE ANY trigger' privilege

Yogesh Chopade, June 23, 2008 - 11:09 am UTC

Hi Tom,

CREATE ANY TRIGGER, or any other grant like this is a very powerful grant, and is not recommended by you in this reply.

Then can you please comment why oracle has even offered this kind of grant? Have you ever seen any need for such grant in your vast experience?

Thanks

Yogesh
Tom Kyte
June 23, 2008 - 1:09 pm UTC

because there is a powerful grant - you take that to mean we shouldn't have it???

root is very powerful
most people should not have root
does that mean root shouldn't be there?

sysdba is ...
most people...
does that....


it is so that a super user can install another schema's objects - like import does.

Accidentally did this.

Ray DeBruyn, June 23, 2008 - 3:49 pm UTC

Hi Tom,

We had a user with way more privilege than needed at one point and I accidentally created a trigger as this user on a table ownen by another.

I knew I was creating it in the wrong user account, but thought that the trigger ownership would always be the same as the table owner. Can I assume the trigger fires for any DML operations regardless of the user performing them? If so, what type of execution/privilege effect would this have?

Ray
Tom Kyte
June 23, 2008 - 4:05 pm UTC

triggers are owned by the defining schema, unless you say otherwise.

Meaning, I can create a trigger on YOUR table.
Meaning, you can drop my trigger.


ops$tkyte%ORA10GR2> create user a identified by a quota unlimited on users default tablespace users;

User created.

ops$tkyte%ORA10GR2> grant create session, create table to a;

Grant succeeded.

ops$tkyte%ORA10GR2> create user b identified by b;

User created.

ops$tkyte%ORA10GR2> grant create session, create ANY trigger to b;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create table t ( x int );

Table created.

a%ORA10GR2>
a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> create or replace trigger t before insert on a.t
  2  begin
  3          dbms_output.put_line( 'i fired' );
  4  end;
  5  /

Trigger created.

b%ORA10GR2> select * from user_triggers;

TRIGGER_NAME                   TRIGGER_TYPE
------------------------------ ----------------
TRIGGERING_EVENT
-------------------------------------------------------------------------------
TABLE_OWNER                    BASE_OBJECT_TYPE TABLE_NAME
------------------------------ ---------------- ------------------------------
COLUMN_NAME
-------------------------------------------------------------------------------
REFERENCING_NAMES
-------------------------------------------------------------------------------
WHEN_CLAUSE
-------------------------------------------------------------------------------
STATUS
--------
DESCRIPTION
-------------------------------------------------------------------------------
ACTION_TYPE
-----------
TRIGGER_BODY
-------------------------------------------------------------------------------
T                              BEFORE STATEMENT
INSERT
A                              TABLE            T

REFERENCING NEW AS NEW OLD AS OLD

ENABLED
t before insert on a.t
PL/SQL
begin
        dbms_output.put_line( 'i fired' );
end;


b%ORA10GR2> insert into a.t values (0);
insert into a.t values (0)
              *
ERROR at line 1:
ORA-00942: table or view does not exist

<b>so, even though I cannot select or do anything to your table, I can create a trigger on it..</b>

b%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> insert into a.t values (0);
i fired

1 row created.

a%ORA10GR2> drop table t purge;

Table dropped.

a%ORA10GR2> connect b/b
Connected.
b%ORA10GR2> select * from user_triggers;

no rows selected


and you can drop my trigger by dropping your table...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library