- users-list - monetdb.org

Fwd: Re: Managing Access to Monetdb Schemas
by ferreira 02 Aug '19

02 Aug '19

-------- Forwarded Message -------- Subject: Re: Managing Access to Monetdb Schemas Date: Fri, 2 Aug 2019 11:06:21 +0200 From: ferreira <pedro.ferreira(a)monetdbsolutions.com> To: Nobody Noone <nobody.noone.nobody.me(a)gmail.com> Hello! I found it strange why we don't support GRANT/REVOKE privileges statements on schemas. I looked into the SQL standard, and there is no mention of it. You can use roles to limit schema access for each user. Here you have an example: CREATE ROLE my_role; CREATE SCHEMA my_schema AUTHORIZATION my_role; CREATE USER my_user with password 'p1' name 'User with role' schema "my_schema"; GRANT my_role to my_user; You can add more fine grained access to each table with table privileges as you mentioned. As for specific rights such as DDL, we don't support it as of now. You can create a feature request on Bugzilla, but as it is not paid, it will have low priority. Best regards, Pedro On 7/31/19 4:40 PM, Nobody Noone wrote: > New to Monetdb, From a DBA perspective I’m having trouble > understanding how to manage access rights to schemas and the tables > therein. My department has many senior analysts who should have admin > rights to the tables within the schema (DML - select, insert, update, > delete, trunc, etc; DDL create table, drop table, create seq, etc). > There are also some users that need select only in a given schema. How > do I manage this without having to grant access per table? Is that > where roles come into play? Say super users get privilege_code_id= 79 > (SELECT,INSERT,UPDATE,DELETE,TRUNCATE) whereas some users would be > assigned privilege_code_id= 1 (SELECT). What about DDL rights? I am > not seeing those in the roles? I do see the ability when the schema is > created with CREATE SCHEMA "ABC" AUTHORIZATION "xyz_user". What I > don’t want to do is have to run a query that searches all tables in a > schema, grant admin DDL to them. > Thanks! > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list

1 0

Cast a string column with empty strings to int
by Ying Zhang 01 Aug '19

01 Aug '19

Hai, I have e.g. the following string column containing either numbers or empty strings: CREATE TABLE tst (s string); INSERT INTO tst VALUES ('1234'), (''), (‘4567'); I’d like to cast ’s' to an INT column. The following query works: sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp; +------+ | L4 | +======+ | 1234 | | null | | 4567 | +------+ 3 tuples But the following query doesn’t: sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan: sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; +--------------------------------------------------------------------------------------------------------------------+ | mal | +====================================================================================================================+ … | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); | | X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); | | X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); | | X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); | | X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); | | X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); | | X_35:bat[:int] := batcalc.int(X_19:bat[:str]); | | X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); | … Is my query wrong or the plan? Thanks, Jennie

2 2

Question with wildcarded expressions for LIKE clause
by Gotwisner, Dave 01 Aug '19

01 Aug '19

I am trying to do some constraints off the system tables to select a unique pattern of tables. We have tables like the following (I can't give the actual table names): * Config (a single record exists here, no merge tables use it) * Data_0000 (a small number of records exist in each of these) * Data_0001 * Data_0002 * Data_0003 * Data (this is a merge table over the last 4) * Test_table (a small number of records exist here, no merge tables use it) * Main__yyyy_mm_dd__hh__0000 (millions of records, yyyy, mm, dd, hh are all numbers, we have one set of tables per hour, and keep significantly over 1 weeks' worth of data) * Main__yyyy_mm_dd__hh__0001 * Main__yyyy_mm_dd__hh__0002 * Main__yyyy_mm_dd__hh__0003 * Main__yyyy_mm_dd__hh (this is a merge table over the last 4) If I do a select * from sys.tables where schema_id in (select id from schemas where name = 'frodo') and type in (select table_type_id from table_types where table_type_name = 'TABLE') I get the right thing - all non merge tables. I then want to further break it down into the tables that end in __[0-9][0-9][0-9][0-9] and those that don't (two separate queries for two separate purposes). Consider frodo the name of our schema. Adding and name like 'data\_\_____' appears to work. This should give me the 4 data_* tables. Adding and name like '%\_\_____' gives me all non-merge tables, including config and test_table. I'm assuming % is the wild card for 0 or more, and _ is the wild card for 1. This matches some documentation I have found. The \ are required because I need to explicitly look for 2 of the underscore character. Am I doing this correctly? Are these the right wildcarding characters? MS SQLServer gives a # as a match for a single digit, is there an equivalent? Or, is this just a bug in MonetDB? Thanks, Dave

3 5

Managing Access to Monetdb Schemas
by Nobody Noone 31 Jul '19

31 Jul '19

New to Monetdb, From a DBA perspective I’m having trouble understanding how to manage access rights to schemas and the tables therein. My department has many senior analysts who should have admin rights to the tables within the schema (DML - select, insert, update, delete, trunc, etc; DDL create table, drop table, create seq, etc). There are also some users that need select only in a given schema. How do I manage this without having to grant access per table? Is that where roles come into play? Say super users get privilege_code_id= 79 (SELECT,INSERT,UPDATE,DELETE,TRUNCATE) whereas some users would be assigned privilege_code_id= 1 (SELECT). What about DDL rights? I am not seeing those in the roles? I do see the ability when the schema is created with CREATE SCHEMA "ABC" AUTHORIZATION "xyz_user". What I don’t want to do is have to run a query that searches all tables in a schema, grant admin DDL to them. Thanks!

1 0

How to manage access rights
by Nobody Noone 30 Jul '19

30 Jul '19

New to Monetdb, From a DBA perspective I’m having trouble understanding how to manage access rights to schemas and the tables therein. My department has many senior analysts who should have admin rights to the tables within the schema (DML - select, insert, update, delete, trunc, etc; DDL create table, drop table, create seq, etc). There are also some users that need select only in a given schema. How do I manage this without having to grant access per table? Is that where roles come into play? Say super users get privilege_code_id= 79 (SELECT,INSERT,UPDATE,DELETE,TRUNCATE) whereas some users would be assigned privilege_code_id= 1 (SELECT). What about DDL rights? I am not seeing those in the roles? I do see the ability when the schema is created with CREATE SCHEMA "ABC" AUTHORIZATION "xyz_user". What I don’t want to do is have to run a query that searches all tables in a schema, grant admin DDL to them. Thanks!

1 0

Issue while migrating to MonetDB v11.33.3 (Apr 2019)
by Sharma, Sreejith 25 Jul '19

25 Jul '19

All, We are migrating our MonetDB database from MonetDB v11.27.13 (Jul2017-SP4) to MonetDB v11.33.3 (Apr 2019). We tried to bring up the DB instance with MonetDB v11.33.3 (Apr 2019), but getting below error though the same when we tried with previous version MonetDB v11.31.13 (Aug2018-SP2) it worked. Could you help us to identify the issue? 2019-07-24 04:00:21 ERR TSV_PROD_DB_T[88767]: #main thread:!ERROR: BBPcheckbats: cannot stat file /monet_data01/DBFARM_TSV_TEST/TSV_PROD_DB_T/bat/10/45/104502.tail 2019-07-24 04:00:21 ERR TSV_PROD_DB_T[88767]: #main thread:!OS: No such file or directory 2019-07-24 04:00:21 ERR TSV_PROD_DB_T[88767]: !FATAL: BBPinit: BBPcheckbats failed 2019-07-24 04:00:21 MSG TSV_PROD_DB_T[88767]: !ERROR: BBPcheckbats: cannot stat file /monet_data01/DBFARM_TSV_TEST/TSV_PROD_DB_T/bat/10/45/104502.tail 2019-07-24 04:00:21 MSG TSV_PROD_DB_T[88767]: !OS: No such file or directory 2019-07-24 04:00:23 MSG merovingian[88722]: database 'TSV_PROD_DB_T' (88767) has exited with exit status 1 2019-07-24 04:00:24 ERR control[88722]: (local): failed to fork mserver: database 'TSV_PROD_DB_T' appears to shut itself down after starting, check monetdbd's logfile (merovingian.log) for possible hints 2019-07-24 04:02:18 MSG control[88722]: (local): served status list Regards, Sreejith

2 1

(Без темы)
by Илья Хайбуллин 25 Jul '19

25 Jul '19

How can I write a workable join by null-safe eqauls? I have two tables: create table monet1_test ("Id" bigserial, key int, value varchar(2000)); create table monet2_test ("Id" bigserial, key int, value varchar(2000)); insert into monet1_test select value "Id", value "key", value "value" from generate_series(1,60000); insert into monet2_test select value "Id", value "key", value "value" from generate_series(1,200000); I tried select count(1) from monet2_test t1 inner join monet1_test t2 on ((t1.key = t2.key) or ((t1.key is null) and (t2.key is null))); and got errors GDK reported error: MT_mmap: CreateFileMapping(0000000000001200, &sa, 4, 22, 1508704256, NULL) failed OS: The printer is out of paper. HEAPalloc: Insufficient space for HEAP of 95997921280 bytes. MT_mmap: CreateFileMapping(00000000000011B4, &sa, 4, 22, 1508704256, NULL) failed OS: The printer is out of paper. HEAPalloc: Insufficient space for HEAP of 95997921280 bytes. I create bug-report: https://www.monetdb.org/bugzilla/show_bug.cgi?id=6737

3 7

Indexing support on Json DataType
by Chatchawan Triperm 22 Jul '19

22 Jul '19

Hi All, Does anyone know whether there is built-in indexing on Json datatype or not. I would like to keep my dynamic columns in Json but worry about indexing. Regards, Chatchawan

3 4

Unexpected query result with merge tables and view after upgrade
by Enrico Maria Carmona 22 Jul '19

22 Jul '19

2 2

Questions on embedding R in MonetDB and client pacakge in R
by Andor 18 Jul '19

18 Jul '19

Hello everyone there, I am a beginner user of MonetDB , mainly working with database from R, new to this email list. Is there a seach function somewhere in this list? I can only see the archieve webpage. I have some general user questions, and would greatly appreciate your comments and suggestions. the embedding R in MonetDB.The very latest materials I could find on this topic on MonetDB websites, dated backed in 10/31/2014, I am wondering where I can find more embedding R in MonetDB related information? tutorial, documents etc the client package working with MonetDB from R I only see monetDB.R but not MonetDBLite in the CRAN packages index, yet in MonetDB site , both packages are introduced. any comments on the MonetDBLite, will it be there in the future? Thanks! Andor

1 0