AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版（兼容Oracle） 运维指南 迁移同步 从Oracle迁移角色、用户及权限至PolarDB PostgreSQL版（兼容Oracle）

从Oracle迁移角色、用户及权限至PolarDB PostgreSQL版（兼容Oracle）

更新时间：
产品详情
我的收藏

将业务从Oracle数据库迁移至PolarDB PostgreSQL版（兼容Oracle）时，需要完整地复制原有的安全体系。这包括所有的用户、角色、角色层级关系以及精确的对象访问权限。手动重新配置大量权限效率低下且容易出错。本文为您介绍如何自动化该过程，确保应用迁移后，其数据库访问权限与在Oracle环境中保持一致，以保障业务的连续性和安全性。

方案架构

迁移过程的核心是利用Oracle的PL/SQL能力动态生成适用于PolarDB PostgreSQL版（兼容Oracle）的DCL和DDL命令。工作流程分为两个主要阶段：

  1. 信息提取（Oracle）：在源Oracle数据库上使用SQLPlus客户端执行PL/SQL脚本。该脚本查询DBA_ROLES、DBA_USERS和DBA_TAB_PRIVS等数据字典视图，将角色、用户和对象权限的定义转换为PolarDB兼容的CREATE ROLE、CREATE USER和GRANT语句。所有生成的语句被导出到一个.sql文件中。

  2. 权限重建（PolarDB）：将生成的.sql文件至可以访问目标PolarDB集群的环境。使用PolarDB-Tools工具连接到PolarDB，并执行此脚本。该脚本会在PolarDB中批量创建角色和用户，并授予对象权限，从而完成权限体系的复制。

准备环境

  1. 完成结构迁移：执行权限迁移前，需使用数据传输服务（DTS）完成从Oracle到PolarDB的表、视图、序列等对象的结构迁移。这是因为GRANT语句依赖于这些对象的存在，否则权限授予会失败。

  2. 准备Oracle数据库用户：准备一个Oracle用户，该用户需至少具备连接权限以及SELECT_CATALOG_ROLE权限。通常，用于DTS结构迁移的用户已满足这些要求。

  3. 准备PolarDB数据库账号：在PolarDB控制台为目标数据库准备一个高权限账号，用于执行后续生成的SQL脚本。

导出权限脚本

根据所选Oracle版本，选择相应的PL/SQL脚本，以生成包含所有待迁移权限信息的SQL脚本。该脚本用于生成角色创建、用户创建和权限授予的语句，并将结果输出至指定文件。

Oracle12c及以上版本

执行以下脚本时，请根据实际情况调整SQL文件的名称与存储位置。

  1. 导出创建角色的语句

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/patch/to/create_role.sql
BEGIN
 FOR rec IN (
 SELECT 'CREATE ROLE ' || ROLE || ';' AS ddl_script
 FROM DBA_ROLES
 WHERE oracle_maintained = 'N' -- 排除系统ROLE
 ORDER BY ROLE
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
 END LOOP;
END;
/
SPOOL OFF
EXIT

  2. 导出创建用户的语句

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/path/to/create_user.sql
BEGIN
 FOR rec IN (
 SELECT 'CREATE USER ' || USERNAME || ';' AS ddl_script
 FROM DBA_USERS
 WHERE oracle_maintained = 'N' -- 排除系统USER
 ORDER BY USERNAME
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
 END LOOP;
END;
/
SPOOL OFF
EXIT

  3. 导出授予对象权限的语句

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET DEFINE OFF
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/path/to/grant_object.sql
DECLARE
 object_type VARCHAR2(100);
BEGIN
 FOR rec IN (
 SELECT PRIVILEGE, p.OWNER, TABLE_NAME, GRANTEE, GRANTABLE, o.object_type
 FROM DBA_TAB_PRIVS p
 JOIN dba_objects o ON p.TABLE_NAME = o.OBJECT_NAME AND o.OWNER = p.OWNER
 WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N')
 OR GRANTEE IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
 OR (
 GRANTEE = 'PUBLIC' AND
 p.owner IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N')
 )
 ORDER BY GRANTEE, p.OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
 ) LOOP
 object_type := rec.object_type;
 IF object_type IN ('TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX PARTITION') THEN
 object_type := NULL;
 END IF;
 IF rec.GRANTABLE = 'YES' then
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.privilege || ' ON ' || object_type || ' "' || rec.owner || '"."' || rec.TABLE_NAME || '" TO ' || rec.grantee || ' WITH GRANT OPTION;');
 else
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.privilege || ' ON ' || object_type || ' "' || rec.owner || '"."' || rec.TABLE_NAME || '" TO ' || rec.grantee || ';');
 end if;
 END LOOP;
END;
/
SPOOL OFF
EXIT

  4. 导出授予角色的语句

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/path/to/grant_role.sql
BEGIN
 FOR rec IN (
 SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION
 FROM DBA_ROLE_PRIVS
 WHERE ((
 GRANTED_ROLE IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N')
 OR GRANTED_ROLE IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
 ) OR (
 GRANTEE IN (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED = 'N')
 OR GRANTEE IN (SELECT ROLE FROM DBA_ROLES WHERE ORACLE_MAINTAINED = 'N')
 ))
 AND grantee NOT IN ('SYS', 'PDBADMIN')
 ORDER BY GRANTEE, GRANTED_ROLE
 ) LOOP
 IF rec.ADMIN_OPTION = 'YES' then
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.GRANTED_ROLE || ' TO ' || rec.grantee || ' WITH ADMIN OPTION;');
 else
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.GRANTED_ROLE || ' TO ' || rec.grantee || ';');
 end if;
 END LOOP;
END;
/
SPOOL OFF
EXIT

Oracle 11

执行以下脚本时，请根据实际情况调整SQL文件的名称与存储位置。

  1. 导出创建角色的语句

    说明

    以下脚本通过预设列表过滤常见的系统角色。如果您的业务环境中有其他需要过滤的系统角色，请在NOT IN (...)和NOT LIKE '...'部分自行添加。

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/patch/to/create_role.sql
BEGIN
 FOR rec IN (
 SELECT 'CREATE ROLE ' || ROLE || ';' AS ddl_script
 FROM DBA_ROLES
 WHERE ROLE NOT IN (
 -- Oracle 11g 常见系统角色
 'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
 'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
 'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
 'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
 'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
 'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
 'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
 'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
 'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
 'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
 'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
 'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
 'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
 'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
 'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
 'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
 'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
 )
 -- 额外过滤条件：排除以特定前缀开头的系统角色
 AND ROLE NOT LIKE 'ORA_%'
 AND ROLE NOT LIKE 'APEX_%'
 AND ROLE NOT LIKE 'FLOWS_%'
 AND ROLE NOT LIKE 'ORACLE_%'
 AND ROLE NOT LIKE 'SYS_%'
 ORDER BY ROLE
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
 END LOOP;
END;
/
SPOOL OFF
EXIT

  2. 导出创建用户的语句

    说明

    以下脚本通过预设列表过滤常见的系统用户。如果您的业务环境中有其他需要过滤的系统用户，请在NOT IN (...)和NOT LIKE '...'部分自行添加。

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/path/to/create_user.sql
BEGIN
 FOR rec IN (
 SELECT 'CREATE USER ' || USERNAME || ';' AS ddl_script
 FROM DBA_USERS
 WHERE USERNAME NOT IN (
 -- Oracle 11g 核心系统用户
 'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
 -- Oracle 组件相关用户
 'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
 'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
 -- OLAP 相关用户
 'OLAPSYS', 'MDDATA',
 -- OWB 相关用户
 'OWBSYS', 'OWBSYS_AUDIT',
 -- APEX 相关用户
 'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
 'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
 -- Spatial 相关用户
 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
 -- 其他系统用户
 'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
 'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
 -- 11g 特有用户
 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
 )
 -- 排除以特定前缀开头的系统用户
 AND USERNAME NOT LIKE 'APEX_%'
 AND USERNAME NOT LIKE 'FLOWS_%'
 AND USERNAME NOT LIKE 'ORA_%'
 AND USERNAME NOT LIKE 'ORACLE_%'
 AND USERNAME NOT LIKE 'SYS_%'
 ORDER BY USERNAME
 ) LOOP
 DBMS_OUTPUT.PUT_LINE(rec.ddl_script);
 END LOOP;
END;
/
SPOOL OFF
EXIT

  3. 导出授予对象权限的语句

    说明

    若您对上述创建角色与创建用户语句中的过滤条件进行了调整，请同步修改以下相关内容，以确保过滤条件的一致性。

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET DEFINE OFF
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/path/to/grant_object.sql
DECLARE
 object_type VARCHAR2(100);
BEGIN
 FOR rec IN (
 SELECT PRIVILEGE, p.OWNER, TABLE_NAME, GRANTEE, GRANTABLE, o.object_type
 FROM DBA_TAB_PRIVS p
 JOIN dba_objects o ON p.TABLE_NAME = o.OBJECT_NAME AND o.OWNER = p.OWNER
 WHERE GRANTEE IN (
 SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
 -- Oracle 11g 核心系统用户
 'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
 -- Oracle 组件相关用户
 'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
 'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
 -- OLAP 相关用户
 'OLAPSYS', 'MDDATA',
 -- OWB 相关用户
 'OWBSYS', 'OWBSYS_AUDIT',
 -- APEX 相关用户
 'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
 'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
 -- Spatial 相关用户
 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
 -- 其他系统用户
 'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
 'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
 -- 11g 特有用户
 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
 )
 -- 排除以特定前缀开头的系统用户
 AND USERNAME NOT LIKE 'APEX_%'
 AND USERNAME NOT LIKE 'FLOWS_%'
 AND USERNAME NOT LIKE 'ORA_%'
 AND USERNAME NOT LIKE 'ORACLE_%'
 AND USERNAME NOT LIKE 'SYS_%'
 )
 OR GRANTEE IN (
 SELECT ROLE FROM DBA_ROLES WHERE ROLE NOT IN (
 -- Oracle 11g 常见系统角色
 'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
 'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
 'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
 'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
 'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
 'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
 'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
 'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
 'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
 'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
 'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
 'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
 'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
 'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
 'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
 'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
 'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
 )
 -- 额外过滤条件：排除以特定前缀开头的系统角色
 AND ROLE NOT LIKE 'ORA_%'
 AND ROLE NOT LIKE 'APEX_%'
 AND ROLE NOT LIKE 'FLOWS_%'
 AND ROLE NOT LIKE 'ORACLE_%'
 AND ROLE NOT LIKE 'SYS_%'
 )
 OR (
 GRANTEE = 'PUBLIC' AND
 p.owner IN (
 SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
 -- Oracle 11g 核心系统用户
 'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
 -- Oracle 组件相关用户
 'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
 'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
 -- OLAP 相关用户
 'OLAPSYS', 'MDDATA',
 -- OWB 相关用户
 'OWBSYS', 'OWBSYS_AUDIT',
 -- APEX 相关用户
 'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
 'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
 -- Spatial 相关用户
 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
 -- 其他系统用户
 'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
 'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
 -- 11g 特有用户
 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
 )
 -- 排除以特定前缀开头的系统用户
 AND USERNAME NOT LIKE 'APEX_%'
 AND USERNAME NOT LIKE 'FLOWS_%'
 AND USERNAME NOT LIKE 'ORA_%'
 AND USERNAME NOT LIKE 'ORACLE_%'
 AND USERNAME NOT LIKE 'SYS_%'
 )
 )
 ORDER BY GRANTEE, p.OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE
 ) LOOP
 object_type := rec.object_type;
 IF object_type IN ('TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX PARTITION') THEN
 object_type := NULL;
 END IF;
 IF rec.GRANTABLE = 'YES' then
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.privilege || ' ON ' || object_type || ' "' || rec.owner || '"."' || rec.TABLE_NAME || '" TO ' || rec.grantee || ' WITH GRANT OPTION;');
 else
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.privilege || ' ON ' || object_type || ' "' || rec.owner || '"."' || rec.TABLE_NAME || '" TO ' || rec.grantee || ';');
 end if;
 END LOOP;
END;
/
SPOOL OFF
EXIT

  4. 导出授予角色的语句

    说明

    若您对上述创建角色与创建用户语句中的过滤条件进行了调整，请同步修改以下相关内容，以确保过滤条件的一致性。

    SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET PAGESIZE 0
SET LINESIZE 2000
SET SERVEROUTPUT ON
-- 执行结果实际存储位置，需要根据实际情况修改
SPOOL /my/path/to/grant_role.sql
BEGIN
 FOR rec IN (
 SELECT GRANTED_ROLE, GRANTEE, ADMIN_OPTION
 FROM DBA_ROLE_PRIVS
 WHERE ((
 GRANTED_ROLE IN (
 SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
 -- Oracle 11g 核心系统用户
 'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
 -- Oracle 组件相关用户
 'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
 'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
 -- OLAP 相关用户
 'OLAPSYS', 'MDDATA',
 -- OWB 相关用户
 'OWBSYS', 'OWBSYS_AUDIT',
 -- APEX 相关用户
 'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
 'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
 -- Spatial 相关用户
 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
 -- 其他系统用户
 'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
 'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
 -- 11g 特有用户
 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
 )
 -- 排除以特定前缀开头的系统用户
 AND USERNAME NOT LIKE 'APEX_%'
 AND USERNAME NOT LIKE 'FLOWS_%'
 AND USERNAME NOT LIKE 'ORA_%'
 AND USERNAME NOT LIKE 'ORACLE_%'
 AND USERNAME NOT LIKE 'SYS_%'
 )
 OR GRANTED_ROLE IN (
 SELECT ROLE FROM DBA_ROLES WHERE ROLE NOT IN (
 -- Oracle 11g 常见系统角色
 'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
 'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
 'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
 'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
 'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
 'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
 'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
 'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
 'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
 'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
 'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
 'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
 'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
 'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
 'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
 'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
 'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
 )
 -- 额外过滤条件：排除以特定前缀开头的系统角色
 AND ROLE NOT LIKE 'ORA_%'
 AND ROLE NOT LIKE 'APEX_%'
 AND ROLE NOT LIKE 'FLOWS_%'
 AND ROLE NOT LIKE 'ORACLE_%'
 AND ROLE NOT LIKE 'SYS_%'
 )
 ) OR (
 GRANTEE IN (
 SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
 -- Oracle 11g 核心系统用户
 'SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN',
 -- Oracle 组件相关用户
 'CTXSYS', 'MDSYS', 'ORDSYS', 'ORDDATA', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
 'WMSYS', 'EXFSYS', 'LBACSYS', 'XDB', 'ANONYMOUS', 'XS$NULL',
 -- OLAP 相关用户
 'OLAPSYS', 'MDDATA',
 -- OWB 相关用户
 'OWBSYS', 'OWBSYS_AUDIT',
 -- APEX 相关用户
 'APEX_030200', 'APEX_040000', 'APEX_040100', 'APEX_040200', 'APEX_050000',
 'APEX_PUBLIC_USER', 'FLOWS_FILES', 'FLOWS_030000', 'FLOWS_040000',
 -- Spatial 相关用户
 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR',
 -- 其他系统用户
 'DIP', 'ORACLE_OCM', 'APPQOSSYS', 'MGMT_VIEW', 'SCOTT', 'HR',
 'TSMSYS', 'WKPROXY', 'WK_TEST', 'PERFSTAT', 'TRACESVR',
 -- 11g 特有用户
 'GSMADMIN_INTERNAL', 'GSMCATUSER', 'GSMUSER', 'SYSBACKUP', 'SYSDG', 'SYSKM'
 )
 -- 排除以特定前缀开头的系统用户
 AND USERNAME NOT LIKE 'APEX_%'
 AND USERNAME NOT LIKE 'FLOWS_%'
 AND USERNAME NOT LIKE 'ORA_%'
 AND USERNAME NOT LIKE 'ORACLE_%'
 AND USERNAME NOT LIKE 'SYS_%'
 )
 OR GRANTEE IN (
 SELECT ROLE FROM DBA_ROLES WHERE ROLE NOT IN (
 -- Oracle 11g 常见系统角色
 'CONNECT', 'RESOURCE', 'DBA', 'SELECT_CATALOG_ROLE',
 'EXECUTE_CATALOG_ROLE', 'DELETE_CATALOG_ROLE', 'EXP_FULL_DATABASE',
 'IMP_FULL_DATABASE', 'LOGSTDBY_ADMINISTRATOR', 'RECOVERY_CATALOG_OWNER',
 'SCHEDULER_ADMIN', 'HS_ADMIN_SELECT_ROLE', 'HS_ADMIN_EXECUTE_ROLE',
 'HS_ADMIN_ROLE', 'GLOBAL_AQ_USER_ROLE', 'OEM_ADVISOR', 'OEM_MONITOR',
 'MGMT_USER', 'DATAPUMP_EXP_FULL_DATABASE', 'DATAPUMP_IMP_FULL_DATABASE',
 'ADM_PARALLEL_EXECUTE_TASK', 'GATHER_SYSTEM_STATISTICS',
 'OPTIMIZER_PROCESSING_RATE', 'ACCESS_CATALOG_ROLE', 'JAVAUSERPRIV',
 'JAVAIDPRIV', 'JAVASYSPRIV', 'JAVADEBUGPRIV', 'EJBCLIENT', 'JMXSERVER',
 'JAVA_ADMIN', 'JAVA_DEPLOY', 'AUTHENTICATEDUSER', 'XDB_SET_INVOKER',
 'XDB_WEBSERVICES', 'XDB_WEBSERVICES_WITH_PUBLIC', 'XDB_WEBSERVICES_OVER_HTTP',
 'XDBADMIN', 'XDB_WEBSERVICES_OVER_HTTPS', 'CTXAPP', 'ORDADMIN',
 'WM_ADMIN_ROLE', 'OLAP_USER', 'OLAP_DBA', 'OLAP_XS_ADMIN',
 'CSW_USR_ROLE', 'WFS_USR_ROLE', 'SPATIAL_CSW_ADMIN',
 'SPATIAL_WFS_ADMIN', 'LBAC_DBA', 'APEX_ADMINISTRATOR_ROLE',
 'FLOWS_FILES_ROLE', 'AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE',
 'CWM_USER', 'DBFS_ROLE', 'OLAPI_TRACE_USER', 'OWB$CLIENT',
 'OWB_DESIGNCENTER_VIEW', 'OWB_USER'
 )
 -- 额外过滤条件：排除以特定前缀开头的系统角色
 AND ROLE NOT LIKE 'ORA_%'
 AND ROLE NOT LIKE 'APEX_%'
 AND ROLE NOT LIKE 'FLOWS_%'
 AND ROLE NOT LIKE 'ORACLE_%'
 AND ROLE NOT LIKE 'SYS_%'
 )
 ))
 AND grantee NOT IN ('SYS', 'PDBADMIN')
 ORDER BY GRANTEE, GRANTED_ROLE
 ) LOOP
 IF rec.ADMIN_OPTION = 'YES' then
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.GRANTED_ROLE || ' TO ' || rec.grantee || ' WITH ADMIN OPTION;');
 else
 DBMS_OUTPUT.PUT_LINE('GRANT ' || rec.GRANTED_ROLE || ' TO ' || rec.grantee || ';');
 end if;
 END LOOP;
END;
/
SPOOL OFF
EXIT

在PolarDB中导入权限信息

在目标PolarDB集群中执行上一步生成的SQL脚本，完成权限创建。

  1. 连接到PolarDB：使用PolarDB-Tools工具和准备好的高权限账号连接到目标PolarDB数据库。

    psql -h <host> -p <port> -U <username> -d <dbname>

  2. 执行脚本：在命令行中，使用\i命令执行上一步骤生成的SQL文件。在执行过程中，请根据实际情况替换SQL文件的名称与目录。

    \i /tmp/create_role.sql

  3. （可选）权限验证：建议进行全面的权限验证，并针对在PolarDB中无法直接映射的Oracle特有权限进行手动适配，以确保应用系统的安全性与功能的完整性。

在PolarDB中进行兼容性调整

导入基础权限后，需要继续在PolarDB集群中执行以下操作以确保业务兼容性。

  1. 为所有用户设置密码：由于上述创建用户的语句未包含原始密码，因此需为每个新创建的用户设置密码。

    -- 将 <user_name> 和 <password> 替换为实际的用户名和密码
ALTER USER <user_name> WITH PASSWORD <password>;

  2. 调整用户与其同名模式的所属关系：在Oracle数据库中，用户（USER）与模式（SCHEMA）是等同的概念，每个用户默认拥有其同名模式中所有对象的权限。然而，在PolarDB中，用户与模式是相互独立的概念，用户不一定拥有与其同名模式的权限。为了与Oracle保持一致，PolarDB中将每个模式（SCHEMA）及其内部对象的所有权（OWNER）更改为其同名用户，从而确保用户对其同名模式具有相应的权限。

    DECLARE
    user_rec RECORD;
    obj_rec RECORD;
    schema_rec RECORD;
    schema_name NAME;
    new_owner NAME;
    current_user_objects INTEGER := 0;
    sql_stmt TEXT;
BEGIN
    FOR user_rec IN
        SELECT DISTINCT u.usename as username, usesysid FROM pg_user u
        WHERE u.usesuper = false
            AND EXISTS (
                SELECT 1 FROM information_schema.schemata s
                WHERE s.schema_name = u.usename
            )
        ORDER BY u.usename
    LOOP
        schema_name := user_rec.username;
        new_owner := user_rec.username;
        current_user_objects := 0;

        RAISE NOTICE 'process schema %, user %', schema_name, new_owner;

        -- schema
        IF (SELECT 1 FROM pg_namespace WHERE nspname = schema_name and nspowner != user_rec.usesysid) THEN
            sql_stmt := format('ALTER SCHEMA %I OWNER TO %I', schema_name, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            commit;
        END IF;

        -- table
        FOR obj_rec IN
            SELECT schemaname, tablename FROM pg_tables WHERE schemaname = schema_name and tableowner <> new_owner
        LOOP
            sql_stmt := format('ALTER TABLE %I.%I OWNER TO %I', obj_rec.schemaname, obj_rec.tablename, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- view
        FOR obj_rec IN
            SELECT schemaname, viewname FROM pg_views WHERE schemaname = schema_name and viewowner <> new_owner
        LOOP
            sql_stmt := format('ALTER VIEW %I.%I OWNER TO %I', obj_rec.schemaname, obj_rec.viewname, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- mat view
        FOR obj_rec IN
            SELECT schemaname, matviewname FROM pg_matviews WHERE schemaname = schema_name and matviewowner <> new_owner
        LOOP
            sql_stmt := format('ALTER MATERIALIZED VIEW %I.%I OWNER TO %I', obj_rec.schemaname, obj_rec.matviewname, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- sequence
        FOR obj_rec IN
            SELECT schemaname, sequencename FROM pg_sequences WHERE schemaname = schema_name and sequenceowner <> new_owner
        LOOP
            sql_stmt := format('ALTER SEQUENCE %I.%I OWNER TO %I', obj_rec.schemaname, obj_rec.sequencename, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- function
        FOR obj_rec IN
            SELECT n.nspname as schemaname, p.proname as funcname, pg_get_function_identity_arguments(p.oid) as args
            FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE n.nspname = schema_name AND p.prokind = 'f'
            and nsppkgns = 0 -- exclude package functions
            and p.proowner <> new_owner::regrole
        LOOP
            sql_stmt := format('ALTER FUNCTION %I.%I(%s) OWNER TO %I', obj_rec.schemaname, obj_rec.funcname, obj_rec.args, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- procedure
        FOR obj_rec IN
            SELECT n.nspname as schemaname, p.proname as procname, pg_get_function_identity_arguments(p.oid) as args
            FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid
            WHERE n.nspname = schema_name AND p.prokind = 'p'
            and nsppkgns = 0 -- exclude package procedures
            and p.proowner <> new_owner::regrole
        LOOP
            sql_stmt := format('ALTER PROCEDURE %I.%I(%s) OWNER TO %I', obj_rec.schemaname, obj_rec.procname, obj_rec.args, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- package
        FOR obj_rec IN
            SELECT n.nspname as schemaname, p.pkgname as pkgname
            FROM pg_pkg p JOIN pg_namespace n ON p.pkgnamespace = n.oid
            WHERE n.nspname = schema_name
            and p.pkgowner <> new_owner::regrole
        LOOP
            sql_stmt := format('ALTER PACKAGE %I.%I OWNER TO %I', obj_rec.schemaname, obj_rec.pkgname, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        -- synonym
        FOR obj_rec IN
            SELECT n.nspname as schemaname, s.synname as synname
            FROM pg_synonym s JOIN pg_namespace n ON s.synnamespace= n.oid
            WHERE n.nspname = schema_name AND n.nsppkgns = 0
            and s.owner <> new_owner::regrole
        LOOP
            sql_stmt := format('ALTER SYNONYM %I.%I OWNER TO %I', obj_rec.schemaname, obj_rec.synname, new_owner);
            DBMS_OUTPUT.PUT_LINE(sql_stmt);
            EXECUTE sql_stmt;
            current_user_objects := current_user_objects + 1;
        END LOOP;
        commit;

        IF current_user_objects > 0 THEN
            RAISE NOTICE 'processed % objects in schema %', current_user_objects, schema_name;
        END IF;
    END LOOP;
END;

  3. 授予模式的使用权限：在Oracle数据库中，访问某个对象时，仅需校验当前用户是否具备该对象的权限。而在PolarDB中，则需首先校验当前用户是否拥有该对象所属模式（SCHEMA）的权限，随后再校验是否拥有该对象的权限。为保持与Oracle的一致性，可以在PolarDB中执行如下脚本，开放所有非系统模式的使用权限给所有用户，从而实现仅校验对象本身权限的效果。

    DECLARE
 schema_rec RECORD;
 sql_stmt TEXT;
BEGIN
 FOR schema_rec IN
 SELECT nspname FROM pg_namespace n
 WHERE nspname NOT IN ('information_schema', 'pg_catalog', 'pg_toast',
 'polar_catalog', 'sys', 'cron', 'wmsys', 'polar_feature_utils',
 'pg_bitmapindex', 'polar_dbms_stats', 'public')
 AND nspname NOT LIKE 'pg_temp_%' AND nspname NOT LIKE 'pg_toast_temp_%'
 AND nsppkgns = 0
 ORDER BY nspname
 LOOP
 sql_stmt := format('GRANT USAGE ON SCHEMA %I TO PUBLIC', schema_rec.nspname);
 DBMS_OUTPUT.PUT_LINE(sql_stmt);
 EXECUTE sql_stmt;
 END LOOP;
END;
上一篇：体验智能SQL转换领航助手（Migration on Pilot）下一篇：购买集群