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
On Thursday, September 19, 2013 2:38:19 PM UTC-7, John Yates wrote:
>
> Thomas,
>
> I work with Tim Monro and will be picking up this project. Any changes
> in how you would approach this item?
>
> John
>
> On Tuesday, July 17, 2012 10:22:33 AM UTC-7, Thomas Mueller wrote:
>>
>> 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.
>>
>>
--
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: trunk/h2/src/main/org/h2/command/Parser.java
===================================================================
--- trunk/h2/src/main/org/h2/command/Parser.java (revision 5192)
+++ trunk/h2/src/main/org/h2/command/Parser.java (working copy)
@@ -3940,6 +3940,13 @@
}
}
+ private int parseAlterRight() {
+ if (readIf("ANY") && readIf("SCHEMA")) {
+ return Right.ALTER_ANY_SCHEMA;
+ }
+ throw DbException.get(ErrorCode.PARSE_ERROR_1); // TODO: find/create better error
+ }
+
private boolean addRoleOrRight(GrantRevoke command) {
if (readIf("SELECT")) {
command.addRight(Right.SELECT);
@@ -3956,6 +3963,10 @@
} else if (readIf("ALL")) {
command.addRight(Right.ALL);
return false;
+ } else if (readIf("ALTER")) {
+ command.addRight(parseAlterRight());
+ command.addTable(null);
+ return true;
} else if (readIf("CONNECT")) {
// ignore this right
return false;
@@ -3971,14 +3982,14 @@
private GrantRevoke parseGrantRevoke(int operationType) {
GrantRevoke command = new GrantRevoke(session);
command.setOperationType(operationType);
- boolean isRoleBased = addRoleOrRight(command);
+ boolean noTableExpected = addRoleOrRight(command);
while (readIf(",")) {
- boolean next = addRoleOrRight(command);
- if (next != isRoleBased) {
+ addRoleOrRight(command);
+ if (command.isRightMode() && command.isRoleMode()) {
throw DbException.get(ErrorCode.ROLES_AND_RIGHT_CANNOT_BE_MIXED);
}
}
- if (!isRoleBased) {
+ if (!noTableExpected) {
if (readIf("ON")) {
do {
Table table = readTableOrView();
Index: trunk/h2/src/main/org/h2/command/ddl/AlterSchemaRename.java
===================================================================
--- trunk/h2/src/main/org/h2/command/ddl/AlterSchemaRename.java (revision 5192)
+++ trunk/h2/src/main/org/h2/command/ddl/AlterSchemaRename.java (working copy)
@@ -47,7 +47,7 @@
if (db.findSchema(newSchemaName) != null || newSchemaName.equals(oldSchema.getName())) {
throw DbException.get(ErrorCode.SCHEMA_ALREADY_EXISTS_1, newSchemaName);
}
- session.getUser().checkAdmin();
+ session.getUser().checkSchemaAdmin();
db.renameDatabaseObject(session, oldSchema, newSchemaName);
ArrayList<SchemaObject> all = db.getAllSchemaObjects();
for (SchemaObject schemaObject : all) {
Index: trunk/h2/src/main/org/h2/command/ddl/CreateSchema.java
===================================================================
--- trunk/h2/src/main/org/h2/command/ddl/CreateSchema.java (revision 5192)
+++ trunk/h2/src/main/org/h2/command/ddl/CreateSchema.java (working copy)
@@ -34,11 +34,12 @@
@Override
public int update() {
- session.getUser().checkAdmin();
+ session.getUser().checkSchemaAdmin();
session.commit(true);
Database db = session.getDatabase();
User user = db.getUser(authorization);
- user.checkAdmin();
+ //user.checkSchemaAdmin(); - disabled until we find a solution for the open database
+ // issue of not having the Right/Role records loaded when this is executed.
if (db.findSchema(schemaName) != null) {
if (ifNotExists) {
return 0;
Index: trunk/h2/src/main/org/h2/command/ddl/DropSchema.java
===================================================================
--- trunk/h2/src/main/org/h2/command/ddl/DropSchema.java (revision 5192)
+++ trunk/h2/src/main/org/h2/command/ddl/DropSchema.java (working copy)
@@ -32,7 +32,7 @@
@Override
public int update() {
- session.getUser().checkAdmin();
+ session.getUser().checkSchemaAdmin();
session.commit(true);
Database db = session.getDatabase();
Schema schema = db.findSchema(schemaName);
Index: trunk/h2/src/main/org/h2/command/ddl/GrantRevoke.java
===================================================================
--- trunk/h2/src/main/org/h2/command/ddl/GrantRevoke.java (revision 5192)
+++ trunk/h2/src/main/org/h2/command/ddl/GrantRevoke.java (working copy)
@@ -183,4 +183,11 @@
return operationType;
}
+ public boolean isRoleMode() {
+ return (roleNames != null);
+ }
+
+ public boolean isRightMode() {
+ return (rightMask != 0);
+ }
}
Index: trunk/h2/src/main/org/h2/engine/Right.java
===================================================================
--- trunk/h2/src/main/org/h2/engine/Right.java (revision 5192)
+++ trunk/h2/src/main/org/h2/engine/Right.java (working copy)
@@ -37,6 +37,11 @@
public static final int UPDATE = 8;
/**
+ * The right bit mask that means: create/alter/drop schema is allowed.
+ */
+ public static final int ALTER_ANY_SCHEMA = 16;
+
+ /**
* The right bit mask that means: select, insert, update, delete, and update
* for this object is allowed.
*/
@@ -77,9 +82,10 @@
buff.append("ALL");
} else {
boolean comma = false;
- comma = appendRight(buff, grantedRight, SELECT, "SELECT", comma);
- comma = appendRight(buff, grantedRight, DELETE, "DELETE", comma);
- comma = appendRight(buff, grantedRight, INSERT, "INSERT", comma);
+ comma = appendRight(buff, grantedRight, SELECT, "SELECT", comma);
+ comma = appendRight(buff, grantedRight, DELETE, "DELETE", comma);
+ comma = appendRight(buff, grantedRight, INSERT, "INSERT", comma);
+ comma = appendRight(buff, grantedRight, ALTER_ANY_SCHEMA, "ALTER ANY SCHEMA", comma);
appendRight(buff, grantedRight, UPDATE, "UPDATE", comma);
}
return buff.toString();
@@ -109,7 +115,10 @@
if (grantedRole != null) {
buff.append(grantedRole.getSQL());
} else {
- buff.append(getRights()).append(" ON ").append(table.getSQL());
+ buff.append(getRights());
+ if (table != null) {
+ buff.append(" ON ").append(table.getSQL());
+ }
}
buff.append(" TO ").append(grantee.getSQL());
return buff.toString();
Index: trunk/h2/src/main/org/h2/engine/User.java
===================================================================
--- trunk/h2/src/main/org/h2/engine/User.java (revision 5192)
+++ trunk/h2/src/main/org/h2/engine/User.java (working copy)
@@ -110,7 +110,7 @@
* @return true if the user has the rights
*/
public boolean hasRight(Table table, int rightMask) {
- if (rightMask != Right.SELECT && !systemUser) {
+ if ((rightMask != Right.SELECT) && !systemUser && (table != null)) {
table.checkWritingAllowed();
}
if (admin) {
@@ -124,22 +124,24 @@
// everybody has access to the metadata information
return true;
}
- String tableType = table.getTableType();
- if (Table.VIEW.equals(tableType)) {
- TableView v = (TableView) table;
- if (v.getOwner() == this) {
- // the owner of a view has access:
- // SELECT * FROM (SELECT * FROM ...)
- return true;
- }
- } else if (tableType == null) {
- // function table
- return true;
+ if (table != null) {
+ String tableType = table.getTableType();
+ if (Table.VIEW.equals(tableType)) {
+ TableView v = (TableView) table;
+ if (v.getOwner() == this) {
+ // the owner of a view has access:
+ // SELECT * FROM (SELECT * FROM ...)
+ return true;
+ }
+ } else if (tableType == null) {
+ // function table
+ return true;
+ }
+ if (table.isTemporary() && !table.isGlobalTemporary()) {
+ // the owner has all rights on local temporary tables
+ return true;
+ }
}
- if (table.isTemporary() && !table.isGlobalTemporary()) {
- // the owner has all rights on local temporary tables
- return true;
- }
if (isRightGrantedRecursive(table, rightMask)) {
return true;
}
@@ -202,6 +204,12 @@
throw DbException.get(ErrorCode.ADMIN_RIGHTS_REQUIRED);
}
}
+
+ public void checkSchemaAdmin() {
+ if (!admin && !hasRight(null, Right.ALTER_ANY_SCHEMA)) {
+ throw DbException.get(ErrorCode.ADMIN_RIGHTS_REQUIRED);
+ }
+ }
@Override
public int getType() {
Index: trunk/h2/src/test/org/h2/test/db/TestRights.java
===================================================================
--- trunk/h2/src/test/org/h2/test/db/TestRights.java (revision 5192)
+++ trunk/h2/src/test/org/h2/test/db/TestRights.java (working copy)
@@ -42,7 +42,8 @@
testDropTempTables();
// testLowerCaseUser();
testSchemaRenameUser();
- testAccessRights();
+ testAccessRights();
+ testSchemaAdminRole();
deleteDb("rights");
}
@@ -198,7 +199,72 @@
stat.execute("drop user test1");
conn.close();
}
+
+ private void testSchemaAdminRole() throws SQLException {
+ if (config.memory) {
+ return;
+ }
+ deleteDb("rights");
+ Connection conn = getConnection("rights");
+ stat = conn.createStatement();
+ // default table type
+ testTableType(conn, "MEMORY");
+ testTableType(conn, "CACHED");
+
+ 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");
+ executeSuccess("CREATE SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
+ conn.close();
+
+ /* try and fail */
+ conn = getConnection("rights;LOG=2", "SCHEMA_CREATOR", getPassword("xyz"));
+ stat = conn.createStatement();
+ assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
+ execute("CREATE SCHEMA SCHEMA_RIGHT_TEST_WILL_FAIL");
+ assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
+ execute("ALTER SCHEMA SCHEMA_RIGHT_TEST_EXISTS RENAME TO SCHEMA_RIGHT_TEST_WILL_FAIL");
+ assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
+ execute("DROP SCHEMA SCHEMA_RIGHT_TEST_EXISTS");
+ conn.close();
+
+ /* give them */
+ 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();
+ 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");
+ conn.close();
+
+ /* revoke them */
+ conn = getConnection("rights");
+ stat = conn.createStatement();
+ executeSuccess("REVOKE ALTER ANY SCHEMA FROM SCHEMA_CREATOR");
+ conn.close();
+
+ /* try and fail */
+ conn = getConnection("rights;LOG=2", "SCHEMA_CREATOR", getPassword("xyz"));
+ stat = conn.createStatement();
+ assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
+ execute("CREATE SCHEMA SCHEMA_RIGHT_TEST");
+ assertThrows(ErrorCode.ADMIN_RIGHTS_REQUIRED, stat).
+ 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");
+ conn.close();
+ }
+
private void testAccessRights() throws SQLException {
if (config.memory) {
return;
