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