Grant privileges to users on MonetDB

Question

I am trying to grant admin, i.e. "monetdb" privileges to users, without any success so far (the privileges should extend to all the tables of the sys schema). As I have a schema with many tables would be very complex to give explicit rights (e.g. SELECT ) to all the users mentioning all the tables: I need to find an efficient way to do this.

I log into MonetDB with SQLWorkbenchJ using the superuser monetdb. I have also tried to directly send queries using R and the MonetDB.R package (with no difference).

I create a user associated to a schema (e.g. the sys schema) as

CREATE USER "user1" WITH PASSWORD 'user1' NAME 'user one' SCHEMA "sys";

Then I try to GRANT "monetdb" privileges to user1

GRANT   monetdb TO "user1";

And I do not get any error.

If I log into MonetDb as user1 and try a simple select (on a pre-existing table of the sys schema) I get:

SELECT * FROM sys.departmentfunctionindex
SELECT: access denied for user1 to table 'sys.departmentfunctionindex'

Clearly I'm missing something.

Any suggestion is welcome.

Answer 1

I think I get it now.

The following works, using SQLWorkbenchJ I log into MonetDB as monetdb (superuser).

I run:

CREATE USER "user20" WITH PASSWORD 'user20' NAME 'user 20'   SCHEMA "sys";

CREATE SCHEMA "mschema" AUTHORIZATION "monetdb";
CREATE table "mschema"."mtestTable"(v1 int, v2 int);
INSERT INTO "mschema"."mtestTable" VALUES (4, 4);
GRANT monetdb to "user20"; -- this gives implicit superuser powers to user20 (but not for the "sys" schema)

Now I log out and login again as user "user20".

I run:

SET SCHEMA mschema; -- I was missing this before but it is essential
SET ROLE monetdb;

At this stage user20 has got all the authorisations of the superuser monetdb, e.g.:

SELECT * FROM "mschema"."mtestTable"; -- can use select
DROP TABLE "mschema"."mtestTable"; -- can use drop etc.

Thanks to @Hannes, @Ying & @Dimitar

Answer 2

Here is an working example for granting SELECT privileges to a separate user with MonetDB:

as admin (e.g. monetdbaccount)

CREATE SCHEMA "somedataschema";
CREATE TABLE "somedataschema"."somepersistenttable" (i INTEGER);
INSERT INTO "somedataschema"."somepersistenttable" VALUES (42);

CREATE USER "someuser" WITH PASSWORD 'someuserpass' NAME 'someusername' SCHEMA "sys";
GRANT SELECT ON "somedataschema"."somepersistenttable" TO "someuser";

CREATE SCHEMA "someuserschema" AUTHORIZATION "someuser";
ALTER USER "someuser" SET SCHEMA "someuserschema";

Now, someuser can SELECT from somepersistenttable, but not modify it. Should this user need a table on its own, someuserschema or temporary tables (deleted when user logs out) could be used. Hence, this works:

SELECT * FROM "somedataschema"."somepersistenttable";

CREATE TABLE "someuserschema"."sometemptable" (i integer);
INSERT INTO "someuserschema"."sometemptable" VALUES (84);
SELECT * FROM "sometemptable";

CREATE TEMPORARY TABLE "sometemptable" (i INTEGER) ON COMMIT PRESERVE ROWS;
INSERT INTO "sometemptable" VALUES (42);
SELECT  * FROM "sometemptable";

And this will produce an insufficient privileges error (same for update, drop, alter etc.):

INSERT INTO "somedataschema"."somepersistenttable" VALUES (43);

Answer 3

Please have a look at this MonetDB bug report. Grant privilege works for user created schemas, but the bug-fix seems not cover the default "sys" schema.

Answer 4

No, you don't have to explicitly grant access to each individual tables. You can still use GRANT monetdb to "user1". See my previous answer, for now, you just need to create your tables under a user created schema. I just tried it in the "default" branch of MonetDB.