【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
可以看到已经正常删除了。
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/,如需转载,请注明出处,否则将追究法律责任。