Oracle在线重定义导致其他用户查询权限丢失

 

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的权限。

 

正规的在线重定义过程就不给出来了，此处简略实验过程能够说明该问题就行了。