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 76475 : getUsersTbl(mvc *m)
33 : {
34 76475 : sql_trans *tr = m->session->tr;
35 76475 : sql_schema *sys = find_sql_schema(tr, "sys");
36 76475 : return find_sql_table(tr, sys, USER_TABLE_NAME);
37 : }
38 :
39 : oid
40 38465 : getUserOIDByName(mvc *m, const char *user)
41 : {
42 38465 : sql_trans *tr = m->session->tr;
43 38465 : sqlstore *store = m->session->tr->store;
44 38465 : sql_table *users = getUsersTbl(m);
45 38465 : sql_column *users_name = find_sql_column(users, "name");
46 38465 : 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 38010 : getUserPassword(mvc *m, oid rid)
63 : {
64 38010 : if (is_oid_nil(rid)) {
65 : return NULL;
66 : }
67 38006 : sql_trans *tr = m->session->tr;
68 38006 : sqlstore *store = m->session->tr->store;
69 38006 : sql_table *users = getUsersTbl(m);
70 38006 : 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 10 : monet5_find_role(ptr _mvc, str role, sqlid *role_id)
145 : {
146 10 : mvc *m = (mvc *) _mvc;
147 10 : sql_trans *tr = m->session->tr;
148 10 : sqlstore *store = m->session->tr->store;
149 10 : sql_schema *sys = find_sql_schema(tr, "sys");
150 10 : sql_table *auths = find_sql_table(tr, sys, "auths");
151 10 : sql_column *auth_name = find_sql_column(auths, "name");
152 10 : oid rid = store->table_api.column_find_row(tr, auth_name, role, NULL);
153 10 : if (is_oid_nil(rid))
154 : return -1;
155 10 : *role_id = store->table_api.column_find_sqlid(m->session->tr, find_sql_column(auths, "id"), rid);
156 10 : return 1;
157 : }
158 :
159 :
160 : static int
161 93 : monet5_drop_user(ptr _mvc, str user)
162 : {
163 93 : mvc *m = (mvc *) _mvc;
164 93 : oid rid;
165 93 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
166 93 : sql_table *users = find_sql_table(m->session->tr, sys, "db_user_info");
167 93 : sql_column *users_name = find_sql_column(users, "name");
168 93 : sqlstore *store = m->session->tr->store;
169 93 : int log_res = LOG_OK;
170 :
171 93 : rid = store->table_api.column_find_row(m->session->tr, users_name, user, NULL);
172 93 : 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 38216 : parse_schema_path_str(mvc *m, str schema_path, bool build) /* this function for both building and validating the schema path */
189 : {
190 38216 : list *l = m->schema_path;
191 38216 : char next_schema[MAX_SCHEMA_SIZE]; /* needs one extra character for null terminator */
192 38216 : int status = outside_str;
193 38216 : size_t bp = 0;
194 :
195 38216 : if (strNil(schema_path))
196 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema path cannot be NULL");
197 :
198 38216 : if (build) {
199 75760 : while (l->t) /* if building, empty schema_path list */
200 37880 : (void) list_remove_node(l, NULL, l->t);
201 37880 : m->schema_path_has_sys = false;
202 37880 : m->schema_path_has_tmp = false;
203 : }
204 :
205 231379 : for (size_t i = 0; schema_path[i]; i++) {
206 193167 : char next = schema_path[i];
207 :
208 193167 : if (next == '"') {
209 76439 : if (status == inside_str && schema_path[i + 1] == '"') {
210 2 : next_schema[bp++] = '"';
211 2 : i++; /* has to advance two positions */
212 38223 : } else if (status == inside_str) {
213 38223 : if (bp == 0)
214 1 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema name cannot be empty");
215 38222 : if (bp == 1023)
216 1 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema has up to 1023 characters");
217 :
218 38221 : if (build) {
219 37885 : char *val = NULL;
220 37885 : next_schema[bp++] = '\0';
221 37885 : 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 37885 : if (strcmp(next_schema, "sys") == 0)
226 37876 : 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 38214 : assert(status == outside_str);
235 : status = inside_str;
236 : }
237 116728 : } 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 116716 : } else if (status == inside_str) {
249 116714 : if (bp == 1023)
250 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "A schema has up to 1023 characters");
251 116714 : if (bp == 0 && next == '%')
252 0 : throw(SQL, "sql.schema_path", SQLSTATE(42000) "The character '%%' is not allowed as the first schema character");
253 116714 : 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 38212 : 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 328 : 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 328 : mvc *m = (mvc *) _mvc;
268 328 : oid rid;
269 328 : str ret, err, pwd, hash, schema_buf = NULL;
270 328 : sqlid user_id;
271 328 : sql_schema *s = find_sql_schema(m->session->tr, "sys");
272 328 : sql_table *db_user_info = find_sql_table(m->session->tr, s, "db_user_info"),
273 328 : *auths = find_sql_table(m->session->tr, s, "auths"),
274 328 : *schemas_tbl = find_sql_table(m->session->tr, s, "schemas");
275 : // Client c = MCgetClient(m->clientid);
276 328 : sqlstore *store = m->session->tr->store;
277 328 : int log_res = 0;
278 328 : bool new_schema = false;
279 :
280 328 : if (schema_id == 0) {
281 : // create default schema matching $user
282 13 : 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 328 : assert(schema_id);
294 :
295 328 : 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 328 : 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 325 : schema_path = default_schema_path;
315 : }
316 :
317 328 : if ((ret = parse_schema_path_str(m, schema_path, false)) != MAL_SUCCEED) {
318 1 : GDKfree(schema_buf);
319 1 : return ret;
320 : }
321 :
322 327 : if (!optimizer)
323 323 : optimizer = default_optimizer;
324 :
325 327 : if (!enc) {
326 95 : 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 327 : err = AUTHGeneratePasswordHash(&hash, pwd);
335 327 : if (!enc)
336 95 : free(pwd);
337 327 : 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 327 : user_id = store_next_oid(m->session->tr->store);
343 327 : sqlid default_role_id = role_id > 0 ? role_id : user_id;
344 327 : 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 326 : GDKfree(schema_buf);
351 326 : GDKfree(hash);
352 :
353 326 : 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 326 : if (new_schema) {
358 : // update schema authorization to be default_role_id
359 12 : 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 455 : monet5_find_user(ptr mp, str user)
375 : {
376 455 : return getUserOIDByName((mvc *) mp, user);
377 : }
378 :
379 : str
380 42 : monet5_password_hash(mvc *m, const char *username)
381 : {
382 42 : str msg, hash = NULL;
383 42 : oid rid = getUserOIDByName(m, username);
384 42 : str password = getUserPassword(m, rid);
385 42 : if (password) {
386 42 : msg = AUTHdecypherValue(&hash, password);
387 42 : GDKfree(password);
388 42 : if (msg) {
389 0 : (void) sql_error(m, 02, SQLSTATE(42000) "monet5_password_hash: %s", getExceptionMessage(msg));
390 0 : freeException(msg);
391 : }
392 : }
393 42 : return hash;
394 : }
395 :
396 : static void
397 223 : monet5_create_privileges(ptr _mvc, sql_schema *s, const char *initpasswd)
398 : {
399 223 : sql_schema *sys;
400 223 : sql_table *t = NULL;
401 223 : sql_table *uinfo = NULL;
402 223 : sql_column *col = NULL;
403 223 : mvc *m = (mvc *) _mvc;
404 223 : sqlid schema_id = 0;
405 223 : str err = NULL;
406 :
407 : /* create the authorisation related tables */
408 223 : mvc_create_table(&t, m, s, REMOTE_USER_INFO, tt_table, 1, SQL_PERSIST, 0, -1, 0);
409 223 : mvc_create_column_(&col, m, t, "table_id", "int", 32);
410 223 : mvc_create_column_(&col, m, t, "username", "varchar", 1024);
411 223 : mvc_create_column_(&col, m, t, "password", "varchar", 256);
412 :
413 223 : mvc_create_table(&t, m, s, "db_user_info", tt_table, 1, SQL_PERSIST, 0, -1, 0);
414 223 : mvc_create_column_(&col, m, t, "name", "varchar", 1024);
415 223 : mvc_create_column_(&col, m, t, "fullname", "varchar", 2048);
416 223 : mvc_create_column_(&col, m, t, "default_schema", "int", 9);
417 223 : mvc_create_column_(&col, m, t, "schema_path", "clob", 0);
418 223 : mvc_create_column_(&col, m, t, "max_memory", "bigint", 64);
419 223 : mvc_create_column_(&col, m, t, "max_workers", "int", 32);
420 223 : mvc_create_column_(&col, m, t, "optimizer", "varchar", 1024);
421 223 : mvc_create_column_(&col, m, t, "default_role", "int", 32);
422 223 : mvc_create_column_(&col, m, t, "password", "varchar", 256);
423 223 : uinfo = t;
424 :
425 223 : sys = find_sql_schema(m->session->tr, "sys");
426 223 : schema_id = sys->base.id;
427 223 : assert(schema_id == 2000);
428 :
429 223 : sqlstore *store = m->session->tr->store;
430 223 : char *username = "monetdb";
431 223 : char *password = initpasswd ? mcrypt_BackendSum(initpasswd, strlen(initpasswd)) : mcrypt_BackendSum("monetdb", strlen("monetdb"));
432 223 : char *hash = NULL;
433 223 : if (password == NULL ||
434 223 : (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 223 : free(password);
441 :
442 223 : char *fullname = "MonetDB Admin";
443 223 : char *schema_path = default_schema_path;
444 : // default values
445 223 : char *optimizer = default_optimizer;
446 223 : lng max_memory = 0;
447 223 : int max_workers = 0;
448 223 : sqlid default_role_id = USER_MONETDB;
449 :
450 223 : 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 223 : GDKfree(hash);
453 : }
454 :
455 : static int
456 164 : monet5_schema_has_user(ptr _mvc, sql_schema *s)
457 : {
458 164 : mvc *m = (mvc *) _mvc;
459 164 : oid rid;
460 164 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
461 164 : sql_table *users = find_sql_table(m->session->tr, sys, "db_user_info");
462 164 : sql_column *users_schema = find_sql_column(users, "default_schema");
463 164 : sqlid schema_id = s->base.id;
464 :
465 164 : sqlstore *store = m->session->tr->store;
466 164 : rid = store->table_api.column_find_row(m->session->tr, users_schema, &schema_id, NULL);
467 164 : if (is_oid_nil(rid))
468 156 : return FALSE;
469 : return TRUE;
470 : }
471 :
472 : static int
473 74 : 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 74 : mvc *m = (mvc *) _mvc;
477 74 : Client c = MCgetClient(m->clientid);
478 74 : str err;
479 74 : int res = LOG_OK;
480 74 : oid rid = oid_nil;
481 :
482 74 : sqlstore *store = m->session->tr->store;
483 74 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
484 74 : sql_table *info = find_sql_table(m->session->tr, sys, "db_user_info");
485 74 : sql_column *users_name = find_sql_column(info, "name");
486 :
487 74 : if (schema_id || schema_path || role_id || max_memory > -1 || max_workers > -1) {
488 70 : 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 70 : 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 74 : 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 73 : if (schema_id) {
578 57 : sql_column *users_schema = find_sql_column(info, "default_schema");
579 :
580 57 : 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 73 : 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 69 : 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 69 : if (max_memory > -1) {
614 2 : sql_column *users_max_memory = find_sql_column(info, "max_memory");
615 :
616 2 : 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 69 : 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 0 : monet5_schema_user_dependencies(ptr _trans, int schema_id)
677 : {
678 0 : rids *A, *U;
679 0 : sql_trans *tr = (sql_trans *) _trans;
680 0 : sql_schema *s = find_sql_schema(tr, "sys");
681 :
682 0 : sql_table *auths = find_sql_table(tr, s, "auths");
683 0 : sql_column *auth_name = find_sql_column(auths, "name");
684 :
685 0 : sql_table *users = find_sql_table(tr, s, "db_user_info");
686 0 : sql_column *users_name = find_sql_column(users, "name");
687 0 : sql_column *users_sch = find_sql_column(users, "default_schema");
688 :
689 0 : sqlstore *store = tr->store;
690 : /* select users with given schema */
691 0 : U = store->table_api.rids_select(tr, users_sch, &schema_id, &schema_id, NULL);
692 : /* select all authorization ids */
693 0 : A = store->table_api.rids_select(tr, auth_name, NULL, NULL);
694 : /* join all authorization with the selected users */
695 0 : if (A && U)
696 0 : A = store->table_api.rids_join(tr, A, auth_name, U, users_name);
697 0 : store->table_api.rids_destroy(U);
698 0 : return A;
699 : }
700 :
701 : void
702 336 : monet5_user_init(backend_functions *be_funcs)
703 : {
704 336 : be_funcs->fcuser = &monet5_create_user;
705 336 : be_funcs->fduser = &monet5_drop_user;
706 336 : be_funcs->ffuser = &monet5_find_user;
707 336 : be_funcs->ffrole = &monet5_find_role;
708 336 : be_funcs->fcrpriv = &monet5_create_privileges;
709 336 : be_funcs->fshuser = &monet5_schema_has_user;
710 336 : be_funcs->fauser = &monet5_alter_user;
711 336 : be_funcs->fruser = &monet5_rename_user;
712 336 : be_funcs->fschuserdep = &monet5_schema_user_dependencies;
713 336 : }
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 37880 : monet5_user_set_def_schema(mvc *m, oid user, str username)
757 : {
758 37880 : oid rid;
759 37880 : sqlid schema_id, default_role_id;
760 37880 : sql_schema *sys = NULL;
761 37880 : sql_table *user_info = NULL;
762 37880 : sql_column *users_name = NULL;
763 37880 : sql_column *users_schema = NULL;
764 37880 : sql_column *users_schema_path = NULL;
765 37880 : sql_column *users_default_role = NULL;
766 37880 : sql_table *schemas = NULL;
767 37880 : sql_column *schemas_name = NULL;
768 37880 : sql_column *schemas_id = NULL;
769 37880 : sql_table *auths = NULL;
770 37880 : sql_column *auths_id = NULL;
771 37880 : sql_column *auths_name = NULL;
772 37880 : str path_err = NULL, other = NULL, schema = NULL, schema_cpy, schema_path = NULL, userrole = NULL;
773 37880 : int ok = 1, res = 0;
774 :
775 37880 : TRC_DEBUG(SQL_TRANS, OIDFMT "\n", user);
776 :
777 37880 : sys = find_sql_schema(m->session->tr, "sys");
778 37880 : user_info = find_sql_table(m->session->tr, sys, "db_user_info");
779 37880 : users_name = find_sql_column(user_info, "name");
780 37880 : users_schema = find_sql_column(user_info, "default_schema");
781 37879 : users_schema_path = find_sql_column(user_info, "schema_path");
782 37878 : users_default_role = find_sql_column(user_info, "default_role");
783 :
784 37879 : sqlstore *store = m->session->tr->store;
785 37879 : rid = store->table_api.column_find_row(m->session->tr, users_name, username, NULL);
786 37880 : 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 37880 : schema_id = store->table_api.column_find_sqlid(m->session->tr, users_schema, rid);
792 37880 : 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 37880 : default_role_id = store->table_api.column_find_sqlid(m->session->tr, users_default_role, rid);
799 :
800 37880 : schemas = find_sql_table(m->session->tr, sys, "schemas");
801 37878 : schemas_name = find_sql_column(schemas, "name");
802 37879 : schemas_id = find_sql_column(schemas, "id");
803 37879 : auths = find_sql_table(m->session->tr, sys, "auths");
804 37879 : auths_id = find_sql_column(auths, "id");
805 37880 : auths_name = find_sql_column(auths, "name");
806 :
807 37880 : rid = store->table_api.column_find_row(m->session->tr, schemas_id, &schema_id, NULL);
808 37880 : 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 37880 : 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 37880 : schema_cpy = schema;
821 37880 : schema = sa_strdup(m->session->sa, schema);
822 37880 : _DELETE(schema_cpy);
823 :
824 : /* check if username exists */
825 37880 : rid = store->table_api.column_find_row(m->session->tr, auths_name, username, NULL);
826 37880 : 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 37880 : m->user_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
834 :
835 : /* check if role exists */
836 37880 : rid = store->table_api.column_find_row(m->session->tr, auths_id, &default_role_id, NULL);
837 37880 : 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 37880 : m->role_id = default_role_id;
844 37880 : 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 37880 : if (!(ok = mvc_set_schema(m, schema)) || (path_err = parse_schema_path_str(m, schema_path, true)) != MAL_SUCCEED) {
853 0 : if (m->session->tr->active && (other = mvc_rollback(m, 0, NULL, false)) != MAL_SUCCEED)
854 0 : freeException(other);
855 0 : _DELETE(schema_path);
856 0 : _DELETE(userrole);
857 0 : freeException(path_err);
858 0 : return ok == 0 ? -3 : -1;
859 : }
860 :
861 :
862 : /* reset the user and schema names */
863 75760 : if (!sqlvar_set_string(find_global_var(m, sys, "current_schema"), schema) ||
864 75760 : !sqlvar_set_string(find_global_var(m, sys, "current_user"), username) ||
865 37880 : !sqlvar_set_string(find_global_var(m, sys, "current_role"), userrole)) {
866 : res = -1;
867 : }
868 37880 : _DELETE(schema_path);
869 37880 : _DELETE(userrole);
870 37880 : return res;
871 : }
872 :
873 : int
874 37880 : monet5_user_get_limits(mvc *m, int user, lng *maxmem, int *maxwrk)
875 : {
876 37880 : oid rid;
877 37880 : sql_schema *sys = NULL;
878 37880 : sql_table *user_info = NULL;
879 37880 : sql_table *auths = NULL;
880 37880 : str username = NULL;
881 37880 : sqlstore *store = m->session->tr->store;
882 37880 : lng max_memory = 0;
883 37880 : int max_workers = 0;
884 :
885 37880 : assert(m->session->tr->active);
886 :
887 37880 : sys = find_sql_schema(m->session->tr, "sys");
888 37880 : auths = find_sql_table(m->session->tr, sys, "auths");
889 37880 : user_info = find_sql_table(m->session->tr, sys, "db_user_info");
890 :
891 37880 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(auths, "id"), &user, NULL);
892 37880 : if (is_oid_nil(rid))
893 : return -2;
894 37880 : if (!(username = store->table_api.column_find_value(m->session->tr, find_sql_column(auths, "name"), rid)))
895 : return -1;
896 37880 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(user_info, "name"), username, NULL);
897 37880 : _DELETE(username);
898 :
899 37880 : if (!is_oid_nil(rid)) {
900 37880 : max_memory = store->table_api.column_find_lng(m->session->tr, find_sql_column(user_info, "max_memory"), rid);
901 37880 : max_workers = store->table_api.column_find_int(m->session->tr, find_sql_column(user_info, "max_workers"), rid);
902 : }
903 :
904 37880 : *maxmem = max_memory > 0 ? max_memory : 0;
905 37880 : *maxwrk = max_workers > 0 ? max_workers : 0;
906 37880 : return 0;
907 : }
908 :
909 : /* move into mvc_remote_create */
910 : /* and mvc_remote_drop */
911 : str
912 94 : remote_create(mvc *m, sqlid id, const str username, const str password, int pw_encrypted)
913 : {
914 94 : int log_res = 0;
915 94 : sql_trans *tr = m->session->tr;
916 94 : sqlstore *store = tr->store;
917 94 : sql_schema *sys = find_sql_schema(tr, "sys");
918 94 : sql_table *remote_user_info = find_sql_table(tr, sys, REMOTE_USER_INFO);
919 :
920 94 : char *pwhash = password, *cypher = NULL;
921 94 : if (!pw_encrypted) {
922 10 : if((pwhash = mcrypt_BackendSum(password, strlen(password))) == NULL)
923 0 : throw(MAL, "addRemoteTableCredentials", SQLSTATE(42000) "Crypt backend hash not found");
924 : }
925 94 : if (strNil(password)) {
926 80 : oid rid = getUserOIDByName(m, username);
927 80 : str cypher = getUserPassword(m, rid);
928 80 : str err = AUTHdecypherValue(&pwhash, cypher);
929 80 : GDKfree(cypher);
930 80 : if (err) {
931 0 : GDKfree(err);
932 0 : throw(MAL, "addRemoteTableCredentials", SQLSTATE(42000) "Crypt backend hash not found");
933 : }
934 : }
935 94 : str msg = AUTHcypherValue(&cypher, pwhash);
936 94 : if (pwhash != password) {
937 90 : if (!pw_encrypted)
938 10 : free(pwhash);
939 : else
940 80 : GDKfree(pwhash);
941 : }
942 94 : if (msg != MAL_SUCCEED)
943 : return msg;
944 94 : log_res = store->table_api.table_insert(m->session->tr, remote_user_info, &id, &username, &cypher, NULL);
945 94 : GDKfree(cypher);
946 94 : if (log_res != 0)
947 0 : throw(SQL, "sql.create_table", SQLSTATE(42000) "Create table failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
948 : return MAL_SUCCEED;
949 : }
950 :
951 : str
952 185 : remote_get(mvc *m, sqlid id, str *username, str *pwhash)
953 : {
954 185 : sql_trans *tr = m->session->tr;
955 185 : sqlstore *store = tr->store;
956 185 : sql_schema *sys = find_sql_schema(tr, "sys");
957 185 : sql_table *remote_user_info = find_sql_table(tr, sys, REMOTE_USER_INFO);
958 185 : sql_column *remote_user_info_id = find_sql_column(remote_user_info, "table_id");
959 185 : oid rid = store->table_api.column_find_row(tr, remote_user_info_id, &id, NULL);
960 :
961 185 : if (is_oid_nil(rid))
962 0 : throw(MAL, "remote", SQLSTATE(42000) "remote table credentials not found");
963 185 : *username = store->table_api.column_find_value(tr, find_sql_column(remote_user_info, "username"), rid);
964 185 : if (strNil(*username)) {
965 0 : GDKfree(*username);
966 0 : *username = GDKstrdup("");
967 : }
968 185 : str cypher = store->table_api.column_find_value(tr, find_sql_column(remote_user_info, "password"), rid);
969 185 : str err = AUTHdecypherValue(pwhash, cypher);
970 185 : GDKfree(cypher);
971 185 : if (err)
972 : return err;
973 : return MAL_SUCCEED;
974 : }
|
