I have created a new SP. I have a role created to which i have given execute permission on this stored procedure.
Now, this SP is a generic one and i want to give execute permission on this SP to all. One way is- i grant this role to all the users explicitly. Another option is i grant this role to a user - Public. I did the 2nd way, but users still get "execute permission error" on this SP. As per my understanding, if you give any access to user - public, it goes to all the users. Could someone point me out what wrong am i doing here.
What exact error is returned?
For SPs there's a SECURITY option either CREATOR/DEFINER/OWNER or INVOKER.
You should check the DDL manual which one you actually need.
from the DDL manual for roles the restriction is :
Trying to grant a role to public should return an error message.
Public is a role and it's always active for every user.
So simply grant the right directly to public.