Data Pump and GRANTs
in [Oracle]
|
From: Syltrem on 17 Jun 2010 09:27 Hi I'm exporting with DataPump using SCHEMA=schemaname, and then I import into another database. The user I'm exporting has been granted EXECUTE on SYS.DBMS_CRYPTO in the source database. When I do the import, thus creating the schema into the other database, the imported user is not granted EXECUTE on SYS.DBMS_CRYPTO which caused many procedures/function fail to compile/validate. This is just an example; none of this users grants are imported. On the other hand, all grants that this user grants to other users (for its own objects to be accessed by others) are successfully imported. If I use IMPDP SQLFILE=somefile, I do not see any grant *to* this user. All I see is grants *by* this user. What can I do so that the user be created with all its grants ? Thanks ! Syltrem
From: gazzag on 17 Jun 2010 09:59 On 17 June, 14:27, "Syltrem" <syltremz...(a)videotron.ca> wrote: > Hi > > I'm exporting with DataPump using SCHEMA=schemaname, and then I import into > another database. > > The user I'm exporting has been granted EXECUTE on SYS.DBMS_CRYPTO in the > source database. > > When I do the import, thus creating the schema into the other database, the > imported user is not granted EXECUTE on SYS.DBMS_CRYPTO which caused many > procedures/function fail to compile/validate. > This is just an example; none of this users grants are imported. > On the other hand, all grants that this user grants to other users (for its > own objects to be accessed by others) are successfully imported. > > If I use IMPDP SQLFILE=somefile, I do not see any grant *to* this user. All > I see is grants *by* this user. > What can I do so that the user be created with all its grants ? > > Thanks ! > Syltrem Which user are you performing the export and import as? Can you post a logfile of the outputs? HTH -g
From: Syltrem on 17 Jun 2010 10:13 "gazzag" <gareth(a)jamms.org> wrote in message news:51d364e4-6a0d-48c0-bce6-0cdfd87904ce(a)i31g2000yqm.googlegroups.com... > On 17 June, 14:27, "Syltrem" <syltremz...(a)videotron.ca> wrote: >> Hi >> >> I'm exporting with DataPump using SCHEMA=schemaname, and then I import >> into >> another database. >> >> The user I'm exporting has been granted EXECUTE on SYS.DBMS_CRYPTO in the >> source database. >> >> When I do the import, thus creating the schema into the other database, >> the >> imported user is not granted EXECUTE on SYS.DBMS_CRYPTO which caused many >> procedures/function fail to compile/validate. >> This is just an example; none of this users grants are imported. >> On the other hand, all grants that this user grants to other users (for >> its >> own objects to be accessed by others) are successfully imported. >> >> If I use IMPDP SQLFILE=somefile, I do not see any grant *to* this user. >> All >> I see is grants *by* this user. >> What can I do so that the user be created with all its grants ? >> >> Thanks ! >> Syltrem > > Which user are you performing the export and import as? Can you post > a logfile of the outputs? > > HTH > -g` Exporting and importing with user IVADBA All the ORA-39082: errors in the IMPORT job are because of GRANTS not being granted to the imported user. These objects can`t compile because of the missing privileges for objects in other schemas. I can generate those manually then recompile, but I would expect datapump to create the user with all its grants... --- EXPORT JOB ---- $ expdp CONTENT=METADATA_ONLY PARALLEL=2 - SCHEMAS=(FINANCE,MANUFACTURING,IPS,INFFINUSER,INFMANUSER,INFWEBUSER,INFAPP,INFAPPUSER,SIQINFMAN) - DUMPFILE=test.dmp DIRECTORY=COPY_DB LOGFILE=exp_tin.LOG Export: Release 10.2.0.4.0 - Production on Wednesday, 16 June, 2010 10:49:11 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connect� � : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Data Mining and Real Application Testing options D�marrage de "IVADBA"."SYS_EXPORT_SCHEMA_01" : ivadba/******** CONTENT=METADATA_ONLY PARALLEL=2 SCHEMAS=(FINANCE,MANUFACTURING,IPS, Traitement du type d'objet SCHEMA_EXPORT/USER Traitement du type d'objet SCHEMA_EXPORT/SYSTEM_GRANT Traitement du type d'objet SCHEMA_EXPORT/ROLE_GRANT Traitement du type d'objet SCHEMA_EXPORT/DEFAULT_ROLE Traitement du type d'objet SCHEMA_EXPORT/TABLESPACE_QUOTA Traitement du type d'objet SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Traitement du type d'objet SCHEMA_EXPORT/SYNONYM/SYNONYM Traitement du type d'objet SCHEMA_EXPORT/DB_LINK Traitement du type d'objet SCHEMA_EXPORT/SEQUENCE/SEQUENCE Traitement du type d'objet SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/TABLE/TABLE Traitement du type d'objet SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/INDEX Traitement du type d'objet SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Traitement du type d'objet SCHEMA_EXPORT/TABLE/COMMENT Traitement du type d'objet SCHEMA_EXPORT/TABLE/AUDIT_OBJ Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/FUNCTION/FUNCTION Traitement du type d'objet SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/PROCEDURE/PROCEDURE Traitement du type d'objet SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Traitement du type d'objet SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Traitement du type d'objet SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Traitement du type d'objet SCHEMA_EXPORT/VIEW/VIEW Traitement du type d'objet SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/VIEW/COMMENT Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Traitement du type d'objet SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Traitement du type d'objet SCHEMA_EXPORT/TABLE/TRIGGER Traitement du type d'objet SCHEMA_EXPORT/EVENT/TRIGGER Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Traitement du type d'objet SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Traitement du type d'objet SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Traitement du type d'objet SCHEMA_EXPORT/MATERIALIZED_VIEW Traitement du type d'objet SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Traitement du type d'objet SCHEMA_EXPORT/JOB Traitement du type d'objet SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ Table ma�tre "IVADBA"."SYS_EXPORT_SCHEMA_01" charg�e/d�charg�e avec succ�s ****************************************************************************** Le fichier de vidage d�fini pour IVADBA.SYS_EXPORT_SCHEMA_01 est : admin_01$disque:[sylvain.copy_db]test.dmp T�che "IVADBA"."SYS_EXPORT_SCHEMA_01" ex�cut�e avec succ�s � 10:53:08 ---- IMPORT JOB ----- $ impdp PARALLEL=2 - SCHEMAS=(FINANCE,MANUFACTURING,IPS,INFFINUSER,INFMANUSER,INFWEBUSER,INFAPP,INFAPPUSER,SIQINFMAN) - DUMPFILE=test.dmp DIREctory=copy_db LOGFILE=imp_mit.LOG Import: Release 10.2.0.4.0 - Production on Wednesday, 16 June, 2010 10:53:10 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connect� � : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production With the Partitioning, Data Mining and Real Application Testing options Table ma�tre "IVADBA"."SYS_IMPORT_SCHEMA_01" charg�e/d�charg�e avec succ�s D�marrage de "IVADBA"."SYS_IMPORT_SCHEMA_01" : ivadba/******** PARALLEL=2 SCHEMAS=(FINANCE,MANUFACTURING,IPS,INFFINUSER,INFMANUSER, Traitement du type d'objet SCHEMA_EXPORT/USER Traitement du type d'objet SCHEMA_EXPORT/SYSTEM_GRANT Traitement du type d'objet SCHEMA_EXPORT/ROLE_GRANT Traitement du type d'objet SCHEMA_EXPORT/DEFAULT_ROLE Traitement du type d'objet SCHEMA_EXPORT/TABLESPACE_QUOTA Traitement du type d'objet SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Traitement du type d'objet SCHEMA_EXPORT/SYNONYM/SYNONYM Traitement du type d'objet SCHEMA_EXPORT/DB_LINK Traitement du type d'objet SCHEMA_EXPORT/SEQUENCE/SEQUENCE Traitement du type d'objet SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/TABLE/TABLE Traitement du type d'objet SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/INDEX Traitement du type d'objet SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Traitement du type d'objet SCHEMA_EXPORT/TABLE/COMMENT Traitement du type d'objet SCHEMA_EXPORT/TABLE/AUDIT_OBJ Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/FUNCTION/FUNCTION Traitement du type d'objet SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/PROCEDURE/PROCEDURE Traitement du type d'objet SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Traitement du type d'objet SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Traitement du type d'objet SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE ORA-39082: Type d'objet ALTER_PROCEDURE:"FINANCE"."RCSP0110AINF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet ALTER_PROCEDURE:"FINANCE"."RCSP0110INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet ALTER_PROCEDURE:"FINANCE"."RCSP0110BINF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet ALTER_PROCEDURE:"MANUFACTURING"."MANP0110BINF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet ALTER_PROCEDURE:"MANUFACTURING"."MANP0110AINF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet ALTER_PROCEDURE:"MANUFACTURING"."MANP0110INF" cr�� avec des avertissements de compilation Traitement du type d'objet SCHEMA_EXPORT/VIEW/VIEW Traitement du type d'objet SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT ORA-39082: Type d'objet VIEW:"FINANCE"."GEM_DB_GET_ALL_INDEX" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"FINANCE"."GEM_DB_GET_ALL_INDEX_SEGMENTS" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"FINANCE"."GEM_DB_GET_INDEX_INFO" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"FINANCE"."GEM_DB_LOAD_INDEX_MDC" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"FINANCE"."GEM_DB_LOAD_INDEX" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"FINANCE"."GEM_DB_LOAD_ALL_INDEX" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"MANUFACTURING"."GEM_DB_GET_ALL_INDEX_SEGMENTS" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"MANUFACTURING"."GEM_DB_GET_ALL_INDEX" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"MANUFACTURING"."GEM_DB_GET_INDEX_INFO" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"MANUFACTURING"."GEM_DB_LOAD_ALL_INDEX" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"MANUFACTURING"."GEM_DB_LOAD_INDEX" cr�� avec des avertissements de compilation ORA-39082: Type d'objet VIEW:"MANUFACTURING"."GEM_DB_LOAD_INDEX_MDC" cr�� avec des avertissements de compilation Traitement du type d'objet SCHEMA_EXPORT/VIEW/COMMENT Traitement du type d'objet SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Traitement du type d'objet SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Traitement du type d'objet SCHEMA_EXPORT/TABLE/TRIGGER ORA-39082: Type d'objet TRIGGER:"FINANCE"."RCSP0201INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet TRIGGER:"FINANCE"."RCSP0201INF" cr�� avec des avertissements de compilation Traitement du type d'objet SCHEMA_EXPORT/EVENT/TRIGGER ORA-39082: Type d'objet TRIGGER:"FINANCE"."RCSP0120INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet TRIGGER:"FINANCE"."RCSP0120INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet TRIGGER:"IPS"."WEBP0120INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet TRIGGER:"IPS"."WEBP0120INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet TRIGGER:"MANUFACTURING"."MANP0120INF" cr�� avec des avertissements de compilation ORA-39082: Type d'objet TRIGGER:"MANUFACTURING"."MANP0120INF" cr�� avec des avertissements de compilation Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Traitement du type d'objet SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Traitement du type d'objet SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Traitement du type d'objet SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Traitement du type d'objet SCHEMA_EXPORT/MATERIALIZED_VIEW Traitement du type d'objet SCHEMA_EXPORT/TABLE/MATERIALIZED_VIEW_LOG Traitement du type d'objet SCHEMA_EXPORT/POST_SCHEMA/PROCOBJ T�che "IVADBA"."SYS_IMPORT_SCHEMA_01" ex�cut�e avec 27 erreur(s) � 11:19:02 Sorry for the french message but it should still be understandable... Thanks Syltrem
From: gazzag on 17 Jun 2010 10:37 On 17 June, 15:13, "Syltrem" <syltremz...(a)videotron.ca> wrote: > > Exporting and importing with user IVADBA > > All the ORA-39082: errors in the IMPORT job are because of GRANTS not being > granted to the imported user. These objects can`t compile because of the > missing privileges for objects in other schemas. <snip> I wonder if metadata filtering is playing a role in this: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1009903 HTH -g
From: Syltrem on 17 Jun 2010 10:51
"gazzag" <gareth(a)jamms.org> wrote in message news:a5fe94ab-e2e4-4e12-b826-2e1225e16b57(a)u7g2000yqm.googlegroups.com... > On 17 June, 15:13, "Syltrem" <syltremz...(a)videotron.ca> wrote: >> >> Exporting and importing with user IVADBA >> >> All the ORA-39082: errors in the IMPORT job are because of GRANTS not >> being >> granted to the imported user. These objects can`t compile because of the >> missing privileges for objects in other schemas. > <snip> > > I wonder if metadata filtering is playing a role in this: > > http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1009903 > > HTH > -g` I don't use INCLUDE nor EXCLUDE... But I saw this : "Cross-schema references are not exported unless the referenced schema is also specified in the list of schemas to be exported" I wonder if this also includes grants. But I can't be expected to export the SYS user... Schema Mode A schema export is specified using the SCHEMAS parameter. This is the default export mode. If you have the EXP_FULL_DATABASE role, then you can specify a list of schemas and optionally include the schema definitions themselves, as well as system privilege grants to those schemas. If you do not have the EXP_FULL_DATABASE role, you can export only your own schema. Cross-schema references are not exported unless the referenced schema is also specified in the list of schemas to be exported. For example, a trigger defined on a table within one of the specified schemas, but that resides in a schema not explicitly specified, is not exported. This is also true for external type definitions upon which tables in the specified schemas depend. In such a case, it is expected that the type definitions already exist in the target instance at import time. Syltrem |