Trigger creation by one user against another user's table

Hello everyone. I'm fairly new to Oracle security and have a question about how to setup security for a user to be able to create/replace triggers on tables owned by another user.

I am using Oracle v8i and Oracle 9i and so the solution needs to be available in each version.

Here is how the database is setup.

USER1 (owns schema)
USER2 USER1 has created a table, MY_TABLE. I need to have USER2 granted just enough privileges to be able to create triggers against USER1.MY_TABLE. From what I've read in the Oracle documentation, I can accomplish the task by granting USER2 the CREATE ANY TRIGGER privilege. However, this privilege allows USER2 the ability to create triggers in any schema in the database and not just the USER1 schema.

Without assigning USER2 with a permission using the "ANY" keyword, is there a way to grant permissions to USER2 so he/she can create/replace a trigger on USER1.MY_TABLE? If so, can you show me how to do it.

I have already tried having USER1 issue a GRANT CREATE TRIGGER TO USER2 and the grant statement is successful, but when I log in as USER2, I am still not able to create a trigger on MY_TABLE. I get insufficient privilege errors. (USER2 also has connect and resource privileges, if that makes any difference.)

Thank you for any help you can provide.

Steve Cummings,
Comshare, Inc. Received on Wed Jul 23 2003 - 13:37:41 CDT