Hi,
I changed the patch to accomplish the expected behavior. Now the rights on
a schema will be valid for any table of the related schema in any time.
Besides, it's worth to note that the rights on a schema takes precedence
over the rights in the table. Let me know if I'm in the right way and/or if
is missing something.
Regards,
Fred
2015-04-12 7:05 GMT-03:00 Fred&Dani&Pandora&Aquiles <[email protected]>:
> Hi Rami,
>
> I have implemented for myown use a user defined function that has the
>> following syntax:
>> F.GRANT_RIGHTS('comma_separated_list_of_rights',
>> '*|comma_separated_list_of_schemas', '*|comma_separated_list_of_users')
>> With this you can grant a set of rights to any number of schemas to any
>> number of users.
>> The implementation was 30-40 lines.
>>
>> The problem is that when I do development I have to run my GRANT_RIGHTS
>> statement over and over again when I drop and recreate a table (with some
>> changes).
>> So the useful feature would be that giving rights to all objects in a
>> schema would mean all objects now and in the future.
>>
>
> I think the MySQL implements such feature.
>
> If someone else starts creating new tables in the same db for some other
>> purpose then it would make sense that he would create a new schema for that
>> new purpose, no?
>>
>
> I have cases in my job that fits in my previous post, where there are
> schemas shared by different users, but not all tables in the related
> schemas are necessarily shared. My main concern is about privileges be
> granted automatically and some changes be executed by unexpected users. On
> the other hand, I agree (by ignoring my previous concern) that would be
> very helpful the permissions in the schema be valid for the related tables
> in any time.
>
> Regards,
>
> Fred
>
--
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/d/optout.
diff --git a/h2/src/main/org/h2/command/Parser.java b/h2/src/main/org/h2/command/Parser.java
index 0f0fa19..6f2742c 100644
--- a/h2/src/main/org/h2/command/Parser.java
+++ b/h2/src/main/org/h2/command/Parser.java
@@ -4262,10 +4262,16 @@
}
if (tableClauseExpected) {
if (readIf("ON")) {
- do {
- Table table = readTableOrView();
- command.addTable(table);
- } while (readIf(","));
+ if (readIf("SCHEMA")){
+ Schema schema = database.getSchema(currentToken);
+ command.setSchema(schema);
+ read();
+ }else{
+ do {
+ Table table = readTableOrView();
+ command.addTable(table);
+ } while (readIf(","));
+ }
}
}
if (operationType == CommandInterface.GRANT) {
diff --git a/h2/src/main/org/h2/command/ddl/GrantRevoke.java b/h2/src/main/org/h2/command/ddl/GrantRevoke.java
index 7b6cd6c..d154156 100644
--- a/h2/src/main/org/h2/command/ddl/GrantRevoke.java
+++ b/h2/src/main/org/h2/command/ddl/GrantRevoke.java
@@ -15,6 +15,7 @@
import org.h2.engine.Role;
import org.h2.engine.Session;
import org.h2.message.DbException;
+import org.h2.schema.Schema;
import org.h2.table.Table;
import org.h2.util.New;
@@ -31,6 +32,7 @@
private int operationType;
private int rightMask;
private final ArrayList<Table> tables = New.arrayList();
+ private Schema schema;
private RightOwner grantee;
public GrantRevoke(Session session) {
@@ -106,16 +108,29 @@
private void grantRight() {
Database db = session.getDatabase();
- for (Table table : tables) {
- Right right = grantee.getRightForTable(table);
+ if (schema != null){
+ Right right = grantee.getRightForSchema(schema);
if (right == null) {
int id = getObjectId();
- right = new Right(db, id, grantee, rightMask, table);
- grantee.grantRight(table, right);
+ right = new Right(db, id, grantee, rightMask, schema);
+ grantee.grantRight(schema, right);
db.addDatabaseObject(session, right);
} else {
right.setRightMask(right.getRightMask() | rightMask);
db.updateMeta(session, right);
+ }
+ }else{
+ for (Table table : tables) {
+ Right right = grantee.getRightForTable(table);
+ if (right == null) {
+ int id = getObjectId();
+ right = new Right(db, id, grantee, rightMask, table);
+ grantee.grantRight(table, right);
+ db.addDatabaseObject(session, right);
+ } else {
+ right.setRightMask(right.getRightMask() | rightMask);
+ db.updateMeta(session, right);
+ }
}
}
}
@@ -139,19 +154,34 @@
}
private void revokeRight() {
- for (Table table : tables) {
- Right right = grantee.getRightForTable(table);
- if (right == null) {
- continue;
+ if (schema != null){
+ Right right = grantee.getRightForSchema(schema);
+ if (right != null) {
+ int mask = right.getRightMask();
+ int newRight = mask & ~rightMask;
+ Database db = session.getDatabase();
+ if (newRight == 0) {
+ db.removeDatabaseObject(session, right);
+ } else {
+ right.setRightMask(newRight);
+ db.updateMeta(session, right);
+ }
}
- int mask = right.getRightMask();
- int newRight = mask & ~rightMask;
- Database db = session.getDatabase();
- if (newRight == 0) {
- db.removeDatabaseObject(session, right);
- } else {
- right.setRightMask(newRight);
- db.updateMeta(session, right);
+ } else{
+ for (Table table : tables) {
+ Right right = grantee.getRightForTable(table);
+ if (right == null) {
+ continue;
+ }
+ int mask = right.getRightMask();
+ int newRight = mask & ~rightMask;
+ Database db = session.getDatabase();
+ if (newRight == 0) {
+ db.removeDatabaseObject(session, right);
+ } else {
+ right.setRightMask(newRight);
+ db.updateMeta(session, right);
+ }
}
}
}
@@ -178,6 +208,15 @@
public void addTable(Table table) {
tables.add(table);
}
+
+ /**
+ * Set the specified schema
+ *
+ * @param schema the schema
+ */
+ public void setSchema(Schema schema){
+ this.schema = schema;
+ }
@Override
public int getType() {
diff --git a/h2/src/main/org/h2/engine/Right.java b/h2/src/main/org/h2/engine/Right.java
index b55d0eb..6d94d5b 100644
--- a/h2/src/main/org/h2/engine/Right.java
+++ b/h2/src/main/org/h2/engine/Right.java
@@ -7,6 +7,7 @@
import org.h2.message.DbException;
import org.h2.message.Trace;
+import org.h2.schema.Schema;
import org.h2.table.Table;
/**
@@ -49,6 +50,7 @@
private Role grantedRole;
private int grantedRight;
private Table grantedTable;
+ private Schema grantedSchema;
private RightOwner grantee;
public Right(Database db, int id, RightOwner grantee, Role grantedRole) {
@@ -63,6 +65,14 @@
this.grantee = grantee;
this.grantedRight = grantedRight;
this.grantedTable = grantedRightOnTable;
+ }
+
+ public Right(Database db, int id, RightOwner grantee, int grantedRight,
+ Schema grantedRightOnSchema) {
+ initDbObjectBase(db, id, "" + id, Trace.USER);
+ this.grantee = grantee;
+ this.grantedRight = grantedRight;
+ this.grantedSchema = grantedRightOnSchema;
}
private static boolean appendRight(StringBuilder buff, int right, int mask,
@@ -101,6 +111,10 @@
return grantedTable;
}
+ public Schema getGrantedSchema() {
+ return grantedSchema;
+ }
+
public DbObject getGrantee() {
return grantee;
}
@@ -126,9 +140,30 @@
return buff.toString();
}
+ /**
+ * The same logic as getCreateSQLForCopy for a table. However, the inclusion of
+ * permissions for schemas made this method necessary, once it creates a copy of
+ * the Right granted for a schema specifically.
+ *
+ * @param schema the schema
+ * @param quotedName the quoted name
+ * @return the SQL statement
+ */
+ private String getCreateSQLForCopy(Schema schema, String quotedName) {
+ StringBuilder buff = new StringBuilder();
+ buff.append("GRANT ").append(getRights());
+ if (schema != null) {
+ buff.append(" ON SCHEMA ").append(schema.getSQL());
+ }
+ buff.append(" TO ").append(grantee.getSQL());
+ return buff.toString();
+ }
+
+
+
@Override
public String getCreateSQL() {
- return getCreateSQLForCopy(grantedTable, null);
+ return grantedSchema != null ? getCreateSQLForCopy(grantedSchema, null) : getCreateSQLForCopy(grantedTable, null);
}
@Override
diff --git a/h2/src/main/org/h2/engine/RightOwner.java b/h2/src/main/org/h2/engine/RightOwner.java
index 4687ad4..4255741 100644
--- a/h2/src/main/org/h2/engine/RightOwner.java
+++ b/h2/src/main/org/h2/engine/RightOwner.java
@@ -6,6 +6,7 @@
package org.h2.engine;
import java.util.HashMap;
+import org.h2.schema.Schema;
import org.h2.table.Table;
import org.h2.util.New;
@@ -24,6 +25,11 @@
* The map of granted rights.
*/
private HashMap<Table, Right> grantedRights;
+
+ /**
+ * The map of granted rights for schemas specifically.
+ */
+ private HashMap<Schema, Right> grantedSchemaRights;
protected RightOwner(Database database, int id, String name,
String traceModule) {
@@ -55,7 +61,9 @@
/**
* Check if a right is already granted to this object or to objects that
- * were granted to this object.
+ * were granted to this object. Besides, the rights for schemas takes
+ * precedence over rights of tables, in other words, the rights of schemas
+ * will be valid for every each table in the related schema.
*
* @param table the table to check
* @param rightMask the right mask to check
@@ -63,6 +71,15 @@
*/
boolean isRightGrantedRecursive(Table table, int rightMask) {
Right right;
+
+ if (grantedSchemaRights != null && table != null) {
+ right = grantedSchemaRights.get(table.getSchema());
+ if (right != null) {
+ if ((right.getRightMask() & rightMask) == rightMask) {
+ return true;
+ }
+ }
+ }
if (grantedRights != null) {
right = grantedRights.get(table);
if (right != null) {
@@ -94,6 +111,20 @@
}
grantedRights.put(table, right);
}
+
+ /**
+ * Grant a right for the given schema. Only one right object per schema is
+ * supported.
+ *
+ * @param schema the schema
+ * @param right the right
+ */
+ public void grantRight(Schema schema, Right right) {
+ if (grantedSchemaRights == null) {
+ grantedSchemaRights = New.hashMap();
+ }
+ grantedSchemaRights.put(schema, right);
+ }
/**
* Revoke the right for the given table.
@@ -107,6 +138,21 @@
grantedRights.remove(table);
if (grantedRights.size() == 0) {
grantedRights = null;
+ }
+ }
+
+ /**
+ * Revoke the right for the given schema.
+ *
+ * @param schema the schema
+ */
+ void revokeRight(Schema schema) {
+ if (grantedSchemaRights == null) {
+ return;
+ }
+ grantedSchemaRights.remove(schema);
+ if (grantedSchemaRights.size() == 0) {
+ grantedSchemaRights = null;
}
}
@@ -154,6 +200,19 @@
}
return grantedRights.get(table);
}
+
+ /**
+ * Get the 'grant schema' right of this object.
+ *
+ * @param schema the granted table
+ * @return the right or null if the right has not been granted
+ */
+ public Right getRightForSchema(Schema schema) {
+ if (grantedSchemaRights == null) {
+ return null;
+ }
+ return grantedSchemaRights.get(schema);
+ }
/**
* Get the 'grant role' right of this object.
diff --git a/h2/src/main/org/h2/table/MetaTable.java b/h2/src/main/org/h2/table/MetaTable.java
index 8542daa..664d6e5 100644
--- a/h2/src/main/org/h2/table/MetaTable.java
+++ b/h2/src/main/org/h2/table/MetaTable.java
@@ -1128,7 +1128,9 @@
"USER" : "ROLE";
if (role == null) {
Table granted = r.getGrantedTable();
- String tableName = identifier(granted.getName());
+ Schema schema = (granted != null) ? granted.getSchema() : r.getGrantedSchema();
+ String tableName = (granted != null) ? identifier(granted.getName()) : "";
+ String schemaName = (schema != null) ? identifier(schema.getName()) : "";
if (!checkIndex(session, tableName, indexFrom, indexTo)) {
continue;
}
@@ -1142,9 +1144,9 @@
// RIGHTS
r.getRights(),
// TABLE_SCHEMA
- identifier(granted.getSchema().getName()),
+ schemaName,
// TABLE_NAME
- identifier(granted.getName()),
+ tableName,
// ID
"" + r.getId()
);
diff --git a/h2/src/test/org/h2/test/TestBase.java b/h2/src/test/org/h2/test/TestBase.java
index 580c38f..d985548 100644
--- a/h2/src/test/org/h2/test/TestBase.java
+++ b/h2/src/test/org/h2/test/TestBase.java
@@ -1006,7 +1006,7 @@
protected void assertThrows(String expectedErrorMessage, Statement stat,
String sql) {
try {
- stat.executeQuery(sql);
+ stat.execute(sql);
fail("Expected error: " + expectedErrorMessage);
} catch (SQLException ex) {
assertStartsWith(ex.getMessage(), expectedErrorMessage);
diff --git a/h2/src/test/org/h2/test/db/TestRights.java b/h2/src/test/org/h2/test/db/TestRights.java
index e2f1b51..51ee209 100644
--- a/h2/src/test/org/h2/test/db/TestRights.java
+++ b/h2/src/test/org/h2/test/db/TestRights.java
@@ -36,6 +36,8 @@
public void test() throws SQLException {
testLinkedTableMeta();
testGrantMore();
+ testGrantSchema();
+ testRevokeSchema();
testOpenNonAdminWithMode();
testDisallowedTables();
testDropOwnUser();
@@ -111,6 +113,121 @@
conn.close();
}
+ private void testGrantSchema() throws SQLException {
+ deleteDb("rights");
+ Connection conn = getConnection("rights");
+ //Test with user
+ stat = conn.createStatement();
+ stat.execute("create user test_user password 'test'");
+ stat.execute("create table test1(id int)");
+ stat.execute("create table test2(id int)");
+ stat.execute("create table test3(id int)");
+ stat.execute("grant insert on schema public to test_user");
+ stat.execute("create table test4(id int)");
+ conn.close();
+ conn = getConnection("rights","test_user","test");
+ stat = conn.createStatement();
+ //Must proceed
+ stat.execute("insert into test1 values (1)");
+ stat.execute("insert into test2 values (1)");
+ stat.execute("insert into test3 values (1)");
+ stat.execute("insert into test4 values (1)");
+ //Must not proceed
+ assertThrows("Not enough rights for object \"PUBLIC.TEST1\"", stat, "select * from test1");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST2\"", stat, "select * from test2");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST3\"", stat, "select * from test3");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST4\"", stat, "select * from test4");
+ conn.close();
+ //Test with role
+ conn = getConnection("rights");
+ stat = conn.createStatement();
+ stat.execute("create role test_role");
+ stat.execute("grant test_role to test_user");
+ stat.execute("grant select on schema public to test_role");
+ stat.execute("create table test5(id int)");
+ //Must proceed
+ stat.execute("insert into test1 values (2)");
+ stat.execute("insert into test2 values (2)");
+ stat.execute("insert into test3 values (2)");
+ stat.execute("insert into test4 values (2)");
+ stat.execute("insert into test5 values (1)");
+ stat.execute("select * from test1");
+ stat.execute("select * from test2");
+ stat.execute("select * from test3");
+ stat.execute("select * from test4");
+ stat.execute("select * from test5");
+ conn.close();
+ deleteDb("rights");
+ }
+
+ private void testRevokeSchema() throws SQLException {
+ deleteDb("rights");
+ Connection conn = getConnection("rights");
+ stat = conn.createStatement();
+ /**
+ * Test with user
+ */
+ stat = conn.createStatement();
+ stat.execute("create user test_user password 'test'");
+ stat.execute("create table test1(id int)");
+ stat.execute("create table test2(id int)");
+ stat.execute("create table test3(id int)");
+ stat.execute("grant insert on schema public to test_user");
+ conn.close();
+ conn = getConnection("rights","test_user","test");
+ stat = conn.createStatement();
+ //Must proceed
+ stat.execute("insert into test1 values (1)");
+ stat.execute("insert into test2 values (1)");
+ stat.execute("insert into test3 values (1)");
+ conn.close();
+ conn = getConnection("rights");
+ stat = conn.createStatement();
+ stat.execute("revoke insert on schema public from test_user");
+ stat.execute("create table test4(id int)");
+ conn.close();
+ conn = getConnection("rights","test_user","test");
+ stat = conn.createStatement();
+ //Must not proceed
+ assertThrows("Not enough rights for object \"PUBLIC.TEST1\"", stat, "insert into test1 values (2)");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST2\"", stat, "insert into test2 values (2)");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST3\"", stat, "insert into test3 values (2)");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST4\"", stat, "insert into test4 values (2)");
+ conn.close();
+ /**
+ * Test with role
+ */
+ conn = getConnection("rights");
+ stat = conn.createStatement();
+ stat.execute("create role test_role");
+ stat.execute("grant test_role to test_user");
+ stat.execute("grant select on schema public to test_role");
+ conn.close();
+ conn = getConnection("rights","test_user","test");
+ stat = conn.createStatement();
+ //Must proceed
+ stat.execute("select * from test1");
+ stat.execute("select * from test2");
+ stat.execute("select * from test3");
+ stat.execute("select * from test4");
+ conn.close();
+ conn = getConnection("rights");
+ stat = conn.createStatement();
+ stat.execute("revoke select on schema public from test_role");
+ stat.execute("create table test5(id int)");
+ conn.close();
+ conn = getConnection("rights","test_user","test");
+ stat = conn.createStatement();
+ //Must not proceed
+ assertThrows("Not enough rights for object \"PUBLIC.TEST1\"", stat, "select * from test1");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST2\"", stat, "select * from test2");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST3\"", stat, "select * from test3");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST4\"", stat, "select * from test4");
+ assertThrows("Not enough rights for object \"PUBLIC.TEST5\"", stat, "select * from test5");
+ conn.close();
+ deleteDb("rights");
+ }
+
private void testOpenNonAdminWithMode() throws SQLException {
if (config.memory) {
return;
