Privileges

Privileges mk Wed, 02/26/2014 - 15:41

Users

Users mk Sat, 03/27/2010 - 22:54

All interactions with the database server are attributed to a particular user known to the system. Therefore, a user-name should be created upfront, much like you would have to on any computer system, Your user name determines the database instances covered by database schemas you have permission to access and your role in managing them. Much like your computer environment, it is essential to restrict access to your valuable database content.

To create, modify and drop a user in the database you can use the following syntax (NB: the use of double and single quotes, i.e., id versus string value, at the various places.):

user_stmt:
    CREATE USER "user_name" WITH [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'user_password' NAME 'name' SCHEMA "schema_name"

The "user_name", 'user_password' and "schema_name" are needed to authenticate yourself against tools, such as mclient and all APIs, to gain access to the particular database schema.

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.

The user name and password can be changed using the alter statement as well.

   | ALTER USER ident  WITH [ ENCRYPTED | UNENCRYPTED] PASSWORD string
   | ALTER USER ident  SET SCHEMA ident
   | ALTER USER ident  WITH [ENCRYPTED | UNENCRYPTED] PASSWORD SET SCHEMA ident
   | ALTER USER RENAME TO ident  
   | ALTER USER SET [ENCRYPTED | UNENCRYPTED] PASSWORD string USING OLD PASSWORD string

Note that for a user to change its own password, the second ALTER USER form has to be used where the current password is given. This also holds for the administrator user (default: monetdb). 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

Roles mk Mon, 05/24/2010 - 07:51

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
  | GRANT role_name TO user_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

Grant and revoke mk Fri, 04/02/2010 - 12:47

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

[WARNING: some of these features will only be available as of the June2016 release]

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