I am creating a repository stored procedure in schema SCHEMA_A ,in which i am selecting data from SCHEMA_A. The procedure is created with definer rights.
Since it is a repository procedure definer will be SYS_REPO.
So,I have given the SELECT and EXECUTE privilege on SCHEMA_A to user _SYS_REPO.
Now,When another user (USER_X) is trying to call the procedure who does not have SELECT and EXECUTE access on SCHEMA_A ,He is not able to run the procedure.
As per my understanding, since procedure is created with definer mode and SYS_REPO have SELECT and EXECUTE privilege on schema SCHEMA_A, USER_X should be able to execute procedure even if he doesn't have SELECT and EXECUTE access on SCHEMA_A
But I see that USER_X is unable to execute this procedure. He gets authorization error.
Please provide your inputs on how definer rights work for procedure.