Create Users and SchemasCreate Users and Schemas mk Tue, 02/25/2020 - 10:47
In order to explore the wealth of functionality offered by MonetDB/SQL, the best strategy is to get started using a toy database. For this we use the VOC database which provides a peephole view into the administrative system of an early multi-national company, the Vereenigde geoctrooieerde Oostindische Compagnie (VOC for short - The (Dutch) East Indian Company) established on March 20, 1602.
After the file has been extracted, load its contents into MonetDB using mclient. Before you load the VOC data set, it is advised to introduce a user different from the omnipresent default monetdb.
The new user is given his own schema for the MonetDB database. Assuming you have started MonetDB with SQL module, proceed as follows:
shell> mclient -u monetdb -d voc password:<monetdb> sql>CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys"; sql>CREATE SCHEMA "voc" AUTHORIZATION "voc"; sql>ALTER USER "voc" SET SCHEMA "voc"; sql>\q
To illustrate the use of the newly created account and schema, the following example connects and creates a table, which is dropped afterwards by aborting the transaction.
shell> mclient -u voc -d voc password:<voc> sql>START TRANSACTION; auto commit mode: off sql>CREATE TABLE test (id int, data varchar(30)); operation successful (0.722ms) sql>\d TABLE voc.test sql>\d test CREATE TABLE "voc"."test" ( "id" INTEGER, "data" VARCHAR(30) );
sql>CREATE TABLE "voc"."test" ("id" int, "data" varchar(30));
sql>ROLLBACK; auto commit mode: on sql>\d