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 : * Privileges
15 : * ==========
16 : *
17 : * Sql has a simple access control schema. There are two types of authorization,
18 : * users and roles. Each user may be part of several roles.
19 : * For each authorization identity a set of privileges is administrated.
20 : * These are administrated on multiple levels where lower levels (ie.
21 : * table or column level) overwrite privileges on higher levels.
22 : *
23 : */
24 :
25 : #include "monetdb_config.h"
26 : #include "sql_privileges.h"
27 : #include "sql_storage.h"
28 : #include "sql_semantic.h"
29 : #include "sql_parser.h"
30 : #include "mal_exception.h"
31 :
32 : #define PRIV_ROLE_ADMIN 0
33 :
34 : #define GLOBAL_OBJID 0
35 :
36 : static const char *
37 0 : priv2string(int priv)
38 : {
39 0 : switch (priv) {
40 : case PRIV_SELECT:
41 : return "SELECT";
42 0 : case PRIV_UPDATE:
43 0 : return "UPDATE";
44 0 : case PRIV_INSERT:
45 0 : return "INSERT";
46 0 : case PRIV_DELETE:
47 0 : return "DELETE";
48 0 : case PRIV_TRUNCATE:
49 0 : return "TRUNCATE";
50 0 : case PRIV_EXECUTE:
51 0 : return "EXECUTE";
52 : }
53 0 : return "UNKNOWN PRIV";
54 : }
55 :
56 : static int
57 118634 : sql_insert_priv(mvc *sql, sqlid auth_id, sqlid obj_id, int privilege, sqlid grantor, int grantable)
58 : {
59 118634 : sql_schema *ss = mvc_bind_schema(sql, "sys");
60 118634 : sql_table *pt = find_sql_table(sql->session->tr, ss, "privileges");
61 118634 : sqlstore *store = sql->session->tr->store;
62 :
63 118634 : return store->table_api.table_insert(sql->session->tr, pt, &obj_id, &auth_id, &privilege, &grantor, &grantable);
64 : }
65 :
66 : static int
67 5 : sql_insert_all_privs(mvc *sql, sqlid auth_id, sqlid obj_id, int grantor, int grantable)
68 : {
69 5 : int log_res = 0;
70 :
71 5 : if ((log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_SELECT, grantor, grantable)) ||
72 5 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_UPDATE, grantor, grantable)) ||
73 5 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_INSERT, grantor, grantable)) ||
74 5 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_DELETE, grantor, grantable)) ||
75 5 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_TRUNCATE, grantor, grantable)))
76 0 : return log_res;
77 : return 0;
78 : }
79 :
80 : static bool
81 1215575 : admin_privs(sqlid grantor)
82 : {
83 1215575 : if (grantor == USER_MONETDB || grantor == ROLE_SYSADMIN) {
84 26 : return true;
85 : }
86 : return false;
87 : }
88 :
89 : int
90 332664 : mvc_schema_privs(mvc *m, sql_schema *s)
91 : {
92 332664 : if (admin_privs(m->user_id) || admin_privs(m->role_id))
93 : return 1;
94 2401 : if (!s)
95 : return 0;
96 2401 : if (m->user_id == s->auth_id || m->role_id == s->auth_id)
97 2383 : return 1;
98 : return 0;
99 : }
100 :
101 : static bool
102 118626 : schema_privs(sqlid grantor, sql_schema *s)
103 : {
104 337233 : if (admin_privs(grantor))
105 : return true;
106 8 : if (!s)
107 : return false;
108 8 : if (grantor == s->auth_id)
109 : return true;
110 : return false;
111 : }
112 :
113 : str
114 10 : sql_grant_global_privs( mvc *sql, char *grantee, int privs, int grant, sqlid grantor)
115 : {
116 10 : bool allowed;
117 10 : sqlid grantee_id;
118 10 : int log_res;
119 :
120 10 : allowed = admin_privs(grantor);
121 :
122 0 : if (!allowed)
123 0 : allowed = sql_grantable(sql, grantor, GLOBAL_OBJID, privs) == 1;
124 :
125 0 : if (!allowed)
126 0 : throw(SQL,"sql.grant_global",SQLSTATE(01007) "GRANT: Grantor '%s' is not allowed to grant global privileges", get_string_global_var(sql, "current_user"));
127 :
128 10 : grantee_id = sql_find_auth(sql, grantee);
129 10 : if (grantee_id <= 0)
130 2 : throw(SQL,"sql.grant_global",SQLSTATE(01007) "GRANT: User/role '%s' unknown", grantee);
131 : /* first check if privilege isn't already given */
132 8 : if ((sql_privilege(sql, grantee_id, GLOBAL_OBJID, privs) >= 0))
133 0 : throw(SQL,"sql.grant_global",SQLSTATE(01007) "GRANT: User/role '%s' already has this privilege", grantee);
134 8 : if ((log_res = sql_insert_priv(sql, grantee_id, GLOBAL_OBJID, privs, grantor, grant)))
135 0 : throw(SQL,"sql.grant_global",SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
136 :
137 : /* Add dependencies created */
138 8 : if ((log_res = sql_trans_add_dependency(sql->session->tr, grantee_id, ddl)) != LOG_OK)
139 0 : throw(SQL, "sql.grant_table", SQLSTATE(HY013) MAL_MALLOC_FAIL);
140 8 : if ((log_res = sql_trans_add_dependency(sql->session->tr, grantor, ddl)) != LOG_OK)
141 0 : throw(SQL, "sql.grant_table", SQLSTATE(HY013) MAL_MALLOC_FAIL);
142 : return MAL_SUCCEED;
143 : }
144 :
145 : char *
146 18653 : sql_grant_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
147 : {
148 18653 : sql_table *t = NULL;
149 18653 : sql_column *c = NULL;
150 18653 : bool allowed;
151 18653 : sqlid grantee_id;
152 18653 : int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE, log_res;
153 :
154 18653 : if (!(t = find_table_or_view_on_scope(sql, NULL, sname, tname, "GRANT", false)))
155 0 : throw(SQL,"sql.grant_table", "%s", sql->errstr);
156 18653 : if (isDeclaredTable(t))
157 0 : throw(SQL,"sql.grant_table", SQLSTATE(42000) "GRANT: cannot grant on a declared table");
158 :
159 18653 : allowed = schema_privs(grantor, t->s);
160 :
161 18653 : if (!cname) {
162 18597 : if (!allowed)
163 7 : allowed = sql_grantable(sql, grantor, t->base.id, privs) == 1;
164 :
165 7 : if (!allowed)
166 2 : throw(SQL,"sql.grant_table", SQLSTATE(01007) "GRANT: Grantor '%s' is not allowed to grant privileges for table '%s'", get_string_global_var(sql, "current_user"), tname);
167 : }
168 56 : if (cname) {
169 56 : c = mvc_bind_column(sql, t, cname);
170 56 : if (!c)
171 1 : throw(SQL,"sql.grant_table",SQLSTATE(42S22) "GRANT: Table '%s' has no column '%s'", tname, cname);
172 : /* allowed on column */
173 55 : if (!allowed)
174 0 : allowed = sql_grantable(sql, grantor, c->base.id, privs) == 1;
175 :
176 0 : if (!allowed)
177 0 : throw(SQL, "sql.grant_table", SQLSTATE(01007) "GRANT: Grantor '%s' is not allowed to grant privilege %s for table '%s'", get_string_global_var(sql, "current_user"), priv2string(privs), tname);
178 : }
179 :
180 18650 : grantee_id = sql_find_auth(sql, grantee);
181 18650 : if (grantee_id <= 0)
182 1 : throw(SQL,"sql.grant_table", SQLSTATE(01007) "GRANT: User/role '%s' unknown", grantee);
183 : /* first check if privilege isn't already given */
184 18654 : if ((privs == all &&
185 10 : (sql_privilege(sql, grantee_id, t->base.id, PRIV_SELECT) >= 0 ||
186 10 : sql_privilege(sql, grantee_id, t->base.id, PRIV_UPDATE) >= 0 ||
187 10 : sql_privilege(sql, grantee_id, t->base.id, PRIV_INSERT) >= 0 ||
188 10 : sql_privilege(sql, grantee_id, t->base.id, PRIV_DELETE) >= 0 ||
189 5 : sql_privilege(sql, grantee_id, t->base.id, PRIV_TRUNCATE) >= 0)) ||
190 18649 : (privs != all && !c && sql_privilege(sql, grantee_id, t->base.id, privs) >= 0) ||
191 18647 : (privs != all && c && sql_privilege(sql, grantee_id, c->base.id, privs) >= 0)) {
192 2 : throw(SQL, "sql.grant_table", SQLSTATE(01007) "GRANT: User/role '%s' already has this privilege", grantee);
193 : }
194 18647 : if (privs == all) {
195 5 : if ((log_res = sql_insert_all_privs(sql, grantee_id, t->base.id, grantor, grant)))
196 0 : throw(SQL, "sql.grant_table", SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
197 18642 : } else if (!c) {
198 18587 : if ((log_res = sql_insert_priv(sql, grantee_id, t->base.id, privs, grantor, grant)))
199 0 : throw(SQL, "sql.grant_table", SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
200 : } else {
201 55 : if ((log_res = sql_insert_priv(sql, grantee_id, c->base.id, privs, grantor, grant)))
202 0 : throw(SQL, "sql.grant_table", SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
203 : }
204 :
205 : /* Add dependencies created */
206 18647 : if (privs == all || !c) {
207 18592 : if (!isNew(t) && (log_res = sql_trans_add_dependency(sql->session->tr, t->base.id, ddl)) != LOG_OK)
208 0 : throw(SQL, "sql.grant_table", SQLSTATE(HY013) MAL_MALLOC_FAIL);
209 : } else {
210 55 : if (!isNew(c) && (log_res = sql_trans_add_dependency(sql->session->tr, c->base.id, ddl)) != LOG_OK)
211 0 : throw(SQL, "sql.grant_table", SQLSTATE(HY013) MAL_MALLOC_FAIL);
212 : }
213 18647 : if ((log_res = sql_trans_add_dependency(sql->session->tr, grantee_id, ddl)) != LOG_OK)
214 0 : throw(SQL, "sql.grant_table", SQLSTATE(HY013) MAL_MALLOC_FAIL);
215 18647 : if ((log_res = sql_trans_add_dependency(sql->session->tr, grantor, ddl)) != LOG_OK)
216 0 : throw(SQL, "sql.grant_table", SQLSTATE(HY013) MAL_MALLOC_FAIL);
217 :
218 : return NULL;
219 : }
220 :
221 : char *
222 99959 : sql_grant_func_privs( mvc *sql, char *grantee, int privs, char *sname, sqlid func_id, int grant, sqlid grantor)
223 : {
224 99959 : sql_schema *s = NULL;
225 99959 : bool allowed;
226 99959 : sqlid grantee_id;
227 99959 : int log_res;
228 :
229 99959 : assert(sname);
230 99959 : if (!(s = mvc_bind_schema(sql, sname)))
231 0 : throw(SQL,"sql.grant_func",SQLSTATE(3F000) "GRANT: no such schema '%s'", sname);
232 99959 : sql_base *b = os_find_id(s->funcs, sql->session->tr, func_id);
233 99959 : sql_func *f = (sql_func*)b;
234 99959 : assert(f);
235 99959 : allowed = schema_privs(grantor, f->s);
236 :
237 : if (!allowed)
238 0 : allowed = sql_grantable(sql, grantor, f->base.id, privs) == 1;
239 :
240 99959 : if (!allowed)
241 0 : throw(SQL, "sql.grant_func", SQLSTATE(01007) "GRANT: Grantor '%s' is not allowed to grant privileges for function '%s'", get_string_global_var(sql, "current_user"), f->base.name);
242 :
243 99959 : grantee_id = sql_find_auth(sql, grantee);
244 99959 : if (grantee_id <= 0)
245 0 : throw(SQL, "sql.grant_func", SQLSTATE(01007) "GRANT: User/role '%s' unknown", grantee);
246 : /* first check if privilege isn't already given */
247 99959 : if (sql_privilege(sql, grantee_id, f->base.id, privs) >= 0)
248 0 : throw(SQL,"sql.grant_func", SQLSTATE(01007) "GRANT: User/role '%s' already has this privilege", grantee);
249 99959 : if ((log_res = sql_insert_priv(sql, grantee_id, f->base.id, privs, grantor, grant)))
250 0 : throw(SQL,"sql.grant_func", SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
251 :
252 : /* Add dependencies created */
253 99959 : if (!isNew(f) && (log_res = sql_trans_add_dependency(sql->session->tr, func_id, ddl)) != LOG_OK)
254 0 : throw(SQL, "sql.grant_func", SQLSTATE(HY013) MAL_MALLOC_FAIL);
255 99959 : if ((log_res = sql_trans_add_dependency(sql->session->tr, grantor, ddl)) != LOG_OK)
256 0 : throw(SQL, "sql.grant_func", SQLSTATE(HY013) MAL_MALLOC_FAIL);
257 : return NULL;
258 : }
259 :
260 : static char *
261 16 : sql_delete_priv(mvc *sql, sqlid auth_id, sqlid obj_id, int privilege, sqlid grantor, int grantable, const char *op, const char *call)
262 : {
263 16 : sql_schema *ss = mvc_bind_schema(sql, "sys");
264 16 : sql_table *privs = find_sql_table(sql->session->tr, ss, "privileges");
265 16 : sql_column *priv_obj = find_sql_column(privs, "obj_id");
266 16 : sql_column *priv_auth = find_sql_column(privs, "auth_id");
267 16 : sql_column *priv_priv = find_sql_column(privs, "privileges");
268 16 : sql_trans *tr = sql->session->tr;
269 16 : sqlstore *store = tr->store;
270 16 : rids *A;
271 16 : oid rid = oid_nil;
272 16 : int log_res = LOG_OK;
273 :
274 16 : (void) grantor;
275 16 : (void) grantable;
276 :
277 : /* select privileges of this auth_id, privilege, obj_id */
278 16 : A = store->table_api.rids_select(tr, priv_auth, &auth_id, &auth_id, priv_priv, &privilege, &privilege, priv_obj, &obj_id, &obj_id, NULL );
279 16 : if (!A)
280 0 : throw(SQL, "sql.delete_prive", SQLSTATE(HY013) MAL_MALLOC_FAIL);
281 : /* remove them */
282 32 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
283 16 : log_res = store->table_api.table_delete(tr, privs, rid);
284 16 : store->table_api.rids_destroy(A);
285 16 : if (log_res != LOG_OK)
286 0 : throw(SQL, op, SQLSTATE(42000) "%s: failed%s", call, log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
287 : return NULL;
288 : }
289 :
290 : char *
291 2 : sql_revoke_global_privs( mvc *sql, char *grantee, int privs, int grant, sqlid grantor)
292 : {
293 2 : bool allowed;
294 2 : sqlid grantee_id;
295 :
296 2 : allowed = admin_privs(grantor);
297 :
298 0 : if (!allowed)
299 0 : allowed = sql_grantable(sql, grantor, GLOBAL_OBJID, privs) == 1;
300 :
301 0 : if (!allowed)
302 0 : throw(SQL, "sql.revoke_global", SQLSTATE(01006) "REVOKE: Grantor '%s' is not allowed to revoke global privileges", get_string_global_var(sql, "current_user"));
303 :
304 2 : grantee_id = sql_find_auth(sql, grantee);
305 2 : if (grantee_id <= 0)
306 0 : throw(SQL, "sql.revoke_global", SQLSTATE(01006) "REVOKE: User/role '%s' unknown", grantee);
307 2 : return sql_delete_priv(sql, grantee_id, GLOBAL_OBJID, privs, grantor, grant, "sql.revoke_global", "REVOKE");
308 : }
309 :
310 : char *
311 13 : sql_revoke_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
312 : {
313 13 : sql_table *t = NULL;
314 13 : sql_column *c = NULL;
315 13 : bool allowed;
316 13 : sqlid grantee_id;
317 13 : int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE;
318 13 : char *msg = NULL;
319 :
320 13 : if (!(t = find_table_or_view_on_scope(sql, NULL, sname, tname, "REVOKE", false)))
321 0 : throw(SQL,"sql.revoke_table","%s", sql->errstr);
322 13 : if (isDeclaredTable(t))
323 0 : throw(SQL,"sql.revoke_table", SQLSTATE(42000) "REVOKE: cannot revoke on a declared table");
324 :
325 13 : allowed = schema_privs(grantor, t->s);
326 : if (!allowed)
327 0 : allowed = sql_grantable(sql, grantor, t->base.id, privs) == 1;
328 :
329 13 : if (!allowed)
330 0 : throw(SQL, "sql.revoke_table", SQLSTATE(01006) "REVOKE: Grantor '%s' is not allowed to revoke privileges for table '%s'", get_string_global_var(sql, "current_user"), tname);
331 :
332 13 : if (cname) {
333 2 : c = mvc_bind_column(sql, t, cname);
334 2 : if (!c)
335 0 : throw(SQL,"sql.revoke_table", SQLSTATE(42S22) "REVOKE: table '%s' has no column '%s'", tname, cname);
336 : /* allowed on column */
337 : if (!allowed)
338 : allowed = sql_grantable(sql, grantor, c->base.id, privs) == 1;
339 :
340 : if (!allowed)
341 : throw(SQL, "sql.revoke_table", SQLSTATE(01006) "REVOKE: Grantor '%s' is not allowed to revoke privilege %s for table '%s'", get_string_global_var(sql, "current_user"), priv2string(privs), tname);
342 : }
343 :
344 13 : grantee_id = sql_find_auth(sql, grantee);
345 13 : if (grantee_id <= 0)
346 0 : throw(SQL,"sql.revoke_table", SQLSTATE(01006) "REVOKE: User/role '%s' unknown", grantee);
347 13 : if (privs == all) {
348 0 : if ((msg = sql_delete_priv(sql, grantee_id, t->base.id, PRIV_SELECT, grantor, grant, "sql.revoke_table", "REVOKE")) ||
349 0 : (msg = sql_delete_priv(sql, grantee_id, t->base.id, PRIV_UPDATE, grantor, grant, "sql.revoke_table", "REVOKE")) ||
350 0 : (msg = sql_delete_priv(sql, grantee_id, t->base.id, PRIV_INSERT, grantor, grant, "sql.revoke_table", "REVOKE")) ||
351 0 : (msg = sql_delete_priv(sql, grantee_id, t->base.id, PRIV_DELETE, grantor, grant, "sql.revoke_table", "REVOKE")) ||
352 0 : (msg = sql_delete_priv(sql, grantee_id, t->base.id, PRIV_TRUNCATE, grantor, grant, "sql.revoke_table", "REVOKE")))
353 0 : return msg;
354 13 : } else if (!c) {
355 11 : msg = sql_delete_priv(sql, grantee_id, t->base.id, privs, grantor, grant, "sql.revoke_table", "REVOKE");
356 : } else {
357 2 : msg = sql_delete_priv(sql, grantee_id, c->base.id, privs, grantor, grant, "sql.revoke_table", "REVOKE");
358 : }
359 : return msg;
360 : }
361 :
362 : char *
363 1 : sql_revoke_func_privs( mvc *sql, char *grantee, int privs, char *sname, sqlid func_id, int grant, sqlid grantor)
364 : {
365 1 : sql_schema *s = NULL;
366 1 : bool allowed;
367 1 : sqlid grantee_id;
368 :
369 1 : assert(sname);
370 1 : if (!(s = mvc_bind_schema(sql, sname)))
371 0 : throw(SQL,"sql.revoke_func", SQLSTATE(3F000) "REVOKE: no such schema '%s'", sname);
372 1 : sql_base *b = os_find_id(s->funcs, sql->session->tr, func_id);
373 1 : sql_func *f = (sql_func*)b;
374 1 : assert(f);
375 1 : allowed = schema_privs(grantor, f->s);
376 : if (!allowed)
377 0 : allowed = sql_grantable(sql, grantor, f->base.id, privs) == 1;
378 :
379 1 : if (!allowed)
380 0 : throw(SQL, "sql.revoke_func", SQLSTATE(01006) "REVOKE: Grantor '%s' is not allowed to revoke privileges for function '%s'", get_string_global_var(sql, "current_user"), f->base.name);
381 :
382 1 : grantee_id = sql_find_auth(sql, grantee);
383 1 : if (grantee_id <= 0)
384 0 : throw(SQL, "sql.revoke_func", SQLSTATE(01006) "REVOKE: User/role '%s' unknown", grantee);
385 1 : return sql_delete_priv(sql, grantee_id, f->base.id, privs, grantor, grant, "sql.revoke_func", "REVOKE");
386 : }
387 :
388 : static bool
389 678 : sql_create_auth_id(mvc *m, sqlid id, str auth)
390 : {
391 678 : int grantor = 0; /* no grantor */
392 678 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
393 678 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
394 678 : sql_column *auth_name = find_sql_column(auths, "name");
395 678 : sqlstore *store = m->session->tr->store;
396 678 : int log_res = LOG_OK;
397 :
398 678 : if (!is_oid_nil(store->table_api.column_find_row(m->session->tr, auth_name, auth, NULL)))
399 : return false;
400 :
401 678 : if ((log_res = store->table_api.table_insert(m->session->tr, auths, &id, &auth, &grantor)) != LOG_OK) {
402 0 : (void)createException(SQL, "sql.create_auth_id", SQLSTATE(42000) "CREATE AUTH: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
403 0 : return false;
404 : }
405 : return true;
406 : }
407 :
408 : str
409 26 : sql_create_role(mvc *m, str auth, sqlid grantor)
410 : {
411 26 : sqlid id;
412 26 : sql_trans *tr = m->session->tr;
413 26 : sqlstore *store = m->session->tr->store;
414 26 : sql_schema *sys = find_sql_schema(tr, "sys");
415 26 : sql_table *auths = find_sql_table(tr, sys, "auths");
416 26 : sql_column *auth_name = find_sql_column(auths, "name");
417 26 : int log_res = LOG_OK;
418 :
419 26 : if (!admin_privs(grantor))
420 0 : throw(SQL, "sql.create_role", SQLSTATE(0P000) "Insufficient privileges to create role '%s'", auth);
421 26 : if (!is_oid_nil(store->table_api.column_find_row(tr, auth_name, auth, NULL)))
422 1 : throw(SQL, "sql.create_role", SQLSTATE(0P000) "Role '%s' already exists", auth);
423 :
424 25 : id = store_next_oid(tr->store);
425 25 : if ((log_res = store->table_api.table_insert(tr, auths, &id, &auth, &grantor)) != LOG_OK)
426 1 : throw(SQL, "sql.create_role", SQLSTATE(42000) "CREATE ROLE: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
427 : return NULL;
428 : }
429 :
430 : str
431 19 : sql_drop_role(mvc *m, str auth)
432 : {
433 19 : sqlid role_id = sql_find_auth(m, auth);
434 19 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
435 19 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
436 19 : sql_table *user_roles = find_sql_table(m->session->tr, sys, "user_role");
437 19 : sql_trans *tr = m->session->tr;
438 19 : sqlstore *store = m->session->tr->store;
439 19 : rids *A;
440 19 : oid rid;
441 19 : int log_res = LOG_OK;
442 :
443 19 : rid = store->table_api.column_find_row(tr, find_sql_column(auths, "name"), auth, NULL);
444 19 : if (is_oid_nil(rid))
445 1 : throw(SQL, "sql.drop_role", SQLSTATE(0P000) "DROP ROLE: no such role '%s'", auth);
446 18 : if ((log_res = store->table_api.table_delete(m->session->tr, auths, rid)) != LOG_OK)
447 0 : throw(SQL, "sql.drop_role", SQLSTATE(42000) "DROP ROLE: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
448 :
449 : /* select user roles of this role_id */
450 18 : A = store->table_api.rids_select(tr, find_sql_column(user_roles, "role_id"), &role_id, &role_id, NULL);
451 18 : if (!A)
452 0 : throw(SQL, "sql.drop_role", SQLSTATE(HY013) MAL_MALLOC_FAIL);
453 : /* remove them */
454 19 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
455 1 : log_res = store->table_api.table_delete(tr, user_roles, rid);
456 18 : store->table_api.rids_destroy(A);
457 18 : if (log_res != LOG_OK)
458 0 : throw(SQL, "sql.drop_role", SQLSTATE(42000) "DROP ROLE: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
459 :
460 : /* Flag as removed */
461 18 : if ((log_res = sql_trans_add_dependency_change(tr, role_id, ddl)) != LOG_OK)
462 0 : throw(SQL, "sql.drop_role", SQLSTATE(HY013) MAL_MALLOC_FAIL);
463 : return NULL;
464 : }
465 :
466 : static oid
467 123515 : sql_privilege_rid(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
468 : {
469 123515 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
470 123515 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
471 123515 : sql_column *priv_obj = find_sql_column(privs, "obj_id");
472 123515 : sql_column *priv_auth = find_sql_column(privs, "auth_id");
473 123515 : sql_column *priv_priv = find_sql_column(privs, "privileges");
474 123515 : sqlstore *store = m->session->tr->store;
475 :
476 123515 : return store->table_api.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &auth_id, priv_priv, &priv, NULL);
477 : }
478 :
479 : int
480 123508 : sql_privilege(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
481 : {
482 123508 : oid rid = sql_privilege_rid(m, auth_id, obj_id, priv);
483 123508 : int res = -1;
484 :
485 123508 : if (!is_oid_nil(rid)) {
486 : /* found priv */
487 1070 : res = priv;
488 : }
489 123508 : return res;
490 : }
491 :
492 : int
493 294 : global_privs(mvc *m, int priv)
494 : {
495 300 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
496 10 : sql_privilege(m, m->user_id, GLOBAL_OBJID, priv) == priv ||
497 8 : sql_privilege(m, m->role_id, GLOBAL_OBJID, priv) == priv ||
498 4 : sql_privilege(m, ROLE_PUBLIC, GLOBAL_OBJID, priv) == priv) {
499 290 : return 1;
500 : }
501 : return 0;
502 : }
503 :
504 : int
505 659723 : table_privs(mvc *m, sql_table *t, int priv)
506 : {
507 : /* temporary tables are owned by the session user */
508 659723 : if (t->persistence == SQL_DECLARED_TABLE ||
509 659563 : (!t->system && t->persistence != SQL_PERSIST) ||
510 490948 : (priv == PRIV_SELECT && (t->persistence != SQL_PERSIST || t->commit_action)))
511 : return 1;
512 619865 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
513 2470 : (t->s && (m->user_id == t->s->auth_id || m->role_id == t->s->auth_id)) ||
514 2020 : sql_privilege(m, m->user_id, t->base.id, priv) == priv ||
515 1922 : sql_privilege(m, m->role_id, t->base.id, priv) == priv ||
516 953 : sql_privilege(m, ROLE_PUBLIC, t->base.id, priv) == priv) {
517 619666 : return 1;
518 : }
519 : return 0;
520 : }
521 :
522 : int
523 546 : column_privs(mvc *m, sql_column *c, int priv)
524 : {
525 : /* only SELECT and UPDATE privileges for columns are available */
526 : /* temporary tables are owned by the session user, so does it's columns */
527 546 : if (c->t->persistence == SQL_DECLARED_TABLE ||
528 546 : (!c->t->system && c->t->persistence != SQL_PERSIST) ||
529 546 : (priv == PRIV_SELECT && (c->t->persistence != SQL_PERSIST || c->t->commit_action)))
530 : return 1;
531 546 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
532 1092 : (c->t->s && (m->user_id == c->t->s->auth_id || m->role_id == c->t->s->auth_id)) ||
533 1026 : sql_privilege(m, m->user_id, c->base.id, priv) == priv ||
534 953 : sql_privilege(m, m->role_id, c->base.id, priv) == priv ||
535 473 : sql_privilege(m, ROLE_PUBLIC, c->base.id, priv) == priv) {
536 73 : return 1;
537 : }
538 : return 0;
539 : }
540 :
541 : int
542 922084 : execute_priv(mvc *m, sql_func *f)
543 : {
544 922084 : int priv = PRIV_EXECUTE;
545 :
546 922376 : if (!f->s || admin_privs(m->user_id) || admin_privs(m->role_id))
547 : return 1;
548 301 : if (m->user_id == f->s->auth_id || m->role_id == f->s->auth_id)
549 : return 1;
550 255 : if (sql_privilege(m, m->user_id, f->base.id, priv) == priv ||
551 226 : sql_privilege(m, m->role_id, f->base.id, priv) == priv ||
552 113 : sql_privilege(m, ROLE_PUBLIC, f->base.id, priv) == priv)
553 133 : return 1;
554 : return 0;
555 : }
556 :
557 : static bool
558 7 : role_granting_privs(mvc *m, oid role_rid, sqlid role_id, sqlid grantor_id)
559 : {
560 7 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
561 7 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
562 7 : sql_column *auths_grantor = find_sql_column(auths, "grantor");
563 7 : sqlid owner_id;
564 7 : sqlstore *store = m->session->tr->store;
565 :
566 7 : owner_id = store->table_api.column_find_sqlid(m->session->tr, auths_grantor, role_rid);
567 7 : if (owner_id == grantor_id)
568 : return true;
569 7 : if (sql_privilege(m, grantor_id, role_id, PRIV_ROLE_ADMIN) == PRIV_ROLE_ADMIN)
570 : return true;
571 : /* check for grant rights in the privs table */
572 : return false;
573 : }
574 :
575 : char *
576 49 : sql_grant_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
577 : {
578 49 : oid rid;
579 49 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
580 49 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
581 49 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
582 49 : sql_column *auths_name = find_sql_column(auths, "name");
583 49 : sql_column *auths_id = find_sql_column(auths, "id");
584 49 : sqlid role_id, grantee_id;
585 49 : sqlstore *store = m->session->tr->store;
586 49 : int log_res = LOG_OK;
587 :
588 49 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
589 49 : if (is_oid_nil(rid))
590 1 : throw(SQL, "sql.grant_role", SQLSTATE(M1M05) "GRANT: no such role '%s' or grantee '%s'", role, grantee);
591 48 : role_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
592 48 : if (!is_oid_nil(backend_find_user(m, role)))
593 0 : throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: '%s' is a USER not a ROLE", role);
594 48 : if (!admin_privs(grantor) && !role_granting_privs(m, rid, role_id, grantor))
595 4 : throw(SQL,"sql.grant_role", SQLSTATE(0P000) "GRANT: Insufficient privileges to grant ROLE '%s'", role);
596 44 : rid = store->table_api.column_find_row(m->session->tr, auths_name, grantee, NULL);
597 44 : if (is_oid_nil(rid))
598 1 : throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: no such role '%s' or grantee '%s'", role, grantee);
599 43 : grantee_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
600 43 : rid = store->table_api.column_find_row(m->session->tr, find_sql_column(roles, "login_id"), &grantee_id, find_sql_column(roles, "role_id"), &role_id, NULL);
601 43 : if (!is_oid_nil(rid))
602 2 : throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: User '%s' already has ROLE '%s'", grantee, role);
603 :
604 41 : if ((log_res = store->table_api.table_insert(m->session->tr, roles, &grantee_id, &role_id)) != LOG_OK)
605 1 : throw(SQL, "sql.grant_role", SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
606 40 : if (admin) {
607 2 : int priv = PRIV_ROLE_ADMIN, one = 1;
608 2 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
609 :
610 2 : if ((log_res = store->table_api.table_insert(m->session->tr, privs, &role_id, &grantee_id, &priv, &grantor, &one)) != LOG_OK)
611 0 : throw(SQL, "sql.grant_role", SQLSTATE(42000) "GRANT: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
612 : }
613 :
614 : /* Add dependencies created */
615 40 : if ((log_res = sql_trans_add_dependency(m->session->tr, grantee_id, ddl)) != LOG_OK)
616 0 : throw(SQL, "sql.grant_role", SQLSTATE(HY013) MAL_MALLOC_FAIL);
617 40 : if ((log_res = sql_trans_add_dependency(m->session->tr, role_id, ddl)) != LOG_OK)
618 0 : throw(SQL, "sql.grant_role", SQLSTATE(HY013) MAL_MALLOC_FAIL);
619 40 : if ((log_res = sql_trans_add_dependency(m->session->tr, grantor, ddl)) != LOG_OK)
620 0 : throw(SQL, "sql.grant_role", SQLSTATE(HY013) MAL_MALLOC_FAIL);
621 : return NULL;
622 : }
623 :
624 : char *
625 10 : sql_revoke_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
626 : /* grantee no longer belongs the role (role) */
627 : {
628 10 : oid rid;
629 10 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
630 10 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
631 10 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
632 10 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
633 10 : sql_column *auths_name = find_sql_column(auths, "name");
634 10 : sql_column *auths_id = find_sql_column(auths, "id");
635 10 : sql_column *roles_role_id = find_sql_column(roles, "role_id");
636 10 : sql_column *roles_login_id = find_sql_column(roles, "login_id");
637 10 : sqlid role_id, grantee_id;
638 10 : sqlstore *store = m->session->tr->store;
639 10 : int log_res = LOG_OK;
640 :
641 10 : rid = store->table_api.column_find_row(m->session->tr, auths_name, grantee, NULL);
642 10 : if (is_oid_nil(rid))
643 1 : throw(SQL,"sql.revoke_role", SQLSTATE(01006) "REVOKE: no such role '%s' or grantee '%s'", role, grantee);
644 9 : grantee_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
645 9 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
646 9 : if (is_oid_nil(rid))
647 0 : throw(SQL,"sql.revoke_role", SQLSTATE(01006) "REVOKE: no such role '%s' or grantee '%s'", role, grantee);
648 9 : role_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
649 9 : if (!admin_privs(grantor) && !role_granting_privs(m, rid, role_id, grantor))
650 0 : throw(SQL,"sql.revoke_role", SQLSTATE(0P000) "REVOKE: insufficient privileges to revoke ROLE '%s'", role);
651 :
652 9 : if (!admin) {
653 9 : rid = store->table_api.column_find_row(m->session->tr, roles_login_id, &grantee_id, roles_role_id, &role_id, NULL);
654 9 : if (!is_oid_nil(rid)) {
655 7 : if ((log_res = store->table_api.table_delete(m->session->tr, roles, rid)) != LOG_OK)
656 0 : throw(SQL, "sql.revoke_role", SQLSTATE(42000) "REVOKE: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
657 : } else
658 2 : throw(SQL,"sql.revoke_role", SQLSTATE(01006) "REVOKE: User '%s' does not have ROLE '%s'", grantee, role);
659 : }
660 7 : rid = sql_privilege_rid(m, grantee_id, role_id, PRIV_ROLE_ADMIN);
661 7 : if (!is_oid_nil(rid)) {
662 1 : if ((log_res = store->table_api.table_delete(m->session->tr, privs, rid)) != LOG_OK)
663 0 : throw(SQL, "sql.revoke_role", SQLSTATE(42000) "REVOKE: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
664 6 : } else if (admin)
665 0 : throw(SQL,"sql.revoke_role", SQLSTATE(01006) "REVOKE: User '%s' does not have ROLE '%s'", grantee, role);
666 : return NULL;
667 : }
668 :
669 : sqlid
670 118838 : sql_find_auth(mvc *m, str auth)
671 : {
672 118838 : sqlid res = -1;
673 118838 : oid rid;
674 118838 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
675 118838 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
676 118838 : sql_column *auths_name = find_sql_column(auths, "name");
677 118838 : sqlstore *store = m->session->tr->store;
678 :
679 118838 : rid = store->table_api.column_find_row(m->session->tr, auths_name, auth, NULL);
680 :
681 118838 : if (!is_oid_nil(rid)) {
682 118828 : sql_column *auths_id = find_sql_column(auths, "id");
683 118828 : sqlid p = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
684 :
685 118828 : if (p > -1)
686 118838 : res = p;
687 : }
688 118838 : return res;
689 : }
690 :
691 : int
692 170 : sql_schema_has_user(mvc *m, sql_schema *s)
693 : {
694 170 : return(backend_schema_has_user(m, s));
695 : }
696 :
697 : static int
698 6 : sql_grantable_(mvc *m, sqlid grantorid, sqlid obj_id, int privs)
699 : {
700 6 : oid rid;
701 6 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
702 6 : sql_table *prvs = find_sql_table(m->session->tr, sys, "privileges");
703 6 : sql_column *priv_obj = find_sql_column(prvs, "obj_id");
704 6 : sql_column *priv_auth = find_sql_column(prvs, "auth_id");
705 6 : sql_column *priv_priv = find_sql_column(prvs, "privileges");
706 6 : sql_column *priv_allowed = find_sql_column(prvs, "grantable");
707 6 : sqlstore *store = m->session->tr->store;
708 6 : int priv;
709 :
710 24 : for (priv = 1; priv <= privs; priv <<= 1) {
711 18 : if (!(priv & privs))
712 8 : continue;
713 10 : rid = store->table_api.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &grantorid, priv_priv, &priv, NULL);
714 10 : if (!is_oid_nil(rid)) {
715 4 : int allowed = store->table_api.column_find_int(m->session->tr, priv_allowed, rid);
716 :
717 : /* switch of priv bit */
718 4 : if (allowed)
719 4 : privs = (privs & ~priv);
720 : }
721 : }
722 6 : if (privs != 0)
723 2 : return 0;
724 : return 1;
725 : }
726 :
727 : int
728 7 : sql_grantable(mvc *m, sqlid grantorid, sqlid obj_id, int privs)
729 : {
730 7 : if (admin_privs(m->user_id) || admin_privs(m->role_id))
731 : return 1;
732 6 : return sql_grantable_(m, grantorid, obj_id, privs);
733 : }
734 :
735 : sqlid
736 34 : mvc_set_role(mvc *m, char *role)
737 : {
738 34 : oid rid;
739 34 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
740 34 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
741 34 : sql_column *auths_name = find_sql_column(auths, "name");
742 34 : sqlid res = 0;
743 34 : sqlstore *store = m->session->tr->store;
744 :
745 34 : TRC_DEBUG(SQL_TRANS, "Set role: %s\n", role);
746 :
747 34 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
748 34 : if (!is_oid_nil(rid)) {
749 32 : sql_column *auths_id = find_sql_column(auths, "id");
750 32 : sqlid id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
751 :
752 32 : if (m->user_id == id) {
753 3 : m->role_id = id;
754 3 : res = 1;
755 : } else {
756 29 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
757 29 : sql_column *role_id = find_sql_column(roles, "role_id");
758 29 : sql_column *login_id = find_sql_column(roles, "login_id");
759 :
760 29 : rid = store->table_api.column_find_row(m->session->tr, login_id, &m->user_id, role_id, &id, NULL);
761 29 : if (!is_oid_nil(rid)) {
762 22 : m->role_id = id;
763 22 : res = 1;
764 : }
765 : }
766 : }
767 34 : return res;
768 : }
769 :
770 : int
771 37320 : mvc_set_schema(mvc *m, char *schema)
772 : {
773 37320 : int ret = 0;
774 37320 : sql_schema *s = find_sql_schema(m->session->tr, schema);
775 37321 : char* new_schema_name = sa_strdup(m->session->sa, schema);
776 :
777 37322 : if (s && new_schema_name) {
778 37321 : m->session->schema_name = new_schema_name;
779 37321 : m->type = Q_SCHEMA;
780 37321 : if (m->session->tr->active)
781 37321 : m->session->schema = s;
782 : ret = 1;
783 : }
784 37322 : return ret;
785 : }
786 :
787 : char *
788 359 : sql_create_user(mvc *sql, char *user, char *passwd, bool enc, char *fullname, char *schema, char *schema_path, lng max_memory, int max_workers, char *optimizer, char *role)
789 : {
790 359 : char *err;
791 359 : sql_schema *s = NULL;
792 359 : sqlid schema_id = 0;
793 359 : sqlid role_id = 0;
794 :
795 359 : if (role)
796 9 : if (backend_find_role(sql, role, &role_id) < 0)
797 0 : throw(SQL,"sql.create_user", SQLSTATE(42M31) "CREATE USER: no such role '%s'", role);
798 :
799 359 : if (!admin_privs(sql->user_id) && !admin_privs(sql->role_id))
800 1 : throw(SQL,"sql.create_user", SQLSTATE(42M31) "Insufficient privileges to create user '%s'", user);
801 :
802 358 : if (!is_oid_nil(backend_find_user(sql, user)))
803 1 : throw(SQL,"sql.create_user", SQLSTATE(42M31) "CREATE USER: user '%s' already exists", user);
804 :
805 357 : if (schema) {
806 324 : if (!(s = find_sql_schema(sql->session->tr, schema)))
807 1 : throw(SQL,"sql.create_user", SQLSTATE(3F000) "CREATE USER: no such schema '%s'", schema);
808 323 : schema_id = s->base.id;
809 323 : if (!isNew(s) && sql_trans_add_dependency(sql->session->tr, schema_id, ddl) != LOG_OK)
810 0 : throw(SQL, "sql.create_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
811 :
812 : } else {
813 : // look for an existing schema matching user
814 33 : if ((s = find_sql_schema(sql->session->tr, user))) {
815 0 : schema_id = s->base.id;
816 0 : if (!isNew(s) && sql_trans_add_dependency(sql->session->tr, schema_id, ddl) != LOG_OK)
817 0 : throw(SQL, "sql.create_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
818 : }
819 : }
820 :
821 :
822 356 : if (sql_trans_add_dependency(sql->session->tr, sql->user_id, ddl) != LOG_OK)
823 0 : throw(SQL, "sql.create_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
824 :
825 356 : if ((err = backend_create_user(sql, user, passwd, enc, fullname, schema_id, schema_path, sql->user_id, max_memory,
826 : max_workers, optimizer, role_id)) != NULL)
827 : {
828 : /* strip off MAL exception decorations */
829 2 : char *r;
830 2 : char *e = err;
831 2 : if ((e = strchr(e, ':')) == NULL) {
832 : e = err;
833 2 : } else if ((e = strchr(++e, ':')) == NULL) {
834 : e = err;
835 : } else {
836 2 : e++;
837 : }
838 2 : r = createException(SQL,"sql.create_user", SQLSTATE(M0M27) "CREATE USER: %s", e);
839 2 : _DELETE(err);
840 2 : return r;
841 : }
842 :
843 : /* the default role must explicitly granted to the new user */
844 354 : if (role_id) {
845 9 : str r;
846 : /* - we don't grant the default role WITH ADMIN OPTION hence admin=0
847 : * - we should use sql->role_id instead of sql->user_id otherwise a user with high privs
848 : * (e.g. sysadmin) might not be able to GRANT the DEFAULT ROLE (see sql_grant_role() impl)
849 : */
850 9 : if ((r = sql_grant_role(sql, user, role, sql->role_id, 0)))
851 : return r;
852 : }
853 :
854 : return NULL;
855 : }
856 :
857 : static int
858 0 : id_cmp(sqlid *id1, sqlid *id2)
859 : {
860 0 : return *id1 == *id2;
861 : }
862 :
863 : static char *
864 107 : sql_drop_granted_users(mvc *sql, sqlid user_id, char *user, list *deleted_users)
865 : {
866 107 : sql_schema *ss = mvc_bind_schema(sql, "sys");
867 107 : sql_table *privs = find_sql_table(sql->session->tr, ss, "privileges");
868 107 : sql_table *user_roles = find_sql_table(sql->session->tr, ss, "user_role");
869 107 : sql_table *auths = find_sql_table(sql->session->tr, ss, "auths");
870 107 : sql_trans *tr = sql->session->tr;
871 107 : sqlstore *store = tr->store;
872 107 : rids *A;
873 107 : oid rid;
874 107 : int log_res = LOG_OK;
875 107 : char *msg = NULL;
876 :
877 107 : if (!list_find(deleted_users, &user_id, (fcmp) &id_cmp)) {
878 107 : if (mvc_check_dependency(sql, user_id, OWNER_DEPENDENCY, NULL))
879 11 : throw(SQL,"sql.drop_user",SQLSTATE(M1M05) "DROP USER: '%s' owns a schema", user);
880 96 : if (backend_drop_user(sql, user) == FALSE)
881 0 : throw(SQL,"sql.drop_user",SQLSTATE(M0M27) "%s", sql->errstr);
882 :
883 : /* select privileges of this user_id */
884 96 : A = store->table_api.rids_select(tr, find_sql_column(privs, "auth_id"), &user_id, &user_id, NULL);
885 96 : if (!A)
886 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
887 : /* remove them */
888 131 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
889 35 : log_res = store->table_api.table_delete(tr, privs, rid);
890 96 : store->table_api.rids_destroy(A);
891 96 : if (log_res != LOG_OK)
892 0 : throw(SQL, "sql.drop_user", SQLSTATE(42000) "DROP USER: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
893 :
894 : /* select privileges granted by this user_id */
895 96 : A = store->table_api.rids_select(tr, find_sql_column(privs, "grantor"), &user_id, &user_id, NULL);
896 96 : if (!A)
897 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
898 : /* remove them */
899 96 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
900 0 : log_res = store->table_api.table_delete(tr, privs, rid);
901 96 : store->table_api.rids_destroy(A);
902 96 : if (log_res != LOG_OK)
903 0 : throw(SQL, "sql.drop_user", SQLSTATE(42000) "DROP USER: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
904 :
905 : /* delete entry from auths table */
906 96 : rid = store->table_api.column_find_row(tr, find_sql_column(auths, "name"), user, NULL);
907 96 : if (is_oid_nil(rid))
908 6 : throw(SQL, "sql.drop_user", SQLSTATE(0P000) "DROP USER: no such user role '%s'", user);
909 90 : if ((log_res = store->table_api.table_delete(tr, auths, rid)) != LOG_OK)
910 0 : throw(SQL, "sql.drop_user", SQLSTATE(42000) "DROP USER: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
911 :
912 : /* select user roles of this user_id */
913 90 : A = store->table_api.rids_select(tr, find_sql_column(user_roles, "login_id"), &user_id, &user_id, NULL);
914 90 : if (!A)
915 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
916 : /* remove them */
917 115 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
918 25 : log_res = store->table_api.table_delete(tr, user_roles, rid);
919 90 : store->table_api.rids_destroy(A);
920 90 : if (log_res != LOG_OK)
921 0 : throw(SQL, "sql.drop_user", SQLSTATE(42000) "DROP USER: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
922 :
923 90 : list_append(deleted_users, &user_id);
924 :
925 : /* select users created by this user_id */
926 90 : A = store->table_api.rids_select(tr, find_sql_column(auths, "grantor"), &user_id, &user_id, NULL);
927 90 : if (!A)
928 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
929 : /* remove them and continue the deletion */
930 90 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK && msg; rid = store->table_api.rids_next(A)) {
931 : sqlid nuid = store->table_api.column_find_sqlid(tr, find_sql_column(auths, "id"), rid);
932 : char *nname = store->table_api.column_find_value(tr, find_sql_column(auths, "name"), rid);
933 :
934 : if (!nname)
935 : msg = createException(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
936 : else if (!(msg = sql_drop_granted_users(sql, nuid, nname, deleted_users)))
937 : log_res = store->table_api.table_delete(tr, auths, rid);
938 : _DELETE(nname);
939 : }
940 90 : store->table_api.rids_destroy(A);
941 90 : if (!msg && log_res != LOG_OK)
942 : throw(SQL, "sql.drop_user", SQLSTATE(42000) "DROP USER: failed%s", log_res == LOG_CONFLICT ? " due to conflict with another transaction" : "");
943 : }
944 : return msg;
945 : }
946 :
947 : char *
948 107 : sql_drop_user(mvc *sql, char *user)
949 : {
950 107 : sqlid user_id = sql_find_auth(sql, user);
951 107 : list *deleted = list_create(NULL);
952 107 : str msg = NULL;
953 :
954 107 : if (!deleted)
955 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
956 107 : msg = sql_drop_granted_users(sql, user_id, user, deleted);
957 107 : list_destroy(deleted);
958 :
959 : /* Flag as removed */
960 107 : if (!msg && sql_trans_add_dependency_change(sql->session->tr, user_id, ddl) != LOG_OK)
961 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
962 : return msg;
963 : }
964 :
965 : char *
966 83 : sql_alter_user(mvc *sql, char *user, char *passwd, bool enc, char *schema, char *schema_path, char *oldpasswd, char *role, lng max_memory, int max_workers)
967 : {
968 83 : sql_schema *s = NULL;
969 83 : sqlid schema_id = 0;
970 83 : sqlid role_id = 0;
971 :
972 83 : if (role)
973 3 : if (backend_find_role(sql, role, &role_id) < 0)
974 0 : throw(SQL,"sql.create_user", SQLSTATE(42M31) "ALTER USER: no such role '%s'", role);
975 :
976 : /* we may be called from MAL (nil) */
977 164 : if (strNil(user))
978 : user = NULL;
979 : /* USER == NULL -> current_user */
980 :
981 83 : if (!admin_privs(sql->user_id) && !admin_privs(sql->role_id) && user != NULL && strcmp(user, get_string_global_var(sql, "current_user")) != 0)
982 0 : throw(SQL,"sql.alter_user", SQLSTATE(M1M05) "Insufficient privileges to change user '%s'", user);
983 :
984 81 : if (user != NULL && is_oid_nil(backend_find_user(sql, user)))
985 1 : throw(SQL,"sql.alter_user", SQLSTATE(42M32) "ALTER USER: no such user '%s'", user);
986 82 : if (schema) {
987 64 : if (!(s = find_sql_schema(sql->session->tr, schema)))
988 1 : throw(SQL,"sql.alter_user", SQLSTATE(3F000) "ALTER USER: no such schema '%s'", schema);
989 63 : schema_id = s->base.id;
990 63 : if (!isNew(s) && sql_trans_add_dependency(sql->session->tr, s->base.id, ddl) != LOG_OK)
991 0 : throw(SQL, "sql.alter_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
992 : }
993 81 : if (backend_alter_user(sql, user, passwd, enc, schema_id, schema_path, oldpasswd, role_id, max_memory, max_workers) == FALSE)
994 5 : throw(SQL,"sql.alter_user", SQLSTATE(M0M27) "%s", sql->errstr);
995 : return NULL;
996 : }
997 :
998 : char *
999 5 : sql_rename_user(mvc *sql, char *olduser, char *newuser)
1000 : {
1001 5 : if (!admin_privs(sql->user_id) && !admin_privs(sql->role_id))
1002 1 : throw(SQL,"sql.rename_user", SQLSTATE(M1M05) "ALTER USER: insufficient privileges to rename user '%s'", olduser);
1003 :
1004 4 : if (is_oid_nil(backend_find_user(sql, olduser)))
1005 1 : throw(SQL,"sql.rename_user", SQLSTATE(42M32) "ALTER USER: no such user '%s'", olduser);
1006 3 : if (!is_oid_nil(backend_find_user(sql, newuser)))
1007 1 : throw(SQL,"sql.rename_user", SQLSTATE(42M31) "ALTER USER: user '%s' already exists", newuser);
1008 2 : if (backend_rename_user(sql, olduser, newuser) == FALSE)
1009 0 : throw(SQL,"sql.rename_user", SQLSTATE(M1M05) "%s", sql->errstr);
1010 : return NULL;
1011 : }
1012 :
1013 : int
1014 226 : sql_create_privileges(mvc *m, sql_schema *s, const char *initpasswd)
1015 : {
1016 226 : int pub, su, p, zero = 0;
1017 226 : sql_table *t = NULL, *privs = NULL;
1018 226 : sql_column *col = NULL;
1019 226 : sql_subfunc *f = NULL;
1020 226 : sql_trans *tr = m->session->tr;
1021 :
1022 : // create db_user_info tbl
1023 226 : backend_create_privileges(m, s, initpasswd);
1024 :
1025 226 : mvc_create_table(&t, m, s, "user_role", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1026 226 : mvc_create_column_(&col, m, t, "login_id", "int", 32);
1027 226 : mvc_create_column_(&col, m, t, "role_id", "int", 32);
1028 :
1029 : /* all roles and users are in the auths table */
1030 226 : mvc_create_table(&t, m, s, "auths", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1031 226 : mvc_create_column_(&col, m, t, "id", "int", 32);
1032 226 : mvc_create_column_(&col, m, t, "name", "varchar", 1024);
1033 226 : mvc_create_column_(&col, m, t, "grantor", "int", 32);
1034 :
1035 226 : mvc_create_table(&t, m, s, "privileges", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1036 226 : mvc_create_column_(&col, m, t, "obj_id", "int", 32);
1037 226 : mvc_create_column_(&col, m, t, "auth_id", "int", 32);
1038 226 : mvc_create_column_(&col, m, t, "privileges", "int", 32);
1039 226 : mvc_create_column_(&col, m, t, "grantor", "int", 32);
1040 226 : mvc_create_column_(&col, m, t, "grantable", "int", 32);
1041 :
1042 : /* add roles public and sysadmin and user monetdb */
1043 226 : sql_create_auth_id(m, ROLE_PUBLIC, "public");
1044 226 : sql_create_auth_id(m, ROLE_SYSADMIN, "sysadmin");
1045 226 : sql_create_auth_id(m, USER_MONETDB, "monetdb");
1046 :
1047 226 : pub = ROLE_PUBLIC;
1048 226 : su = USER_MONETDB;
1049 226 : p = PRIV_SELECT;
1050 226 : privs = find_sql_table(tr, s, "privileges");
1051 :
1052 226 : sqlstore *store = m->session->tr->store;
1053 226 : t = find_sql_table(tr, s, "schemas");
1054 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1055 226 : t = find_sql_table(tr, s, "types");
1056 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1057 226 : t = find_sql_table(tr, s, "functions");
1058 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1059 226 : t = find_sql_table(tr, s, "args");
1060 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1061 226 : t = find_sql_table(tr, s, "sequences");
1062 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1063 226 : t = find_sql_table(tr, s, "dependencies");
1064 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1065 226 : t = find_sql_table(tr, s, "_tables");
1066 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1067 226 : t = find_sql_table(tr, s, "_columns");
1068 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1069 226 : t = find_sql_table(tr, s, "keys");
1070 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1071 226 : t = find_sql_table(tr, s, "idxs");
1072 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1073 226 : t = find_sql_table(tr, s, "triggers");
1074 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1075 226 : t = find_sql_table(tr, s, "objects");
1076 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1077 226 : t = find_sql_table(tr, s, "tables");
1078 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1079 226 : t = find_sql_table(tr, s, "columns");
1080 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1081 226 : t = find_sql_table(tr, s, "comments");
1082 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1083 226 : t = find_sql_table(tr, s, "user_role");
1084 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1085 226 : t = find_sql_table(tr, s, "auths");
1086 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1087 226 : t = find_sql_table(tr, s, "privileges");
1088 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1089 226 : t = find_sql_table(tr, s, "table_partitions");
1090 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1091 226 : t = find_sql_table(tr, s, "range_partitions");
1092 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1093 226 : t = find_sql_table(tr, s, "value_partitions");
1094 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1095 : // restrict access to db_user_info to monetdb role
1096 226 : t = find_sql_table(tr, s, "db_user_info");
1097 226 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &su, &p, &zero, &zero);
1098 :
1099 :
1100 226 : p = PRIV_EXECUTE;
1101 226 : f = sql_bind_func_(m, s->base.name, "env", NULL, F_UNION, true, true);
1102 226 : store->table_api.table_insert(m->session->tr, privs, &f->func->base.id, &pub, &p, &zero, &zero);
1103 226 : f = sql_bind_func_(m, s->base.name, "var", NULL, F_UNION, true, true);
1104 226 : store->table_api.table_insert(m->session->tr, privs, &f->func->base.id, &pub, &p, &zero, &zero);
1105 :
1106 : /* owned by the users anyway
1107 : s = mvc_bind_schema(m, "tmp");
1108 : t = find_sql_table(tr, s, "profile");
1109 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1110 : t = find_sql_table(tr, s, "_tables");
1111 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1112 : t = find_sql_table(tr, s, "_columns");
1113 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1114 : t = find_sql_table(tr, s, "keys");
1115 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1116 : t = find_sql_table(tr, s, "idxs");
1117 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1118 : t = find_sql_table(tr, s, "triggers");
1119 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1120 : t = find_sql_table(tr, s, "objects");
1121 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1122 : */
1123 :
1124 226 : return 0;
1125 : }
|
