Re: Truncate table call in procedure

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 Thu Jan 20 2000 - 18:26:52 CST