ANBOB™

在存储过程中调 用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 的权限!

打赏

微信扫一扫，打赏作者吧～