0

I'm using Oracle 11gR2, and I have two schemas -- A and B, and each has a similar CUSTOMER table.

When B.CUSTOMER is updated, I want the same updates to happen to A.CUSTOMER. Schema A has full privileges to schema B, but I want schema B to have as few privileges on schema A as possible.

I wrote a procedure in schema A, A.UPDATE_CUSTOMER that takes a B.CUSTOMER%ROWTYPE variable and uses it to update A.CUSTOMER, and granted execute on the procedure to schema B.

I created a for each row after update trigger on B.CUSTOMER that calls this update procedure, and it works as desired. But I'm thinking that user B could explicitly call the procedure outside of the trigger, and I don't want to allow this.

Is there a way to prevent A.UPDATE_CUSTOMER from being called by B outside of the trigger? Or is there some other way to prevent user B from updating A.CUSTOMER outside of the trigger?

Mike
  • 1,039
  • 1
  • 12
  • 20

1 Answers1

0

The only natural way I can think of doing this is to disallow "create session" for B, and having all changes to B's schema made by user C who is granted the required privileges to do so. C would not of course have the privilege on A's schema.

I believe that triggers are implicitly authid definer, but if not the trigger could call a procedure that is.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96