I have some issue with oracle privileges i do not understand. The environment where I need this is complex but I will describe on an smaller example:
There is a schema math with a function and a public synonym for it
math
-> function: math.add
-> public synonym: add
Then there is a role mathematician which includes the grant for the privilege to execute that function
mathematician
--> grant execute on math.add to mathematician
this role is granted to another schema bob
bob
--> grant math to bob
Now the problem
I can use add in bob like select add (1,2) from dual but i cannot create a view in bob that uses add
trying so gives me:
ORA-24344: success with compilation error
ORA-01031: insufficient privileges error
If I grant execute on add directly to bob grant execute on math.add to bob it works.
Its not the lack of create view privilege or something, also it marks the add as problem.
Would be great if someone can explain why this happens and lead me to solve this.
thank you!
Thank you very much Patrick. I tried to find that on google and Stackoverflow with no success.
"The owner must be granted these privileges directly, rather than through a role ..." when creating a view per the documentation. Where "these" is refering to the view's dependent objects. Please read over Alex Poole's answer to this post, stackoverflow.com/questions/56189418/…. This should address your issue.