Line data Source code
1 : /*
2 : * SPDX-License-Identifier: MPL-2.0
3 : *
4 : * This Source Code Form is subject to the terms of the Mozilla Public
5 : * License, v. 2.0. If a copy of the MPL was not distributed with this
6 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
7 : *
8 : * Copyright 2024 MonetDB Foundation;
9 : * Copyright August 2008 - 2023 MonetDB B.V.;
10 : * Copyright 1997 - July 2008 CWI.
11 : */
12 :
13 : /*
14 : * @f sql_user
15 : * @t SQL catalog management
16 : * @a N. Nes, F. Groffen
17 : * @+ SQL user
18 : * The SQL user and authorisation implementation differs per backend. This
19 : * file implements the authorisation and user management based on the M5
20 : * system authorisation.
21 : */
22 : #include "monetdb_config.h"
23 : #include "sql_user.h"
24 : #include "sql_mvc.h"
25 : #include "sql_privileges.h"
26 : #include "mal_interpreter.h"
27 : #include "mal_authorize.h"
28 : #include "mcrypt.h"
29 : #include "sql_execute.h"
30 :
31 : static inline sql_table*
32 75126 : getUsersTbl(mvc *m)
33 : {
34 75126 : sql_trans *tr = m->session->tr;
35 75126 : sql_schema *sys = find_sql_schema(tr, "sys");
36 75125 : return find_sql_table(tr, sys, USER_TABLE_NAME);
37 : }
38 :
39 : oid
40 37811 : getUserOIDByName(mvc *m, const char *user)
41 : {
42 37811 : sql_trans *tr = m->session->tr;
43 37811 : sqlstore *store = m->session->tr->store;
44 37811 : sql_table *users = getUsersTbl(m);
45 37811 : sql_column *users_name = find_sql_column(users, "name");
46 37811 : return store->table_api.column_find_row(tr, users_name, user, NULL);
47 : }
48 :
49 :
50 : static str
51 2 : getUserName(mvc *m, oid rid)
52 : {
53 2 : if (is_oid_nil(rid))
54 : return NULL;
55 2 : sql_trans *tr = m->session->tr;
56 2 : sqlstore *store = m->session->tr->store;
57 2 : sql_table *users = getUsersTbl(m);
58 2 : return store->table_api.column_find_value(tr, find_sql_column(users, "name"), rid);
59 : }
60 :
61 : str
62 37318 : getUserPassword(mvc *m, oid rid)
63 : {
64 37318 : if (is_oid_nil(rid)) {
65 : return NULL;
66 : }
67 37311 : sql_trans *tr = m->session->tr;
68 37311 : sqlstore *store = m->session->tr->store;
69 37311 : sql_table *users = getUsersTbl(m);
70 37311 : return store->table_api.column_find_value(tr, find_sql_column(users, USER_PASSWORD_COLUMN), rid);
71 : }
72 :
73 :
74 : static int
75 3 : setUserPassword(mvc *m, oid rid, str value)
76 : {
77 3 : str err = NULL;
78 3 : str hash = NULL;
79 3 : int res;
80 3 : if (is_oid_nil(rid)) {
81 0 : (void) sql_error(m, 02, SQLSTATE(42000) "setUserPassword: invalid user");
82 0 : return LOG_ERR;
83 : }
84 3 : if (strNil(value)) {
85 0 : (void) sql_error(m, 02, SQLSTATE(42000) "setUserPassword: password cannot be nil");
86 0 : return LOG_ERR;
87 : }
88 3 : if ((err = AUTHverifyPassword(value)) != MAL_SUCCEED) {
89 0 : (void) sql_error(m, 02, SQLSTATE(42000) "setUserPassword: %s", getExceptionMessage(err));
90 0 : freeException(err);
91 0 : return LOG_ERR;
92 : }
93 3 : if ((err = AUTHcypherValue(&hash, value)) != MAL_SUCCEED) {
94 0 : (void) sql_error(m, 02, SQLSTATE(42000) "setUserPassword: %s", getExceptionMessage(err));
95 0 : freeException(err);
96 0 : GDKfree(hash);
97 0 : return LOG_ERR;
98 : }
99 :
100 3 : sql_trans *tr = m->session->tr;
101 3 : sqlstore *store = m->session->tr->store;
102 3 : sql_table *users = getUsersTbl(m);
103 3 : res = store->table_api.column_update_value(tr, find_sql_column(users, USER_PASSWORD_COLUMN), rid, hash);
104 3 : GDKfree(hash);
105 3 : return res;
106 : }
107 :
108 :
109 : static int
110 2 : changeUserPassword(mvc *m, oid rid, str oldpass, str newpass)
111 : {
112 2 : str err = NULL;
113 2 : str hash = NULL;
114 2 : str passValue = NULL;
115 2 : if (is_oid_nil(rid)) {
116 0 : (void) sql_error(m, 02, SQLSTATE(42000) "changeUserPassword: invalid user");
117 0 : return LOG_ERR;
118 : }
119 2 : if (strNil(newpass)) {
120 0 : (void) sql_error(m, 02, SQLSTATE(42000) "changeUserPassword: password cannot be nil");
121 0 : return LOG_ERR;
122 : }
123 2 : if (oldpass) {
124 : // validate old password match
125 2 : if ((err = AUTHdecypherValue(&hash, passValue=getUserPassword(m, rid))) != MAL_SUCCEED) {
126 0 : (void) sql_error(m, 02, SQLSTATE(42000) "changeUserPassword: %s", getExceptionMessage(err));
127 0 : freeException(err);
128 0 : GDKfree(passValue);
129 0 : return LOG_ERR;
130 : }
131 2 : GDKfree(passValue);
132 2 : if (strcmp(oldpass, hash) != 0) {
133 1 : (void) sql_error(m, 02, SQLSTATE(42000) "changeUserPassword: password mismatch");
134 1 : GDKfree(hash);
135 1 : return LOG_ERR;
136 : }
137 1 : GDKfree(hash);
138 : }
139 1 : return setUserPassword(m, rid, newpass);
140 : }
141 :
142 :
143 : static int
144 12 : monet5_find_role(ptr _mvc, str role, sqlid *role_id)
145 : {
146 12 : mvc *m = (mvc *) _mvc;
147 12 : sql_trans *tr = m->session->tr;
148 12 : sqlstore *store = m->session->tr->store;
149 12 : sql_schema *sys = find_sql_schema(tr, "sys");
150 12 : sql_table *auths = find_sql_table(tr, sys, "auths");
151 12 : sql_column *auth_name = find_sql_column(auths, "name");
152 12 : oid rid = store->table_api.column_find_row(tr, auth_name, role, NULL);
153 12 : if (is_oid_nil(rid))
154 : return -1;
155 12 : *role_id = store->table_api.column_find_sqlid(m->session->tr, find_sql_column(auths, "id"), rid);
156 12 : return 1;
157 : }
158 :
159 :
160 : static int
161 96 : monet5_drop_user(ptr _mvc, str user)
162 : {
163 96 : mvc *m = (mvc *) _mvc;
164 96 : oid rid;
165 96 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
166 96 : sql_table *users = find_sql_table(m->session->tr, sys, "db_user_info");
167 96 : sql_column *users_name = find_sql_column(users, "name");
168 96 : sqlstore *store = m->session->tr->store;
169 96 : int log_res = LOG_OK;
170 :
171 96 : rid = store->table_api.column_find_row(m->session->tr, users_name, user, NULL);
172 96 : if (!is_oid_nil(rid) && (log_res = store->table_api.table_delete(m->session->tr, users, rid)) != LOG_OK) {
173 0 : (void) sql_error(m, 02, "DROP USER: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
174 0 : return FALSE;
175 : }
176 :
177 : return TRUE;
178 : }
179 :
180 : #define outside_str 1
181 : #define inside_str 2
182 : #define default_schema_path "\"sys\"" /* "sys" will be the default schema path */
183 : #define default_optimizer "default_pipe"
184 : #define MAX_SCHEMA_SIZE 1024
185 :
186 :
187 : static str
188 37534 : parse_schema_path_str(mvc *m, str schema_path, bool build) /* this function for both building and validating the schema path */
189 : {
190 37534 : list *l = m->schema_path;
191 37534 : char next_schema[MAX_SCHEMA_SIZE]; /* needs one extra character for null terminator */
192 37534 : int status = outside_str;
193 37534 : size_t bp = 0;
194 :
195 37534 : if (strNil(schema_path))
196 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema path cannot be NULL");
197 :
198 37534 : if (build) {
199 74342 : while (l->t) /* if building, empty schema_path list */
200 37171 : (void) list_remove_node(l, NULL, l->t);
201 37171 : m->schema_path_has_sys = false;
202 37171 : m->schema_path_has_tmp = false;
203 : }
204 :
205 227286 : for (size_t i = 0; schema_path[i]; i++) {
206 189756 : char next = schema_path[i];
207 :
208 189756 : if (next == '"') {
209 75074 : if (status == inside_str && schema_path[i + 1] == '"') {
210 2 : next_schema[bp++] = '"';
211 2 : i++; /* has to advance two positions */
212 37540 : } else if (status == inside_str) {
213 37540 : if (bp == 0)
214 1 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema name cannot be empty");
215 37539 : if (bp == 1023)
216 1 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema has up to 1023 characters");
217 :
218 37538 : if (build) {
219 37176 : char *val = NULL;
220 37176 : next_schema[bp++] = '\0';
221 37176 : if (!(val = _STRDUP(next_schema)) || !list_append(l, val)) {
222 0 : _DELETE(val);
223 0 : throw(SQL, "sql.schema_path", SQLSTATE(HY013) MAL_MALLOC_FAIL);
224 : }
225 37176 : if (strcmp(next_schema, "sys") == 0)
226 37167 : m->schema_path_has_sys = true;
227 9 : else if (strcmp(next_schema, "tmp") == 0)
228 0 : m->schema_path_has_tmp = true;
229 : }
230 :
231 : bp = 0;
232 : status = outside_str;
233 : } else {
234 37532 : assert(status == outside_str);
235 : status = inside_str;
236 : }
237 114682 : } else if (next == ',') {
238 12 : if (status == outside_str && schema_path[i + 1] == '"') {
239 : status = inside_str;
240 : i++; /* has to advance two positions */
241 2 : } else if (status == inside_str) {
242 2 : if (bp == 1023)
243 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema has up to 1023 characters");
244 2 : next_schema[bp++] = ','; /* used inside a schema name */
245 0 : } else if (status == outside_str) {
246 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "The '\"' character is expected after the comma separator");
247 : }
248 114670 : } else if (status == inside_str) {
249 114668 : if (bp == 1023)
250 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema has up to 1023 characters");
251 114668 : if (bp == 0 && next == '%')
252 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "The character '%%' is not allowed as the first schema character");
253 114668 : next_schema[bp++] = next;
254 : } else {
255 2 : assert(status == outside_str);
256 2 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema in the path must be within '\"'");
257 : }
258 : }
259 37530 : if (status == inside_str)
260 1 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema path cannot end inside inside a schema name");
261 : return MAL_SUCCEED;
262 : }
263 :
264 : static str
265 355 : monet5_create_user(ptr _mvc, str user, str passwd, bool enc, str fullname, sqlid schema_id, str schema_path, sqlid grantorid, lng max_memory, int max_workers, str optimizer, sqlid role_id)
266 : {
267 355 : mvc *m = (mvc *) _mvc;
268 355 : oid rid;
269 355 : str ret, err, pwd, hash, schema_buf = NULL;
270 355 : sqlid user_id;
271 355 : sql_schema *s = find_sql_schema(m->session->tr, "sys");
272 355 : sql_table *db_user_info = find_sql_table(m->session->tr, s, "db_user_info"),
273 355 : *auths = find_sql_table(m->session->tr, s, "auths"),
274 355 : *schemas_tbl = find_sql_table(m->session->tr, s, "schemas");
275 : // Client c = MCgetClient(m->clientid);
276 355 : sqlstore *store = m->session->tr->store;
277 355 : int log_res = 0;
278 355 : bool new_schema = false;
279 :
280 355 : if (schema_id == 0) {
281 : // create default schema matching $user
282 33 : switch (sql_trans_create_schema(m->session->tr, user, m->role_id, m->user_id, &schema_id)) {
283 0 : case -1:
284 0 : throw(SQL,"sql.create_user",SQLSTATE(HY013) MAL_MALLOC_FAIL);
285 0 : case -2:
286 : case -3:
287 0 : throw(SQL,"sql.create_user",SQLSTATE(42000) "Create user schema failed due to transaction conflict");
288 : default:
289 : break;
290 : }
291 : new_schema = true;
292 : }
293 355 : assert(schema_id);
294 :
295 355 : if (is_oid_nil(rid = store->table_api.column_find_row(m->session->tr, find_sql_column(schemas_tbl, "id"), &schema_id, NULL)))
296 0 : throw(SQL,"sql.create_user",SQLSTATE(42000) "User schema not found");
297 :
298 355 : if (!schema_path) {
299 : // schema_name = store->table_api.column_find_value(m->session->tr, find_sql_column(schemas_tbl, "name"), rid);
300 : // if (schema_name) {
301 : // // "\"$schema_name\"\0"
302 : // if ((strlen(schema_name) + 4) > MAX_SCHEMA_SIZE) {
303 : // if (schema_name)
304 : // GDKfree(schema_name);
305 : // throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema has up to 1023 characters");
306 : // }
307 : // schema_buf = GDKmalloc(MAX_SCHEMA_SIZE);
308 : // snprintf(schema_buf, MAX_SCHEMA_SIZE, "\"%s\"", schema_name);
309 : // schema_path = schema_buf;
310 : // GDKfree(schema_name);
311 : // } else {
312 : // schema_path = default_schema_path;
313 : // }
314 352 : schema_path = default_schema_path;
315 : }
316 :
317 355 : if ((ret = parse_schema_path_str(m, schema_path, false)) != MAL_SUCCEED) {
318 1 : GDKfree(schema_buf);
319 1 : return ret;
320 : }
321 :
322 354 : if (!optimizer)
323 348 : optimizer = default_optimizer;
324 :
325 354 : if (!enc) {
326 116 : if (!(pwd = mcrypt_BackendSum(passwd, strlen(passwd)))) {
327 0 : GDKfree(schema_buf);
328 0 : throw(MAL, "sql.create_user", SQLSTATE(42000) "Crypt backend hash not found");
329 : }
330 : } else {
331 : pwd = passwd;
332 : }
333 :
334 354 : err = AUTHGeneratePasswordHash(&hash, pwd);
335 354 : if (!enc)
336 116 : free(pwd);
337 354 : if (err != MAL_SUCCEED) {
338 0 : GDKfree(schema_buf);
339 0 : throw(MAL, "sql.create_user", SQLSTATE(42000) "create backend hash failure");
340 : }
341 :
342 354 : user_id = store_next_oid(m->session->tr->store);
343 354 : sqlid default_role_id = role_id > 0 ? role_id : user_id;
344 354 : if ((log_res = store->table_api.table_insert(m->session->tr, db_user_info, &user, &fullname, &schema_id, &schema_path, &max_memory, &max_workers, &optimizer, &default_role_id, &hash))) {
345 1 : GDKfree(schema_buf);
346 1 : GDKfree(hash);
347 1 : throw(SQL, "sql.create_user", SQLSTATE(42000) "Create user failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
348 : }
349 : // clean up
350 353 : GDKfree(schema_buf);
351 353 : GDKfree(hash);
352 :
353 353 : if ((log_res = store->table_api.table_insert(m->session->tr, auths, &user_id, &user, &grantorid))) {
354 0 : throw(SQL, "sql.create_user", SQLSTATE(42000) "Create user failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
355 : }
356 :
357 353 : if (new_schema) {
358 : // update schema authorization to be default_role_id
359 32 : switch (sql_trans_change_schema_authorization(m->session->tr, schema_id, default_role_id)) {
360 0 : case -1:
361 0 : throw(SQL,"sql.create_user",SQLSTATE(HY013) MAL_MALLOC_FAIL);
362 0 : case -2:
363 : case -3:
364 0 : throw(SQL,"sql.create_user",SQLSTATE(42000) "Update schema authorization failed due to transaction conflict");
365 : default:
366 : break;
367 : }
368 :
369 : }
370 : return ret;
371 : }
372 :
373 : static oid
374 493 : monet5_find_user(ptr mp, str user)
375 : {
376 493 : return getUserOIDByName((mvc *) mp, user);
377 : }
378 :
379 : str
380 51 : monet5_password_hash(mvc *m, const char *username)
381 : {
382 51 : str msg, hash = NULL;
383 51 : oid rid = getUserOIDByName(m, username);
384 51 : str password = getUserPassword(m, rid);
385 51 : if (password) {
386 50 : msg = AUTHdecypherValue(&hash, password);
387 50 : GDKfree(password);
388 50 : if (msg) {
389 0 : (void) sql_error(m, 02, SQLSTATE(42000) "monet5_password_hash: %s", getExceptionMessage(msg));
390 0 : freeException(msg);
391 : }
392 : }
393 51 : return hash;
394 : }
395 :
396 : static void
397 225 : monet5_create_privileges(ptr _mvc, sql_schema *s, const char *initpasswd)
398 : {
399 225 : sql_schema *sys;
400 225 : sql_table *t = NULL;
401 225 : sql_table *uinfo = NULL;
402 225 : sql_column *col = NULL;
403 225 : mvc *m = (mvc *) _mvc;
404 225 : sqlid schema_id = 0;
405 225 : str err = NULL;
406 :
407 : /* create the authorisation related tables */
408 225 : mvc_create_table(&t, m, s, REMOTE_USER_INFO, tt_table, 1, SQL_PERSIST, 0, -1, 0);
409 225 : mvc_create_column_(&col, m, t, "table_id", "int", 32);
410 225 : mvc_create_column_(&col, m, t, "username", "varchar", 1024);
411 225 : mvc_create_column_(&col, m, t, "password", "varchar", 256);
412 :
413 225 : mvc_create_table(&t, m, s, "db_user_info", tt_table, 1, SQL_PERSIST, 0, -1, 0);
414 225 : mvc_create_column_(&col, m, t, "name", "varchar", 1024);
415 225 : mvc_create_column_(&col, m, t, "fullname", "varchar", 2048);
416 225 : mvc_create_column_(&col, m, t, "default_schema", "int", 9);
417 225 : mvc_create_column_(&col, m, t, "schema_path", "varchar", 0);
418 225 : mvc_create_column_(&col, m, t, "max_memory", "bigint", 64);
419 225 : mvc_create_column_(&col, m, t, "max_workers", "int", 32);
420 225 : mvc_create_column_(&col, m, t, "optimizer", "varchar", 1024);
421 225 : mvc_create_column_(&col, m, t, "default_role", "int", 32);
422 225 : mvc_create_column_(&col, m, t, "password", "varchar", 256);
423 225 : uinfo = t;
424 :
425 225 : sys = find_sql_schema(m->session->tr, "sys");
426 225 : schema_id = sys->base.id;
427 225 : assert(schema_id == 2000);
428 :
429 225 : sqlstore *store = m->session->tr->store;
430 225 : const char *username = "monetdb";
431 225 : char *password = initpasswd ? mcrypt_BackendSum(initpasswd, strlen(initpasswd)) : mcrypt_BackendSum("monetdb", strlen("monetdb"));
432 225 : char *hash = NULL;
433 225 : if (password == NULL ||
434 225 : (err = AUTHGeneratePasswordHash(&hash, password)) != MAL_SUCCEED) {
435 0 : TRC_CRITICAL(SQL_TRANS, "generate password hash failure");
436 0 : freeException(err);
437 0 : free(password);
438 0 : return ;
439 : }
440 225 : free(password);
441 :
442 225 : const char *fullname = "MonetDB Admin";
443 225 : const char *schema_path = default_schema_path;
444 : // default values
445 225 : char *optimizer = default_optimizer;
446 225 : lng max_memory = 0;
447 225 : int max_workers = 0;
448 225 : sqlid default_role_id = USER_MONETDB;
449 :
450 225 : store->table_api.table_insert(m->session->tr, uinfo, &username, &fullname, &schema_id, &schema_path, &max_memory,
451 : &max_workers, &optimizer, &default_role_id, &hash);
452 225 : GDKfree(hash);
453 : }
454 :
455 : static int
456 172 : monet5_schema_has_user(ptr _mvc, sql_schema *s)
457 : {
458 172 : mvc *m = (mvc *) _mvc;
459 172 : oid rid;
460 172 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
461 172 : sql_table *users = find_sql_table(m->session->tr, sys, "db_user_info");
462 172 : sql_column *users_schema = find_sql_column(users, "default_schema");
463 172 : sqlid schema_id = s->base.id;
464 :
465 172 : sqlstore *store = m->session->tr->store;
466 172 : rid = store->table_api.column_find_row(m->session->tr, users_schema, &schema_id, NULL);
467 172 : if (is_oid_nil(rid))
468 164 : return FALSE;
469 : return TRUE;
470 : }
471 :
472 : static int
473 81 : monet5_alter_user(ptr _mvc, str user, str passwd, bool enc, sqlid schema_id, str schema_path, str oldpasswd, sqlid
474 : role_id, lng max_memory, int max_workers)
475 : {
476 81 : mvc *m = (mvc *) _mvc;
477 81 : Client c = MCgetClient(m->clientid);
478 81 : str err;
479 81 : int res = LOG_OK;
480 81 : oid rid = oid_nil;
481 :
482 81 : sqlstore *store = m->session->tr->store;
483 81 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
484 81 : sql_table *info = find_sql_table(m->session->tr, sys, "db_user_info");
485 81 : sql_column *users_name = find_sql_column(info, "name");
486 :
487 81 : if (schema_id || schema_path || role_id || max_memory > -1 || max_workers > -1) {
488 77 : rid = store->table_api.column_find_row(m->session->tr, users_name, user, NULL);
489 : // user should be checked here since the way `ALTER USER ident ...` stmt is
490 77 : if (is_oid_nil(rid)) {
491 0 : (void) sql_error(m, 02, "ALTER USER: local inconsistency, "
492 : "your database is damaged, auth not found in SQL catalog");
493 0 : return FALSE;
494 : }
495 : }
496 :
497 :
498 81 : if (passwd != NULL) {
499 4 : str pwd = NULL;
500 4 : str opwd = NULL;
501 4 : if (!enc) {
502 4 : pwd = mcrypt_BackendSum(passwd, strlen(passwd));
503 4 : if (pwd == NULL) {
504 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: crypt backend hash not found");
505 0 : return FALSE;
506 : }
507 4 : if (oldpasswd != NULL) {
508 2 : opwd = mcrypt_BackendSum(oldpasswd, strlen(oldpasswd));
509 2 : if (opwd == NULL) {
510 0 : free(pwd);
511 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: crypt backend hash not found");
512 0 : return FALSE;
513 : }
514 : }
515 : } else {
516 : pwd = passwd;
517 : opwd = oldpasswd;
518 : }
519 :
520 4 : if (user) {
521 : // verify query user value is not the session user
522 2 : str username = NULL;
523 2 : if ((username = getUserName(m, c->user)) == NULL) {
524 0 : if (!enc) {
525 0 : free(pwd);
526 0 : free(opwd);
527 : }
528 0 : (void) sql_error(m, 02, "ALTER USER: invalid user");
529 0 : return (FALSE);
530 : }
531 2 : if (strcmp(username, user) == 0) {
532 0 : GDKfree(username);
533 0 : if (!enc) {
534 0 : free(pwd);
535 0 : free(opwd);
536 : }
537 0 : (void) sql_error(m, 02, "ALTER USER: "
538 : "use 'ALTER USER SET [ ENCRYPTED ] PASSWORD xxx "
539 : "USING OLD PASSWORD yyy' "
540 : "when changing your own password");
541 0 : return (FALSE);
542 : }
543 2 : GDKfree(username);
544 : // verify current user is MAL_ADMIN ?
545 2 : if ((err = AUTHrequireAdmin(c)) != MAL_SUCCEED) {
546 0 : (void) sql_error(m, 02, "ALTER USER: %s", getExceptionMessage(err));
547 0 : freeException(err);
548 0 : if (!enc) {
549 0 : free(pwd);
550 0 : free(opwd);
551 : }
552 0 : return (FALSE);
553 : }
554 2 : if (setUserPassword(m, getUserOIDByName(m, user), pwd) != LOG_OK) {
555 0 : if (!enc) {
556 0 : free(pwd);
557 0 : free(opwd);
558 : }
559 0 : return (FALSE);
560 : }
561 :
562 : } else {
563 2 : if (changeUserPassword(m, c->user, opwd, pwd) != LOG_OK) {
564 1 : if (!enc) {
565 1 : free(pwd);
566 1 : free(opwd);
567 : }
568 1 : return (FALSE);
569 : }
570 : }
571 3 : if (!enc) {
572 3 : free(pwd);
573 3 : free(opwd);
574 : }
575 : }
576 :
577 80 : if (schema_id) {
578 63 : sql_column *users_schema = find_sql_column(info, "default_schema");
579 :
580 63 : if ((res = store->table_api.column_update_value(m->session->tr, users_schema, rid, &schema_id))) {
581 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
582 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
583 0 : return (FALSE);
584 : }
585 : }
586 :
587 80 : if (schema_path) {
588 8 : sql_column *sp = find_sql_column(info, "schema_path");
589 :
590 8 : if ((err = parse_schema_path_str(m, schema_path, false)) != MAL_SUCCEED) {
591 4 : (void) sql_error(m, 02, "ALTER USER: %s", getExceptionMessage(err));
592 4 : freeException(err);
593 4 : return (FALSE);
594 : }
595 :
596 4 : if ((res = store->table_api.column_update_value(m->session->tr, sp, rid, schema_path))) {
597 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
598 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
599 0 : return (FALSE);
600 : }
601 : }
602 :
603 76 : if (role_id) {
604 3 : sql_column *users_role = find_sql_column(info, "default_role");
605 :
606 3 : if ((res = store->table_api.column_update_value(m->session->tr, users_role, rid, &role_id))) {
607 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
608 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
609 0 : return (FALSE);
610 : }
611 : }
612 :
613 76 : if (max_memory > -1) {
614 3 : sql_column *users_max_memory = find_sql_column(info, "max_memory");
615 :
616 3 : if ((res = store->table_api.column_update_value(m->session->tr, users_max_memory, rid, &max_memory))) {
617 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
618 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
619 0 : return (FALSE);
620 : }
621 : }
622 :
623 76 : if (max_workers > -1) {
624 2 : sql_column *users_max_workers = find_sql_column(info, "max_workers");
625 :
626 2 : if ((res = store->table_api.column_update_value(m->session->tr, users_max_workers, rid, &max_workers))) {
627 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
628 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
629 0 : return (FALSE);
630 : }
631 : }
632 :
633 : return TRUE;
634 : }
635 :
636 : static int
637 2 : monet5_rename_user(ptr _mvc, str olduser, str newuser)
638 : {
639 2 : mvc *m = (mvc *) _mvc;
640 2 : oid rid;
641 2 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
642 2 : sql_table *info = find_sql_table(m->session->tr, sys, "db_user_info");
643 2 : sql_column *users_name = find_sql_column(info, "name");
644 2 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
645 2 : sql_column *auths_name = find_sql_column(auths, "name");
646 2 : int res = LOG_OK;
647 :
648 2 : sqlstore *store = m->session->tr->store;
649 2 : rid = store->table_api.column_find_row(m->session->tr, users_name, olduser, NULL);
650 2 : if (is_oid_nil(rid)) {
651 0 : (void) sql_error(m, 02, "ALTER USER: local inconsistency, "
652 : "your database is damaged, user not found in SQL catalog");
653 0 : return (FALSE);
654 : }
655 2 : if ((res = store->table_api.column_update_value(m->session->tr, users_name, rid, newuser))) {
656 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
657 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
658 0 : return (FALSE);
659 : }
660 :
661 2 : rid = store->table_api.column_find_row(m->session->tr, auths_name, olduser, NULL);
662 2 : if (is_oid_nil(rid)) {
663 0 : (void) sql_error(m, 02, "ALTER USER: local inconsistency, "
664 : "your database is damaged, auth not found in SQL catalog");
665 0 : return (FALSE);
666 : }
667 2 : if ((res = store->table_api.column_update_value(m->session->tr, auths_name, rid, newuser))) {
668 0 : (void) sql_error(m, 02, SQLSTATE(42000) "ALTER USER: failed%s",
669 : res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
670 0 : return (FALSE);
671 : }
672 : return (TRUE);
673 : }
674 :
675 : static void *
676 17 : monet5_schema_user_dependencies(ptr _trans, int schema_id)
677 : {
678 17 : rids *A, *U;
679 17 : sql_trans *tr = (sql_trans *) _trans;
680 17 : sql_schema *s = find_sql_schema(tr, "sys");
681 :
682 17 : sql_table *auths = find_sql_table(tr, s, "auths");
683 17 : sql_column *auth_name = find_sql_column(auths, "name");
684 :
685 17 : sql_table *users = find_sql_table(tr, s, "db_user_info");
686 17 : sql_column *users_name = find_sql_column(users, "name");
687 17 : sql_column *users_sch = find_sql_column(users, "default_schema");
688 :
689 17 : sqlstore *store = tr->store;
690 : /* select users with given schema */
691 17 : U = store->table_api.rids_select(tr, users_sch, &schema_id, &schema_id, NULL);
692 : /* select all authorization ids */
693 17 : A = store->table_api.rids_select(tr, auth_name, NULL, NULL);
694 : /* join all authorization with the selected users */
695 17 : if (A && U)
696 17 : A = store->table_api.rids_join(tr, A, auth_name, U, users_name);
697 17 : store->table_api.rids_destroy(U);
698 17 : return A;
699 : }
700 :
701 : void
702 332 : monet5_user_init(backend_functions *be_funcs)
703 : {
704 332 : be_funcs->fcuser = &monet5_create_user;
705 332 : be_funcs->fduser = &monet5_drop_user;
706 332 : be_funcs->ffuser = &monet5_find_user;
707 332 : be_funcs->ffrole = &monet5_find_role;
708 332 : be_funcs->fcrpriv = &monet5_create_privileges;
709 332 : be_funcs->fshuser = &monet5_schema_has_user;
710 332 : be_funcs->fauser = &monet5_alter_user;
711 332 : be_funcs->fruser = &monet5_rename_user;
712 332 : be_funcs->fschuserdep = &monet5_schema_user_dependencies;
713 332 : }
714 :
715 : int
716 0 : monet5_user_get_def_schema(mvc *m, int user, str *schema)
717 : {
718 0 : oid rid;
719 0 : sqlid schema_id = int_nil;
720 0 : sql_schema *sys = NULL;
721 0 : sql_table *user_info = NULL;
722 0 : sql_table *schemas = NULL;
723 0 : sql_table *auths = NULL;
724 0 : str username = NULL, sname = NULL;
725 0 : sqlstore *store = m->session->tr->store;
726 :
727 0 : sys = find_sql_schema(m->session->tr, "sys");
728 0 : auths = find_sql_table(m->session->tr, sys, "auths");
729 0 : user_info = find_sql_table(m->session->tr, sys, "db_user_info");
730 0 : schemas = find_sql_table(m->session->tr, sys, "schemas");
731 :
732 0 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(auths, "id"), &user, NULL);
733 0 : if (is_oid_nil(rid))
734 : return -2;
735 0 : if (!(username = store->table_api.column_find_value(m->session->tr, find_sql_column(auths, "name"), rid)))
736 : return -1;
737 0 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(user_info, "name"), username, NULL);
738 0 : _DELETE(username);
739 :
740 0 : if (!is_oid_nil(rid))
741 0 : schema_id = store->table_api.column_find_sqlid(m->session->tr, find_sql_column(user_info, "default_schema"), rid);
742 0 : if (is_int_nil(schema_id))
743 : return -3;
744 0 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(schemas, "id"), &schema_id, NULL);
745 0 : if (is_oid_nil(rid))
746 : return -3;
747 :
748 0 : if (!(sname = store->table_api.column_find_value(m->session->tr, find_sql_column(schemas, "name"), rid)))
749 : return -1;
750 0 : *schema = sa_strdup(m->session->sa, sname);
751 0 : _DELETE(sname);
752 0 : return *schema ? 0 : -1;
753 : }
754 :
755 : int
756 37171 : monet5_user_set_def_schema(mvc *m, oid user, str username)
757 : {
758 37171 : oid rid;
759 37171 : sqlid schema_id, default_role_id;
760 37171 : sql_schema *sys = NULL;
761 37171 : sql_table *user_info = NULL;
762 37171 : sql_column *users_name = NULL;
763 37171 : sql_column *users_schema = NULL;
764 37171 : sql_column *users_schema_path = NULL;
765 37171 : sql_column *users_default_role = NULL;
766 37171 : sql_table *schemas = NULL;
767 37171 : sql_column *schemas_name = NULL;
768 37171 : sql_column *schemas_id = NULL;
769 37171 : sql_table *auths = NULL;
770 37171 : sql_column *auths_id = NULL;
771 37171 : sql_column *auths_name = NULL;
772 37171 : str path_err = NULL, other = NULL, schema = NULL, schema_cpy, schema_path = NULL, userrole = NULL;
773 37171 : int ok = 1, res = 0;
774 :
775 37171 : TRC_DEBUG(SQL_TRANS, OIDFMT "\n", user);
776 :
777 37171 : sys = find_sql_schema(m->session->tr, "sys");
778 37171 : user_info = find_sql_table(m->session->tr, sys, "db_user_info");
779 37171 : users_name = find_sql_column(user_info, "name");
780 37171 : users_schema = find_sql_column(user_info, "default_schema");
781 37171 : users_schema_path = find_sql_column(user_info, "schema_path");
782 37168 : users_default_role = find_sql_column(user_info, "default_role");
783 :
784 37171 : sqlstore *store = m->session->tr->store;
785 37171 : rid = store->table_api.column_find_row(m->session->tr, users_name, username, NULL);
786 37171 : if (is_oid_nil(rid)) {
787 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
788 0 : freeException(other);
789 0 : return -2;
790 : }
791 37171 : schema_id = store->table_api.column_find_sqlid(m->session->tr, users_schema, rid);
792 37171 : if (!(schema_path = store->table_api.column_find_value(m->session->tr, users_schema_path, rid))) {
793 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
794 0 : freeException(other);
795 0 : return -1;
796 : }
797 :
798 37171 : default_role_id = store->table_api.column_find_sqlid(m->session->tr, users_default_role, rid);
799 :
800 37171 : schemas = find_sql_table(m->session->tr, sys, "schemas");
801 37171 : schemas_name = find_sql_column(schemas, "name");
802 37171 : schemas_id = find_sql_column(schemas, "id");
803 37171 : auths = find_sql_table(m->session->tr, sys, "auths");
804 37171 : auths_id = find_sql_column(auths, "id");
805 37171 : auths_name = find_sql_column(auths, "name");
806 :
807 37171 : rid = store->table_api.column_find_row(m->session->tr, schemas_id, &schema_id, NULL);
808 37171 : if (is_oid_nil(rid)) {
809 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
810 0 : freeException(other);
811 0 : _DELETE(schema_path);
812 0 : return -3;
813 : }
814 37171 : if (!(schema = store->table_api.column_find_value(m->session->tr, schemas_name, rid))) {
815 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
816 0 : freeException(other);
817 0 : _DELETE(schema_path);
818 0 : return -1;
819 : }
820 37171 : schema_cpy = schema;
821 37171 : schema = sa_strdup(m->session->sa, schema);
822 37170 : _DELETE(schema_cpy);
823 :
824 : /* check if username exists */
825 37171 : rid = store->table_api.column_find_row(m->session->tr, auths_name, username, NULL);
826 37170 : if (is_oid_nil(rid)) {
827 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
828 0 : freeException(other);
829 0 : _DELETE(schema_path);
830 0 : return -2;
831 : }
832 :
833 37170 : m->user_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
834 :
835 : /* check if role exists */
836 37171 : rid = store->table_api.column_find_row(m->session->tr, auths_id, &default_role_id, NULL);
837 37171 : if (is_oid_nil(rid)) {
838 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
839 0 : freeException(other);
840 0 : _DELETE(schema_path);
841 0 : return -4;
842 : }
843 37171 : m->role_id = default_role_id;
844 37171 : if (!(userrole = store->table_api.column_find_value(m->session->tr, auths_name, rid))) {
845 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
846 0 : freeException(other);
847 0 : _DELETE(schema_path);
848 0 : return -1;
849 : }
850 :
851 : /* while getting the session's schema, set the search path as well */
852 : /* new default schema */
853 37171 : m->session->def_schema_name = schema;
854 37171 : if (!(ok = mvc_set_schema(m, schema)) || (path_err = parse_schema_path_str(m, schema_path, true)) != MAL_SUCCEED) {
855 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
856 0 : freeException(other);
857 0 : _DELETE(schema_path);
858 0 : _DELETE(userrole);
859 0 : freeException(path_err);
860 0 : return ok == 0 ? -3 : -1;
861 : }
862 :
863 :
864 : /* reset the user and schema names */
865 74341 : if (!sqlvar_set_string(find_global_var(m, sys, "current_schema"), schema) ||
866 74342 : !sqlvar_set_string(find_global_var(m, sys, "current_user"), username) ||
867 37170 : !sqlvar_set_string(find_global_var(m, sys, "current_role"), userrole)) {
868 : res = -1;
869 : }
870 37171 : _DELETE(schema_path);
871 37171 : _DELETE(userrole);
872 37171 : return res;
873 : }
874 :
875 : int
876 37171 : monet5_user_get_limits(mvc *m, int user, lng *maxmem, int *maxwrk)
877 : {
878 37171 : oid rid;
879 37171 : sql_schema *sys = NULL;
880 37171 : sql_table *user_info = NULL;
881 37171 : sql_table *auths = NULL;
882 37171 : str username = NULL;
883 37171 : sqlstore *store = m->session->tr->store;
884 37171 : lng max_memory = 0;
885 37171 : int max_workers = 0;
886 :
887 37171 : assert(m->session->tr->active);
888 :
889 37171 : sys = find_sql_schema(m->session->tr, "sys");
890 37169 : auths = find_sql_table(m->session->tr, sys, "auths");
891 37170 : user_info = find_sql_table(m->session->tr, sys, "db_user_info");
892 :
893 37171 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(auths, "id"), &user, NULL);
894 37171 : if (is_oid_nil(rid))
895 : return -2;
896 37171 : if (!(username = store->table_api.column_find_value(m->session->tr, find_sql_column(auths, "name"), rid)))
897 : return -1;
898 37171 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(user_info, "name"), username, NULL);
899 37171 : _DELETE(username);
900 :
901 37170 : if (!is_oid_nil(rid)) {
902 37170 : max_memory = store->table_api.column_find_lng(m->session->tr, find_sql_column(user_info, "max_memory"), rid);
903 37171 : max_workers = store->table_api.column_find_int(m->session->tr, find_sql_column(user_info, "max_workers"), rid);
904 : }
905 :
906 37171 : *maxmem = max_memory > 0 ? max_memory : 0;
907 37171 : *maxwrk = max_workers > 0 ? max_workers : 0;
908 37171 : return 0;
909 : }
910 :
911 : /* move into mvc_remote_create */
912 : /* and mvc_remote_drop */
913 : str
914 101 : remote_create(mvc *m, sqlid id, const char *username, const char *password, int pw_encrypted)
915 : {
916 101 : int log_res = 0;
917 101 : sql_trans *tr = m->session->tr;
918 101 : sqlstore *store = tr->store;
919 101 : sql_schema *sys = find_sql_schema(tr, "sys");
920 101 : sql_table *remote_user_info = find_sql_table(tr, sys, REMOTE_USER_INFO);
921 :
922 101 : char *pwhash = NULL, *cypher = NULL;
923 101 : if (!pw_encrypted) {
924 10 : if((pwhash = mcrypt_BackendSum(password, strlen(password))) == NULL)
925 0 : throw(MAL, "addRemoteTableCredentials", SQLSTATE(42000) "Crypt backend hash not found");
926 : }
927 101 : if (strNil(password)) {
928 85 : oid rid = getUserOIDByName(m, username);
929 85 : str cypher = getUserPassword(m, rid);
930 85 : str err = AUTHdecypherValue(&pwhash, cypher);
931 85 : GDKfree(cypher);
932 85 : if (err) {
933 0 : GDKfree(err);
934 0 : throw(MAL, "addRemoteTableCredentials", SQLSTATE(42000) "Crypt backend hash not found");
935 : }
936 : }
937 196 : str msg = AUTHcypherValue(&cypher, pwhash ? pwhash : password);
938 101 : if (pwhash != NULL) {
939 95 : if (!pw_encrypted)
940 10 : free(pwhash);
941 : else
942 85 : GDKfree(pwhash);
943 : }
944 101 : if (msg != MAL_SUCCEED)
945 : return msg;
946 101 : log_res = store->table_api.table_insert(m->session->tr, remote_user_info, &id, &username, &cypher, NULL);
947 101 : GDKfree(cypher);
948 101 : if (log_res != 0)
949 0 : throw(SQL, "sql.create_table", SQLSTATE(42000) "Create table failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
950 : return MAL_SUCCEED;
951 : }
952 :
953 : str
954 186 : remote_get(mvc *m, sqlid id, str *username, str *pwhash)
955 : {
956 186 : sql_trans *tr = m->session->tr;
957 186 : sqlstore *store = tr->store;
958 186 : sql_schema *sys = find_sql_schema(tr, "sys");
959 186 : sql_table *remote_user_info = find_sql_table(tr, sys, REMOTE_USER_INFO);
960 186 : sql_column *remote_user_info_id = find_sql_column(remote_user_info, "table_id");
961 186 : oid rid = store->table_api.column_find_row(tr, remote_user_info_id, &id, NULL);
962 :
963 186 : if (is_oid_nil(rid))
964 0 : throw(MAL, "remote", SQLSTATE(42000) "remote table credentials not found");
965 186 : *username = store->table_api.column_find_value(tr, find_sql_column(remote_user_info, "username"), rid);
966 186 : if (strNil(*username)) {
967 0 : GDKfree(*username);
968 0 : *username = GDKstrdup("");
969 : }
970 186 : str cypher = store->table_api.column_find_value(tr, find_sql_column(remote_user_info, "password"), rid);
971 186 : str err = AUTHdecypherValue(pwhash, cypher);
972 186 : GDKfree(cypher);
973 186 : if (err)
974 : return err;
975 : return MAL_SUCCEED;
976 : }
|
