【DB筆試面試185】在Oracle中，如何讓普通使用者可以TRUNCATE其他使用者的表？

在Oracle中，如何讓普通使用者可以TRUNCATE其他使用者的表？

使用者1若要刪除使用者2的索引，則使用者1需要有DROP ANY INDEX的許可權。使用者1若要TRUNCATE使用者2的表，則使用者1需要有DROP ANY TABLE的許可權。但是，DROP ANY INDEX和DROP ANY TABLE的許可權過大，一般不能賦予普通使用者這2個許可權，那麼可以透過寫儲存過程來實現該功能，如下所示：

CREATE OR REPLACE PROCEDURE PRO_TRUNC_DROP_LHR(COMMAND IN VARCHAR2,

O_TYPE IN VARCHAR2,

OWNER IN VARCHAR2,

O_NAME IN VARCHAR2) AUTHID DEFINER AS

V_SQL VARCHAR2(4000);

BEGIN

IF UPPER(COMMAND) IN ('DROP', 'TRUNCATE') AND

UPPER(O_TYPE) IN ('TABLE', 'INDEX') THEN

V_SQL := COMMAND || ' ' || O_TYPE || ' ' || OWNER || '.' || O_NAME;

EXECUTE IMMEDIATE V_SQL;

END IF;

END PRO_TRUNC_DROP_LHR;

使用示例如下所示：

建立使用者1和使用者2，分別賦予CONNECT和RESOURCE許可權：

SQL> SHOW USER

USER is "SYS"

SQL>

SQL> CREATE USER LHR_U1 IDENTIFIED BY LHR_U1;

User created.

SQL> CREATE USER LHR_U2 IDENTIFIED BY LHR_U2;

User created.

SQL> GRANT CONNECT,RESOURCE TO LHR_U1;

Grant succeeded.

SQL> GRANT CONNECT,RESOURCE TO LHR_U2;

Grant succeeded.

使用者2建立表U2_T_LHR：

SQL> CONN LHR_U2/LHR_U2

Connected.

SQL> CREATE TABLE U2_T_LHR AS SELECT * FROM DUAL;

Table created.

SQL> SELECT * FROM U2_T_LHR;

D

-

X

SQL> CREATE INDEX IDX_U2_T_LHR ON U2_T_LHR(DUMMY);

Index created.

SQL>

SQL> GRANT SELECT,DELETE,UPDATE ON U2_T_LHR TO LHR_U1;

Grant succeeded.

使用者2建立儲存過程並賦予使用者1的執行許可權：

SQL> CREATE OR REPLACE PROCEDURE PRO_TRUNC_DROP_LHR(COMMAND IN VARCHAR2,

2 O_TYPE IN VARCHAR2,

3 OWNER IN VARCHAR2,

4 O_NAME IN VARCHAR2) AUTHID DEFINER AS

5 V_SQL VARCHAR2(4000);

6 BEGIN

7 IF UPPER(COMMAND) IN ('DROP', 'TRUNCATE') AND

8 UPPER(O_TYPE) IN ('TABLE', 'INDEX') THEN

9 V_SQL := COMMAND || ' ' || O_TYPE || ' ' || OWNER || '.' || O_NAME;

10 EXECUTE IMMEDIATE V_SQL;

11 END IF;

12 END PRO_TRUNC_DROP_LHR;

13 /

Procedure created.

SQL> GRANT EXECUTE ON PRO_TRUNC_DROP_LHR TO LHR_U1;

Grant succeeded.

使用者1開始查詢：

SQL> conn LHR_U1/LHR_U1

Connected.

SQL>

SQL> SELECT * FROM LHR_U2.U2_T_LHR;

D

-

X

SQL> TRUNCATE TABLE LHR_U2.U2_T_LHR;

TRUNCATE TABLE LHR_U2.U2_T_LHR

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL> DROP INDEX LHR_U2.IDX_U2_T_LHR;

DROP INDEX LHR_U2.IDX_U2_T_LHR

*

ERROR at line 1:

ORA-01418: specified index does not exist

SQL> DROP TABLE LHR_U2.U2_T_LHR;

DROP TABLE LHR_U2.U2_T_LHR

*

ERROR at line 1:

ORA-01031: insufficient privileges

可以看到TRUNCATE、DROP都沒有許可權，下面採用儲存過程刪除：

SQL> EXEC LHR_U2.PRO_TRUNC_DROP_LHR('TRUNCATE','TABLE','LHR_U2','U2_T_LHR');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM LHR_U2.U2_T_LHR;

no rows selected

SQL> EXEC LHR_U2.PRO_TRUNC_DROP_LHR('DROP','INDEX','LHR_U2','IDX_U2_T_LHR');

PL/SQL procedure successfully completed.

SQL> EXEC LHR_U2.PRO_TRUNC_DROP_LHR('DROP','TABLE','LHR_U2','U2_T_LHR');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM LHR_U2.U2_T_LHR;

SELECT * FROM LHR_U2.U2_T_LHR

*

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> CONN LHR_U2/LHR_U2

Connected.