Grant and revokeGrant and revoke mk Fri, 04/02/2010 - 12:47
Qualified users can grant roles and other users various privileges on tables or execute privilege on functions and procedures or global privileges. The table privileges are any combination of SELECT, INSERT, DELETE, TRUNCATE, 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.
grantees: [ PUBLIC | authid ] ','...
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). As of the June2016 release MonetDB supports 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.
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 DELETE and TRUNCATE 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 (id INT, val VARCHAR(128));
CREATE TABLE copy_schema.t2 (id INT, num 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 ',';