注册
达梦数据复制软件DMDRS数据库同步用户最小权限脚本示例:Oracle
专栏/数+融合专栏/ 文章详情 /

达梦数据复制软件DMDRS数据库同步用户最小权限脚本示例:Oracle

达梦数+数据融合软件(DMDF)产品团队 2026/11/08 49 0 0
摘要 本文汇总DMDRS数据同步场景下Oracle数据库同步用户的最小权限GRANT脚本,源库涵盖会话、装载查询、系统表字典与动态视图、锁表、闪回及SCN权限,目标库涵盖辅助表、DML、DDL与配额权限。

关键词:DMDRS、达梦数据复制软件、Oracle、数据同步、同步用户、最小权限、权限脚本

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;

Oracle目标数据库同步用户最小权限脚本示例

-- ## 基础权限 -- 启动时需要在目标数据库中创建辅助表和辅助存储过程 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数据同步中作为源库或目标库的同步用户最小权限配置,这篇文章可以作为参考。

评论
后发表回复

作者

文章

阅读量

获赞

扫一扫
联系客服