Expdp With Include Does Not Export Role Of Role
(Doc ID 2411819.1)
Last updated on SEPTEMBER 14, 2024
Applies to:
Oracle Database Cloud Schema Service - Version N/A and laterOracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
Goal
In the situation below, Data Pump does not export the role of a role:
CREATE ROLE "ROLE1" NOT IDENTIFIED;
revoke role1 from sys;
CREATE ROLE "ROLE1_1" NOT IDENTIFIED;
revoke role1_1 from sys;
CREATE ROLE "ROLE1_2" NOT IDENTIFIED;
revoke role1_2 from sys;
CREATE ROLE "ROLE2" NOT IDENTIFIED;
revoke role2 from sys;
CREATE ROLE "ROLE2_1" NOT IDENTIFIED;
revoke role2_1 from sys;
CREATE ROLE "ROLE2_2" NOT IDENTIFIED;
revoke role2_2 from sys;
revoke role1 from sys;
CREATE ROLE "ROLE1_1" NOT IDENTIFIED;
revoke role1_1 from sys;
CREATE ROLE "ROLE1_2" NOT IDENTIFIED;
revoke role1_2 from sys;
CREATE ROLE "ROLE2" NOT IDENTIFIED;
revoke role2 from sys;
CREATE ROLE "ROLE2_1" NOT IDENTIFIED;
revoke role2_1 from sys;
CREATE ROLE "ROLE2_2" NOT IDENTIFIED;
revoke role2_2 from sys;
*These roles are not exported
Grant role1_1 to role1;
Grant role1_2 to role1;
Grant role2_1 to role2;
Grant role2_2 to role2;
Grant role1_1 to role1;
Grant role1_2 to role1;
Grant role2_1 to role2;
Grant role2_2 to role2;
Users are created:
CREATE USER "TESTROLE1" PROFILE "DEFAULT" IDENTIFIED BY TESTROLE1 DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "DATA" QUOTA UNLIMITED ON "DATA2" QUOTA UNLIMITED ON "DATA3" ACCOUNT UNLOCK;
CREATE USER "TESTROLE2" PROFILE "DEFAULT" IDENTIFIED BY TESTROLE2 DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "DATA" QUOTA UNLIMITED ON "DATA2" QUOTA UNLIMITED ON "DATA3" ACCOUNT UNLOCK;
CREATE USER "TESTROLE2" PROFILE "DEFAULT" IDENTIFIED BY TESTROLE2 DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "TEMP" QUOTA UNLIMITED ON "DATA" QUOTA UNLIMITED ON "DATA2" QUOTA UNLIMITED ON "DATA3" ACCOUNT UNLOCK;
Grants are perfrmed:
grant create session to testrole1;
grant create session to testrole2;
grant create table to testrole1;
grant create table to testrole2;
grant role1 to testrole1;
grant role2 to testrole2;
connect "TESTROLE1"/<PASSWORD>
create table table1 (col1 varchar2(100), col2 varchar2(100));
grant select on table1 to role1_1;
grant select on table1 to role1_2;
connect "TESTROLE2"/<PASSWORD>
create table table2 (col2 varchar2(100), col1 varchar2(100));
grant select on table2 to role2_1;
grant select on table2 to role2_2;
grant create session to testrole2;
grant create table to testrole1;
grant create table to testrole2;
grant role1 to testrole1;
grant role2 to testrole2;
connect "TESTROLE1"/<PASSWORD>
create table table1 (col1 varchar2(100), col2 varchar2(100));
grant select on table1 to role1_1;
grant select on table1 to role1_2;
connect "TESTROLE2"/<PASSWORD>
create table table2 (col2 varchar2(100), col1 varchar2(100));
grant select on table2 to role2_1;
grant select on table2 to role2_2;
expdp.par
CONTENT=METADATA_ONLY
FULL=Y
INCLUDE=SCHEMA:"IN (select username from dba_users where (username like '%TESTROLE%'))"
INCLUDE=GRANT
INCLUDE=ROLE_GRANT
DUMPFILE=Test_Role.dmp
LOGFILE=Test_Role.log
reuse_dumpfiles=y
FULL=Y
INCLUDE=SCHEMA:"IN (select username from dba_users where (username like '%TESTROLE%'))"
INCLUDE=GRANT
INCLUDE=ROLE_GRANT
DUMPFILE=Test_Role.dmp
LOGFILE=Test_Role.log
reuse_dumpfiles=y
Solution
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
| Goal |
| Solution |