Skip to Main Content
  • Questions

  • Creating triggers on (tables in) another schema

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Paul.

Asked: June 23, 2016 - 9:36 am UTC

Last updated: August 15, 2019 - 2:18 am UTC

Version: any

Viewed 10K+ times! This question is

You Asked

In Oracle you can have triggers owned by schema x on tables of schema y.
Unfortunately there seems to be no fine grained way to authorize this - user x needs CREATE ANY TRIGGER privilege.
(contrary to the similar situation with indexes, user y can grant CREATE INDEX on a table to user y; CREATE ANY INDEX is not needed).
CREATE ANY TRIGGER raises some security issues of course - I rather wouldn't grant any ANY privilege.

My questions
1) Oracle allows it, but do you consider it good practice to have triggers of schema x on tables of schema y?
2) Can you think of a better way (than CREATE ANY TRIGGER grant) to realize this?

Thanks in advance for your advice!

and Connor said...

Personally, I'm not a fan of cross-schema triggers, or in general, much of cross-schema dependencies.

An exception to this might be schema's used for security, but there is still a known and strong association between them, eg, schema A with all the tables, and another schema B exposing views on those tables etc. So whilst separate, we would view "A+B" as being the logical definition of the application.

But back to triggers - the reason I'm hesitant on cross-schema triggers is the mess than can occur when export schemas and the like - it's easy to end up with references to non-existent objects, or confusion where someone wants to know why some data changes in schema X but cannot see the trigger that caused it, because its in schema Y etc.

Just my two cents worth. Others welcome to share their views.

(And I agree with you 100% ... Any privilege with the word "ANY" in it, raises alarm bells)

Rating

  (1 rating)

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

Comments

can a trigger access a table in another schema in oracle

mike, August 14, 2019 - 9:35 pm UTC

The answer is yes.
But what type of grants do you need.
If I gant select directly to the schema ... the trigger works fine.
When I grant select to a role and grant the role to the schema I get an error table does not exist.
Connor McDonald
August 15, 2019 - 2:18 am UTC

Head to our Resources tab and search for "RolesAndProcedures.html" in the Presentations section. We've done a whitepaper explaining this.
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

Security

All of the vital components for a secure database are covered in the Security guide.