In OBIEE, is there any way to achieve this?
When users with different responsibilities login to OBIEE, and access same report, OBIEE should hit different physical table (or opaque view query)
Thanks in advance
Yes. You can use session/request variables to change the target table in the RPD.
While Christian is right and the answer is "yes", you are maybe overthinking what you need.
Because keeping it simple: why a same "report" (do you mean analysis? dashboard page?) would be sources from different places based on a role? Wouldn't that be more 2 different analysis and so the need would be covered by showing analysis A to user A and analysis B to user B based on their roles or something like that?
Keep in mind that you need to maintain your model, and if you start switching sources based on roles, it will become quite difficult for anyone to have a clear view of what is used where (think about lineage etc.).