Data definition

Data definition mk Thu, 06/30/2011 - 09:49

The MonetDB/SQL supports the full range of object definitions.

Table definitions

Table definitions mk Sat, 03/27/2010 - 22:34

The CREATE TABLE statement conforms to the full SQL standard. Tables are assigned to the current schema unless the schema name is explicitly given as part of the table name. Table names should be unique amongst those mentioned within the same schema and distinct from index-, contraint-x, and view-names.

table_def:
     CREATE TABLE table_name table_content_source
   | CREATE MERGE TABLE table_name table_content_source
   | CREATE REPLICA TABLE table_name table_content_source
   | CREATE REMOTE TABLE table_name table_content_source ON STRING

table_content_source:
   '(' table_element_list ') 
  |  ['(' column_name_list ')' ] AS select_query  { WITH NO DATA | WITH DATA }

The derived (temporary) tables are either filled upon creation or automatically upon use within queries.

table_def:
   | CREATE [LOCAL | GLOBAL] TEMPORARY  TABLE table_name '(' table_content_source ')'
                     [ ON COMMIT [ DELETE ROWS |  PRESERVE ROWS |  DROP ] ]

Temporary local tables are limited to the client session. They are stored automatically under the schema 'tmp'. The qualifiers denote the actions taken during transaction commit over a temporary table. If the ON COMMIT clause is omitted then all tuples are dropped while retaining the structure.

Table elements

Table elements mk Sun, 03/28/2010 - 13:46
table_element:
    column_name data_type [column_option ...]
  | CONSTRAINT constraint_name table_constraint_type
  | column_name WITH OPTIONS '(' column_option ','... ')'
  | LIKE table_name
  | column_name SERIAL
  | column_name BIGSERIAL

Identity columns take their values from a sequence generator. The MySQL (auto_increment) and PostgreSQL (serial data type) syntax  are also supported. The SERIAL type is mapped to either an INTEGER or BIGINT (when using BIGSERIAL).

Column and Table constraints are both supported. Besides the simple not null check also unique, primary, and foreign keys are supported. We currently check constraints directly on insert, update and delete, so immediate. This limitation stems from the missing triggers. The null matching on foreign keys is limited to the simple match type (null values satisfy the constraint). The full and partial match types are not supported. The referential action is currently limited to restrict, i.e. an update fails if other columns have references to it. The CHECK constraints are currently not enforced. Also they are not recorded in a data dictionary table.

table_constraint_type:
    UNIQUE '(' column_name ',' ... ')'
   | PRIMARY KEY '(' column_name ',' ... ')'
   | CHECK '(' column_logical_expression ')'
   | FOREIGN KEY '(' column_name ',' ... ')' REFERENCES table_name ['(' column_name ',' ... ')'] [ match_options ] [ ref_actions ]

A table constraint definition is not tied to a particular column, but rather to a column group. A column constraint is a notational convenience when the constraint only affects one column.

column_option:
    DEFAULT default_value
   | [ constraint_name ] column_constraint_type
   | GENERATED ALWAYS AS IDENTITY [ '(' serial_parameters ')' ]
   | AUTO_INCREMENT

 

column_constraint_type:
    NOT NULL | NULL | UNIQUE | PRIMARY KEY | CHECK '(' column_logical_expression ')'
  | REFERENCES table_name [ '(' column_name ',' ... ')' ] [ match_options ] [ ref_actions ]

match_options:
    MATCH { FULL | PARTIAL | SIMPLE }

ref_actions:
     { ON UPDATE | ON DELETE } { NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT }

A constraint over a referenced value can be either a FULL, PARTIAL or SIMPLE (default) match. A ful match requires all column values to be not null unless all are null.The simple match is more relaxed; any of the keys may be null. The partial match is consider noise for the time being.

The serial columns are a slight extension over the serial types defined earlier. In particular, we can designate RESTART option when the sequence is exhausted. This may involve a subquery over the database.

serial_parameters:
     [ START WITH nonzero-bigint ]
   | [ RESTART | RESTART WITH subquery
   | RESTART WITH nonzero-bigint ]
   | [INCREMENT BY nonzero-bigint ]
   | [MINVALUE nonzero-bigint | NOMINVALUE]
   | [MAXVALUE nonzero-bigint | NOMAXVALUE ]
   | [CACHE nonzero-bigint ]
   | [CYCLE | NONCYCLE]

Index definitions

Index definitions mk Sat, 03/27/2010 - 22:46

The index statements in the SQL standard are recognized, but their implementation is different from competitive products. MonetDB/SQL interprets these statements as an advice and often freely neglects it, relying on its own decision to create and maintain indexes for fast access. Also the UNIQUE qualifier is not honored or enforced. To add a uniqueness constraint for a table or column use UNIQUE in the CREATE TABLE or ALTER TABLE statement.

index_def:
    CREATE [ UNIQUE ] INDEX ident ON qname '(' ident ','... ')'

Alter statement

Alter statement mk Sat, 03/27/2010 - 22:39

The ALTER statement can be used to change the TABLE properties, it requires authorization to do so. Addition of a column follows the same syntax and functionality as the CREATE TABLE statement. This form can be used to remove individual columns from a table. If the table is used in a foreign key relationship the actions to be take can be further specified as RESTRICT to forbid it if there are external references to the column values. The CASCADE nullifies all references to the values being removed.

alter_statement:

         ALTER TABLE qname ADD    [COLUMN] { column_def | table_constraint }
      |  ALTER TABLE qname ALTER [COLUMN] ident SET DEFAULT value
      |  ALTER TABLE qname ALTER [COLUMN] ident SET [NOT] NULL
      |  ALTER TABLE qname ALTER [COLUMN] ident DROP DEFAULT
      |  ALTER TABLE qname ALTER [COLUMN] ident SET STORAGE {string | NULL}
      |  ALTER TABLE qname DROP [COLUMN] ident [RESTRICT | CASCADE]
      |  ALTER TABLE qname DROP CONSTRAINT ident [RESTRICT | CASCADE]
 

An individual table can be protected using the READ ONLY and INSERT ONLY mode. All attempts to update are flagged as a SQL error. The reverse operation is ALTER TABLE qname READ WRITE, which makes the table accessible for all update operations.

        | ALTER TABLE qname SET { { READ | INSERT } ONLY | READ WRITE }

The ALTER statement has been extended with ADD TABLE and DROP TABLE options to allow adding/removing partition tables to/from a MERGE TABLE.

   | ALTER TABLE qname ADD TABLE qname
   | ALTER TABLE qname DROP TABLE ident [ RESTRICT | CASCADE ]

View definitions

View definitions mk Fri, 04/02/2010 - 10:39

Regular view specifications are supported. Recursive views and reference-able views are not supported.

view_def:
    CREATE VIEW view_name ['(' column_name ',' ...')']
                AS select_query  [WITH CHECK OPTION]

Schema definitions

Schema definitions mk Sat, 03/27/2010 - 22:50

A SCHEMA is a container for tables, views, indices, triggers, functions and procedures.  Schema access and modification is strictly controlled using the user role and authorisation scheme.

Every SCHEMA belongs to a database. One reason for organizing the logically separated data in schemas rather than databases is that in this way there will still be just one MonetDB process running.

schema:
   CREATE SCHEMA schema_name_clause
      [ DEFAULT CHARACTER SET ident ]
      [ PATH schema_name ','... ]
      [ schema_element ... ]

The DEFAULT CHARACTER SET and PATH options are here for compatibility reasons with the the SQL standard, however they are not yet implemented. The default character set is UTF-8.

schema_name_clause:
   ident | [ ident ] AUTHORIZATION ident

The AUTHORIZATION option allows specifying the name of the user or the role that will own the schema. If omitted, the user who has executed the query will be the owner. The owner of the schema is allowed to create, alter and drop tables. With the AUTHORIZATION option, an explicit name for the schema is optional. If omitted, the schema automatically gets the name of the authorised user/role as its name.

Notes on schema creation:

  1. The ownership of a schema can be assigned to only one user/role, and it can not be modified after its creation. Therefore, to share the ownership of a schema, one must assign the ownership of a schema to a role at the creation of the schema. Subsequently, the role can be granted to multiple users to own the schema. 
  2. Only the 'monetdb' user and the 'sysadmin' role can create a new schema. Therefore, to allow other users other to create schema, the 'monetdb' user should assign the 'sysadmin' role to the intended users.

schema_element:
   grant | revoke | create_statement | drop_statement | alter_statement

schema:
  | DROP SCHEMA qname drop_action

drop_action:
   RESTRICT | CASCADE

The drop_action option is supported for compatibility with the SQL standard, however it is not implemented yet. Currently it runs with the CASCADE option, meaning that once a schema is dropped every object inside the schema is dropped as well, such as tables and functions. However objects that are dependent on the schema, such as users will not automatically be dropped and will stop the schema from being dropped as well. One can either ALTER the user and give it a different default schema, or to simply drop the user if it is no longer needed.

schema:
  | SET SCHEMA ident

When opening the database, by default the “sys” schema is set. Another automatically created schema is “tmp”, used for temporally local tables. It is not possible for a user to access the “tmp” schema of another user.

One can create a new schema using the CREATE command and change to it, by using the SET command. When creating a table without specifying the schema, the table will be created in the schema that is currently in usage. In order to create a table in a different schema, use the schema name as a prefix.

Drop statement

Drop statement mk Mon, 05/24/2010 - 07:58

The objects created can be removed provided the authorization permissions are set. Note: If you do not specify the full signature of the function the DROP query will successfully execute if there is only one function with this name, if not the query is aborted. The DROP ALL is used to drop all the functions with the name specified in the query.

drop_statement:
     DROP TABLE qname [ RESTRICT | CASCADE ]
   | DROP [ALL] FUNCTION qname '(' [ data_type ','... ] ')' [ RESTRICT | CASCADE ]
   | DROP [ALL] PROCEDURE qname '(' [ data_type ','... ] ')' [ RESTRICT | CASCADE ]
   | DROP VIEW qname [ RESTRICT | CASCADE ]
   | DROP ROLE qname
   | DROP USER ident
   | DROP INDEX qname
   | DROP TRIGGER qname
   | DROP SEQUENCE qname

 

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