Thread: Re: [NOVICE] Question on TRUNCATE privleges
Thomas Hallgren wrote: > > It looks to me like the asymmetry between CREATE TRIGGER and DROP > > TRIGGER is actually required by SQL99, though, so changing it would > > be a hard sell (unless SQL2003 fixes it?). > > > > Comments anyone? > > > Why not say that TRUNCATE requires the same privilige as a DELETE and > add a trigger type that fires (once) on a TRUNCATE? That would give an > owner a chance to prevent it. Such a trigger would probably be useful > for other things too. Uh, that seems like it adds extra complexity just for this single case. Why don't we allow TRUNCATE by non-owners only if no triggers are defined, and if they are defined, we throw an error and mention it is because triggers/contraints exist? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Uh, that seems like it adds extra complexity just for this single case.
Yeah. I've dropped the idea personally -- the suggestion that the table
owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if he
wants to allow others to do it seems to me to cover the problem.
> Why don't we allow TRUNCATE by non-owners only if no triggers are
> defined, and if they are defined, we throw an error and mention it is
> because triggers/contraints exist?
I don't think we should put weird special cases in the rights checking
to allow this -- that's usually a recipe for introducing unintended
security holes.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Uh, that seems like it adds extra complexity just for this single case. > > Yeah. I've dropped the idea personally -- the suggestion that the table > owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if he > wants to allow others to do it seems to me to cover the problem. > > > Why don't we allow TRUNCATE by non-owners only if no triggers are > > defined, and if they are defined, we throw an error and mention it is > > because triggers/contraints exist? > > I don't think we should put weird special cases in the rights checking > to allow this -- that's usually a recipe for introducing unintended > security holes. Yea, good point. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
"Keith Worthington" <keithw@narrowpathinc.com> writes:
> On Thu, 24 Feb 2005 17:15:42 -0500, Tom Lane wrote
>> Yeah. I've dropped the idea personally -- the suggestion that the table
>> owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if
>> he wants to allow others to do it seems to me to cover the problem.
> Could someone point me in the direction of documentation on this SECURITY
> DEFINER feature?
See CREATE FUNCTION. Something like (untested)
create function truncate_my_table() returns void as
$$ truncate my_table $$ language sql security definer;
You'd probably then revoke the default public EXECUTE rights on this
function, and grant EXECUTE only to selected users.
regards, tom lane
On Thu, 24 Feb 2005 17:15:42 -0500, Tom Lane wrote > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Uh, that seems like it adds extra complexity just for this single case. > > Yeah. I've dropped the idea personally -- the suggestion that the table > owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if > he wants to allow others to do it seems to me to cover the problem. Could someone point me in the direction of documentation on this SECURITY DEFINER feature? Kind Regards, Keith
On Thu, 24 Feb 2005 17:15:42 -0500, Tom Lane wrote > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Uh, that seems like it adds extra complexity just for this single case. > > Yeah. I've dropped the idea personally -- the suggestion that the table > owner can provide a SECURITY DEFINER procedure to do the TRUNCATE if > he wants to allow others to do it seems to me to cover the problem. Could someone point me in the direction of documentation on this SECURITY DEFINER feature? Kind Regards, Keith