This is a feature proposal.
Currently h2 supports only object privileges (select, insert, update,
delete).
Actually execute is excluded. Execute applies only for functions and
procedures (for which the other privileges do not apply).
But what is truly missing are system privileges
create, alter and drop privileges.
CREATE privilege makes sense only in relation with schema (because it
makes no sense to have create privileges on object that already exists).
ALTER and DROP can be object specific (and thus can be only given after
the object has been created).
Let me explain my (and rather common) use case.
My organization wants to keep all it's data in one database.
It has multiple applications and for each application a separate developer.
Now as a DBA I would like to define the privileges in the following manner.
No one (other than DBAs = Admin in h2) may do anyting in the PUBLIC
(root) schema.
For every application I would create separate schemas.
APP1, APP2 ...
Then I would create the users for application developers.
DEV1, DEV2 ...
For DEV1 I would like to give all privileges to schema APP1 (and to
USER2 all privileges to schema APP2)
This means that he could create, alter, drop, select, insert, update,
delete and execute all objects within that schema.
So giving schema privileges would function differently than object
specific privileges because they would be dynamic.
With this framework in place we could say that no one ever has system
privileges (create, alter, drop) for INFORMATION_SCHEMA.
And everybody always has select privileges for that schema (but no
insert, delete or update privileges).
That's the way it is already but it would be now clearer.
Further we could extend the grant and revoke statements so that one
could grant/revoke privileges for schemas.
When deciding what are the privileges of a user on a specific database
object then the schema privileges would always be added to the object
specific privileges.
This raises one problem.
What if you would like to exclude just a few privileges from some
objects in the schema?
That would be difficult because you would have to remove the privileges
from the schema and grant them to all objects separately.
That's why we could also introduce syntax
grant privilege on schema.*to user
This would be just a shorthand for granting the rights to objects within
that schema separately.
Keeping the admin user as an all-powerful user is a good idea because
there are systems (eg. derby) where it is possible to revoke all the
rights from yourself and be left out in the cold.
If this makes any sense to you Thomas I could start trying to implement
this.
- Rami Ojares
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
