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

    Grants issue !!




      O/S : HP Unix

      DB :


      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




      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.



        • 1. Re: Grants issue !!



          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






          • 2. Re: Grants issue !!

            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.