procedure中truncate other schema's table

在存储过程中调 用truncate，截取其它方案的表，如何授权？
请看下面的实验
os:linux   red hat 企业5
oracle version:10g
过程在test2中建立t2表，在test用户中建立procedure p_trun_t2处理test2中的t2表;
---------------------------------
SQL> conn test2/test2;
Connected.
SQL> select * from session_roles;
ROLE
------------------------------------------------------------
CONNECT
RESOURCE
SQL> create table t2(id int);
Table created.
SQL> insert into t2 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> conn test/test
Connected.
SQL> show user
USER is "TEST"
SQL> select * from test2.t2;
select * from test2.t2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn system/oracle
Connected.
SQL> grant dba to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> select * from test2.t2;
ID
----------
1
SQL> truncate table test2.t2;
Table truncated.
SQL> create or replace procedure p_trun_t2
2  is
3  v_sql varchar2(1000);
4  begin
5  v_sql := 'truncate table test2.t2';
6  execute immediate v_sql;
7  end;
8  /
Procedure created.
SQL> exec p_trun_t2;
BEGIN p_trun_t2; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TEST.P_TRUN_T2", line 6
ORA-06512: at line 1
SQL> show user
USER is "TEST"
SQL> create or replace procedure p_trun_t2
2  is
3  v_sql varchar2(1000);
4  begin
5  v_sql := 'delete test2.t2';
6  execute immediate v_sql;
7  end;
8  /
Procedure created.
SQL> exec p_trun_t2;
BEGIN p_trun_t2; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TEST.P_TRUN_T2", line 6
ORA-06512: at line 1
SQL> delete test2.t2;
0 rows deleted.
SQL> conn system/oracle
Connected.
SQL> grant drop any table to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> exec p_trun_t2;
BEGIN p_trun_t2; END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "TEST.P_TRUN_T2", line 6
ORA-06512: at line 1
SQL> conn system/oracle
Connected.
SQL> grant delete any table to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> exec p_trun_t2;
PL/SQL procedure successfully completed.
SQL> conn test2/test2
Connected.
SQL> insert into t2 values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> conn test/test;
Connected.
SQL> exec p_trun_t2;
PL/SQL procedure successfully completed.
SQL> select * from test2.t2;
no rows selected
SQL> conn system/oracle
Connected.
SQL> revoke drop any table from test;
Revoke succeeded.
SQL> conn test/test;
Connected.
SQL> exec p_trun_t2;
PL/SQL procedure successfully completed.
SQL> conn system/oracle
Connected.
SQL> revoke dba from test;
Revoke succeeded.
SQL> conn test/test;
Connected.
SQL> exec p_trun_t2;
PL/SQL procedure successfully completed.
SQL> revoke delete any table  from test;
Revoke succeeded.
SQL> conn test/test;
Connected.
SQL> exec p_trun_t2;
BEGIN p_trun_t2; END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "TEST.P_TRUN_T2", line 6
ORA-06512: at line 1
--上面是在procedure 中用的delete test2.t2;
SQL> l
1   create or replace procedure p_trun_t2
2      is
3      v_sql varchar2(1000);
4      begin
5      v_sql := 'truncate table test2.t2';
6      execute immediate v_sql;
7*     end;
SQL> conn system/oracle
Connected.
SQL> grant drop any table to test;
Grant succeeded.
SQL> conn test/test;
Connected.
SQL> select * from session_roles;
ROLE
------------------------------------------------------------
CONNECT
RESOURCE
SQL> select * from session_privs;
PRIVILEGE
--------------------------------------------------------------------------------
CREATE SESSION
CREATE TABLE
DROP ANY TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
10 rows selected.
SQL> truncate table test2.t2;
Table truncated.
SQL> exec p_trun_t2;
PL/SQL procedure successfully completed.
SQL>
ps:delete 别人的table,只需显示授予delete any table权限,而truncate 别人的 table 需要 drop any table，这个权限很危险，最好方案是在test2中建立一个procedure进行 truncate,给test 执行procedure 的权限!