2 Replies Latest reply: Oct 21, 2013 2:51 PM by learner1 RSS

    Grants issue !!

    learner1

      Hi,

       

      O/S : HP Unix

      DB : 11.2.0.3

       

      Server Source_A : Let's say i have user with the name 'Rob' and this user has been assigned a role 'MY_SRC_ROLE' .

       

      I developed a table under rob schema and granted access to this table via role

       

      GRANT DELETE, INSERT, SELECT, UPDATE ON rob.emp TO MY_SRC_ROLE;

       

      I have 100 more users & they have been granted this role 'MY_SRC_ROLE'. These 100 users can now access emp table via Role 'MY_SRC_ROLE' without any issues.

       

      Now i took a datapump export & performed datapump import on target server which is also HP Unix with 11.20.3 . On target server i have user 'JACK' and a role called 'MY_WORK_ROLE'. 5000 users have been granted 'MY_WORK_ROLE' on this server.

       

      I have used remap tablespace clause & remap schema clause in datapump import script. Once i performed an import , due to schema remap , i can see JACK now owns table 'emp', however grants are still not there, I tried searching on google & oracle documentation, if somehow we can remap ROLE GRANTS also while doing datapump imp, but i couldn't find supporting syntax. Am i missing something or probably can i assume datapump import is not capable to handle this particular scenario ? I was able to do it by manipulating sqlfile and replacing role name in that but i am looking for a sol. within datapump itself.

       

      So bottom line how can grants assigned to ROLE 'X' be transferred to 'Role Y' via datapump import.

       

      Let me know, if any any additional details are required.

       

      -Learner    

        • 1. Re: Grants issue !!
          DK2010

          Hi,

           

          AFAIK, you can not remap/rname the roles. you can generate the Dynamic script and Apply on Target

           

          something like that

           

          spool role_target.sql

           

           

          select 'grant '|| PRIVILEGE || ' MY_WORK_ROLE;' from dba_sys_privs where GRANTEE='MY_SRC_ROLE';

           

           

          spool off

           

           

          @role_target.sql

           

          HTH

          • 2. Re: Grants issue !!
            learner1

            Thanks HTH for the response. I wonder why Oracle doesn't give this provision , especially when they have given schema remap provision.

             

            Anyways thanks for the confirmation, appreciate it.

             

            -Learner