ORA-31685: Object type DB_LINK:"NCAOODS"."LINK_SNAPSHOTM" failed due to insufficient privileges. Fai

一.问题描述

2014/11/11导入数据到CPDWH01时出现创建db link失败的报错，使用system或sys用户导入数据的时候都出现以下报错：

Import: Release 11.2.0.4.0 - Production on Tue Nov 11 09:28:04 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR dumpfile=CPDWH01_COMP_%U.dmp logfile=CPDWH01_COMP_imp.log parallel=12
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-31685: Object type DB_LINK:"NCAOODS"."LINK_SNAPSHOTM" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "LINK_SNAPSHOTM"  CONNECT TO "NCAOWIIM" IDENTIFIED BY VALUES ':1'  USING 'CPWII02'
ORA-31685: Object type DB_LINK:"NCAOODS"."LINK_SNAPSHOT" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "LINK_SNAPSHOT"  CONNECT TO "NCAOWII" IDENTIFIED BY VALUES ':1'  USING 'CPWII02'
ORA-31685: Object type DB_LINK:"NCAOODS"."LINK_WII" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "LINK_WII"  CONNECT TO "NXXXWII" IDENTIFIED BY VALUES ':1'  USING 'CPWII01'
ORA-31685: Object type DB_LINK:"QS"."LINK_SNAPSHOT" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "LINK_SNAPSHOT"  CONNECT TO "NCAOWII" IDENTIFIED BY VALUES ':1'  USING 'CPWII02'
ORA-31685: Object type DB_LINK:"QS"."LINK_SNAPSHOTM" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "LINK_SNAPSHOTM"  CONNECT TO "NCAOWIIM" IDENTIFIED BY VALUES ':1'  USING 'CPWII02'
ORA-31685: Object type DB_LINK:"QS"."LINK_WII" failed due to insufficient privileges. Failing sql is:
CREATE DATABASE LINK "LINK_WII"  CONNECT TO "NXXXWII" IDENTIFIED BY VALUES ':1'  USING 'CPWII01'
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT

二.问题分析

查找相关资料后发现有多个案例是导入数据时出现view/table等,都出现ORA-31685的报错,暴露的核心问题都是权限方面的问题:

1.用户没有创建对应表的权限;

2.用户没有在指定表空间上创建对象的权限；

故考虑确认是否是创建db_link全新啊方面的问题:

1.检查发现有一部分owner的db_link可以成功导入(无法导入的仅NCAOODS和QS两个Owner)，且tns名都没有问题，导入成功的如下:

OWNER                DB_LINK                        USERNAME             HOST            CREATED-------------------- ------------------------------ -------------------- --------------- -------------------CMSAIO               LINK_WII                       NXXXWII              CPWII01         2014-11-11 09:28:19CMSAIO               LINK_SNAPSHOTM                 NCAOWIIM             CPWII02         2014-11-11 09:28:19CMSAIO               CMSUAT_NEW                     CMSAIO               CUDWH01         2014-11-11 09:28:19

2.确认创建db_link的权限:

SQL> select grantee,privilege from dba_sys_privs where privilege like '%DATABASE LINK%';GRANTEE                        PRIVILEGE------------------------------ ----------------------------------------SYS                            CREATE DATABASE LINKSYS                            DROP PUBLIC DATABASE LINK<span style="color:#ff0000;">QS                             CREATE PUBLIC DATABASE LINKNCAOODS                        CREATE PUBLIC DATABASE LINK</span>IMP_FULL_DATABASE              CREATE PUBLIC DATABASE LINKRECOVERY_CATALOG_OWNER         CREATE DATABASE LINKNGMCxxx                        CREATE DATABASE LINKDBA                            DROP PUBLIC DATABASE LINKIMP_FULL_DATABASE              DROP PUBLIC DATABASE LINKIMP_FULL_DATABASE              CREATE DATABASE LINKCMSAIO                         CREATE DATABASE LINKSYS                            CREATE PUBLIC DATABASE LINKDBA                            CREATE DATABASE LINKDBA                            CREATE PUBLIC DATABASE LINK<span style="color:#ff0000;">--可见用户QS/NCAOODS中只包含CREATE PUBLIC DATABASE LINK，并不包含CREATE DATABASE LINK的权限,其他可以导入的都是创建私有db link的权限。</span>16 rows selected.

3.确认无法导入db_link的用户的权限

SQL> select grantee,granted_role from dba_role_privs where grantee in ('QS','NCAOODS');GRANTEE                        GRANTED_ROLE------------------------------ ------------------------------QS                             QS_CHO_CMGNCAOODS                        QS_SELECTNCAOODS                        RESOURCEQS                             SELECT_CATALOG_ROLENCAOODS                        CONNECTQS                             RESOURCENCAOODS                        QS_CONTROLNCAOODS                        QS_DEVELOPERQS                             CONNECTQS                             QS_CHO_UAT

三.问题重现

在scott用户下创建私有db_link后，revoke创建私有db link权限并赋予创建public db link的权限。

导出scott数据后重新导入，出现同样的错误。在导入用户(Processing object type SCHEMA_EXPORT/USER)执行完后就赋予创建私有db link的权限后不会报错。(不过这个有点考研手速啦)