monetdb

monet db user / role / database / tables

monetdb create ebi_dwh  
monetdb release ebi_dwh

CREATE ROLE "ebi_reader";
CREATE ROLE "ebi_writer";

CREATE SCHEMA "ebi" AUTHORIZATION ebi_reader;
CREATE SCHEMA "ebi_data" AUTHORIZATION ebi_writer;

CREATE USER "ebi" WITH PASSWORD 'pwd' NAME 'data_views user' SCHEMA "ebi" DEFAULT ROLE "ebi_reader";
CREATE USER "ebi_data" WITH PASSWORD 'pwd' NAME 'data_tables user' SCHEMA "ebi_data" DEFAULT ROLE "ebi_writer";

GRANT ebi_reader TO ebi;
GRANT ebi_reader TO ebi_data;
GRANT ebi_writer TO ebi_data;

mclient -u ebi_data -d ebi_dwh
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Dec2023)
Database: MonetDB v11.49.1 (Dec2023), 'mapi:monetdb://:50000/ebi_dwh'
FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>create table ebi.test_via_ebidata (id int, name varchar(5));
CREATE TABLE: insufficient privileges for user 'ebi_data' in schema 'ebi'

Hello, What am I doing wrong? I thought ebi_data is allowed to create tables in the ebi schema because I granted

GRANT ebi_reader TO ebi_data

What did I overlook?" Thank you in advance Br

I thought ebi_data is allowed to create tables in the ebi schema because I granted

GRANT ebi_reader TO ebi_data
Solution

  • You also need to set the role of ebi_data to ebi_reader:

    $ mclient -d ebi_dwh -u ebi_data
...
sql>create table ebi.test_via_ebidata (id int, name varchar(5));
CREATE TABLE: insufficient privileges for user 'ebi_data' in schema 'ebi'
sql>set role ebi_reader;
operation successful
sql>create table ebi.test_via_ebidata (id int, name varchar(5));
operation successful

    You need to be an owner of a schema to have the privilege to create a table. the owner of the schema ebi is the role ebi_reader.

    When user ebi_data logs in, the user assumes its default role ebi_writer. To create a table in the schema ebi, ebi_data needs to switch its role to ebi_reader.

    PS> I guess there was a typo in your query. I changed it into CREATE SCHEMA "ebi" AUTHORIZATION ebi_reader;