Re: import to different user with the new correct grant doesn't work

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Mon, 18 Dec 2006 17:30:07 +0000
Message-ID: <rpjdo2pd9thdlo36vgti9tmmlssq09iqbf@4ax.com>

On 15 Dec 2006 06:06:38 -0800, "Bryce" <brice.dutheil_at_gmail.com> wrote:

>I have a problem with the import util from oracle (Import: Release
>10.2.0.1.0 - Production on Fri Dec 15 14:45:49 2006).
>
>I would like to import from 2 schemas to 2 different schema, and the
>import tool works fine exept for the grantas that it sets to the
>original user.
>
>adminuser (grants select, insert, etc to realuser)
>realuser (got grants from adminuser)
>
>imp userid=sys/sys fromuser=adminuser,realuser
>touser=testadminuser,testuser
>
>All the schemas are on the same database server.
>
>And once my import is done, testuser didn't get anygrant to
>TESTADMINUSER objects while REALUSER now have grants from ADMINUSER and
>from TESTADMINUSER)
>
>Is there a way to import related schemas into the same schemas named
>differently.

As you're on 10g it is well worth looking into Data Pump, the replacement for imp/exp. Basically Data Pump is what imp/exp should have been in the first place, and it will quite easily handle this case with the REMAP_SCHEMA option.

e.g. after having done:

$ expdp test/test dumpfile=cdos.dmp schemas=adminuser,realuser [output snipped]

$ impdp test/test dumpfile=cdos.dmp remap_schema=adminuser:testadminuser remap_schema=realuser:testuser

Import: Release 10.2.0.1.0 - Production on Monday, 18 December, 2006 17:25:38

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
Master table "TEST"."SYS_IMPORT_FULL_02" successfully loaded/unloaded Starting "TEST"."SYS_IMPORT_FULL_02": test/******** dumpfile=cdos.dmp remap_schema=adminuser:testadminuser remap_schema=realuser:testuser

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/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TESTADMINUSER"."T"                             0 KB       0 rows

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Job "TEST"."SYS_IMPORT_FULL_02" successfully completed at 17:25:45

$ sqlplus testuser/testuser

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 18 17:27:56 2006

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> select table_name from all_tab_privs where table_schema = 'ADMINUSER';

no rows selected

SQL> select table_name from all_tab_privs where table_schema = 'TESTADMINUSER';

TABLE_NAME

Data Pump is also exposed through PL/SQL APIs so you could also do this from within the database rather than from the commandline.

-- 
Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Received on Mon Dec 18 2006 - 11:30:07 CST