non admin user cannot create temporary tables

[monetdb-team]

Date: 2011-10-29 14:58:42 +0200
From: ultan <>
To: SQL devs <>
Version: 11.9.1 (Apr2012) [obsolete]
CC: @njnes

Last updated: 2012-05-25 12:58:45 +0200

Comment 16494

Date: 2011-10-29 14:58:42 +0200
From: ultan <>

User-Agent: Mozilla/5.0 (X11; Linux i686; rv:7.0.1) Gecko/20100101 Firefox/7.0.1
Build Identifier:

Creating a user under monetdb and then connecting as that user, it would be expected that that user could create temporary tables in the tmp schema.

Reproducible: Always

Steps to Reproduce:

connect with mclient to a database as admin user monetdb
create a user
disconnect
connect with mclient as new user
attempt to create a temporary table

Actual Results:

receive error that user has insufficient privileges.

For example:

ultan@boxer:/$ mclient -d TEST2H
user:monetdb
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP1)
Database: MonetDB v11.5.3, 'TEST2H'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create user TestUser with password 'password' name 'uoc' schema "sys";
operation successful (117.441ms)
sql>\q
ultan@boxer:/$ mclient -u TestUser -d TEST2H
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Aug2011-SP1)
Database: MonetDB v11.5.3, 'TEST2H'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create global temporary table t (col1 int);
CREATE TABLE: insufficient privileges for user 'ultan' in schema 'tmp'
sql>create local temporary table t (col1 int);
CREATE TABLE: insufficient privileges for user 'ultan' in schema 'tmp'
sql>create temporary table t (col1 int);
CREATE TABLE: insufficient privileges for user 'ultan' in schema 'tmp'
sql>

Expected Results:

expect temporary table creation to be allowed and successful

Comment 16581

Date: 2011-11-29 10:45:31 +0100
From: @grobian

With the Dec2011 branch:

% $INSTALL_DIR/bin/mclient -u testuser test2h
password:
Welcome to mclient, the MonetDB/SQL interactive terminal (Dec2011-a3d851cb1775)
Database: MonetDB v11.7.0 (Dec2011-a3d851cb1775), 'mapi:monetdb://vomac.cwi.nl:54321/test2h'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create global temporary table t (col1 int);
CREATE TABLE: insufficient privileges for user 'testuser' in schema 'tmp'
sql>create local temporary table t (col1 int);
operation successful (17.816ms)
sql>create temporary table t (col1 int);
CREATE TABLE: name 't' already in use
sql>drop table t;
operation successful (4.918ms)
sql>create temporary table t (col1 int);
operation successful (3.024ms)
sql>

That is, we now allow creating temporary tables and local temporary tables, but not global temporary tables. Is this enough for you?

Comment 17237

Date: 2012-05-07 20:42:02 +0200
From: @njnes

global temporaries change the global schema, as such by default new users do not have such rights.