I'd like some brief feedback on this idea before I try to make a real
proposal.
The use case is this:
You have an application with several roles: * admin user - owns all the objects related to that application* normal
user- INSERT/UPDATE/DELETE plus sequence usage* read-only user - for reporting
The problem is managing all the permissions requires a lot of care, and
it's difficult to easily verify that all the permissions are set as you
expect on all of the objects. Because it's more difficult to manage, I
think many people just have a single user for all of these things.
My idea is to have a "GRANT mask":
CREATE ROLE foo_ro GRANT (SELECT ON TABLE, USAGE ON SCHEMA) FROM foo;
[syntax not meant as a real proposal yet, just for illustration]
And foo_ro would get the SELECT ON TABLE and USAGE ON SCHEMA privileges
from foo, but not any INSERT privileges. That way, you can add roles
without having to do a GRANT on each object separately. And, more
importantly, you can pretty easily observe that the privileges are what
you expect without inspecting the objects individually.
This idea is meant to be a better solution than the "GRANT ... *" that
MySQL offers.
Questions:
1. Is this a reasonable approach from a security standpoint, or is it
fundamentally flawed?
2. Does it violate the SQL standard?
3. Is it completely orthogonal to "IN ROLE" and "INHERITS", or should
they be made to work together somehow?
Regards,Jeff Davis