---
title: "源端 Oracle 数据库的最小化权限 | OceanBase 文档中心"
description: 源端 Oracle 数据库的最小化权限 本文为您介绍以 Oracle 数据库作为源端时，数据迁移各阶段需要的最小化权限。 创建数据源 GRANT ALTER SESSION to oms_user; GRANT CREATE SESSION to oms_user; GRANT CONNECT to oms_user…
---
切换语言

- 中文站 - 简体中文
- International - English
- 日本站 - 日本語

文档反馈![](https://mdn.alipayobjects.com/huamei_22khvb/afts/img/A*L03BS6f-o40AAAAAAAAAAAAADiGDAQ/original) 迁移服务 OMSV 4.2.2 企业版

# 源端 Oracle 数据库的最小化权限

更新时间：2024-04-10 23:00:08

本文为您介绍以 Oracle 数据库作为源端时，数据迁移各阶段需要的最小化权限。

## 创建数据源

```sql
GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on DUAL to oms_user;
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on PRODUCT_COMPONENT_VERSION to oms_user;
GRANT SELECT on V_$DATABASE to oms_user;
GRANT SELECT ANY DICTIONARY to oms_user;

```

## 结构迁移

```sql
GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

-- 待迁移的所有表的 SELECT 权限
GRANT SELECT on any-replicated-table to oms_user;

```

## 全量迁移

```sql
GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on DBA_CONSTRAINTS to oms_user;
GRANT SELECT on DBA_CONS_COLUMNS to oms_user;
GRANT SELECT on DBA_EXTENTS to oms_user;
GRANT SELECT on DBA_INDEXES to oms_user;
GRANT SELECT on DBA_IND_COLUMNS to oms_user;
GRANT SELECT on DBA_MVIEWS to oms_user;
GRANT SELECT on DBA_MVIEW_LOGS to oms_user;
GRANT SELECT on DBA_OBJECTS to oms_user;
GRANT SELECT on DBA_PART_KEY_COLUMNS to oms_user;
GRANT SELECT on DBA_SUBPART_KEY_COLUMNS to oms_user;
GRANT SELECT on DBA_TABLES to oms_user;
GRANT SELECT on DBA_TAB_COLS to oms_user;
GRANT SELECT on DBA_TAB_COLUMNS to oms_user;
GRANT SELECT on DBA_TAB_PARTITIONS to oms_user;
GRANT SELECT on DBA_TAB_SUBPARTITIONS to oms_user;
GRANT SELECT on DBA_USERS to oms_user;
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on SYS.SMON_SCN_TIME to oms_user;
GRANT SELECT on V_$INSTANCE to oms_user;
GRANT SELECT on V_$NLS_PARAMETERS to oms_user;

```

## 增量同步

```sql
GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on ALL_CONSTRAINTS to oms_user;
GRANT SELECT on ALL_INDEXES to oms_user;
GRANT SELECT on ALL_IND_COLUMNS to oms_user;
-- 查询对象的一些属性信息
GRANT SELECT on ALL_OBJECTS to oms_user;
GRANT SELECT on ALL_TAB_COLS to oms_user;
-- 查询黑白名单用户
GRANT SELECT on ALL_USERS to oms_user;
GRANT SELECT on DBA_CONSTRAINTS to oms_user;
GRANT SELECT on DBA_CONS_COLUMNS to oms_user;
GRANT SELECT on DBA_MVIEWS to oms_user;
-- 查询 all_objects 需要排除掉物化视图
GRANT SELECT on DBA_MVIEW_LOGS to oms_user;
-- 查询数据库当前时间，数据库时区
GRANT SELECT on DUAL to oms_user;
-- 查询 RAC 有几个节点
GRANT SELECT on GV_$INSTANCE to oms_user;
-- 查询数据库字符集
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on SYS.CDEF$ to oms_user;
GRANT SELECT on SYS.COL$ to oms_user;
GRANT SELECT on SYS.CON$ to oms_user;
GRANT SELECT on SYS.ICOL$ to oms_user;
GRANT SELECT on SYS.OBJ$ to oms_user;
-- 查询黑名单用户的 USER#，据此过滤黑名单用户的表
GRANT SELECT on SYS.USER$ to oms_user;
-- 查询归档文件
GRANT SELECT on V_$ARCHIVED_LOG to oms_user;
GRANT SELECT on V_$ARCHIVE_DEST to oms_user;
-- 查询是否开启补充日志，Oracle 打开方式等
GRANT SELECT on V_$DATABASE to oms_user;
GRANT SELECT on V_$LOG to oms_user;
-- 查询在线日志
GRANT SELECT on V_$LOGFILE to oms_user;
-- 查询 logminer 返回的结果集
GRANT SELECT on V_$LOGMNR_CONTENTS to oms_user;
GRANT SELECT on V_$PARAMETER to oms_user;

GRANT EXECUTE on SYS.DBMS_LOGMNR to oms_user;

-- 待迁移的所有表的 SELECT 权限。如果在增量同步过程中有新增表也需要同步的话，也需要具备该表的 SELECT 权限
GRANT SELECT on any-replicated-table to oms_user;

-- 待迁移的所有表的 flashback 权限和 SELECT ANY TRANSACTION 的权限 （默认不使用 Flashback Query，此时无需该权限）
GRANT FLASHBACK on any-replicated-table to oms_user;
GRANT SELECT ANY TRANSACTION to oms_user;

-- 12c 及之后版本需要授予下述权限
GRANT LOGMINING TO oms_user;  
GRANT SELECT on DBA_PDBS to oms_user;

```

如果存在以下情况，需要额外授予权限：

- 如果是 Oracle 数据库 12C 及之后版本，增量同步时需要额外授予下述权限。

  ```sql
  GRANT LOGMINING TO oms_user;  
  GRANT SELECT on DBA_PDBS to oms_user;

  ```
 - 如果增量同步时需要开启 Flashback Query（默认未开启），请额外授予下述权限。

  ```sql
  GRANT FLASHBACK on any-replicated-table to oms_user;
  GRANT SELECT ANY TRANSACTION to oms_user;

  ```
 - 如果迁移源端为 12C/18C/19C 的可插拔数据库 Pluggable DataBase（PDB），拉取 PDB 的账号需要是 Common 用户。并且，授予的权限均需加上 `CONTAINER=ALL`。

  ```sql
  alter session set container=CDB$ROOT;
  create user C##XXX identified by yyy;

  ```

  上述授权语句均需加上 `CONTAINER=ALL`，需要同步的业务表的授权除外。例如，`GRANT ALTER SESSION to oms_user;` 需要调整为 `GRANT ALTER SESSION to C##XXX CONTAINER=ALL;`。
 - 如果增量同步过程中，需要同步新增表的增量数据，请授予该新增表 SELECT 权限。

  如果创建数据迁移项目前，无法确定有哪些新增表，则需要授予 `SELECT ANY TABLE` 权限。

## 正向切换

```sql
GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

GRANT SELECT on ALL_CONSTRAINTS to oms_user;
GRANT SELECT on ALL_CONS_COLUMNS to oms_user;
GRANT SELECT on ALL_IND_EXPRESSIONS to oms_user;
GRANT SELECT on ALL_LOBS to oms_user;
GRANT SELECT on ALL_MVIEWS to oms_user;
GRANT SELECT on ALL_MVIEW_LOGS to oms_user;
GRANT SELECT on ALL_TABLES to oms_user;
GRANT SELECT on ALL_TAB_COLUMNS to oms_user;
GRANT SELECT on ALL_TAB_PRIVS to oms_user;
GRANT SELECT on ALL_TYPES to oms_user;
GRANT SELECT on ALL_USERS to oms_user;
GRANT SELECT on ALL_VIEWS to oms_user;
GRANT SELECT on DUAL to oms_user;
GRANT SELECT on NLS_DATABASE_PARAMETERS to oms_user;
GRANT SELECT on PRODUCT_COMPONENT_VERSION to oms_user;
GRANT SELECT on USER_ROLE_PRIVS to oms_user;
GRANT SELECT on V_$DATABASE to oms_user;
GRANT SELECT on V_$PWFILE_USERS to oms_user;

```

## 反向增量

```sql
GRANT ALTER SESSION to oms_user;
GRANT CREATE SESSION to oms_user;
GRANT CONNECT to oms_user;

-- 创建内部事务表，以保证无主键表的数据质量
GRANT CREATE ANY TABLE to oms_user;
-- 删除内部事务表，按分区删除更快
GRANT DROP ANY TABLE to oms_user;
GRANT INSERT ANY TABLE to oms_user;
GRANT DELETE ANY TABLE to oms_user;
GRANT UPDATE ANY TABLE to oms_user;
GRANT UNLIMITED TABLESPACE to oms_user;

```

如果需要反向同步 DDL，还需要授予相关 DDL 的权限。示例如下：

```sql
GRANT ALTER ANY TABLE to oms_user;

```

 上一篇 下一篇 ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线