bombless bombless - 1 year ago 89
MySQL Question

Does MySQL support interior mutability or sticky bit?

Say, I have a complex MySQL procedure. The procedure is basically a complex

query that use data inside a table to output some records.

For convenience, the procedure need to write to a temporary table to calculate the final result.

So can I grant properly so that a user that can only read the database can call this procedure to get its output, although the procedure actually need to write to the database? Because essentially this procedure don't intend to write, it just cache some data to a specific table with a name start with underscore so it shouldn't break anything seriously, beside costing storage of disk.

Answer Source

A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute. These privileges may be entirely different from those of the invoking user.

As long as the user defining the procedure has the necessary permissions and doesn't explicitly force the procedure to run in INVOKER context, the permissions of the invoking user have no impact on what the procedure can do. The permissions of the defining user apply to the procedure's actions.

The invoking user only needs to be able to run it. No special arrangements are necessary, because this is the default behavior.

The DEFINER defaults to the user creating the procedure. If that account has the SUPER privilege, they can actually declare a different definer account whose privileges will apply.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download