Re: Adding support for "GRANT ALTER ANY SCHEMA TO <user>"

[Thomas Mueller]

Hi,

> I'm not sure if this functionality falls under the existing roadmap feature
> "Access rights: finer grained access control (grant access for specific
> functions)".

Yes, I think it does.

> create new database schema's

> "ALTER ANY SCHEMA" (an MS SQL Server syntax that I've never used) rather
> than three rights "CREATE SCHEMA", "DROP SCHEMA", "MODIFY SCHEMA".

The syntax "alter any schema" is OK. It seems there is no standard way
to describe this.

> 1. Is this a reasonable enhancement request?

Yes, sure. Thanks a lot for describing the use case.

> 2. Is the syntax "GRANT ALTER ANY SCHEMA TO <user>" compatible with future
> objectives of H2?

Yes.

> 3. In case I find the time write this patch, is the correct approach to
> define a new H2 right (or 3 if I make each right independent) and then check
> for this right/these rights in the DDL classes CreateSchema, DropSchema,
> AlterSchema?

That's a good question. The minimal solution would be to add just one
new right: ALTER_ANY_SCHEMA. As this is an implementation detail and
not stored anywhere, this could be changed later if required.

> From what I can see the current H2 right checks refer to
> specific tables (e.g. User.hasRight(Table table, int rightMask)) - so should
> this be extended to support database level right checks? Or should such
> checks be implimented elsewhere

I think the method could be kept, but just pass the new right mask,
and null for the table. Later on, a new method could be created.

> I am aware that this is only half of the solution as in order to make use of
> newly created schemas Users also require the future H2 road mapped feature
> "GRANT ALL ON * TO <user>".

Would a user that has the right "alter any schema" also be allowed to
modify data? I kind of think that would make sense, even if not
compatible with MS SQL Server. At least as long as there is no "GRANT
ALL ON * TO <user>".

Regards,
Thomas

[John Yates]

Thomas,

  I work with Tim Monro and will be picking up this project.  Any changes in how you would approach this item?

John

[John Yates]

I have gone ahead an implemented this item and am attaching a patch.  A key area where I had to make some choices was dealing with loading a database that has a non-admin owned Schema.  The system checks that the assigned owner has the rights to the Schema.  At this point in the loading process, the Right and Role tables are not loaded, so the check would fail.  The documentation says setting the owner has no effect, so I've removed the check for now.  The alternatives require more architectural discussion.

Comments encouraged.

John

[Noel Grandin]

Thanks John.
I have committed your patch, with some small changes.

[John Yates]

Thanks, Noel.  The changes make sense - and several (now that I have seen http://www.h2database.com/html/build.html#providing_patches) I should have done myself and will with any future patches.

[John Yates]

The attached patch addresses this part of the (discussed above) functionality.  It does so by allowing all table function rights to the grantee of ALTER ANY SCHEMA rights.

It also fixes a problem that might happen if the database is unwritable and this feature is invoked.  It calls previously existing code so it includes this type of check.

[Noel Grandin]

On 2013-11-19 23:10, John Yates wrote:
> The attached patch addresses this part of the (discussed above) functionality. It does so by allowing all table
> function rights to the grantee of ALTER ANY SCHEMA rights.

Hi

At this point I am confused. I didn't see the earlier discussion before this thread, so perhaps I am missing something.

But it seems to me that with this patch, there is no longer any difference between
GRANT ALTER ANY SCHEMA to <user>
and
ALTER USER <user> ADMIN TRUE

What am I missing?

Regards, Noel.

[John Yates]

 The rights involved with the patch are only those associated with standard tables.  The user granted this right will be unable to (for example) create or manage users (for example, would not be able to run ALTER USER <user> ADMIN TRUE).  It is a powerful right.  But not an all powerful right.

[Noel Grandin]

Thanks for the explanation, and for the patch. Patch has been applied.