truncate其他用户的表,需要的是DROP ANY TABLE
alter table不行的,
grant all on sales to XXX 也是alter table的权限,所以不能truncate
1、在功能上,truncate是清空一个表的内容,它相当于delete from table_name。
2、delete是dml操作,truncate是ddl操作;因此,用delete删除整个表的数据时,会产生大量的roolback,占用很多的rollback segments, 而truncate不会。
3、在内存中,用delete删除数据,表空间中其被删除数据的表占用的空间还在,便于以后的使用,另外它是“假相”的删除,相当于windows中用delete删除数据是把数据放到回收站中,还可以恢复,当然如果这个时候重新启动系统(OS或者RDBMS),它也就不能恢复了!
而用truncate清除数据,内存中表空间中其被删除数据的表占用的空间会被立即释放,相当于windows中用shift+delete删除数据,不能够恢复!
4、truncate 调整high water mark 而delete不;truncate之后,TABLE的HWM退回到 INITIAL和NEXT的位置(默认)delete 则不可以。
5、truncate 只能对TABLE,delete 可以是table,view,synonym。
6、TRUNCATE TABLE 的对象必须是本模式下的,或者有drop any table的权限 而 DELETE 则是对象必须是本模式下的,或被授予 DELETE ON SCHEMA.TABLE 或DELETE ANY TABLE的权限。
7、在外层中,truncate或者delete后,其占用的空间都将释放。
8、truncate和delete只删除数据,而drop则删除整个表(结构和数据)。
或者在其他用户下建立一个store-proc, 这个store-proc使用 AUTHID DEFINER
3、权限问题导致
平时所做的对其它用户下资源的操作权限有些是通过角色授权的,但在存储过程、函数、触发器不能直接使用,需要单独授权。授权语句举例:
grant select on sys.XXX TO XXX;
4、需要在存储过程中使用role权限(这个我没太看懂,以下代码部分为粘贴,来自http://www.cnblogs.com/jzbml/p/5991918.html)
复制代码
SQL> select * from dba_role_privs where grantee='SUK';
GRANTEE GRANTED_ROLE ADMIN_OPTION DEFAULT_ROLE
------------ ------------ ------------ ------------
SUK DBA NO YES
SUK CONNECT NO YES
SUK RESOURCE NO YES
--用户SUK拥有DBA这个role
--再创建一个测试存储过程:
create or replace procedure p_create_table
is
begin
Execute Immediate 'create table create_table(id int)';
end p_create_table;
--然后测试
SQL> begin p_create_table end;
begin p_create_table; end;
ORA-01031: 权限不足
ORA-06512: 在"SUK.P_CREATE_TABLE", line 3
ORA-06512: 在line 1
--可以看到,即使拥有DBA role,也不能创建表。role在存储过程中不可用。
--遇到这种情况,我们一般需要显式进行系统权限,如grant create table to suk;
--但这种方法太麻烦,有时候可能需要进行非常多的授权才能执行存储过程
--实际上,oracle给我们提供了在存储过程中使用role权限的方法:
--修改存储过程,加入Authid Current_User时存储过程可以使用role权限。
create or replace procedure p_create_table
Authid Current_User is
begin
Execute Immediate 'create table create_table(id int)';
end p_create_table
;
--再尝试执行:
begin p_create_table end;
PL/SQL procedure successfully completed
drop table create_table
对同义词synonym是不能用truncate的。
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as scott
SQL> SELECT COUNT(1) FROM T_EMP;
COUNT(1)
----------
3
SQL> TRUNCATE TABLE T_EMP;S
TRUNCATE TABLE T_EMP
ORA-00942: 表或视图不存在 因为它确实不是table。。
SQL> truncate synonym sales;
truncate synonym sales
ORA-03290: 无效的截断命令 - 缺失 CLUSTER 或 TABLE 关键字
SQL>
SQL> SELECT S.OBJECT_NAME,S.OBJECT_TYPE,s.status FROM user_objects s WHERE s.object_name='T_EMP';
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
T_EMP SYNONYM VALID
SQL> SELECT dbms_metadata.get_ddl('SYNONYM','T_EMP') TEXT FROM dual;
TEXT
--------------------------------------------------------------------------------
CREATE OR REPLACE SYNONYM "SCOTT"."T_EMP" FOR "SCOTT"."TEST2"
SQL> TRUNCATE TABLE TEST2;
Table truncated
SQL> SELECT COUNT(1) FROM T_EMP;
COUNT(1)
----------
0
SQL>
------------------------------
oracle文档中对truncate权限的要求是需要某表在当前登录的用户下,或者当前登录的用户有drop any table的权限。
但是如果不满足第一个条件的情况下,要让某用户满足第二个条件就导致权限过大了。
参考网上的讨论,可以使用存储过程来时间。
例如有两个用户 u1,u2,u1下有表 test1a
现在想要实现u2能 truncate u1下的表 test1a。
可以使用用户u1创建存储过程,
create procedure u1.stgtruncate(table_name in varchar2) as
begin
execute immediate 'truncate table '||table_name;
end;
然后将该存储过程的权限赋予u2,
grant execute on u1.stgtruncate to u2;
现在登录u2,通过执行如下sql即可truncate u1下的表test1a
call u1.stgtruncate('test1a');
在Oracle中, 如何让普通用户可以TRUNCATE其他用户的表 ?
用户1若要删除用户2的索引,则用户1需要有DROP ANY INDEX的权限。
用户1若要TRUNCATE用户2的表(paritition 也是一样),则用户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
---
假如想清空某个用户下的多个表或所有表
则可以通过将存储过程的表名写成传参形式。
2.1 创建存储过程
create procedure scott.stgtruncate(table_name in varchar2) as
begin
execute immediate 'truncate table '||table_name;
end;
2.2 授予目标用户对该存储过程的执行权限
grant execute on scott.stg
truncate to jiao ;
2.3 调用存储过程清空表
call scott.stgtruncate('t1');