【DB笔试面试185】在Oracle中，如何让普通用户可以TRUNCATE其他用户的表？

【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.

SQL> 

SQL> SELECT * FROM USER_INDEXES;

no rows selected

可以看到已经正常删除了。

DB笔试面试历史连接

http://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w

---

---

About Me

............................................................................................................................................. ● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文博客园地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群号：230161599（满）、618766405 ● 微信群：可加我微信，我拉大家进群，非诚勿扰 ● 联系我请加QQ好友（646634621），注明添加缘由 ● 于 2018-05-01 06:00 ~ 2018-05-31 24:00 在魔都完成 ● 最新修改时间：2018-05-01 06:00 ~ 2018-05-31 24:00 ● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解 ● 版权所有，欢迎分享本文，转载请保留出处 ............................................................................................................................................. ● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/ ● 小麦苗OCP、OCM、高可用网络班：http://blog.itpub.net/26736162/viewspace-2148098/ ............................................................................................................................................. 使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。    小麦苗的微信公众号      小麦苗的DBA宝典QQ群2     《DBA笔试面试宝典》读者群       小麦苗的微店 .............................................................................................................................................

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2155367/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26736162/viewspace-2155367/