FreeLists / oracle-l / RE: Privileges on Triggers

["Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>]

It isn't the user doing the update who needs the privileges.  It is the owner 
of the trigger.  The trigger will run as the owner of the trigger.  All of the 
objects referenced in the trigger must be accessible to the trigger owner, and 
the privileges can't be granted through a ROLE.  The trigger owner must have 
explicitly granted access to the objects referenced in the trigger, or the 
trigger owner must have one of the "... ANY TABLE" system privileges.

A public synonym is only necessary if the objects referenced in the trigger do 
not have the owner in front of them, but this will pop up when you try to 
create the trigger.  It will get errors.



Pat

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
Sent: Thursday, November 01, 2007 4:02 PM
To: Koppelaars, Toon; oracle-l
Subject: RE: Privileges on Triggers

Toon,

I'm learning something here, thanks for the response. If I understand correctly 
if the trigger selects from a table then all I need to do is grant select on 
that table to the user who will be using the trigger, correct? By the same 
token if the trigger updates a table then the user doing the updating via the 
trigger must also have appropriate update access on the object, is that correct?

The public synonym bit came from a response I saw else where when googling the 
problem. It sort of made sense in that the trigger is owned by usera but is 
being used by userb so in order for usera to use the code calling the trigger 
it seems a public synonym would be necessary.
Again, I'm not certain I am understanding that part either.

Thanks.


Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
-----Original Message-----
From: Koppelaars, Toon [mailto:T.Koppelaars@xxxxxxxxxxxxxxxxxxxx]
Sent: Thursday, November 01, 2007 1:41 PM
To: William Wagman; oracle-l
Subject: RE: Privileges on Triggers

Bill,

Triggers are not directly executed by users.
Triggers are indirectly executed when the user executes a DML-statement (an 
insert, update or delete statement).

So, there is no concept of 'granting someone execute on a trigger'.
Instead you grant someone to perform DML on a table (that has triggers attached 
to it).

I don't see how creating a public synonym for a trigger could 'fix' a problem, 
you were experiencing with regards to triggers executing or not.

Toon


-----Oorspronkelijk bericht-----
Van: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]Namens William Wagman
Verzonden: donderdag 1 november 2007 21:13
Aan: oracle-l
Onderwerp: Privileges on Triggers


Greetings,

I have been playing around trying to grant execute on a trigger to a particular 
user. After playing with this for a while and reading docs I have come to the 
conclusion that execute on a trigger is not an allowed grant. Instead the 
access is given via a public synonym. For example, give the particular trigger, 
usera.trigger. If usera wants to allow userb to use the trigger it is 
sufficient merely to create a public synonym, create public synonym trigger on 
usera.trigger and user b will then be able to use the trigger. Seems to 
simplistic for me, is that correct or am I missing something?

Thanks.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman@xxxxxxxxxxx
(530) 754-6208
--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l



___________________________________________________________________________________________________
CONFIDENTIALITY AND PRIVACY NOTICE
Information transmitted by this email is proprietary to Medtronic and is 
intended for use only by the individual or entity to which it is addressed, and 
may contain information that is private, privileged, confidential or exempt 
from disclosure under applicable law. If you are not the intended recipient or 
it appears that this mail has been forwarded to you without proper authority, 
you are notified that any use or dissemination of this information in any 
manner is strictly prohibited. In such cases, please delete this mail from your 
records.

To view this notice in other languages you can either select the following link 
or manually copy and paste the link into the address bar of a web browser: 
http://emaildisclaimer.medtronic.com
--
http://www.freelists.org/webpage/oracle-l