Re: Truncate table call in procedure

Actually, DELETE ANY TABLE was the only way I could get around it.

--
Terry Dykstra
Canadian Forest Oil Ltd.
Check out Sybase Developer's Network: http://www.sybase.com/sdn

Remco Blaakmeer <remco_at_rd1936.quicknet.nl> wrote in message news:86894c$6hd$4_at_rd1936.quicknet.nl...
> In article <lkKh4.93229$n3.1687611_at_news0.telusplanet.net>,
> "Terry Dykstra" <tdykstra_at_cfol.ab.ca> writes:
> > You need to use the dbms_sql package:
> >
> > sql_curs := DBMS_SQL.OPEN_CURSOR;
> > DBMS_SQL.PARSE(sql_curs, 'truncate table
> > theowner.thetable',DBMS_SQL.NATIVE);
> > li_rc := DBMS_SQL.EXECUTE(sql_curs);
> > DBMS_SQL.CLOSE_CURSOR(sql_curs);
>
> Yes. And if you put this into a stored program unit, one of the following
> must be true:
>
> - the program unit and the table have the same owner
> - the owner of the program unit has the DROP ANY TABLE system privilege
> (some documentation says you need the DELETE ANY TABLE privilege, but
> as far as I can test it that is wrong)
>
> Note that the DROP ANY TABLE privilege is very dangerous. A malicious
> user with this privilege can destroy your entire database. It is safer
> to store the table and the program unit in the same schema and grant the
> EXECUTE privilege on the program unit only to users who need it.
>
> In the second case, it is not sufficient if the owner of the program
> unit has got the privilege through a role. When a stored program unit is
> executed, no roles are enabled so only privileges granted to directly to
> the owner (or to PUBLIC) are effective.
>
> Remco
> --
> rd1936: 1:05am up 12 days, 5:44, 9 users, load average: 2.98, 3.12,
3.13 Received on Fri Jan 21 2000 - 10:39:09 CST