Forum Stats

  • 3,757,058 Users
  • 2,251,192 Discussions
  • 7,869,716 Comments

Discussions

grants not getting imported

Smohib
Smohib Member Posts: 217
edited May 19, 2014 3:37AM in General Database Discussions

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

Answers

  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond

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

      COUNT(*)

    ----------

             0

    GRANT  is not a physical object which can be exported.

  • 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
    Salman Qureshi Member Posts: 2,483

    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
    Hemant K Chitale Member Posts: 15,759 Blue Diamond

    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
    Srini Chavali-Oracle Member Posts: 29,596 Blue Diamond

    Pl post in the export/import forum

This discussion has been closed.