1/*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2019 MonetDB B.V.
7 */
8
9/*
10 * @f sql_user
11 * @t SQL catalog management
12 * @a N. Nes, F. Groffen
13 * @+ SQL user
14 * The SQL user and authorisation implementation differs per backend. This
15 * file implements the authorisation and user management based on the M5
16 * system authorisation.
17 */
18#include "monetdb_config.h"
19#include "sql_user.h"
20#include "sql_mvc.h"
21#include "sql_privileges.h"
22#include "bat5.h"
23#include "mal_interpreter.h"
24#include "mal_authorize.h"
25#include "mcrypt.h"
26
27#if 0
28int
29sql_find_auth_schema(mvc *m, str auth)
30{
31 int res = -1;
32 oid rid;
33 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
34 sql_table *users = find_sql_table(sys, "db_user_info");
35 sql_column *users_name = find_sql_column(users, "name");
36
37 rid = table_funcs.column_find_row(m->session->tr, users_name, auth, NULL);
38
39 if (!is_oid_nil(rid)) {
40 sql_column *users_schema = find_sql_column(users, "default_schema");
41 int *p = (int *) table_funcs.column_find_value(m->session->tr, users_schema, rid);
42
43 if (p) {
44 res = *p;
45 _DELETE(p);
46 }
47 }
48 return res;
49}
50#endif
51
52static int
53monet5_drop_user(ptr _mvc, str user)
54{
55 mvc *m = (mvc *) _mvc;
56 oid rid;
57 sql_schema *sys;
58 sql_table *users;
59 sql_column *users_name;
60 str err;
61 Client c = MCgetClient(m->clientid);
62
63 err = AUTHremoveUser(c, user);
64 if (err !=MAL_SUCCEED) {
65 (void) sql_error(m, 02, "DROP USER: %s", getExceptionMessage(err));
66 _DELETE(err);
67 return FALSE;
68 }
69 sys = find_sql_schema(m->session->tr, "sys");
70 users = find_sql_table(sys, "db_user_info");
71 users_name = find_sql_column(users, "name");
72
73 rid = table_funcs.column_find_row(m->session->tr, users_name, user, NULL);
74 if (!is_oid_nil(rid))
75 table_funcs.table_delete(m->session->tr, users, rid);
76 /* FIXME: We have to ignore this inconsistency here, because the
77 * user was already removed from the system authorisation. Once
78 * we have warnings, we could issue a warning about this
79 * (seemingly) inconsistency between system and sql shadow
80 * administration. */
81
82 return TRUE;
83}
84
85static str
86monet5_create_user(ptr _mvc, str user, str passwd, char enc, str fullname, sqlid schema_id, sqlid grantorid)
87{
88 mvc *m = (mvc *) _mvc;
89 oid uid = 0;
90 bat bid = 0;
91 str ret;
92 sqlid user_id;
93 str pwd;
94 sql_schema *s = find_sql_schema(m->session->tr, "sys");
95 sql_table *db_user_info, *auths;
96 Client c = MCgetClient(m->clientid);
97
98 if (!enc) {
99 pwd = mcrypt_BackendSum(passwd, strlen(passwd));
100 if (pwd == NULL) {
101 BBPunfix(bid);
102 throw(MAL, "sql.create_user", SQLSTATE(42000) "Crypt backend hash not found");
103 }
104 } else {
105 pwd = passwd;
106 }
107 /* add the user to the M5 authorisation administration */
108 ret = AUTHaddUser(&uid, c, user, pwd);
109 if (!enc)
110 free(pwd);
111 if (ret != MAL_SUCCEED)
112 return ret;
113
114 user_id = store_next_oid();
115 db_user_info = find_sql_table(s, "db_user_info");
116 auths = find_sql_table(s, "auths");
117 table_funcs.table_insert(m->session->tr, db_user_info, user, fullname, &schema_id);
118 table_funcs.table_insert(m->session->tr, auths, &user_id, user, &grantorid);
119 return NULL;
120}
121
122static int
123monet5_find_user(ptr mp, str user)
124{
125 BAT *uid, *nme;
126 BUN p;
127 mvc *m = (mvc *) mp;
128 Client c = MCgetClient(m->clientid);
129 str err;
130
131 if ((err = AUTHgetUsers(&uid, &nme, c)) != MAL_SUCCEED) {
132 _DELETE(err);
133 return -1;
134 }
135 p = BUNfnd(nme, user);
136 BBPunfix(uid->batCacheid);
137 BBPunfix(nme->batCacheid);
138
139 /* yeah, I would prefer to return something different too */
140 return (p == BUN_NONE ? -1 : 1);
141}
142
143str
144db_users_wrap(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
145{
146 bat *r = getArgReference_bat(stk, pci, 0);
147 BAT *uid, *nme;
148 str err;
149
150 (void) mb;
151 if ((err = AUTHgetUsers(&uid, &nme, cntxt)) != MAL_SUCCEED)
152 return err;
153 BBPunfix(uid->batCacheid);
154 *r = nme->batCacheid;
155 BBPkeepref(*r);
156 return MAL_SUCCEED;
157}
158
159str
160db_password_wrap(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci)
161{
162 (void) mb;
163
164 if (stk->stk[pci->argv[0]].vtype == TYPE_bat) {
165 BAT *b = BATdescriptor(*getArgReference_bat(stk, pci, 1));
166 if (b == NULL)
167 throw(SQL, "sql.password", SQLSTATE(HY002) RUNTIME_OBJECT_MISSING);
168 BAT *bn = COLnew(b->hseqbase, TYPE_str, BATcount(b), TRANSIENT);
169 if (bn == NULL) {
170 BBPunfix(b->batCacheid);
171 throw(SQL, "sql.password", SQLSTATE(HY001) MAL_MALLOC_FAIL);
172 }
173 BATiter bi = bat_iterator(b);
174 BUN p, q;
175 BATloop(b, p, q) {
176 char *hash, *msg;
177 msg = AUTHgetPasswordHash(&hash, cntxt, BUNtvar(bi, p));
178 if (msg != MAL_SUCCEED) {
179 BBPunfix(b->batCacheid);
180 BBPreclaim(bn);
181 return msg;
182 }
183 if (BUNappend(bn, hash, false) != GDK_SUCCEED) {
184 BBPunfix(b->batCacheid);
185 BBPreclaim(bn);
186 throw(SQL, "sql.password", SQLSTATE(HY001) MAL_MALLOC_FAIL);
187 }
188 GDKfree(hash);
189 }
190 BBPunfix(b->batCacheid);
191 BBPkeepref(bn->batCacheid);
192 *getArgReference_bat(stk, pci, 0) = bn->batCacheid;
193 return MAL_SUCCEED;
194 }
195 str *hash = getArgReference_str(stk, pci, 0);
196 str *user = getArgReference_str(stk, pci, 1);
197
198 return AUTHgetPasswordHash(hash, cntxt, *user);
199}
200
201static void
202monet5_create_privileges(ptr _mvc, sql_schema *s)
203{
204 sql_table *t, *uinfo;
205 mvc *m = (mvc *) _mvc;
206 char *err = NULL;
207 sqlid schema_id = 0;
208 str monetdbuser = "monetdb";
209 list *res, *ops;
210
211 /* create the authorisation related tables */
212 t = mvc_create_table(m, s, "db_user_info", tt_table, 1, SQL_PERSIST, 0, -1, 0);
213 mvc_create_column_(m, t, "name", "varchar", 1024);
214 mvc_create_column_(m, t, "fullname", "varchar", 2048);
215 mvc_create_column_(m, t, "default_schema", "int", 9);
216 uinfo = t;
217
218 (void) err;
219 res = sa_list(m->sa);
220 list_append(res, sql_create_arg(m->sa, "name", sql_bind_subtype(m->sa, "varchar", 2048, 0), ARG_OUT));
221
222 /* add function */
223 ops = sa_list(m->sa);
224 /* following funcion returns a table (single column) of user names
225 with the approriate scenario (sql) */
226 mvc_create_func(m, NULL, s, "db_users", ops, res, F_UNION, FUNC_LANG_SQL, "sql", "db_users", "CREATE FUNCTION db_users () RETURNS TABLE( name varchar(2048)) EXTERNAL NAME sql.db_users;", FALSE, FALSE, TRUE);
227
228 t = mvc_init_create_view(m, s, "users",
229 "SELECT u.\"name\" AS \"name\", "
230 "ui.\"fullname\", ui.\"default_schema\" "
231 "FROM db_users() AS u LEFT JOIN "
232 "\"sys\".\"db_user_info\" AS ui "
233 "ON u.\"name\" = ui.\"name\";");
234 if (!t) {
235 fprintf(stderr, "!monet5_create_privileges: failed to create 'users' view\n");
236 return ;
237 }
238
239 mvc_create_column_(m, t, "name", "varchar", 1024);
240 mvc_create_column_(m, t, "fullname", "varchar", 2024);
241 mvc_create_column_(m, t, "default_schema", "int", 9);
242
243 schema_id = sql_find_schema(m, "sys");
244 assert(schema_id >= 0);
245
246 table_funcs.table_insert(m->session->tr, uinfo, monetdbuser, "MonetDB Admin", &schema_id);
247}
248
249static int
250monet5_schema_has_user(ptr _mvc, sql_schema *s)
251{
252 mvc *m = (mvc *) _mvc;
253 oid rid;
254 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
255 sql_table *users = find_sql_table(sys, "db_user_info");
256 sql_column *users_schema = find_sql_column(users, "default_schema");
257 sqlid schema_id = s->base.id;
258
259 rid = table_funcs.column_find_row(m->session->tr, users_schema, &schema_id, NULL);
260 if (is_oid_nil(rid))
261 return FALSE;
262 return TRUE;
263}
264
265static int
266monet5_alter_user(ptr _mvc, str user, str passwd, char enc, sqlid schema_id, str oldpasswd)
267{
268 mvc *m = (mvc *) _mvc;
269 Client c = MCgetClient(m->clientid);
270 str err;
271
272 if (passwd != NULL) {
273 str pwd = NULL;
274 str opwd = NULL;
275 if (!enc) {
276 pwd = mcrypt_BackendSum(passwd, strlen(passwd));
277 if (pwd == NULL) {
278 (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: crypt backend hash not found");
279 return FALSE;
280 }
281 if (oldpasswd != NULL) {
282 opwd = mcrypt_BackendSum(oldpasswd, strlen(oldpasswd));
283 if (opwd == NULL) {
284 free(pwd);
285 (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: crypt backend hash not found");
286 return FALSE;
287 }
288 }
289 } else {
290 pwd = passwd;
291 opwd = oldpasswd;
292 }
293 if (user == NULL) {
294 err = AUTHchangePassword(c, opwd, pwd);
295 if (!enc) {
296 free(pwd);
297 free(opwd);
298 }
299 if (err !=MAL_SUCCEED) {
300 (void) sql_error(m, 02, "ALTER USER: %s", getExceptionMessage(err));
301 freeException(err);
302 return (FALSE);
303 }
304 } else {
305 str username = NULL;
306 if ((err = AUTHresolveUser(&username, c->user)) !=MAL_SUCCEED) {
307 if (!enc) {
308 free(pwd);
309 free(opwd);
310 }
311 (void) sql_error(m, 02, "ALTER USER: %s", getExceptionMessage(err));
312 freeException(err);
313 return (FALSE);
314 }
315 if (strcmp(username, user) == 0) {
316 /* avoid message about changePassword (from MAL level) */
317 GDKfree(username);
318 if (!enc) {
319 free(pwd);
320 free(opwd);
321 }
322 (void) sql_error(m, 02, "ALTER USER: "
323 "use 'ALTER USER SET [ ENCRYPTED ] PASSWORD xxx "
324 "USING OLD PASSWORD yyy' "
325 "when changing your own password");
326 return (FALSE);
327 }
328 GDKfree(username);
329 err = AUTHsetPassword(c, user, pwd);
330 if (!enc) {
331 free(pwd);
332 free(opwd);
333 }
334 if (err !=MAL_SUCCEED) {
335 (void) sql_error(m, 02, "ALTER USER: %s", getExceptionMessage(err));
336 freeException(err);
337 return (FALSE);
338 }
339 }
340 }
341
342 if (schema_id) {
343 oid rid;
344 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
345 sql_table *info = find_sql_table(sys, "db_user_info");
346 sql_column *users_name = find_sql_column(info, "name");
347 sql_column *users_schema = find_sql_column(info, "default_schema");
348
349 /* FIXME: we don't really check against the backend here */
350 rid = table_funcs.column_find_row(m->session->tr, users_name, user, NULL);
351 if (is_oid_nil(rid))
352 return FALSE;
353
354 table_funcs.column_update_value(m->session->tr, users_schema, rid, &schema_id);
355 }
356
357 return TRUE;
358}
359
360static int
361monet5_rename_user(ptr _mvc, str olduser, str newuser)
362{
363 mvc *m = (mvc *) _mvc;
364 Client c = MCgetClient(m->clientid);
365 str err;
366 oid rid;
367 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
368 sql_table *info = find_sql_table(sys, "db_user_info");
369 sql_column *users_name = find_sql_column(info, "name");
370 sql_table *auths = find_sql_table(sys, "auths");
371 sql_column *auths_name = find_sql_column(auths, "name");
372
373 if ((err = AUTHchangeUsername(c, olduser, newuser)) !=MAL_SUCCEED) {
374 (void) sql_error(m, 02, "ALTER USER: %s", getExceptionMessage(err));
375 freeException(err);
376 return (FALSE);
377 }
378
379 rid = table_funcs.column_find_row(m->session->tr, users_name, olduser, NULL);
380 if (is_oid_nil(rid)) {
381 (void) sql_error(m, 02, "ALTER USER: local inconsistency, "
382 "your database is damaged, user not found in SQL catalog");
383 return (FALSE);
384 }
385 table_funcs.column_update_value(m->session->tr, users_name, rid, newuser);
386
387 rid = table_funcs.column_find_row(m->session->tr, auths_name, olduser, NULL);
388 if (is_oid_nil(rid)) {
389 (void) sql_error(m, 02, "ALTER USER: local inconsistency, "
390 "your database is damaged, auth not found in SQL catalog");
391 return (FALSE);
392 }
393 table_funcs.column_update_value(m->session->tr, auths_name, rid, newuser);
394
395 return (TRUE);
396}
397
398static void *
399monet5_schema_user_dependencies(ptr _trans, int schema_id)
400{
401 rids *A, *U;
402 sql_trans *tr = (sql_trans *) _trans;
403 sql_schema *s = find_sql_schema(tr, "sys");
404
405 sql_table *auths = find_sql_table(s, "auths");
406 sql_column *auth_name = find_sql_column(auths, "name");
407
408 sql_table *users = find_sql_table(s, "db_user_info");
409 sql_column *users_name = find_sql_column(users, "name");
410 sql_column *users_sch = find_sql_column(users, "default_schema");
411
412 /* select users with given schema */
413 U = table_funcs.rids_select(tr, users_sch, &schema_id, &schema_id, NULL);
414 /* select all authorization ids */
415 A = table_funcs.rids_select(tr, auth_name, NULL, NULL);
416 /* join all authorization with the selected users */
417 A = table_funcs.rids_join(tr, A, auth_name, U, users_name);
418 table_funcs.rids_destroy(U);
419 return A;
420}
421
422void
423monet5_user_init(backend_functions *be_funcs)
424{
425 be_funcs->fcuser = &monet5_create_user;
426 be_funcs->fduser = &monet5_drop_user;
427 be_funcs->ffuser = &monet5_find_user;
428 be_funcs->fcrpriv = &monet5_create_privileges;
429 be_funcs->fshuser = &monet5_schema_has_user;
430 be_funcs->fauser = &monet5_alter_user;
431 be_funcs->fruser = &monet5_rename_user;
432 be_funcs->fschuserdep = &monet5_schema_user_dependencies;
433}
434
435str
436monet5_user_get_def_schema(mvc *m, int user)
437{
438 oid rid;
439 sqlid schema_id;
440 sql_schema *sys = NULL;
441 sql_table *user_info = NULL;
442 sql_column *users_name = NULL;
443 sql_column *users_schema = NULL;
444 sql_table *schemas = NULL;
445 sql_column *schemas_name = NULL;
446 sql_column *schemas_id = NULL;
447 sql_table *auths = NULL;
448 sql_column *auths_id = NULL;
449 sql_column *auths_name = NULL;
450 void *p = 0;
451 str username = NULL;
452 str schema = NULL;
453
454 sys = find_sql_schema(m->session->tr, "sys");
455 auths = find_sql_table(sys, "auths");
456 auths_id = find_sql_column(auths, "id");
457 auths_name = find_sql_column(auths, "name");
458 rid = table_funcs.column_find_row(m->session->tr, auths_id, &user, NULL);
459 if (!is_oid_nil(rid))
460 username = table_funcs.column_find_value(m->session->tr, auths_name, rid);
461
462 user_info = find_sql_table(sys, "db_user_info");
463 users_name = find_sql_column(user_info, "name");
464 users_schema = find_sql_column(user_info, "default_schema");
465 rid = table_funcs.column_find_row(m->session->tr, users_name, username, NULL);
466 if (!is_oid_nil(rid))
467 p = table_funcs.column_find_value(m->session->tr, users_schema, rid);
468
469 _DELETE(username);
470 assert(p);
471 schema_id = *(sqlid *) p;
472 _DELETE(p);
473
474 schemas = find_sql_table(sys, "schemas");
475 schemas_name = find_sql_column(schemas, "name");
476 schemas_id = find_sql_column(schemas, "id");
477
478 rid = table_funcs.column_find_row(m->session->tr, schemas_id, &schema_id, NULL);
479 if (!is_oid_nil(rid))
480 schema = table_funcs.column_find_value(m->session->tr, schemas_name, rid);
481 if(!stack_set_string(m, "current_schema", schema))
482 return NULL;
483 return schema;
484}
485
486str
487monet5_user_set_def_schema(mvc *m, oid user)
488{
489 oid rid;
490 sqlid schema_id;
491 sql_schema *sys = NULL;
492 sql_table *user_info = NULL;
493 sql_column *users_name = NULL;
494 sql_column *users_schema = NULL;
495 sql_table *schemas = NULL;
496 sql_column *schemas_name = NULL;
497 sql_column *schemas_id = NULL;
498 sql_table *auths = NULL;
499 sql_column *auths_name = NULL;
500 str other;
501
502 void *p = 0;
503
504 str schema = NULL;
505 str username = NULL;
506 str err = NULL;
507
508 if (m->debug &1)
509 fprintf(stderr, "monet5_user_set_def_schema " OIDFMT "\n", user);
510
511 if ((err = AUTHresolveUser(&username, user)) !=MAL_SUCCEED) {
512 freeException(err);
513 return (NULL); /* don't reveal that the user doesn't exist */
514 }
515
516 if(mvc_trans(m) < 0) {
517 GDKfree(username);
518 return NULL;
519 }
520
521 sys = find_sql_schema(m->session->tr, "sys");
522 user_info = find_sql_table(sys, "db_user_info");
523 users_name = find_sql_column(user_info, "name");
524 users_schema = find_sql_column(user_info, "default_schema");
525
526 rid = table_funcs.column_find_row(m->session->tr, users_name, username, NULL);
527 if (!is_oid_nil(rid))
528 p = table_funcs.column_find_value(m->session->tr, users_schema, rid);
529
530 assert(p);
531 schema_id = *(sqlid *) p;
532 _DELETE(p);
533
534 schemas = find_sql_table(sys, "schemas");
535 schemas_name = find_sql_column(schemas, "name");
536 schemas_id = find_sql_column(schemas, "id");
537 auths = find_sql_table(sys, "auths");
538 auths_name = find_sql_column(auths, "name");
539
540 rid = table_funcs.column_find_row(m->session->tr, schemas_id, &schema_id, NULL);
541 if (!is_oid_nil(rid))
542 schema = table_funcs.column_find_value(m->session->tr, schemas_name, rid);
543
544 /* only set schema if user is found */
545 rid = table_funcs.column_find_row(m->session->tr, auths_name, username, NULL);
546 if (!is_oid_nil(rid)) {
547 sql_column *auths_id = find_sql_column(auths, "id");
548 int id;
549 p = table_funcs.column_find_value(m->session->tr, auths_id, rid);
550 id = *(int *) p;
551 _DELETE(p);
552
553 m->user_id = m->role_id = id;
554 } else {
555 schema = NULL;
556 }
557
558 if (!schema || !mvc_set_schema(m, schema)) {
559 if (m->session->tr->active) {
560 if((other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
561 freeException(other);
562 }
563 GDKfree(username);
564 return NULL;
565 }
566 /* reset the user and schema names */
567 if(!stack_set_string(m, "current_schema", schema) ||
568 !stack_set_string(m, "current_user", username) ||
569 !stack_set_string(m, "current_role", username)) {
570 schema = NULL;
571 }
572 GDKfree(username);
573 if((other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED) {
574 freeException(other);
575 return NULL;
576 }
577 return schema;
578}
579