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 107780 : sql_insert_priv(mvc *sql, sqlid auth_id, sqlid obj_id, int privilege, sqlid grantor, int grantable)
57 : {
58 107780 : sql_schema *ss = mvc_bind_schema(sql, "sys");
59 107780 : sql_table *pt = find_sql_table(sql->session->tr, ss, "privileges");
60 107780 : sqlstore *store = sql->session->tr->store;
61 :
62 107780 : 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 1339008 : admin_privs(sqlid grantor)
81 : {
82 1339008 : if (grantor == USER_MONETDB || grantor == ROLE_SYSADMIN) {
83 25 : return true;
84 : }
85 : return false;
86 : }
87 :
88 : int
89 314043 : mvc_schema_privs(mvc *m, sql_schema *s)
90 : {
91 314043 : if (admin_privs(m->user_id) || admin_privs(m->role_id))
92 : return 1;
93 2398 : if (!s)
94 : return 0;
95 2398 : if (m->user_id == s->auth_id || m->role_id == s->auth_id)
96 2380 : return 1;
97 : return 0;
98 : }
99 :
100 : static bool
101 107774 : schema_privs(sqlid grantor, sql_schema *s)
102 : {
103 305466 : 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 8 : sql_grant_global_privs( mvc *sql, char *grantee, int privs, int grant, sqlid grantor)
114 : {
115 8 : bool allowed;
116 8 : sqlid grantee_id;
117 8 : int log_res;
118 :
119 8 : 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 8 : grantee_id = sql_find_auth(sql, grantee);
128 8 : 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 6 : 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 6 : 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 6 : 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 6 : 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 17864 : sql_grant_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
146 : {
147 17864 : sql_table *t = NULL;
148 17864 : sql_column *c = NULL;
149 17864 : bool allowed;
150 17864 : sqlid grantee_id;
151 17864 : int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE, log_res;
152 :
153 17864 : 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 17864 : if (isDeclaredTable(t))
156 0 : throw(SQL,"sql.grant_table", SQLSTATE(42000) "GRANT: cannot grant on a declared table");
157 :
158 17864 : allowed = schema_privs(grantor, t->s);
159 :
160 17864 : if (!cname) {
161 17812 : 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 52 : if (cname) {
168 52 : c = mvc_bind_column(sql, t, cname);
169 52 : 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 51 : 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 17861 : grantee_id = sql_find_auth(sql, grantee);
180 17861 : 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 17865 : 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 17860 : (privs != all && !c && sql_privilege(sql, grantee_id, t->base.id, privs) >= 0) ||
190 17858 : (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 17858 : 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 17853 : } else if (!c) {
197 17802 : 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 51 : 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 17858 : if (privs == all || !c) {
206 17807 : 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 51 : 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 17858 : 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 17858 : 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 89896 : sql_grant_func_privs( mvc *sql, char *grantee, int privs, char *sname, sqlid func_id, int grant, sqlid grantor)
222 : {
223 89896 : sql_schema *s = NULL;
224 89896 : bool allowed;
225 89896 : sqlid grantee_id;
226 89896 : int log_res;
227 :
228 89896 : assert(sname);
229 89896 : if (!(s = mvc_bind_schema(sql, sname)))
230 0 : throw(SQL,"sql.grant_func",SQLSTATE(3F000) "GRANT: no such schema '%s'", sname);
231 89896 : sql_base *b = os_find_id(s->funcs, sql->session->tr, func_id);
232 89896 : sql_func *f = (sql_func*)b;
233 89896 : assert(f);
234 89896 : allowed = schema_privs(grantor, f->s);
235 :
236 : if (!allowed)
237 0 : allowed = sql_grantable(sql, grantor, f->base.id, privs) == 1;
238 :
239 89896 : 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 89896 : grantee_id = sql_find_auth(sql, grantee);
243 89896 : 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 89896 : 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 89896 : 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 89896 : 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 89896 : 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 669 : sql_create_auth_id(mvc *m, sqlid id, str auth)
389 : {
390 669 : int grantor = 0; /* no grantor */
391 669 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
392 669 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
393 669 : sql_column *auth_name = find_sql_column(auths, "name");
394 669 : sqlstore *store = m->session->tr->store;
395 669 : int log_res = LOG_OK;
396 :
397 669 : if (!is_oid_nil(store->table_api.column_find_row(m->session->tr, auth_name, auth, NULL)))
398 : return false;
399 :
400 669 : 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 25 : sql_create_role(mvc *m, str auth, sqlid grantor)
409 : {
410 25 : sqlid id;
411 25 : sql_trans *tr = m->session->tr;
412 25 : sqlstore *store = m->session->tr->store;
413 25 : sql_schema *sys = find_sql_schema(tr, "sys");
414 25 : sql_table *auths = find_sql_table(tr, sys, "auths");
415 25 : sql_column *auth_name = find_sql_column(auths, "name");
416 25 : int log_res = LOG_OK;
417 :
418 25 : if (!admin_privs(grantor))
419 0 : throw(SQL, "sql.create_role", SQLSTATE(0P000) "Insufficient privileges to create role '%s'", auth);
420 25 : 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 24 : id = store_next_oid(tr->store);
424 24 : 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 110684 : sql_privilege_rid(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
467 : {
468 110684 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
469 110684 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
470 110684 : sql_column *priv_obj = find_sql_column(privs, "obj_id");
471 110684 : sql_column *priv_auth = find_sql_column(privs, "auth_id");
472 110684 : sql_column *priv_priv = find_sql_column(privs, "privileges");
473 110684 : sqlstore *store = m->session->tr->store;
474 :
475 110684 : 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 110677 : sql_privilege(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
480 : {
481 110677 : oid rid = sql_privilege_rid(m, auth_id, obj_id, priv);
482 110677 : int res = -1;
483 :
484 110677 : if (!is_oid_nil(rid)) {
485 : /* found priv */
486 777 : res = priv;
487 : }
488 110677 : return res;
489 : }
490 :
491 : int
492 492 : global_privs(mvc *m, int priv)
493 : {
494 498 : 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 488 : return 1;
499 : }
500 : return 0;
501 : }
502 :
503 : int
504 623017 : table_privs(mvc *m, sql_table *t, int priv)
505 : {
506 : /* temporary tables are owned by the session user */
507 623017 : if (t->persistence == SQL_DECLARED_TABLE ||
508 622875 : (!t->system && t->persistence != SQL_PERSIST) ||
509 461105 : (priv == PRIV_SELECT && (t->persistence != SQL_PERSIST || t->commit_action)))
510 : return 1;
511 587805 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
512 1676 : (t->s && (m->user_id == t->s->auth_id || m->role_id == t->s->auth_id)) ||
513 1232 : sql_privilege(m, m->user_id, t->base.id, priv) == priv ||
514 1134 : sql_privilege(m, m->role_id, t->base.id, priv) == priv ||
515 559 : sql_privilege(m, ROLE_PUBLIC, t->base.id, priv) == priv) {
516 587646 : return 1;
517 : }
518 : return 0;
519 : }
520 :
521 : int
522 34053 : 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 34053 : if (c->t->persistence == SQL_DECLARED_TABLE ||
527 34053 : (!c->t->system && c->t->persistence != SQL_PERSIST) ||
528 34053 : (priv == PRIV_SELECT && (c->t->persistence != SQL_PERSIST || c->t->commit_action)))
529 : return 1;
530 27879 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
531 566 : (c->t->s && (m->user_id == c->t->s->auth_id || m->role_id == c->t->s->auth_id)) ||
532 500 : sql_privilege(m, m->user_id, c->base.id, priv) == priv ||
533 427 : sql_privilege(m, m->role_id, c->base.id, priv) == priv ||
534 210 : sql_privilege(m, ROLE_PUBLIC, c->base.id, priv) == priv) {
535 27669 : return 1;
536 : }
537 : return 0;
538 : }
539 :
540 : int
541 1126627 : execute_priv(mvc *m, sql_func *f)
542 : {
543 1126627 : int priv = PRIV_EXECUTE;
544 :
545 1129407 : if (!f->s || admin_privs(m->user_id) || admin_privs(m->role_id))
546 : return 1;
547 2792 : if (m->user_id == f->s->auth_id || m->role_id == f->s->auth_id)
548 : return 1;
549 185 : if (sql_privilege(m, m->user_id, f->base.id, priv) == priv ||
550 156 : sql_privilege(m, m->role_id, f->base.id, priv) == priv ||
551 78 : sql_privilege(m, ROLE_PUBLIC, f->base.id, priv) == priv)
552 95 : 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 45 : sql_grant_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
576 : {
577 45 : oid rid;
578 45 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
579 45 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
580 45 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
581 45 : sql_column *auths_name = find_sql_column(auths, "name");
582 45 : sql_column *auths_id = find_sql_column(auths, "id");
583 45 : sqlid role_id, grantee_id;
584 45 : sqlstore *store = m->session->tr->store;
585 45 : int log_res = LOG_OK;
586 :
587 45 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
588 45 : 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 44 : role_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
591 44 : 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 44 : 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 40 : rid = store->table_api.column_find_row(m->session->tr, auths_name, grantee, NULL);
596 40 : 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 39 : grantee_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
599 39 : 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 39 : 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 37 : 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 36 : 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 36 : 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 36 : 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 36 : 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 107975 : sql_find_auth(mvc *m, str auth)
670 : {
671 107975 : sqlid res = -1;
672 107975 : oid rid;
673 107975 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
674 107975 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
675 107975 : sql_column *auths_name = find_sql_column(auths, "name");
676 107975 : sqlstore *store = m->session->tr->store;
677 :
678 107975 : rid = store->table_api.column_find_row(m->session->tr, auths_name, auth, NULL);
679 :
680 107975 : if (!is_oid_nil(rid)) {
681 107965 : sql_column *auths_id = find_sql_column(auths, "id");
682 107965 : sqlid p = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
683 :
684 107965 : if (p > -1)
685 107975 : res = p;
686 : }
687 107975 : return res;
688 : }
689 :
690 : int
691 164 : sql_schema_has_user(mvc *m, sql_schema *s)
692 : {
693 164 : 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 32 : mvc_set_role(mvc *m, char *role)
736 : {
737 32 : oid rid;
738 32 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
739 32 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
740 32 : sql_column *auths_name = find_sql_column(auths, "name");
741 32 : sqlid res = 0;
742 32 : sqlstore *store = m->session->tr->store;
743 :
744 32 : TRC_DEBUG(SQL_TRANS, "Set role: %s\n", role);
745 :
746 32 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
747 32 : if (!is_oid_nil(rid)) {
748 30 : sql_column *auths_id = find_sql_column(auths, "id");
749 30 : sqlid id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
750 :
751 30 : if (m->user_id == id) {
752 3 : m->role_id = id;
753 3 : res = 1;
754 : } else {
755 27 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
756 27 : sql_column *role_id = find_sql_column(roles, "role_id");
757 27 : sql_column *login_id = find_sql_column(roles, "login_id");
758 :
759 27 : rid = store->table_api.column_find_row(m->session->tr, login_id, &m->user_id, role_id, &id, NULL);
760 27 : if (!is_oid_nil(rid)) {
761 20 : m->role_id = id;
762 20 : res = 1;
763 : }
764 : }
765 : }
766 32 : return res;
767 : }
768 :
769 : int
770 38005 : mvc_set_schema(mvc *m, char *schema)
771 : {
772 38005 : int ret = 0;
773 38005 : sql_schema *s = find_sql_schema(m->session->tr, schema);
774 38005 : char* new_schema_name = sa_strdup(m->session->sa, schema);
775 :
776 38005 : if (s && new_schema_name) {
777 38004 : m->session->schema_name = new_schema_name;
778 38004 : m->type = Q_SCHEMA;
779 38004 : if (m->session->tr->active)
780 38004 : m->session->schema = s;
781 : ret = 1;
782 : }
783 38005 : return ret;
784 : }
785 :
786 : char *
787 331 : 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 331 : char *err;
790 331 : sql_schema *s = NULL;
791 331 : sqlid schema_id = 0;
792 331 : sqlid role_id = 0;
793 :
794 331 : if (role)
795 7 : 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 331 : 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 330 : 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 329 : if (schema) {
805 316 : 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 315 : schema_id = s->base.id;
808 315 : 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 13 : 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 328 : 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 328 : 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 326 : if (role_id) {
844 7 : 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 7 : 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 104 : sql_drop_granted_users(mvc *sql, sqlid user_id, char *user, list *deleted_users)
864 : {
865 104 : sql_schema *ss = mvc_bind_schema(sql, "sys");
866 104 : sql_table *privs = find_sql_table(sql->session->tr, ss, "privileges");
867 104 : sql_table *user_roles = find_sql_table(sql->session->tr, ss, "user_role");
868 104 : sql_table *auths = find_sql_table(sql->session->tr, ss, "auths");
869 104 : sql_trans *tr = sql->session->tr;
870 104 : sqlstore *store = tr->store;
871 104 : rids *A;
872 104 : oid rid;
873 104 : int log_res = LOG_OK;
874 104 : char *msg = NULL;
875 :
876 104 : if (!list_find(deleted_users, &user_id, (fcmp) &id_cmp)) {
877 104 : 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 93 : 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 93 : A = store->table_api.rids_select(tr, find_sql_column(privs, "auth_id"), &user_id, &user_id, NULL);
884 93 : if (!A)
885 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
886 : /* remove them */
887 128 : 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 93 : store->table_api.rids_destroy(A);
890 93 : 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 93 : A = store->table_api.rids_select(tr, find_sql_column(privs, "grantor"), &user_id, &user_id, NULL);
895 93 : if (!A)
896 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
897 : /* remove them */
898 93 : 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 93 : store->table_api.rids_destroy(A);
901 93 : 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 93 : rid = store->table_api.column_find_row(tr, find_sql_column(auths, "name"), user, NULL);
906 93 : if (is_oid_nil(rid))
907 6 : throw(SQL, "sql.drop_user", SQLSTATE(0P000) "DROP USER: no such user role '%s'", user);
908 87 : 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 87 : A = store->table_api.rids_select(tr, find_sql_column(user_roles, "login_id"), &user_id, &user_id, NULL);
913 87 : if (!A)
914 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
915 : /* remove them */
916 112 : 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 87 : store->table_api.rids_destroy(A);
919 87 : 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 87 : list_append(deleted_users, &user_id);
923 :
924 : /* select users created by this user_id */
925 87 : A = store->table_api.rids_select(tr, find_sql_column(auths, "grantor"), &user_id, &user_id, NULL);
926 87 : if (!A)
927 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
928 : /* remove them and continue the deletion */
929 87 : 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 87 : store->table_api.rids_destroy(A);
940 87 : 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 104 : sql_drop_user(mvc *sql, char *user)
948 : {
949 104 : sqlid user_id = sql_find_auth(sql, user);
950 104 : list *deleted = list_create(NULL);
951 104 : str msg = NULL;
952 :
953 104 : if (!deleted)
954 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
955 104 : msg = sql_drop_granted_users(sql, user_id, user, deleted);
956 104 : list_destroy(deleted);
957 :
958 : /* Flag as removed */
959 104 : 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 76 : 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 76 : sql_schema *s = NULL;
968 76 : sqlid schema_id = 0;
969 76 : sqlid role_id = 0;
970 :
971 76 : 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 150 : if (strNil(user))
977 : user = NULL;
978 : /* USER == NULL -> current_user */
979 :
980 76 : 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 74 : 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 75 : if (schema) {
986 58 : 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 57 : schema_id = s->base.id;
989 57 : 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 74 : 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 223 : sql_create_privileges(mvc *m, sql_schema *s, const char *initpasswd)
1014 : {
1015 223 : int pub, su, p, zero = 0;
1016 223 : sql_table *t = NULL, *privs = NULL;
1017 223 : sql_column *col = NULL;
1018 223 : sql_subfunc *f = NULL;
1019 223 : sql_trans *tr = m->session->tr;
1020 :
1021 : // create db_user_info tbl
1022 223 : backend_create_privileges(m, s, initpasswd);
1023 :
1024 223 : mvc_create_table(&t, m, s, "user_role", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1025 223 : mvc_create_column_(&col, m, t, "login_id", "int", 32);
1026 223 : mvc_create_column_(&col, m, t, "role_id", "int", 32);
1027 :
1028 : /* all roles and users are in the auths table */
1029 223 : mvc_create_table(&t, m, s, "auths", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1030 223 : mvc_create_column_(&col, m, t, "id", "int", 32);
1031 223 : mvc_create_column_(&col, m, t, "name", "varchar", 1024);
1032 223 : mvc_create_column_(&col, m, t, "grantor", "int", 32);
1033 :
1034 223 : mvc_create_table(&t, m, s, "privileges", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1035 223 : mvc_create_column_(&col, m, t, "obj_id", "int", 32);
1036 223 : mvc_create_column_(&col, m, t, "auth_id", "int", 32);
1037 223 : mvc_create_column_(&col, m, t, "privileges", "int", 32);
1038 223 : mvc_create_column_(&col, m, t, "grantor", "int", 32);
1039 223 : mvc_create_column_(&col, m, t, "grantable", "int", 32);
1040 :
1041 : /* add roles public and sysadmin and user monetdb */
1042 223 : sql_create_auth_id(m, ROLE_PUBLIC, "public");
1043 223 : sql_create_auth_id(m, ROLE_SYSADMIN, "sysadmin");
1044 223 : sql_create_auth_id(m, USER_MONETDB, "monetdb");
1045 :
1046 223 : pub = ROLE_PUBLIC;
1047 223 : su = USER_MONETDB;
1048 223 : p = PRIV_SELECT;
1049 223 : privs = find_sql_table(tr, s, "privileges");
1050 :
1051 223 : sqlstore *store = m->session->tr->store;
1052 223 : t = find_sql_table(tr, s, "schemas");
1053 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1054 223 : t = find_sql_table(tr, s, "types");
1055 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1056 223 : t = find_sql_table(tr, s, "functions");
1057 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1058 223 : t = find_sql_table(tr, s, "args");
1059 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1060 223 : t = find_sql_table(tr, s, "sequences");
1061 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1062 223 : t = find_sql_table(tr, s, "dependencies");
1063 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1064 223 : t = find_sql_table(tr, s, "_tables");
1065 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1066 223 : t = find_sql_table(tr, s, "_columns");
1067 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1068 223 : t = find_sql_table(tr, s, "keys");
1069 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1070 223 : t = find_sql_table(tr, s, "idxs");
1071 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1072 223 : t = find_sql_table(tr, s, "triggers");
1073 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1074 223 : t = find_sql_table(tr, s, "objects");
1075 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1076 223 : t = find_sql_table(tr, s, "tables");
1077 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1078 223 : t = find_sql_table(tr, s, "columns");
1079 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1080 223 : t = find_sql_table(tr, s, "comments");
1081 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1082 223 : t = find_sql_table(tr, s, "user_role");
1083 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1084 223 : t = find_sql_table(tr, s, "auths");
1085 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1086 223 : t = find_sql_table(tr, s, "privileges");
1087 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1088 223 : t = find_sql_table(tr, s, "table_partitions");
1089 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1090 223 : t = find_sql_table(tr, s, "range_partitions");
1091 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1092 223 : t = find_sql_table(tr, s, "value_partitions");
1093 223 : 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 223 : t = find_sql_table(tr, s, "db_user_info");
1096 223 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &su, &p, &zero, &zero);
1097 :
1098 :
1099 223 : p = PRIV_EXECUTE;
1100 223 : f = sql_bind_func_(m, s->base.name, "env", NULL, F_UNION, true);
1101 223 : store->table_api.table_insert(m->session->tr, privs, &f->func->base.id, &pub, &p, &zero, &zero);
1102 223 : f = sql_bind_func_(m, s->base.name, "var", NULL, F_UNION, true);
1103 223 : 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 223 : return 0;
1124 : }
|
