DataPump Import (IMPDPD) Adds WITH GRANT OPTION To GRANT Statements (Doc ID 1305823.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

If the source database has mixed GRANT statements, some with and some without 'WITH GRANT OPTION', then DataPump import adds 'WITH GRANT OPTION' to all GRANT statements.

Following test case will show this:


connect system/password

create directory dp_dir as '/tmp';
grant read,write on directory dp_dir to public;

create user a identified by a default tablespace users temporary tablespace temp;
grant connect, resource to a;

create user b identified by b default tablespace users temporary tablespace temp;
grant connect, resource to b;

connect a/a

create table t (c1 number, c2 varchar2(10));

GRANT select on t to b WITH GRANT OPTION;
GRANT insert on t to b;

expdp system/password directory=dp_dir dumpfile=exp_grant.dmp logfile=exp_grant.log schemas=a,b

impdp system/password directory=dp_dir dumpfile=exp_grant.dmp logfile=imp_grant.log sqlfile=imp_grant.sql schemas=a

cat imp_grant.sql

...
-- new object type path: SCHEMA_EXPORT/TABLE/TABLE
-- CONNECT SYSTEM
CREATE TABLE "A"."T"
( "C1" NUMBER,
"C2" VARCHAR2(10 BYTE)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ;
-- new object type path: SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
-- CONNECT A
GRANT INSERT, SELECT ON "A"."T" TO "B";
GRANT INSERT, SELECT ON "A"."T" TO "B" WITH GRANT OPTION;

User B will have now grants WITH GRANT OPTION on select as well as on insert!

The traditional import utility (IMP) imports GRANTs correctly:

exp system/password file=exp_gr.dmp log=exp_gr.log owner=a,b

imp system/password file=exp_gr.dmp log=imp_gr.logfromuser=a touser=a show=y ignore=y

cat imp_gr.log

. importing A's objects into A
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'S112W3', inst_scn=>'2494748');"
"COMMIT; END;"
"ALTER SESSION SET CURRENT_SCHEMA= "A""
"CREATE TABLE "T" ("C1" NUMBER, "C2" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 IN"
"ITRANS 1 MAXTRANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
. . skipping table "T"

"GRANT SELECT ON "T" TO "B" WITH GRANT OPTION"
"GRANT INSERT ON "T" TO "B""

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms