Oracle在线重定义导致其他用户查询权限丢失
最近通过对大表进行在线重定义迁移表空间的时候，发现其他用户对该表的查询权限丢失。
现在模拟下该问题。
版本：11.2.0.4.201020
脚本：
create table ZKM.FILE_MS ( ID NUMBER NOT NULL primary key , SFILE_NAME VARCHAR2(200) , RFILE_NAME VARCHAR2(200) , RSTATUS VARCHAR2(2) , RINFO VARCHAR2(2000) , MID VARCHAR2(100) , CREATE_TIME DATE ); grant select on ZKM.FILE_MS to SCOTT; create table ZKM.FILE_MS_TMP ( ID NUMBER NOT NULL primary key, SFILE_NAME VARCHAR2(200) , RFILE_NAME VARCHAR2(200) , RSTATUS VARCHAR2(2) , RINFO VARCHAR2(2000) , MID VARCHAR2(100) , CREATE_TIME DATE ) PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month')) ( partition part_t01 values less than(to_date('2019-08', 'yyyy-mm'))); EXEC Dbms_Redefinition.can_redef_table('ZKM','FILE_MS'); BEGIN DBMS_REDEFINITION.start_redef_table( uname => 'ZKM', orig_table => 'FILE_MS', int_table => 'FILE_MS_TMP'); END; / BEGIN dbms_redefinition.sync_interim_table( uname => 'ZKM', orig_table => 'FILE_MS', int_table => 'FILE_MS_TMP'); END; / BEGIN dbms_redefinition.finish_redef_table( uname => 'ZKM', orig_table => 'FILE_MS', int_table => 'FILE_MS_TMP'); END; / drop table ZKM.FILE_MS_TMP purge; drop user zkm cascade;
首先构造表FILE_MS，然后对该表进行在线重定义。
09:35:18 SYS@testdb(328)> create table ZKM.FILE_MS 09:35:43 2 ( 09:35:43 3 ID NUMBER NOT NULL primary key , 09:35:43 4 SFILE_NAME VARCHAR2(200) , 09:35:43 5 RFILE_NAME VARCHAR2(200) , 09:35:43 6 RSTATUS VARCHAR2(2) , 09:35:43 7 RINFO VARCHAR2(2000) , 09:35:43 8 MID VARCHAR2(100) , 09:35:43 9 CREATE_TIME DATE 09:35:43 10 ); Table created. Elapsed: 00:00:00.01 09:35:45 SYS@testdb(328)> grant select on ZKM.FILE_MS to SCOTT; Grant succeeded. Elapsed: 00:00:00.01 09:36:08 SYS@testdb(328)> set line 500 09:36:13 SYS@testdb(328)> select * from dba_tab_privs where grantee='SCOTT'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY ------------------------- ------------------------- ------------------------------ ------------------------- ------------------------------ --------- --------- SCOTT ZKM FILE_MS ZKM SELECT NO NO Elapsed: 00:00:00.01
之后改造为分区表。
09:43:47 SYS@testdb(328)> create table ZKM.FILE_MS_TMP 09:43:49 2 ( 09:43:49 3 ID NUMBER NOT NULL primary key, 09:43:49 4 SFILE_NAME VARCHAR2(200) , 09:43:49 5 RFILE_NAME VARCHAR2(200) , 09:43:49 6 RSTATUS VARCHAR2(2) , 09:43:49 7 RINFO VARCHAR2(2000) , 09:43:49 8 MID VARCHAR2(100) , 09:43:49 9 CREATE_TIME DATE 09:43:49 10 ) 09:43:49 11 PARTITION BY RANGE (CREATE_TIME) INTERVAL (numtoyminterval(1, 'month')) 09:43:49 12 ( partition part_t01 values less than(to_date('2019-08', 'yyyy-mm'))); Table created. Elapsed: 00:00:00.02 09:43:50 SYS@testdb(328)> EXEC Dbms_Redefinition.can_redef_table('ZKM','FILE_MS'); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 09:43:54 SYS@testdb(328)> BEGIN 09:43:59 2 DBMS_REDEFINITION.start_redef_table( 09:43:59 3 uname => 'ZKM', 09:43:59 4 orig_table => 'FILE_MS', 09:43:59 5 int_table => 'FILE_MS_TMP'); 09:43:59 6 END; 09:43:59 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.75 09:44:00 SYS@testdb(328)> BEGIN 09:44:03 2 dbms_redefinition.sync_interim_table( 09:44:03 3 uname => 'ZKM', 09:44:03 4 orig_table => 'FILE_MS', 09:44:03 5 int_table => 'FILE_MS_TMP'); 09:44:03 6 END; 09:44:03 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.03 09:44:03 SYS@testdb(328)> BEGIN 09:44:07 2 dbms_redefinition.finish_redef_table( 09:44:07 3 uname => 'ZKM', 09:44:07 4 orig_table => 'FILE_MS', 09:44:07 5 int_table => 'FILE_MS_TMP'); 09:44:07 6 END; 09:44:07 7 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.32 09:44:08 SYS@testdb(328)> select * from dba_tab_privs where grantee='SCOTT'; GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY ------------------------- ------------------------- ------------------------------ ------------------------- ------------------------------ --------- --------- SCOTT ZKM FILE_MS_TMP ZKM SELECT NO NO Elapsed: 00:00:00.05
可以看到，scott用户一开始对FILE_MS有权限，在线重定义后则转变为对FILE_MS_TMP表有查询权限。
可以得知，对表的查询权限和索引，约束类似，转换表定义后是跟着源表来的。
因此，需要在执行dbms_redefinition.finish_redef_table之前对用户scott授权对FILE_MS_TMP的查询权限，这样dbms_redefinition.finish_redef_table转换后FILE_MS_TMP的权限就变为FILE_MS的权限。
正规的在线重定义过程就不给出来了，此处简略实验过程能够说明该问题就行了。