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.
>> 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>".
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/groups/opt_out.
Index: src/docsrc/html/changelog.html
===================================================================
--- src/docsrc/html/changelog.html (revision 5301)
+++ src/docsrc/html/changelog.html (working copy)
@@ -37,6 +37,7 @@
</li><li>Add support for DB2 "WITH UR" clause, patch from litailang
</li><li>Added support for ON DUPLICATE KEY UPDATE like MySQL with the values() function to update with the value that
was to be inserted. Patch from Jean-Francois Noel.
+</li><li>Extend support of "GRANT ALTER ANY SCHEMA TO <user>" to allow grantee ability to manipulate tables
</li></ul>
<h2>Version 1.3.174 (2013-10-19)</h2>
Index: src/main/org/h2/engine/User.java
===================================================================
--- src/main/org/h2/engine/User.java (revision 5301)
+++ src/main/org/h2/engine/User.java (working copy)
@@ -125,6 +125,9 @@
return true;
}
if (table != null) {
+ if (hasRight(null, Right.ALTER_ANY_SCHEMA)) {
+ return true;
+ }
String tableType = table.getTableType();
if (Table.VIEW.equals(tableType)) {
TableView v = (TableView) table;
@@ -212,7 +215,7 @@
* @throws DbException if this user is not a schema admin
*/
public void checkSchemaAdmin() {
- if (!admin && !hasRight(null, Right.ALTER_ANY_SCHEMA)) {
+ if (!hasRight(null, Right.ALTER_ANY_SCHEMA)) {
throw DbException.get(ErrorCode.ADMIN_RIGHTS_REQUIRED);
}
}
Index: src/test/org/h2/test/db/TestRights.java
===================================================================
--- src/test/org/h2/test/db/TestRights.java (revision 5301)
+++ src/test/org/h2/test/db/TestRights.java (working copy)
@@ -212,15 +212,20 @@
testTableType(conn, "MEMORY");
testTableType(conn, "CACHED");
+ /* make sure admin can still do it. */
+
executeSuccess("CREATE USER SCHEMA_CREATOR PASSWORD 'xyz'");
executeSuccess("CREATE SCHEMA SCHEMA_RIGHT_TEST");
executeSuccess("ALTER SCHEMA SCHEMA_RIGHT_TEST RENAME TO SCHEMA_RIGHT_TEST_RENAMED");
executeSuccess("DROP SCHEMA SCHEMA_RIGHT_TEST_RENAMED");
+
+ /* create this for tests below */
executeSuccess("CREATE SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
+ executeSuccess("CREATE TABLE SCHEMA_RIGHT_TEST_EXISTS.TEST_EXISTS(ID INT PRIMARY KEY, NAME VARCHAR)");
conn.close();
- // try and fail
+ // try and fail (no rights yet)
conn = getConnection("rights;LOG=2", "SCHEMA_CREATOR", getPassword("xyz"));
stat = conn.createStatement();
assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).execute(
@@ -231,29 +236,42 @@
"DROP SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
conn.close();
- // give them
+ // grant the right
conn = getConnection("rights");
stat = conn.createStatement();
- executeSuccess("DROP SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
executeSuccess("GRANT ALTER ANY SCHEMA TO SCHEMA_CREATOR");
conn.close();
// try and succeed
conn = getConnection("rights;LOG=2", "SCHEMA_CREATOR", getPassword("xyz"));
stat = conn.createStatement();
+
+ /* should be able to create a schema and manipulate tables on that schema... */
executeSuccess("CREATE SCHEMA SCHEMA_RIGHT_TEST");
- executeSuccess("ALTER SCHEMA SCHEMA_RIGHT_TEST RENAME TO SCHEMA_RIGHT_TEST_RENAMED");
- executeSuccess("DROP SCHEMA SCHEMA_RIGHT_TEST_RENAMED");
- executeSuccess("CREATE SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
+ executeSuccess("ALTER SCHEMA SCHEMA_RIGHT_TEST RENAME TO S");
+ executeSuccess("CREATE TABLE S.TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
+ executeSuccess("ALTER TABLE S.TEST ADD COLUMN QUESTION VARCHAR");
+ executeSuccess("INSERT INTO S.TEST (ID, NAME) VALUES (42, 'Adams')");
+ executeSuccess("UPDATE S.TEST Set NAME = 'Douglas'");
+ executeSuccess("DELETE FROM S.TEST");
+ executeSuccess("DROP SCHEMA S");
+
+ /* ...and on other schemata */
+ executeSuccess("CREATE TABLE TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
+ executeSuccess("ALTER TABLE TEST ADD COLUMN QUESTION VARCHAR");
+ executeSuccess("INSERT INTO TEST (ID, NAME) VALUES (42, 'Adams')");
+ executeSuccess("UPDATE TEST Set NAME = 'Douglas'");
+ executeSuccess("DELETE FROM TEST");
+
conn.close();
- // revoke them
+ // revoke the right
conn = getConnection("rights");
stat = conn.createStatement();
executeSuccess("REVOKE ALTER ANY SCHEMA FROM SCHEMA_CREATOR");
conn.close();
- // try and fail
+ // try again and fail
conn = getConnection("rights;LOG=2", "SCHEMA_CREATOR", getPassword("xyz"));
stat = conn.createStatement();
assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
@@ -262,6 +280,14 @@
execute("ALTER SCHEMA SCHEMA_RIGHT_TEST_EXISTS RENAME TO SCHEMA_RIGHT_TEST_RENAMED");
assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
execute("DROP SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
+ assertThrows(ErrorCode.NOT_ENOUGH_RIGHTS_FOR_1, stat).
+ execute("CREATE TABLE SCHEMA_RIGHT_TEST_EXISTS.TEST(ID INT PRIMARY KEY, NAME VARCHAR)");
+ assertThrows(ErrorCode.NOT_ENOUGH_RIGHTS_FOR_1, stat).
+ execute("INSERT INTO SCHEMA_RIGHT_TEST_EXISTS.TEST_EXISTS (ID, NAME) VALUES (42, 'Adams')");
+ assertThrows(ErrorCode.NOT_ENOUGH_RIGHTS_FOR_1, stat).
+ execute("UPDATE SCHEMA_RIGHT_TEST_EXISTS.TEST_EXISTS Set NAME = 'Douglas'");
+ assertThrows(ErrorCode.NOT_ENOUGH_RIGHTS_FOR_1, stat).
+ execute("DELETE FROM SCHEMA_RIGHT_TEST_EXISTS.TEST_EXISTS");
conn.close();
}
