全部產品
Search
文件中心

ApsaraDB for OceanBase:源端 Oracle 資料庫的最小化許可權

更新時間:Jul 01, 2024

本文為您介紹以 Oracle 資料庫作為源端時,資料移轉各階段需要的最小化許可權。

建立資料來源

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;

結構遷移

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;

全量遷移

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;

-- 待遷移的所有表的 SELECT 許可權
GRANT SELECT on any-replicated-table to oms_user;

增量同步處理

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 及以上版本,增量同步處理時需要額外授予下述許可權。

    GRANT LOGMINING TO oms_user;  
    GRANT SELECT on DBA_PDBS to oms_user;
  • 如果增量同步處理時需要開啟 Flashback Query(預設未開啟),請額外授予下述許可權。

    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

    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 許可權。

正向切換

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;

反向增量

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 的許可權。樣本如下:

GRANT ALTER ANY TABLE to oms_user;