Hello!

I found it strange why we don't support GRANT/REVOKE privileges statements on schemas. I looked into the SQL standard, and there is no mention of it. You can use roles to limit schema access for each user. Here you have an example:

CREATE ROLE my_role;

CREATE SCHEMA my_schema AUTHORIZATION my_role;

CREATE USER my_user with password 'p1' name 'User with role' schema "my_schema";

GRANT my_role to my_user;

You can add more fine grained access to each table with table privileges as you mentioned.

As for specific rights such as DDL, we don't support it as of now. You can create a feature request on Bugzilla, but as it is not paid, it will have low priority.

Best regards,

Pedro

New to Monetdb, From a DBA perspective I’m having trouble understanding how to manage access rights to schemas and the tables therein. My department has many senior analysts who should have admin rights to the tables within the schema (DML - select, insert, update, delete, trunc, etc; DDL create table, drop table, create seq, etc). There are also some users that need select only in a given schema. How do I manage this without having to grant access per table? Is that where roles come into play? Say super users get privilege_code_id= 79 (SELECT,INSERT,UPDATE,DELETE,TRUNCATE) whereas some users would be assigned  privilege_code_id= 1 (SELECT). What about DDL rights? I am not seeing those in the roles? I do see the ability when the schema is created with CREATE SCHEMA "ABC" AUTHORIZATION "xyz_user". What I don’t want to do is have to run a query that searches all tables in a schema, grant admin DDL to them.
Thanks!

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list