This content has been marked as final. Show 2 replies
O/S : HP Unix
DB : 18.104.22.168
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.