关键词:DMDRS、达梦数据复制软件、Oracle、数据同步、同步用户、最小权限、权限脚本
-- ## 基础权限
-- 需要创建会话权限
GRANT CONNECT TO DMDRS;
-- ## 装载相关权限
-- 需要非同步用户下待装载表/序列的查询权限
GRANT SELECT ON <待装载表/序列的模式名>.<待装载表名/序列名> TO DMDRS;
-- 需要系统表、系统字典和动态视图的查询权限
GRANT SELECT ON SYS.OBJ$ TO DMDRS;
GRANT SELECT ON SYS.TAB$ TO DMDRS;
GRANT SELECT ON SYS.USER$ TO DMDRS;
GRANT SELECT ON SYS.NTAB$ TO DMDRS;
GRANT SELECT ON SYS.SEG$ TO DMDRS;
GRANT SELECT ON SYS.EXTERNAL_TAB$ TO DMDRS;
GRANT SELECT ON DBA_CONS_COLUMNS TO DMDRS;
GRANT SELECT ON DBA_CONSTRAINTS TO DMDRS;
GRANT SELECT ON V_$INSTANCE TO DMDRS;
GRANT SELECT ON GV_$INSTANCE TO DMDRS;
GRANT SELECT ON SYS.CDEF$ TO DMDRS;
GRANT SELECT ON SYS.COLTYPE$ TO DMDRS;
GRANT SELECT ON SYS.TYPE$ TO DMDRS;
GRANT SELECT ON SYS.ATTRIBUTE$ TO DMDRS;
GRANT SELECT ON SYS.COLLECTION$ TO DMDRS;
GRANT SELECT ON SYS.PARTOBJ$ TO DMDRS;
GRANT SELECT ON SYS.COM$ TO DMDRS;
GRANT SELECT ON V_$DATABASE TO DMDRS;
GRANT SELECT ON SYS.CON$ TO DMDRS;
GRANT SELECT ON SYS.IND$ TO DMDRS;
GRANT SELECT ON SYS.COL$ TO DMDRS;
GRANT SELECT ON SYS.CCOL$ TO DMDRS;
GRANT SELECT ON SYS.TS$ TO DMDRS;
GRANT SELECT ON SYS.DEFERRED_STG$ TO DMDRS;
GRANT SELECT ON SYS.LOB$ TO DMDRS;
GRANT SELECT ON SYS.LOBFRAG$ TO DMDRS;
GRANT SELECT ON SYS.TABPART$ TO DMDRS;
GRANT SELECT ON SYS.TABSUBPART$ TO DMDRS;
GRANT SELECT ON SYS.TABCOMPART$ TO DMDRS;
GRANT SELECT ON SYS.SUBPARTCOL$ TO DMDRS;
GRANT SELECT ON SYS.SEQ$ TO DMDRS;
GRANT SELECT ON DBA_INDEXES TO DMDRS;
GRANT SELECT ON DBA_IND_COLUMNS TO DMDRS;
GRANT SELECT ON SYS.PARTCOL$ TO DMDRS;
GRANT SELECT ON SYS.DEFSUBPART$ TO DMDRS;
GRANT SELECT ON SYS.SOURCE$ TO DMDRS;
GRANT SELECT ON SYS.TYPED_VIEW$ TO DMDRS;
GRANT SELECT ON SYS.VIEW$ TO DMDRS;
GRANT SELECT ON SYS.SYN$ TO DMDRS;
GRANT SELECT ON "SYS"."_ALL_SYNONYMS_TREE" TO DMDRS;
GRANT SELECT ON DBA_EXTENTS TO DMDRS;
GRANT SELECT ON SYS.SNAP$ TO DMDRS;
GRANT SELECT ON SYS.SUM$ TO DMDRS;
GRANT SELECT ON V_$PARAMETER TO DMDRS;
GRANT SELECT ON SYS.TRIGGER$ TO DMDRS;
GRANT SELECT ON SYS.V_$THREAD TO DMDRS;
GRANT SELECT ON V_$DATABASE_INCARNATION TO DMDRS;
GRANT SELECT ON V_$TRANSACTION TO DMDRS;
GRANT SELECT ON V_$LOCKED_OBJECT TO DMDRS;
GRANT SELECT ON GV_$TRANSACTION TO DMDRS;
GRANT SELECT ON GV_$LOCKED_OBJECT TO DMDRS;
GRANT SELECT ON DBA_PART_INDEXES TO DMDRS;
GRANT SELECT ON SYS.LOBCOMPPART$ TO DMDRS;
GRANT SELECT ON SYS.PARTLOB$ TO DMDRS;
GRANT SELECT ON SYS.ECOL$ TO DMDRS;
GRANT SELECT ON SYS.ATTRCOL$ TO DMDRS;
GRANT SELECT ON SYS.ICOL$ TO DMDRS;
GRANT SELECT ON SYS.METHOD$ TO DMDRS;
GRANT SELECT ON SYS.RESULT$ TO DMDRS;
GRANT SELECT ON SYS.PARAMETER$ TO DMDRS;
GRANT SELECT ON SYS.PLAN_TABLE$ TO DMDRS;
-- Oracle 12c及以上版本且带有容器的数据库需要容器视图的查询权限
GRANT SELECT ON V_$CONTAINERS TO DMDRS;
-- Oracle DG且源DMDRS部署在DG环境的备数据库
GRANT SELECT ON V_$MANAGED_STANDBY TO DMDRS;
GRANT SELECT ON V_$DATAGUARD_CONFIG TO DMDRS;
-- 需要锁表权限
GRANT LOCK ANY TABLE TO DMDRS;
-- 装载使用FLASHBACK掩码,需要闪回权限
GRANT FLASHBACK ANY TABLE to DMDRS;
-- ## 同步相关权限
-- 需要获取系统SCN权限
GRANT EXECUTE ON DBMS_FLASHBACK TO DMDRS;
-- 需要系统表、系统字典和动态视图的查询权限
GRANT SELECT ON SYS.COL$ TO DMDRS;
GRANT SELECT ON SYS.LOB$ TO DMDRS;
GRANT SELECT ON SYS.TABPART$ TO DMDRS;
GRANT SELECT ON SYS.TABCOMPART$ TO DMDRS;
GRANT SELECT ON SYS.LOBFRAG$ TO DMDRS;
GRANT SELECT ON V_$LOGFILE TO DMDRS;
GRANT SELECT ON V_$LOG TO DMDRS;
GRANT SELECT ON V_$ARCHIVED_LOG TO DMDRS;
GRANT SELECT ON SYS.TABSUBPART$ TO DMDRS;
GRANT SELECT ON SYS.CCOL$ TO DMDRS;
GRANT SELECT ON SYS.TS$ TO DMDRS;
GRANT SELECT ON SYS.TYPE$ TO DMDRS;
GRANT SELECT ON SYS.ATTRIBUTE$ TO DMDRS;
GRANT SELECT ON SYS.COLLECTION$ TO DMDRS;
GRANT SELECT ON V_$SESSION TO DMDRS;
GRANT SELECT ON V_$SESSTAT TO DMDRS;
GRANT SELECT ON V_$STATNAME TO DMDRS;
-- Oracle DG且源DMDRS部署在DG环境的备数据库
GRANT SELECT ON V_$STANDBY_LOG TO DMDRS;
-- 若需要同步XMLTYPE类型的数据,需要将XDB模式下XDB$TTSET、表名开头为X$NM和X$QN的表的查询权限赋予用户。若使用可插拔数据库,管理员用户需先切换到可插拔数据库再授权
GRANT SELECT ON XDB.XDB$TTSET TO DMDRS;
GRANT SELECT ON XDB.<以X$NM开头的表名> TO DMDRS;
GRANT SELECT ON XDB.<以X$QN开头的表名> TO DMDRS;
-- ## 基础权限
-- 启动时需要在目标数据库中创建辅助表和辅助存储过程
GRANT RESOURCE TO DMDRS;
-- 在连接数据库时需要创建会话
GRANT CONNECT TO DMDRS;
-- 启动时需要查询数据库的相关参数信息
GRANT SELECT ON V_$INSTANCE TO DMDRS;
GRANT SELECT ON V_$PARAMETER TO DMDRS;
GRANT SELECT ON GV_$SESSION TO DMDRS;
GRANT SELECT ON GV_$LOCK TO DMDRS;
GRANT SELECT ON DBA_TAB_PARTITIONS TO DMDRS;
-- ## 装载与同步相关权限
-- 需要如下系统表、系统字典及动态视图的查询权限
GRANT SELECT ON SYS.OBJ$ TO DMDRS;
GRANT SELECT ON SYS.USER$ TO DMDRS;
GRANT SELECT ON SYS.COL$ TO DMDRS;
GRANT SELECT ON SYS.COLTYPE$ TO DMDRS;
GRANT SELECT ON SYS.CCOL$ TO DMDRS;
GRANT SELECT ON SYS.CDEF$ TO DMDRS;
GRANT SELECT ON SYS.ATTRCOL$ TO DMDRS;
GRANT SELECT ON DBA_IND_COLUMNS TO DMDRS;
GRANT SELECT ON DBA_INDEXES TO DMDRS;
GRANT SELECT ON SYS.IND$ TO DMDRS;
GRANT SELECT ON SYS.SEQ$ TO DMDRS;
GRANT SELECT ON DBA_TAB_SUBPARTITIONS TO DMDRS;
GRANT SELECT ON DBA_CONSTRAINTS TO DMDRS;
GRANT SELECT ON GV_$INSTANCE TO DMDRS;
GRANT SELECT ON V_$DATABASE TO DMDRS;
GRANT SELECT ON SYS.PARTOBJ$ TO DMDRS;
-- 需要非同步用户模式下已存在的待同步表/序列的操作权限
GRANT INSERT ON <目标表的模式名>.<目标表名> TO DMDRS;
GRANT UPDATE ON <目标表的模式名>.<目标表名> TO DMDRS;
GRANT DELETE ON <目标表的模式名>.<目标表名> TO DMDRS;
GRANT SELECT ON <目标序列的模式名>.<目标序列名> TO DMDRS;
-- 需要非同步用户模式下未存在的表/序列的数据的操作权限。也可以在目标同步表创建后,同上行所述单独为每张表赋予操作权限
GRANT INSERT ANY TABLE TO DMDRS;
GRANT UPDATE ANY TABLE TO DMDRS;
GRANT DELETE ANY TABLE TO DMDRS;
GRANT SELECT ANY TABLE TO DMDRS;
GRANT SELECT ANY SEQUENCE TO DMDRS;
GRANT ALTER ANY SEQUENCE TO DMDRS;
-- 需要非同步用户模式下对象的DDL权限
GRANT CREATE ANY <对象类型> TO DMDRS;
GRANT ALTER ANY <对象类型> TO DMDRS;
GRANT DROP ANY <对象类型> TO DMDRS;
-- 需要自定义类型对象的查询权限
GRANT SELECT ON SYS.TYPE$ TO DMDRS;
GRANT SELECT ON SYS.ATTRIBUTE$ TO DMDRS;
GRANT SELECT ON SYS.COLLECTION$ TO DMDRS;
-- 同步用户创建时未指定表空间且未配额时,需要修改用户表空间配额
ALTER USER DMDRS QUOTA UNLIMITED ON USERS;
如果你在使用AI助手时想了解Oracle在DMDRS数据同步中作为源库或目标库的同步用户最小权限配置,这篇文章可以作为参考。
文章
阅读量
获赞
