When creating a stored procedure, you can set the sql security characteristic to either definer or invoker. As an example, I have a stored procedure that does a select from a table, and an application user (appuser) that calls the stored procedure. If the sql security is set to invoker, then I have to give appuser both select and execute privileges. If the sql security is set to definer, then the definer needs select privileges and appuser only needs execute.
What I'd like to be able to do is to give appuser the execute privilege and not have to give any privileges on the underlying tables to the definer. Is this possible? We do almost 100% of our work through stored procedures. It would be a lot easier to manage just the execute privilege. Are there reasons why this is not a good idea? This is how we manage security with our other DBMS and it's worked quite well, but it doesn't have the definer/invoker characteristic for stored procs either. Any suggestions about how to manage users/privileges would be appreciated. Donna
