grants not getting imported

Smohib

Hi all,

I have export (exp) dump of a schema (9.2.0.1.0)

I am importing (imp) the dump into newly created database (11.1.0.6.0)

It successfully imports saying "import terminated successfully with warnings" (views & triggers with compilation errors...)

I have all the objects tables,functions,procedures etc but I dont have "grants"...

I verified from 9i database that the count of objects is same, roles is also same (select distinct role,owner from ROLE_TAB_PRIVS where owner='user1')

the grants part....(select granted_role from DBA_ROLE_PRIVS where grantee='user1')

the import command i use is

imp 'system/oracle as sysdba' file=dump.dmp log=test.log grants=y compile=y indexes=y fromuser=user1 touser=user1 ignore=y

I am connecting as sysdba & checking the count

export of the whole schema is taken without any warnings.

Please guide me if I am wrong at any step..

Thanks,

Mohib

sb92075

SQL> select count(*) from dba_objects where object_type = 'GRANT';

  COUNT(*)

----------

         0

GRANT  is not a physical object which can be exported.

Unknown

exports and imports should NOT be run 'as sysdba'. There are notes on My Oracle Support to that effect.

Also GRANTS made by SYS will NOT be exported. as SYS is not exported.

You need to dump them separately.

-----------

Sybrand Bakker

Senior Oracle DBA

Salman Qureshi

Hi Sybrand,

Can you please let me know the MOS note regarding export/import done using SYS user? I think user level (fromuser touser)export/import should not have any issue by using SYS user.

You also said "Also GRANTS made by SYS will NOT be exported. as SYS is not exported", I tested this on oracle 11.2 by granting select on a table of user "test" to user "test1" by logging in as SYS and then exporting user "test" into a new user "test2" Now user "test1" is able to select this table in "test2" because grants were also imported which were granted by user SYS. Any artical regarding this?

Thanks

Salman

Hemant K Chitale

you misunderstand.  Grants on SYS objects (which are done by SYS !) are not exported.

As for not using SYS -- i.e. an "AS SYSDBA" connection --  for export import, the restriction is because an export by SYS doesn't guarantee that consistency is achieved.  What were your export command line parameters ?

See Oracle Support Note# 277237.1

Hemant K Chitale

Srini Chavali-Oracle

Pl post in the export/import forum