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 111008 : sql_insert_priv(mvc *sql, sqlid auth_id, sqlid obj_id, int privilege, sqlid grantor, int grantable)
57 : {
58 111008 : sql_schema *ss = mvc_bind_schema(sql, "sys");
59 111008 : sql_table *pt = find_sql_table(sql->session->tr, ss, "privileges");
60 111008 : sqlstore *store = sql->session->tr->store;
61 :
62 111008 : return store->table_api.table_insert(sql->session->tr, pt, &obj_id, &auth_id, &privilege, &grantor, &grantable);
63 : }
64 :
65 : static int
66 4 : sql_insert_all_privs(mvc *sql, sqlid auth_id, sqlid obj_id, int grantor, int grantable)
67 : {
68 4 : int log_res = 0;
69 :
70 4 : if ((log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_SELECT, grantor, grantable)) ||
71 4 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_UPDATE, grantor, grantable)) ||
72 4 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_INSERT, grantor, grantable)) ||
73 4 : (log_res = sql_insert_priv(sql, auth_id, obj_id, PRIV_DELETE, grantor, grantable)) ||
74 4 : (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 1300923 : admin_privs(sqlid grantor)
81 : {
82 1300923 : if (grantor == USER_MONETDB || grantor == ROLE_SYSADMIN) {
83 23 : return true;
84 : }
85 : return false;
86 : }
87 :
88 : int
89 319608 : mvc_schema_privs(mvc *m, sql_schema *s)
90 : {
91 319608 : if (admin_privs(m->user_id) || admin_privs(m->role_id))
92 : return 1;
93 2396 : if (!s)
94 : return 0;
95 2396 : if (m->user_id == s->auth_id || m->role_id == s->auth_id)
96 2378 : return 1;
97 : return 0;
98 : }
99 :
100 : static bool
101 110998 : schema_privs(sqlid grantor, sql_schema *s)
102 : {
103 315228 : if (admin_privs(grantor))
104 : return true;
105 6 : if (!s)
106 : return false;
107 6 : 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 17772 : sql_grant_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
146 : {
147 17772 : sql_table *t = NULL;
148 17772 : sql_column *c = NULL;
149 17772 : bool allowed;
150 17772 : sqlid grantee_id;
151 17772 : int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE, log_res;
152 :
153 17772 : 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 17772 : if (isDeclaredTable(t))
156 0 : throw(SQL,"sql.grant_table", SQLSTATE(42000) "GRANT: cannot grant on a declared table");
157 :
158 17772 : allowed = schema_privs(grantor, t->s);
159 :
160 17772 : if (!cname) {
161 17716 : if (!allowed)
162 6 : allowed = sql_grantable(sql, grantor, t->base.id, privs) == 1;
163 :
164 6 : if (!allowed)
165 1 : 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 17770 : grantee_id = sql_find_auth(sql, grantee);
180 17770 : 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 17773 : if ((privs == all &&
184 8 : (sql_privilege(sql, grantee_id, t->base.id, PRIV_SELECT) >= 0 ||
185 8 : sql_privilege(sql, grantee_id, t->base.id, PRIV_UPDATE) >= 0 ||
186 8 : sql_privilege(sql, grantee_id, t->base.id, PRIV_INSERT) >= 0 ||
187 8 : sql_privilege(sql, grantee_id, t->base.id, PRIV_DELETE) >= 0 ||
188 4 : sql_privilege(sql, grantee_id, t->base.id, PRIV_TRUNCATE) >= 0)) ||
189 17769 : (privs != all && !c && sql_privilege(sql, grantee_id, t->base.id, privs) >= 0) ||
190 17767 : (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 17767 : if (privs == all) {
194 4 : 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 17763 : } else if (!c) {
197 17708 : 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 17767 : if (privs == all || !c) {
206 17712 : 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 17767 : 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 17767 : 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 93217 : sql_grant_func_privs( mvc *sql, char *grantee, int privs, char *sname, sqlid func_id, int grant, sqlid grantor)
222 : {
223 93217 : sql_schema *s = NULL;
224 93217 : bool allowed;
225 93217 : sqlid grantee_id;
226 93217 : int log_res;
227 :
228 93217 : assert(sname);
229 93217 : if (!(s = mvc_bind_schema(sql, sname)))
230 0 : throw(SQL,"sql.grant_func",SQLSTATE(3F000) "GRANT: no such schema '%s'", sname);
231 93217 : sql_base *b = os_find_id(s->funcs, sql->session->tr, func_id);
232 93217 : sql_func *f = (sql_func*)b;
233 93217 : assert(f);
234 93217 : allowed = schema_privs(grantor, f->s);
235 :
236 : if (!allowed)
237 0 : allowed = sql_grantable(sql, grantor, f->base.id, privs) == 1;
238 :
239 93217 : 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 93217 : grantee_id = sql_find_auth(sql, grantee);
243 93217 : 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 93217 : 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 93217 : 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 93217 : 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 93217 : 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 11 : 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 11 : sql_schema *ss = mvc_bind_schema(sql, "sys");
263 11 : sql_table *privs = find_sql_table(sql->session->tr, ss, "privileges");
264 11 : sql_column *priv_obj = find_sql_column(privs, "obj_id");
265 11 : sql_column *priv_auth = find_sql_column(privs, "auth_id");
266 11 : sql_column *priv_priv = find_sql_column(privs, "privileges");
267 11 : sql_trans *tr = sql->session->tr;
268 11 : sqlstore *store = tr->store;
269 11 : rids *A;
270 11 : oid rid = oid_nil;
271 11 : int log_res = LOG_OK;
272 :
273 11 : (void) grantor;
274 11 : (void) grantable;
275 :
276 : /* select privileges of this auth_id, privilege, obj_id */
277 11 : 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 11 : if (!A)
279 0 : throw(SQL, "sql.delete_prive", SQLSTATE(HY013) MAL_MALLOC_FAIL);
280 : /* remove them */
281 22 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
282 11 : log_res = store->table_api.table_delete(tr, privs, rid);
283 11 : store->table_api.rids_destroy(A);
284 11 : 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 8 : sql_revoke_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
311 : {
312 8 : sql_table *t = NULL;
313 8 : sql_column *c = NULL;
314 8 : bool allowed;
315 8 : sqlid grantee_id;
316 8 : int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE;
317 8 : char *msg = NULL;
318 :
319 8 : 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 8 : if (isDeclaredTable(t))
322 0 : throw(SQL,"sql.revoke_table", SQLSTATE(42000) "REVOKE: cannot revoke on a declared table");
323 :
324 8 : allowed = schema_privs(grantor, t->s);
325 : if (!allowed)
326 0 : allowed = sql_grantable(sql, grantor, t->base.id, privs) == 1;
327 :
328 8 : 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 8 : 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 8 : grantee_id = sql_find_auth(sql, grantee);
344 8 : if (grantee_id <= 0)
345 0 : throw(SQL,"sql.revoke_table", SQLSTATE(01006) "REVOKE: User/role '%s' unknown", grantee);
346 8 : 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 8 : } else if (!c) {
354 6 : 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 663 : sql_create_auth_id(mvc *m, sqlid id, str auth)
389 : {
390 663 : int grantor = 0; /* no grantor */
391 663 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
392 663 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
393 663 : sql_column *auth_name = find_sql_column(auths, "name");
394 663 : sqlstore *store = m->session->tr->store;
395 663 : int log_res = LOG_OK;
396 :
397 663 : if (!is_oid_nil(store->table_api.column_find_row(m->session->tr, auth_name, auth, NULL)))
398 : return false;
399 :
400 663 : 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 23 : sql_create_role(mvc *m, str auth, sqlid grantor)
409 : {
410 23 : sqlid id;
411 23 : sql_trans *tr = m->session->tr;
412 23 : sqlstore *store = m->session->tr->store;
413 23 : sql_schema *sys = find_sql_schema(tr, "sys");
414 23 : sql_table *auths = find_sql_table(tr, sys, "auths");
415 23 : sql_column *auth_name = find_sql_column(auths, "name");
416 23 : int log_res = LOG_OK;
417 :
418 23 : if (!admin_privs(grantor))
419 0 : throw(SQL, "sql.create_role", SQLSTATE(0P000) "Insufficient privileges to create role '%s'", auth);
420 23 : 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 22 : id = store_next_oid(tr->store);
424 22 : 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 113678 : sql_privilege_rid(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
467 : {
468 113678 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
469 113678 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
470 113678 : sql_column *priv_obj = find_sql_column(privs, "obj_id");
471 113678 : sql_column *priv_auth = find_sql_column(privs, "auth_id");
472 113678 : sql_column *priv_priv = find_sql_column(privs, "privileges");
473 113678 : sqlstore *store = m->session->tr->store;
474 :
475 113678 : 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 113673 : sql_privilege(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
480 : {
481 113673 : oid rid = sql_privilege_rid(m, auth_id, obj_id, priv);
482 113673 : int res = -1;
483 :
484 113673 : if (!is_oid_nil(rid)) {
485 : /* found priv */
486 720 : res = priv;
487 : }
488 113673 : return res;
489 : }
490 :
491 : int
492 490 : global_privs(mvc *m, int priv)
493 : {
494 496 : 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 486 : return 1;
499 : }
500 : return 0;
501 : }
502 :
503 : int
504 630203 : table_privs(mvc *m, sql_table *t, int priv)
505 : {
506 : /* temporary tables are owned by the session user */
507 630203 : if (t->persistence == SQL_DECLARED_TABLE ||
508 630047 : (!t->system && t->persistence != SQL_PERSIST) ||
509 462846 : (priv == PRIV_SELECT && (t->persistence != SQL_PERSIST || t->commit_action)))
510 : return 1;
511 595133 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
512 1544 : (t->s && (m->user_id == t->s->auth_id || m->role_id == t->s->auth_id)) ||
513 1131 : sql_privilege(m, m->user_id, t->base.id, priv) == priv ||
514 1065 : sql_privilege(m, m->role_id, t->base.id, priv) == priv ||
515 525 : sql_privilege(m, ROLE_PUBLIC, t->base.id, priv) == priv) {
516 594993 : return 1;
517 : }
518 : return 0;
519 : }
520 :
521 : int
522 33779 : 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 33779 : if (c->t->persistence == SQL_DECLARED_TABLE ||
527 33779 : (!c->t->system && c->t->persistence != SQL_PERSIST) ||
528 33779 : (priv == PRIV_SELECT && (c->t->persistence != SQL_PERSIST || c->t->commit_action)))
529 : return 1;
530 27647 : if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
531 538 : (c->t->s && (m->user_id == c->t->s->auth_id || m->role_id == c->t->s->auth_id)) ||
532 472 : sql_privilege(m, m->user_id, c->base.id, priv) == priv ||
533 399 : sql_privilege(m, m->role_id, c->base.id, priv) == priv ||
534 196 : sql_privilege(m, ROLE_PUBLIC, c->base.id, priv) == priv) {
535 27451 : return 1;
536 : }
537 : return 0;
538 : }
539 :
540 : int
541 1011783 : execute_priv(mvc *m, sql_func *f)
542 : {
543 1011783 : int priv = PRIV_EXECUTE;
544 :
545 1013875 : if (!f->s || admin_privs(m->user_id) || admin_privs(m->role_id))
546 : return 1;
547 2101 : if (m->user_id == f->s->auth_id || m->role_id == f->s->auth_id)
548 : return 1;
549 149 : if (sql_privilege(m, m->user_id, f->base.id, priv) == priv ||
550 120 : sql_privilege(m, m->role_id, f->base.id, priv) == priv ||
551 60 : sql_privilege(m, ROLE_PUBLIC, f->base.id, priv) == priv)
552 80 : return 1;
553 : return 0;
554 : }
555 :
556 : static bool
557 2 : role_granting_privs(mvc *m, oid role_rid, sqlid role_id, sqlid grantor_id)
558 : {
559 2 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
560 2 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
561 2 : sql_column *auths_grantor = find_sql_column(auths, "grantor");
562 2 : sqlid owner_id;
563 2 : sqlstore *store = m->session->tr->store;
564 :
565 2 : owner_id = store->table_api.column_find_sqlid(m->session->tr, auths_grantor, role_rid);
566 2 : if (owner_id == grantor_id)
567 : return true;
568 2 : 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 36 : sql_grant_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
576 : {
577 36 : oid rid;
578 36 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
579 36 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
580 36 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
581 36 : sql_column *auths_name = find_sql_column(auths, "name");
582 36 : sql_column *auths_id = find_sql_column(auths, "id");
583 36 : sqlid role_id, grantee_id;
584 36 : sqlstore *store = m->session->tr->store;
585 36 : int log_res = LOG_OK;
586 :
587 36 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
588 36 : 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 35 : role_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
591 35 : 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 35 : if (!admin_privs(grantor) && !role_granting_privs(m, rid, role_id, grantor))
594 1 : throw(SQL,"sql.grant_role", SQLSTATE(0P000) "GRANT: Insufficient privileges to grant ROLE '%s'", role);
595 34 : rid = store->table_api.column_find_row(m->session->tr, auths_name, grantee, NULL);
596 34 : 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 33 : grantee_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
599 33 : 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 33 : if (!is_oid_nil(rid))
601 1 : throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: User '%s' already has ROLE '%s'", grantee, role);
602 :
603 32 : 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 31 : if (admin) {
606 1 : int priv = PRIV_ROLE_ADMIN, one = 1;
607 1 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
608 :
609 1 : 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 31 : 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 31 : 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 31 : 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 8 : sql_revoke_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
625 : /* grantee no longer belongs the role (role) */
626 : {
627 8 : oid rid;
628 8 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
629 8 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
630 8 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
631 8 : sql_table *privs = find_sql_table(m->session->tr, sys, "privileges");
632 8 : sql_column *auths_name = find_sql_column(auths, "name");
633 8 : sql_column *auths_id = find_sql_column(auths, "id");
634 8 : sql_column *roles_role_id = find_sql_column(roles, "role_id");
635 8 : sql_column *roles_login_id = find_sql_column(roles, "login_id");
636 8 : sqlid role_id, grantee_id;
637 8 : sqlstore *store = m->session->tr->store;
638 8 : int log_res = LOG_OK;
639 :
640 8 : rid = store->table_api.column_find_row(m->session->tr, auths_name, grantee, NULL);
641 8 : 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 7 : grantee_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
644 7 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
645 7 : 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 7 : role_id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
648 7 : 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 7 : if (!admin) {
652 7 : rid = store->table_api.column_find_row(m->session->tr, roles_login_id, &grantee_id, roles_role_id, &role_id, NULL);
653 7 : if (!is_oid_nil(rid)) {
654 5 : 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 5 : rid = sql_privilege_rid(m, grantee_id, role_id, PRIV_ROLE_ADMIN);
660 5 : if (!is_oid_nil(rid)) {
661 0 : 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 5 : } 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 111189 : sql_find_auth(mvc *m, str auth)
670 : {
671 111189 : sqlid res = -1;
672 111189 : oid rid;
673 111189 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
674 111189 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
675 111189 : sql_column *auths_name = find_sql_column(auths, "name");
676 111189 : sqlstore *store = m->session->tr->store;
677 :
678 111189 : rid = store->table_api.column_find_row(m->session->tr, auths_name, auth, NULL);
679 :
680 111189 : if (!is_oid_nil(rid)) {
681 111179 : sql_column *auths_id = find_sql_column(auths, "id");
682 111179 : sqlid p = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
683 :
684 111179 : if (p > -1)
685 111189 : res = p;
686 : }
687 111189 : return res;
688 : }
689 :
690 : int
691 152 : sql_schema_has_user(mvc *m, sql_schema *s)
692 : {
693 152 : return(backend_schema_has_user(m, s));
694 : }
695 :
696 : static int
697 5 : sql_grantable_(mvc *m, sqlid grantorid, sqlid obj_id, int privs)
698 : {
699 5 : oid rid;
700 5 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
701 5 : sql_table *prvs = find_sql_table(m->session->tr, sys, "privileges");
702 5 : sql_column *priv_obj = find_sql_column(prvs, "obj_id");
703 5 : sql_column *priv_auth = find_sql_column(prvs, "auth_id");
704 5 : sql_column *priv_priv = find_sql_column(prvs, "privileges");
705 5 : sql_column *priv_allowed = find_sql_column(prvs, "grantable");
706 5 : sqlstore *store = m->session->tr->store;
707 5 : int priv;
708 :
709 22 : for (priv = 1; priv <= privs; priv <<= 1) {
710 17 : if (!(priv & privs))
711 8 : continue;
712 9 : rid = store->table_api.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &grantorid, priv_priv, &priv, NULL);
713 9 : 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 5 : if (privs != 0)
722 1 : return 0;
723 : return 1;
724 : }
725 :
726 : int
727 6 : sql_grantable(mvc *m, sqlid grantorid, sqlid obj_id, int privs)
728 : {
729 6 : if (admin_privs(m->user_id) || admin_privs(m->role_id))
730 : return 1;
731 5 : return sql_grantable_(m, grantorid, obj_id, privs);
732 : }
733 :
734 : sqlid
735 25 : mvc_set_role(mvc *m, char *role)
736 : {
737 25 : oid rid;
738 25 : sql_schema *sys = find_sql_schema(m->session->tr, "sys");
739 25 : sql_table *auths = find_sql_table(m->session->tr, sys, "auths");
740 25 : sql_column *auths_name = find_sql_column(auths, "name");
741 25 : sqlid res = 0;
742 25 : sqlstore *store = m->session->tr->store;
743 :
744 25 : TRC_DEBUG(SQL_TRANS, "Set role: %s\n", role);
745 :
746 25 : rid = store->table_api.column_find_row(m->session->tr, auths_name, role, NULL);
747 25 : if (!is_oid_nil(rid)) {
748 23 : sql_column *auths_id = find_sql_column(auths, "id");
749 23 : sqlid id = store->table_api.column_find_sqlid(m->session->tr, auths_id, rid);
750 :
751 23 : if (m->user_id == id) {
752 2 : m->role_id = id;
753 2 : res = 1;
754 : } else {
755 21 : sql_table *roles = find_sql_table(m->session->tr, sys, "user_role");
756 21 : sql_column *role_id = find_sql_column(roles, "role_id");
757 21 : sql_column *login_id = find_sql_column(roles, "login_id");
758 :
759 21 : rid = store->table_api.column_find_row(m->session->tr, login_id, &m->user_id, role_id, &id, NULL);
760 21 : if (!is_oid_nil(rid)) {
761 15 : m->role_id = id;
762 15 : res = 1;
763 : }
764 : }
765 : }
766 25 : return res;
767 : }
768 :
769 : int
770 37767 : mvc_set_schema(mvc *m, char *schema)
771 : {
772 37767 : int ret = 0;
773 37767 : sql_schema *s = find_sql_schema(m->session->tr, schema);
774 37768 : char* new_schema_name = sa_strdup(m->session->sa, schema);
775 :
776 37769 : if (s && new_schema_name) {
777 37767 : m->session->schema_name = new_schema_name;
778 37767 : m->type = Q_SCHEMA;
779 37767 : if (m->session->tr->active)
780 37767 : m->session->schema = s;
781 : ret = 1;
782 : }
783 37769 : return ret;
784 : }
785 :
786 : char *
787 318 : 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 318 : char *err;
790 318 : sql_schema *s = NULL;
791 318 : sqlid schema_id = 0;
792 318 : sqlid role_id = 0;
793 :
794 318 : 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 318 : 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 317 : 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 316 : if (schema) {
805 302 : 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 301 : schema_id = s->base.id;
808 301 : 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 14 : 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 315 : 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 315 : 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 313 : 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 89 : sql_drop_granted_users(mvc *sql, sqlid user_id, char *user, list *deleted_users)
864 : {
865 89 : sql_schema *ss = mvc_bind_schema(sql, "sys");
866 89 : sql_table *privs = find_sql_table(sql->session->tr, ss, "privileges");
867 89 : sql_table *user_roles = find_sql_table(sql->session->tr, ss, "user_role");
868 89 : sql_table *auths = find_sql_table(sql->session->tr, ss, "auths");
869 89 : sql_trans *tr = sql->session->tr;
870 89 : sqlstore *store = tr->store;
871 89 : rids *A;
872 89 : oid rid;
873 89 : int log_res = LOG_OK;
874 89 : char *msg = NULL;
875 :
876 89 : if (!list_find(deleted_users, &user_id, (fcmp) &id_cmp)) {
877 89 : 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 78 : 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 78 : A = store->table_api.rids_select(tr, find_sql_column(privs, "auth_id"), &user_id, &user_id, NULL);
884 78 : if (!A)
885 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
886 : /* remove them */
887 113 : 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 78 : store->table_api.rids_destroy(A);
890 78 : 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 78 : A = store->table_api.rids_select(tr, find_sql_column(privs, "grantor"), &user_id, &user_id, NULL);
895 78 : if (!A)
896 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
897 : /* remove them */
898 78 : 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 78 : store->table_api.rids_destroy(A);
901 78 : 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 78 : rid = store->table_api.column_find_row(tr, find_sql_column(auths, "name"), user, NULL);
906 78 : if (is_oid_nil(rid))
907 6 : throw(SQL, "sql.drop_user", SQLSTATE(0P000) "DROP USER: no such user role '%s'", user);
908 72 : 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 72 : A = store->table_api.rids_select(tr, find_sql_column(user_roles, "login_id"), &user_id, &user_id, NULL);
913 72 : if (!A)
914 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
915 : /* remove them */
916 92 : for(rid = store->table_api.rids_next(A); !is_oid_nil(rid) && log_res == LOG_OK; rid = store->table_api.rids_next(A))
917 20 : log_res = store->table_api.table_delete(tr, user_roles, rid);
918 72 : store->table_api.rids_destroy(A);
919 72 : 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 72 : list_append(deleted_users, &user_id);
923 :
924 : /* select users created by this user_id */
925 72 : A = store->table_api.rids_select(tr, find_sql_column(auths, "grantor"), &user_id, &user_id, NULL);
926 72 : if (!A)
927 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
928 : /* remove them and continue the deletion */
929 72 : 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 72 : store->table_api.rids_destroy(A);
940 72 : 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 89 : sql_drop_user(mvc *sql, char *user)
948 : {
949 89 : sqlid user_id = sql_find_auth(sql, user);
950 89 : list *deleted = list_create(NULL);
951 89 : str msg = NULL;
952 :
953 89 : if (!deleted)
954 0 : throw(SQL, "sql.drop_user", SQLSTATE(HY013) MAL_MALLOC_FAIL);
955 89 : msg = sql_drop_granted_users(sql, user_id, user, deleted);
956 89 : list_destroy(deleted);
957 :
958 : /* Flag as removed */
959 89 : 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 68 : 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 68 : sql_schema *s = NULL;
968 68 : sqlid schema_id = 0;
969 68 : sqlid role_id = 0;
970 :
971 68 : 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 134 : if (strNil(user))
977 : user = NULL;
978 : /* USER == NULL -> current_user */
979 :
980 68 : 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 66 : 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 67 : if (schema) {
986 50 : 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 49 : schema_id = s->base.id;
989 49 : 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 66 : 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 221 : sql_create_privileges(mvc *m, sql_schema *s, const char *initpasswd)
1014 : {
1015 221 : int pub, su, p, zero = 0;
1016 221 : sql_table *t = NULL, *privs = NULL;
1017 221 : sql_column *col = NULL;
1018 221 : sql_subfunc *f = NULL;
1019 221 : sql_trans *tr = m->session->tr;
1020 :
1021 : // create db_user_info tbl
1022 221 : backend_create_privileges(m, s, initpasswd);
1023 :
1024 221 : mvc_create_table(&t, m, s, "user_role", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1025 221 : mvc_create_column_(&col, m, t, "login_id", "int", 32);
1026 221 : mvc_create_column_(&col, m, t, "role_id", "int", 32);
1027 :
1028 : /* all roles and users are in the auths table */
1029 221 : mvc_create_table(&t, m, s, "auths", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1030 221 : mvc_create_column_(&col, m, t, "id", "int", 32);
1031 221 : mvc_create_column_(&col, m, t, "name", "varchar", 1024);
1032 221 : mvc_create_column_(&col, m, t, "grantor", "int", 32);
1033 :
1034 221 : mvc_create_table(&t, m, s, "privileges", tt_table, 1, SQL_PERSIST, 0, -1, 0);
1035 221 : mvc_create_column_(&col, m, t, "obj_id", "int", 32);
1036 221 : mvc_create_column_(&col, m, t, "auth_id", "int", 32);
1037 221 : mvc_create_column_(&col, m, t, "privileges", "int", 32);
1038 221 : mvc_create_column_(&col, m, t, "grantor", "int", 32);
1039 221 : mvc_create_column_(&col, m, t, "grantable", "int", 32);
1040 :
1041 : /* add roles public and sysadmin and user monetdb */
1042 221 : sql_create_auth_id(m, ROLE_PUBLIC, "public");
1043 221 : sql_create_auth_id(m, ROLE_SYSADMIN, "sysadmin");
1044 221 : sql_create_auth_id(m, USER_MONETDB, "monetdb");
1045 :
1046 221 : pub = ROLE_PUBLIC;
1047 221 : su = USER_MONETDB;
1048 221 : p = PRIV_SELECT;
1049 221 : privs = find_sql_table(tr, s, "privileges");
1050 :
1051 221 : sqlstore *store = m->session->tr->store;
1052 221 : t = find_sql_table(tr, s, "schemas");
1053 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1054 221 : t = find_sql_table(tr, s, "types");
1055 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1056 221 : t = find_sql_table(tr, s, "functions");
1057 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1058 221 : t = find_sql_table(tr, s, "args");
1059 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1060 221 : t = find_sql_table(tr, s, "sequences");
1061 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1062 221 : t = find_sql_table(tr, s, "dependencies");
1063 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1064 221 : t = find_sql_table(tr, s, "_tables");
1065 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1066 221 : t = find_sql_table(tr, s, "_columns");
1067 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1068 221 : t = find_sql_table(tr, s, "keys");
1069 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1070 221 : t = find_sql_table(tr, s, "idxs");
1071 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1072 221 : t = find_sql_table(tr, s, "triggers");
1073 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1074 221 : t = find_sql_table(tr, s, "objects");
1075 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1076 221 : t = find_sql_table(tr, s, "tables");
1077 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1078 221 : t = find_sql_table(tr, s, "columns");
1079 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1080 221 : t = find_sql_table(tr, s, "comments");
1081 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1082 221 : t = find_sql_table(tr, s, "user_role");
1083 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1084 221 : t = find_sql_table(tr, s, "auths");
1085 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1086 221 : t = find_sql_table(tr, s, "privileges");
1087 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1088 221 : t = find_sql_table(tr, s, "table_partitions");
1089 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1090 221 : t = find_sql_table(tr, s, "range_partitions");
1091 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
1092 221 : t = find_sql_table(tr, s, "value_partitions");
1093 221 : 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 221 : t = find_sql_table(tr, s, "db_user_info");
1096 221 : store->table_api.table_insert(m->session->tr, privs, &t->base.id, &su, &p, &zero, &zero);
1097 :
1098 :
1099 221 : p = PRIV_EXECUTE;
1100 221 : f = sql_bind_func_(m, s->base.name, "env", NULL, F_UNION, true, true);
1101 221 : store->table_api.table_insert(m->session->tr, privs, &f->func->base.id, &pub, &p, &zero, &zero);
1102 221 : f = sql_bind_func_(m, s->base.name, "var", NULL, F_UNION, true, true);
1103 221 : 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 221 : return 0;
1124 : }
|
