Privileges

All interactions with the database server are attributed to a particular user known to the database system. Therefore, a user login name should be created upfront, much like you would have to on any computer system. Your user name determines the schemas you have permission to access and your role in managing them. Much like your computers file system, it is essential to restrict access to your valuable database content via grant and revoke privileges and/or roles.

To create, alter or drop a user in the database you can use the following syntax:

user_statement:
    CREATE USER user_name
          WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'user_password' NAME 'full name'
          [ SCHEMA schema_name ] [ SCHEMA PATH 'schema_names_list' ]
          [ MAX_MEMORY num_bytes | MAX_MEMORY sizestr | NO MAX_MEMORY ]
          [ MAX_WORKERS count | NO MAX_WORKERS ]
          [ OPTIMIZER optimizer ] [ DEFAULT ROLE role_name ]
   | ALTER USER user_name RENAME TO new_user_name
   | ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD 'new_password'
          USING OLD PASSWORD 'user_password'
   | ALTER USER user_name
          [ WITH [ENCRYPTED | UNENCRYPTED] PASSWORD 'user_password' ]
          [ SET SCHEMA schema_name ] [ SCHEMA PATH 'schema_names_list' ]
          [ DEFAULT ROLE role_name ]
          [ MAX_MEMORY num_bytes | MAX_MEMORY sizestr | NO MAX_MEMORY ]
          [ MAX_WORKERS count | NO MAX_WORKERS ]
   | DROP USER user_name

The properties of the user can be changed using the different alter user statements. The SET PASSWORD USING OLD PASSWORD clause can be used by a user to change their own password. The SCHEMA PATH is a single-quoted string containing a comma-separated list of double-quoted schema names. For example, '"myschema","sys"'. MAX_MEMORY specifies the maximum amount of memory the user is allowed to use. Besides number of bytes it also accepts strings of the form ‘10MiB’, ‘10G’, etc. The MAX_MEMORY is approximate. MAX_WORKERS is the maximum number of worker threads the user is allowed to use. This is also approximate.

Only a MonetDB administrator may create new database users.

Note that for a user (including the administrator user: monetdb) to change its own password, the ALTER USER SET form has to be used where the current password is given. When the ENCRYPTED clause is given for PASSWORD, a hashed form of the password has to be supplied. The type of this hash depends on how the server was configured, but defaults to SHA512.

Roles

Every user takes a role with respect to the privileges on a database scheme. A user can be granted multiple roles and a role can be granted to multiple users.

By the creation of a new user, a role with the same name is automatically created, which is also the default role the user takes. However, only the role can be granted to other users/roles.

Privileges cannot be inherited via another role. For example Alice can only SELECT, Bob can only INSERT, and Alice is granted to Bob. If Bob in turn is granted to Charlie, then Charlie can only INSERT, but not SELECT.

role_statement:
    CREATE ROLE role_name   [ WITH ADMIN grantor ]
  | DROP ROLE role_name   [ WITH ADMIN grantor ]
  | SET ROLE role_name

grantor:
    CURRENT_USER | CURRENT_ROLE

A user can only assume a role after he/she has logged in. The user assumes a role by using the SET ROLE command. Then the user can use all the right given to that role. Two roles cannot be assumed at once.

Grant and Revoke

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.

grant:
     GRANT privileges TO grantees   [ WITH GRANT OPTION ] [ FROM grantor ]
   | GRANT authid [, ... ] TO grantees   [ WITH ADMIN OPTION ]   [ FROM grantor ]

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

privileges:
     object_privileges ON  [ TABLE ]  qname
   | EXECUTE ON [ FUNCTION | AGGREGATE ] qname
   | global_privilege [ ',' global_privilege ]

object_privileges:
    ALL [ PRIVILEGES ] | table-operation [, ... ]

table-operation:
     INSERT
   | DELETE
   | TRUNCATE
   | UPDATE [ opt_column_list ]
   | SELECT [ opt_column_list ]
   | REFERENCES [ opt_column_list ]

global_privilege:
    COPY INTO | COPY FROM

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).

Examples

You may view the list of users in the database via query:

SELECT * FROM sys.users;

The user name, password and database name are needed to authenticate yourself against tools such as mclient and all APIs, to gain access to the particular database. Once connected the current schema is the one as specified for the user.

Every user plays a certain role. So when a user is created, a role with the same name is automatically created as well. The difference is that the user has all corresponding permissions, e.g. the user has all permissions on the table it creates. However, the role with the same name remains empty until permissions have been granted to it explicitly.

Alter permissions

SELECT CURRENT_USER;
ALTER USER SET PASSWORD 'make it private' USING OLD PASSWORD 'monetdb';

Grant permissions per table

The current user must have permission to GRANT privileges.

GRANT ALL ON TABLE web_event TO PUBLIC;
GRANT SELECT, INSERT ON TABLE "web_log" TO jan, piet, controller WITH GRANT OPTION;
GRANT EXECUTE ON FUNCTION refresh_func TO PUBLIC;
GRANT COPY INTO, COPY FROM ON TABLE new_facts TO mrdata;

REVOKE INSERT, DELETE, TRUNCATE ON TABLE web_event FROM jan;
REVOKE EXECUTE ON FUNCTION refresh_func FROM piet;

GRANT controller TO jan WITH ADMIN OPTION;
REVOKE controller FROM jan;

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;

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 ',';