Skip to main content

Grant and revoke

Qualified users can grant roles and other users various privileges on tables. These privileges are any combination of SELECT, INSERT, UPDATE and REFERENCES. ALTER and INDEX privileges cannot be granted. Only the owner has the right to alter a table definition. Any privilege can be later revoked, which takes any combination of the previously granted privileges.

grant:
    GRANT privileges TO grantees [ WITH GRANT OPTION ]
  | GRANT role_name ',' ... TO grantees [ WITH ADMIN OPTION ] [ WITH ADMIN grantor ]

revoke:
    REVOKE [ GRANT OPTION FOR ] privileges FROM grantees [ FROM grantor ]
  | REVOKE [ ADMIN OPTION FOR ] role_name ','... FROM grantees [ FROM grantor ]

privileges:
    object_privileges ON [ TABLE ] qname

object_privileges:
    ALL [ PRIVILEGES ] | operation ','...

operation:
    INSERT | DELETE | UPDATE [ opt_column_list ] | SELECT [ opt_column_list ]
  | REFERENCES [ opt_column_list ]
  | EXECUTE grantees: [ PUBLIC | authid ] ','...

grantor:
    CURRENT_USER | CURRENT_ROLE

Bulk I/O permissions

For reasons of security, by default, MonetDB only allows the special user monetdb to execute the bulk I/O statements COPY INTO (copy data from the database into a file) and COPY FROM (copy data from a file into the database). In the feature release following Jul2015, MonetDB allows granting a user/role the privilege of using the bulk I/O statements. These privileges are global privileges, i.e., they apply to all tables on which the user/role has the INSERT permission.

privileges:
  | global_privilege [ ',' global_privilege ]

global_privilege:
    COPY INTO | COPY FROM

Examples

1. Grant permissions per table

It is possible to grant one privilege at a time to a user, per table:

GRANT SELECT ON bookSchema.toOrderTable TO libraryUser;
GRANT INSERT ON bookSchema.tOrderTable TO libraryUser;

However, if you want the user to be able to SELECT, INSERT, UPDATE and DELETE then it can be done in one go:

GRANT ALL ON bookSchema.orderTable TO libraryWorker;

2. Parallel bulk load in shared schema

The following queries grant both the ownership of a schema and the bulk data loading permission (i.e., COPY FROM) to multiple users. Then, those users can log in and bulk load the data into different tables (of the same schema) in parallel.

-- 'monetdb' user
CREATE ROLE copy_role;
GRANT COPY FROM TO copy_role;
CREATE SCHEMA copy_schema AUTHORIZATION copy_role;

CREATE USER user1 WITH PASSWORD 'user1' NAME 'copy user' SCHEMA copy_schema;
CREATE USER user2 WITH PASSWORD 'user2' NAME 'copy user' SCHEMA copy_schema;

GRANT copy_role TO user1;
GRANT copy_role TO user2;

CREATE TABLE copy_schema.t1 (i INT, j INT);
CREATE TABLE copy_schema.t2 (i INT, j INT);

-- normal user user1
SET ROLE copy_role;
COPY INTO t1 FROM '/[path-to]/input.csv' USING DELIMITERS ',';

-- normal user user2
SET ROLE copy_role;
COPY INTO t2 FROM '/[path-to]/input.csv' USING DELIMITERS ',';