Hi,
Oracle Version: 12c (12.1.0.2.0)
Issue: Here is the problem we are facing with one of the shell script + permissions:
A shell script connects to the database using sqlplus using Schema A to execute a procedure in Schema A called A.proc1
A.proc1 in turn calls another procedure sitting in a different Schema B.proc2. So the flow is:
Shell Script -> Connects to the DB using A -> Calls A.proc1 -> A.proc1 calls B.proc2
We have granted "EXECUTE PROCEDURE B.proc2" to a role RoleXY and have granted RoleXY to A but we still get
"ORA-00904: B.proc2: invalid identifier" when the shell script executes.
When I tried granting "EXECUTE PROCEDURE B.proc2" directly to A, the shell script executed successfully without an error.
Why would this happen?
Is there a difference between:
Granting "EXECUTE PROCEDURE B.proc2" to RoleXY and then granting RoleXY to A
vs
Granting "EXECUTE PROCEDURE B.proc2" to A directly?
Any help/input is appreciated.
Thank you!