What you are asking for is exactly what DEFINER security does. The
applicxation owner grants appuser the right to execute the procedure,
but not to SELECT from any tables. The procedure is then run with the
security attributes of the definer of the procedure, the application
owner, even though it is the application user that runs it.
This is no different than other DBMS systems, the difference being that
you have the option of defining a procedure with INVOKER rights, in
which case the procedure will run with the security attributes of the
application user, and you need to grant that user access to any tables
that are accessed within the procedure.
So in essence, MySQL doesn't limit you compared to most other DBMS's, it
gives you more options.
Cheers
/Karlsson
[EMAIL PROTECTED] wrote:
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
--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
<___/ www.mysql.com Cellphone: +46 708 608121
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]