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 or change a session user 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
set_user_statement:
SET USER [ '=' ] user_name
| SET CURRENT_USER [ '=' ] user_name
| SET SESSION_USER [ '=' ] user_name
| SET SESSION AUTHORIZATION [ '=' ] 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.
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 ]
grantor:
CURRENT_USER | CURRENT_ROLE
set_role_statement:
SET ROLE role_name
| SET CURRENT_ROLE [ '=' ] role_name
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.
Qualified users can grant roles and other users various privileges on tables or
EXECUTE ON privilege for functions, procedures, aggregates or global privileges.
The table privileges are any combination of
SELECT, INSERT, DELETE, TRUNCATE, UPDATE and REFERENCES.
Only the owner (the user who created the table) 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
qname:
[ schema_name '.' ] object_name
By default, only the monetdb user is allowed 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).
With the special global privileges COPY INTO and COPY FROM it is possible
to grant a user/role the privilege of also using any of these bulk I/O statements.
Note These global privileges apply to all tables on which the user/role has the SELECT or INSERT permission.
There are some standard SQL keywords that return the current user or current role. They are also called pseudo columns and can be used in SQL queries.
SELECT CURRENT_USER as "CURRENT_USER", CURRENT_ROLE as "CURRENT_ROLE", USER as "USER", SESSION_USER as "SESSION_USER";
+--------------+--------------+---------+--------------+
| CURRENT_USER | CURRENT_ROLE | USER | SESSION_USER |
+==============+==============+=========+==============+
| monetdb | monetdb | monetdb | monetdb |
+--------------+--------------+---------+--------------+
The keywords USER and SESSION_USER are synonyms for CURRENT_USER, so they return the same values.
You can also change the current/session user or role, provided the user has the right privileges.
CREATE USER "Mary" WITH PASSWORD 'How Soon Is Now?' NAME 'Mary Smith';
SET USER = "Mary";
SELECT CURRENT_USER as "CURRENT_USER", CURRENT_ROLE as "CURRENT_ROLE", USER as "USER", SESSION_USER as "SESSION_USER";
+--------------+--------------+------+--------------+
| CURRENT_USER | CURRENT_ROLE | USER | SESSION_USER |
+==============+==============+======+==============+
| Mary | monetdb | Mary | Mary |
+--------------+--------------+------+--------------+
SET SESSION_USER = monetdb;
SET SESSION AUTHORIZATION "Mary";
CREATE ROLE controller;
GRANT controller TO monetdb WITH ADMIN OPTION;
SET ROLE controller;
SELECT CURRENT_ROLE;
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.
You may view the list of roles in the database via query:
SELECT * FROM sys.roles;
You may view the list of all authorization objects in the database via query:
SELECT * FROM sys.auths;
Associated system tables: sys.users, sys.roles, sys.auths.
SELECT CURRENT_USER;
ALTER USER SET PASSWORD 'make it private' USING OLD PASSWORD 'monetdb';
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;
You may view the assigned privileges to user objects in the database via query:
SELECT p.obj_id, ids.obj_type, ids.name, ids.schema_id, p.auth_id, a.name as auth_name, pc.privilege_code_name as privilege, p.grantor, p.grantable
FROM sys.privileges p
INNER JOIN sys.ids ids ON p.obj_id = ids.id
INNER JOIN sys.auths a ON a.id = p.auth_id
INNER JOIN sys.privilege_codes pc ON p."privileges" = pc.privilege_code_id
WHERE NOT ids.system;
Associated system tables: sys.privileges, sys.ids, sys.auths, sys.privilege_codes.
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 ',';