1/*
2 * This Source Code Form is subject to the terms of the Mozilla Public
3 * License, v. 2.0. If a copy of the MPL was not distributed with this
4 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
5 *
6 * Copyright 1997 - July 2008 CWI, August 2008 - 2019 MonetDB B.V.
7 */
8
9/*
10 * Privileges
11 * ==========
12 *
13 * Sql has a simple access control schema. There are two types of authorization,
14 * users and roles. Each user may be part of several roles.
15 * For each authorization identity a set of privileges is administrated.
16 * These are administrated on multiple levels where lower levels (ie.
17 * table or column level) overwrite privileges on higher levels.
18 *
19 */
20
21#include "monetdb_config.h"
22#include "sql_privileges.h"
23#include "sql_semantic.h"
24#include "sql_parser.h"
25#include "mal_exception.h"
26
27#define PRIV_ROLE_ADMIN 0
28
29#define GLOBAL_OBJID 0
30
31static const char *
32priv2string(int priv)
33{
34 switch (priv) {
35 case PRIV_SELECT:
36 return "SELECT";
37 case PRIV_UPDATE:
38 return "UPDATE";
39 case PRIV_INSERT:
40 return "INSERT";
41 case PRIV_DELETE:
42 return "DELETE";
43 case PRIV_TRUNCATE:
44 return "TRUNCATE";
45 case PRIV_EXECUTE:
46 return "EXECUTE";
47 }
48 return "UNKNOWN PRIV";
49}
50
51static void
52sql_insert_priv(mvc *sql, sqlid auth_id, sqlid obj_id, int privilege, sqlid grantor, int grantable)
53{
54 sql_schema *ss = mvc_bind_schema(sql, "sys");
55 sql_table *pt = mvc_bind_table(sql, ss, "privileges");
56
57 table_funcs.table_insert(sql->session->tr, pt, &obj_id, &auth_id, &privilege, &grantor, &grantable);
58}
59
60static void
61sql_insert_all_privs(mvc *sql, sqlid auth_id, sqlid obj_id, int grantor, int grantable)
62{
63 sql_insert_priv(sql, auth_id, obj_id, PRIV_SELECT, grantor, grantable);
64 sql_insert_priv(sql, auth_id, obj_id, PRIV_UPDATE, grantor, grantable);
65 sql_insert_priv(sql, auth_id, obj_id, PRIV_INSERT, grantor, grantable);
66 sql_insert_priv(sql, auth_id, obj_id, PRIV_DELETE, grantor, grantable);
67 sql_insert_priv(sql, auth_id, obj_id, PRIV_TRUNCATE, grantor, grantable);
68}
69
70static bool
71admin_privs(sqlid grantor)
72{
73 if (grantor == USER_MONETDB || grantor == ROLE_SYSADMIN) {
74 return true;
75 }
76 return false;
77}
78
79int
80mvc_schema_privs(mvc *m, sql_schema *s)
81{
82 if (admin_privs(m->user_id) || admin_privs(m->role_id))
83 return 1;
84 if (!s)
85 return 0;
86 if (m->user_id == s->auth_id || m->role_id == s->auth_id)
87 return 1;
88 return 0;
89}
90
91static bool
92schema_privs(sqlid grantor, sql_schema *s)
93{
94 if (admin_privs(grantor))
95 return true;
96 if (!s)
97 return false;
98 if (grantor == s->auth_id)
99 return true;
100 return false;
101}
102
103str
104sql_grant_global_privs( mvc *sql, char *grantee, int privs, int grant, sqlid grantor)
105{
106 sql_trans *tr = sql->session->tr;
107 bool allowed;
108 sqlid grantee_id;
109
110 allowed = admin_privs(grantor);
111
112 if (!allowed)
113 allowed = sql_grantable(sql, grantor, GLOBAL_OBJID, privs, 0) == 1;
114
115 if (!allowed)
116 throw(SQL,"sql.grant_global",SQLSTATE(0L000) "GRANT: Grantor '%s' is not allowed to grant global privileges", stack_get_string(sql,"current_user"));
117
118 grantee_id = sql_find_auth(sql, grantee);
119 if (grantee_id <= 0)
120 throw(SQL,"sql.grant_global",SQLSTATE(42M32) "GRANT: User/role '%s' unknown", grantee);
121 /* first check if privilege isn't already given */
122 if ((sql_privilege(sql, grantee_id, GLOBAL_OBJID, privs, 0)))
123 throw(SQL,"sql.grant_global",SQLSTATE(42M32) "GRANT: User/role '%s' already has this privilege", grantee);
124 sql_insert_priv(sql, grantee_id, GLOBAL_OBJID, privs, grantor, grant);
125 tr->schema_updates++;
126 return MAL_SUCCEED;
127}
128
129char *
130sql_grant_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
131{
132 sql_trans *tr = sql->session->tr;
133 sql_schema *s = NULL;
134 sql_table *t = NULL;
135 sql_column *c = NULL;
136 bool allowed;
137 sqlid grantee_id;
138 int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE;
139
140 if (sname)
141 s = mvc_bind_schema(sql, sname);
142 if (s)
143 t = mvc_bind_table(sql, s, tname);
144 if (!t)
145 throw(SQL,"sql.grant_table",SQLSTATE(42S02) "GRANT: no such table '%s'", tname);
146
147 allowed = schema_privs(grantor, t->s);
148
149 if (!cname) {
150 if (!allowed)
151 allowed = sql_grantable(sql, grantor, t->base.id, privs, 0) == 1;
152
153 if (!allowed)
154 throw(SQL,"sql.grant_table", SQLSTATE(0L000) "GRANT: Grantor '%s' is not allowed to grant privileges for table '%s'", stack_get_string(sql,"current_user"), tname);
155 }
156 if (cname) {
157 c = mvc_bind_column(sql, t, cname);
158 if (!c)
159 throw(SQL,"sql.grant_table",SQLSTATE(42S22) "GRANT: Table '%s' has no column '%s'", tname, cname);
160 /* allowed on column */
161 if (!allowed)
162 allowed = sql_grantable(sql, grantor, c->base.id, privs, 0) == 1;
163
164 if (!allowed)
165 throw(SQL, "sql.grant_table", SQLSTATE(0L000) "GRANT: Grantor '%s' is not allowed to grant privilege %s for table '%s'", stack_get_string(sql, "current_user"), priv2string(privs), tname);
166 }
167
168 grantee_id = sql_find_auth(sql, grantee);
169 if (grantee_id <= 0)
170 throw(SQL,"sql.grant_table", SQLSTATE(42M32) "GRANT: User/role '%s' unknown", grantee);
171 /* first check if privilege isn't already given */
172 if ((privs == all &&
173 (sql_privilege(sql, grantee_id, t->base.id, PRIV_SELECT, 0) ||
174 sql_privilege(sql, grantee_id, t->base.id, PRIV_UPDATE, 0) ||
175 sql_privilege(sql, grantee_id, t->base.id, PRIV_INSERT, 0) ||
176 sql_privilege(sql, grantee_id, t->base.id, PRIV_DELETE, 0) ||
177 sql_privilege(sql, grantee_id, t->base.id, PRIV_TRUNCATE, 0))) ||
178 (privs != all && !c && sql_privilege(sql, grantee_id, t->base.id, privs, 0)) ||
179 (privs != all && c && sql_privilege(sql, grantee_id, c->base.id, privs, 0))) {
180 throw(SQL, "sql.grant", SQLSTATE(42M32) "GRANT: User/role '%s' already has this privilege", grantee);
181 }
182 if (privs == all) {
183 sql_insert_all_privs(sql, grantee_id, t->base.id, grantor, grant);
184 } else if (!c) {
185 sql_insert_priv(sql, grantee_id, t->base.id, privs, grantor, grant);
186 } else {
187 sql_insert_priv(sql, grantee_id, c->base.id, privs, grantor, grant);
188 }
189 tr->schema_updates++;
190 return NULL;
191}
192
193char *
194sql_grant_func_privs( mvc *sql, char *grantee, int privs, char *sname, sqlid func_id, int grant, sqlid grantor)
195{
196 sql_trans *tr = sql->session->tr;
197 sql_schema *s = NULL;
198 sql_func *f = NULL;
199 bool allowed;
200 sqlid grantee_id;
201
202 if (sname)
203 s = mvc_bind_schema(sql, sname);
204 if (s) {
205 node *n = find_sql_func_node(s, func_id);
206 if (n)
207 f = n->data;
208 }
209 assert(f);
210 allowed = schema_privs(grantor, f->s);
211
212 if (!allowed)
213 allowed = sql_grantable(sql, grantor, f->base.id, privs, 0) == 1;
214
215 if (!allowed)
216 throw(SQL, "sql.grant_func", SQLSTATE(0L000) "GRANT: Grantor '%s' is not allowed to grant privileges for function '%s'", stack_get_string(sql,"current_user"), f->base.name);
217
218 grantee_id = sql_find_auth(sql, grantee);
219 if (grantee_id <= 0)
220 throw(SQL, "sql.grant_func", SQLSTATE(42M32) "GRANT: User/role '%s' unknown", grantee);
221 /* first check if privilege isn't already given */
222 if (sql_privilege(sql, grantee_id, f->base.id, privs, 0))
223 throw(SQL,"sql.grant", SQLSTATE(42M32) "GRANT: User/role '%s' already has this privilege", grantee);
224 sql_insert_priv(sql, grantee_id, f->base.id, privs, grantor, grant);
225 tr->schema_updates++;
226 return NULL;
227}
228
229static void
230sql_delete_priv(mvc *sql, sqlid auth_id, sqlid obj_id, int privilege, sqlid grantor, int grantable)
231{
232 sql_schema *ss = mvc_bind_schema(sql, "sys");
233 sql_table *privs = mvc_bind_table(sql, ss, "privileges");
234 sql_column *priv_obj = find_sql_column(privs, "obj_id");
235 sql_column *priv_auth = find_sql_column(privs, "auth_id");
236 sql_column *priv_priv = find_sql_column(privs, "privileges");
237 sql_trans *tr = sql->session->tr;
238 rids *A;
239 oid rid = oid_nil;
240
241 (void) grantor;
242 (void) grantable;
243
244 /* select privileges of this auth_id, privilege, obj_id */
245 A = table_funcs.rids_select(tr, priv_auth, &auth_id, &auth_id, priv_priv, &privilege, &privilege, priv_obj, &obj_id, &obj_id, NULL );
246
247 /* remove them */
248 for(rid = table_funcs.rids_next(A); !is_oid_nil(rid); rid = table_funcs.rids_next(A))
249 table_funcs.table_delete(tr, privs, rid);
250 table_funcs.rids_destroy(A);
251}
252
253char *
254sql_revoke_global_privs( mvc *sql, char *grantee, int privs, int grant, sqlid grantor)
255{
256 bool allowed;
257 sqlid grantee_id;
258
259 allowed = admin_privs(grantor);
260
261 if (!allowed)
262 allowed = sql_grantable(sql, grantor, GLOBAL_OBJID, privs, 0) == 1;
263
264 if (!allowed)
265 throw(SQL, "sql.revoke_global", SQLSTATE(0L000) "REVOKE: Grantor '%s' is not allowed to revoke global privileges", stack_get_string(sql,"current_user"));
266
267 grantee_id = sql_find_auth(sql, grantee);
268 if (grantee_id <= 0)
269 throw(SQL, "sql.revoke_global", SQLSTATE(42M32) "REVOKE: User/role '%s' unknown", grantee);
270 sql_delete_priv(sql, grantee_id, GLOBAL_OBJID, privs, grantor, grant);
271 sql->session->tr->schema_updates++;
272 return NULL;
273}
274
275char *
276sql_revoke_table_privs( mvc *sql, char *grantee, int privs, char *sname, char *tname, char *cname, int grant, sqlid grantor)
277{
278 sql_schema *s = NULL;
279 sql_table *t = NULL;
280 sql_column *c = NULL;
281 bool allowed;
282 sqlid grantee_id;
283 int all = PRIV_SELECT | PRIV_UPDATE | PRIV_INSERT | PRIV_DELETE | PRIV_TRUNCATE;
284
285 if (sname)
286 s = mvc_bind_schema(sql, sname);
287 if (s)
288 t = mvc_bind_table(sql, s, tname);
289 if (!t)
290 throw(SQL,"sql.revoke_table", SQLSTATE(42S02) "REVOKE: no such table '%s'", tname);
291
292 allowed = schema_privs(grantor, t->s);
293 if (!allowed)
294 allowed = sql_grantable(sql, grantor, t->base.id, privs, 0) == 1;
295
296 if (!allowed)
297 throw(SQL, "sql.revoke_table", SQLSTATE(0L000) "REVOKE: Grantor '%s' is not allowed to revoke privileges for table '%s'", stack_get_string(sql,"current_user"), tname);
298
299 if (cname) {
300 c = mvc_bind_column(sql, t, cname);
301 if (!c)
302 throw(SQL,"sql.revoke_table", SQLSTATE(42S22) "REVOKE: table '%s' has no column '%s'", tname, cname);
303 /* allowed on column */
304 if (!allowed)
305 allowed = sql_grantable(sql, grantor, c->base.id, privs, 0) == 1;
306
307 if (!allowed)
308 throw(SQL, "sql.revoke_table", SQLSTATE(0L000) "REVOKE: Grantor '%s' is not allowed to revoke privilege %s for table '%s'", stack_get_string(sql, "current_user"), priv2string(privs), tname);
309 }
310
311 grantee_id = sql_find_auth(sql, grantee);
312 if (grantee_id <= 0)
313 throw(SQL,"sql.revoke_table", SQLSTATE(42M32) "REVOKE: User/role '%s' unknown", grantee);
314 if (privs == all) {
315 sql_delete_priv(sql, grantee_id, t->base.id, PRIV_SELECT, grantor, grant);
316 sql_delete_priv(sql, grantee_id, t->base.id, PRIV_UPDATE, grantor, grant);
317 sql_delete_priv(sql, grantee_id, t->base.id, PRIV_INSERT, grantor, grant);
318 sql_delete_priv(sql, grantee_id, t->base.id, PRIV_DELETE, grantor, grant);
319 sql_delete_priv(sql, grantee_id, t->base.id, PRIV_TRUNCATE, grantor, grant);
320 } else if (!c) {
321 sql_delete_priv(sql, grantee_id, t->base.id, privs, grantor, grant);
322 } else {
323 sql_delete_priv(sql, grantee_id, c->base.id, privs, grantor, grant);
324 }
325 sql->session->tr->schema_updates++;
326 return NULL;
327}
328
329char *
330sql_revoke_func_privs( mvc *sql, char *grantee, int privs, char *sname, sqlid func_id, int grant, sqlid grantor)
331{
332 sql_schema *s = NULL;
333 sql_func *f = NULL;
334 bool allowed;
335 sqlid grantee_id;
336
337 if (sname)
338 s = mvc_bind_schema(sql, sname);
339 if (s) {
340 node *n = find_sql_func_node(s, func_id);
341 if (n)
342 f = n->data;
343 }
344 assert(f);
345 allowed = schema_privs(grantor, f->s);
346 if (!allowed)
347 allowed = sql_grantable(sql, grantor, f->base.id, privs, 0) == 1;
348
349 if (!allowed)
350 throw(SQL, "sql.revoke_func", SQLSTATE(0L000) "REVOKE: Grantor '%s' is not allowed to revoke privileges for function '%s'", stack_get_string(sql,"current_user"), f->base.name);
351
352 grantee_id = sql_find_auth(sql, grantee);
353 if (grantee_id <= 0)
354 throw(SQL, "sql.revoke_func", SQLSTATE(42M32) "REVOKE: User/role '%s' unknown", grantee);
355 sql_delete_priv(sql, grantee_id, f->base.id, privs, grantor, grant);
356 sql->session->tr->schema_updates++;
357 return NULL;
358}
359
360static bool
361sql_create_auth_id(mvc *m, sqlid id, str auth)
362{
363 int grantor = 0; /* no grantor */
364 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
365 sql_table *auths = find_sql_table(sys, "auths");
366 sql_column *auth_name = find_sql_column(auths, "name");
367
368 if (!is_oid_nil(table_funcs.column_find_row(m->session->tr, auth_name, auth, NULL)))
369 return false;
370
371 table_funcs.table_insert(m->session->tr, auths, &id, auth, &grantor);
372 m->session->tr->schema_updates++;
373 return true;
374}
375
376str
377sql_create_role(mvc *m, str auth, sqlid grantor)
378{
379 sqlid id;
380 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
381 sql_table *auths = find_sql_table(sys, "auths");
382 sql_column *auth_name = find_sql_column(auths, "name");
383
384 if (!admin_privs(grantor))
385 throw(SQL, "sql.create_role", SQLSTATE(0P000) "Insufficient privileges to create role '%s'", auth);
386
387 if (!is_oid_nil(table_funcs.column_find_row(m->session->tr, auth_name, auth, NULL)))
388 throw(SQL, "sql.create_role", SQLSTATE(0P000) "Role '%s' already exists", auth);
389
390 id = store_next_oid();
391 table_funcs.table_insert(m->session->tr, auths, &id, auth, &grantor);
392 m->session->tr->schema_updates++;
393 return NULL;
394}
395
396str
397sql_drop_role(mvc *m, str auth)
398{
399 oid rid;
400 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
401 sql_table *auths = find_sql_table(sys, "auths");
402 sql_column *auth_name = find_sql_column(auths, "name");
403
404 rid = table_funcs.column_find_row(m->session->tr, auth_name, auth, NULL);
405 if (is_oid_nil(rid))
406 throw(SQL, "sql.drop_role", SQLSTATE(0P000) "DROP ROLE: no such role '%s'", auth);
407 table_funcs.table_delete(m->session->tr, auths, rid);
408 m->session->tr->schema_updates++;
409 return NULL;
410}
411
412static oid
413sql_privilege_rid(mvc *m, sqlid auth_id, sqlid obj_id, int priv, int sub)
414{
415 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
416 sql_table *privs = find_sql_table(sys, "privileges");
417 sql_column *priv_obj = find_sql_column(privs, "obj_id");
418 sql_column *priv_auth = find_sql_column(privs, "auth_id");
419 sql_column *priv_priv = find_sql_column(privs, "privileges");
420
421 (void) sub;
422 return table_funcs.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &auth_id, priv_priv, &priv, NULL);
423}
424
425int
426sql_privilege(mvc *m, sqlid auth_id, sqlid obj_id, int priv, int sub)
427{
428 oid rid = sql_privilege_rid(m, auth_id, obj_id, priv, sub);
429 int res = 0;
430
431 if (!is_oid_nil(rid)) {
432 /* found priv */
433 res = priv;
434 }
435 return res;
436}
437
438int
439global_privs(mvc *m, int priv)
440{
441 if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
442 sql_privilege(m, m->user_id, GLOBAL_OBJID, priv, 0) == priv ||
443 sql_privilege(m, m->role_id, GLOBAL_OBJID, priv, 0) == priv ||
444 sql_privilege(m, ROLE_PUBLIC, GLOBAL_OBJID, priv, 0) == priv) {
445 return 1;
446 }
447 return 0;
448}
449
450int
451table_privs(mvc *m, sql_table *t, int priv)
452{
453 /* temporary tables are owned by the session user */
454 if (t->persistence == SQL_DECLARED_TABLE ||
455 (!t->system && t->persistence != SQL_PERSIST) ||
456 (priv == PRIV_SELECT && (t->persistence != SQL_PERSIST || t->commit_action)))
457 return 1;
458 if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
459 (t->s && (m->user_id == t->s->auth_id || m->role_id == t->s->auth_id)) ||
460 sql_privilege(m, m->user_id, t->base.id, priv, 0) == priv ||
461 sql_privilege(m, m->role_id, t->base.id, priv, 0) == priv ||
462 sql_privilege(m, ROLE_PUBLIC, t->base.id, priv, 0) == priv) {
463 return 1;
464 }
465 return 0;
466}
467
468int
469column_privs(mvc *m, sql_column *c, int priv)
470{
471 /* only SELECT and UPDATE privileges for columns are available */
472 /* temporary tables are owned by the session user, so does it's columns */
473 if (c->t->persistence == SQL_DECLARED_TABLE ||
474 (!c->t->system && c->t->persistence != SQL_PERSIST) ||
475 (priv == PRIV_SELECT && (c->t->persistence != SQL_PERSIST || c->t->commit_action)))
476 return 1;
477 if (admin_privs(m->user_id) || admin_privs(m->role_id) ||
478 (c->t->s && (m->user_id == c->t->s->auth_id || m->role_id == c->t->s->auth_id)) ||
479 sql_privilege(m, m->user_id, c->base.id, priv, 0) == priv ||
480 sql_privilege(m, m->role_id, c->base.id, priv, 0) == priv ||
481 sql_privilege(m, ROLE_PUBLIC, c->base.id, priv, 0) == priv) {
482 return 1;
483 }
484 return 0;
485}
486
487int
488execute_priv(mvc *m, sql_func *f)
489{
490 int priv = PRIV_EXECUTE;
491
492 if (!f->s || admin_privs(m->user_id) || admin_privs(m->role_id))
493 return 1;
494 if (m->user_id == f->s->auth_id || m->role_id == f->s->auth_id)
495 return 1;
496 if (sql_privilege(m, m->user_id, f->base.id, priv, 0) == priv ||
497 sql_privilege(m, m->role_id, f->base.id, priv, 0) == priv ||
498 sql_privilege(m, ROLE_PUBLIC, f->base.id, priv, 0) == priv)
499 return 1;
500 return 0;
501}
502
503static bool
504role_granting_privs(mvc *m, oid role_rid, sqlid role_id, sqlid grantor_id)
505{
506 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
507 sql_table *auths = find_sql_table(sys, "auths");
508 sql_column *auths_grantor = find_sql_column(auths, "grantor");
509 sqlid owner_id;
510 void *val;
511
512 val = table_funcs.column_find_value(m->session->tr, auths_grantor, role_rid);
513 owner_id = *(sqlid*)val;
514 _DELETE(val);
515
516 if (owner_id == grantor_id)
517 return true;
518 if (sql_privilege(m, grantor_id, role_id, PRIV_ROLE_ADMIN, 0))
519 return true;
520 /* check for grant rights in the privs table */
521 return false;
522}
523
524char *
525sql_grant_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
526{
527 oid rid;
528 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
529 sql_table *auths = find_sql_table(sys, "auths");
530 sql_table *roles = find_sql_table(sys, "user_role");
531 sql_column *auths_name = find_sql_column(auths, "name");
532 sql_column *auths_id = find_sql_column(auths, "id");
533 sqlid role_id, grantee_id;
534 void *val;
535
536 rid = table_funcs.column_find_row(m->session->tr, auths_name, role, NULL);
537 if (is_oid_nil(rid))
538 throw(SQL, "sql.grant_role", SQLSTATE(M1M05) "GRANT: Cannot grant ROLE '%s' to user '%s'", role, grantee);
539 val = table_funcs.column_find_value(m->session->tr, auths_id, rid);
540 role_id = *(sqlid*)val;
541 _DELETE(val);
542
543 if (backend_find_user(m, role) >= 0)
544 throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: '%s' is a USER not a ROLE", role);
545 if (!admin_privs(grantor) && !role_granting_privs(m, rid, role_id, grantor))
546 throw(SQL,"sql.grant_role", SQLSTATE(0P000) "GRANT: Insufficient privileges to grant ROLE '%s'", role);
547 rid = table_funcs.column_find_row(m->session->tr, auths_name, grantee, NULL);
548 if (is_oid_nil(rid))
549 throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: Cannot grant ROLE '%s' to user '%s'", role, grantee);
550 val = table_funcs.column_find_value(m->session->tr, auths_id, rid);
551 grantee_id = *(sqlid*)val;
552 _DELETE(val);
553 rid = table_funcs.column_find_row(m->session->tr, find_sql_column(roles, "login_id"), &grantee_id, find_sql_column(roles, "role_id"), &role_id, NULL);
554 if (!is_oid_nil(rid))
555 throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: User '%s' already has ROLE '%s'", grantee, role);
556
557 table_funcs.table_insert(m->session->tr, roles, &grantee_id, &role_id);
558 if (admin) {
559 int priv = PRIV_ROLE_ADMIN, one = 1;
560 sql_table *privs = find_sql_table(sys, "privileges");
561
562 table_funcs.table_insert(m->session->tr, privs, &role_id, &grantee_id, &priv, &grantor, &one);
563 }
564 m->session->tr->schema_updates++;
565 return NULL;
566}
567
568char *
569sql_revoke_role(mvc *m, str grantee, str role, sqlid grantor, int admin)
570/* grantee no longer belongs the role (role) */
571{
572 oid rid;
573 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
574 sql_table *auths = find_sql_table(sys, "auths");
575 sql_table *roles = find_sql_table(sys, "user_role");
576 sql_column *auths_name = find_sql_column(auths, "name");
577 sql_column *auths_id = find_sql_column(auths, "id");
578 sql_column *roles_role_id = find_sql_column(roles, "role_id");
579 sql_column *roles_login_id = find_sql_column(roles, "login_id");
580 sqlid role_id, grantee_id;
581 void *val;
582
583 rid = table_funcs.column_find_row(m->session->tr, auths_name, grantee, NULL);
584 if (is_oid_nil(rid))
585 throw(SQL,"sql.revoke_role", SQLSTATE(42M32) "REVOKE: no such role '%s' or grantee '%s'", role, grantee);
586 val = table_funcs.column_find_value(m->session->tr, auths_id, rid);
587 grantee_id = *(sqlid*)val;
588 _DELETE(val);
589
590 rid = table_funcs.column_find_row(m->session->tr, auths_name, role, NULL);
591 if (is_oid_nil(rid))
592 throw(SQL,"sql.revoke_role", SQLSTATE(42M32) "REVOKE: no such role '%s' or grantee '%s'", role, grantee);
593 val = table_funcs.column_find_value(m->session->tr, auths_id, rid);
594 role_id = *(sqlid*)val;
595 _DELETE(val);
596 if (!admin_privs(grantor) && !role_granting_privs(m, rid, role_id, grantor))
597 throw(SQL,"sql.revoke_role", SQLSTATE(0P000) "REVOKE: insufficient privileges to revoke ROLE '%s'", role);
598
599 if (!admin) {
600 rid = table_funcs.column_find_row(m->session->tr, roles_login_id, &grantee_id, roles_role_id, &role_id, NULL);
601 if (!is_oid_nil(rid))
602 table_funcs.table_delete(m->session->tr, roles, rid);
603 else
604 throw(SQL,"sql.revoke_role", SQLSTATE(42M32) "REVOKE: User '%s' does not have ROLE '%s'", grantee, role);
605 } else {
606 rid = sql_privilege_rid(m, grantee_id, role_id, PRIV_ROLE_ADMIN, 0);
607 if (!is_oid_nil(rid))
608 table_funcs.table_delete(m->session->tr, roles, rid);
609 else
610 throw(SQL,"sql.revoke_role", SQLSTATE(42M32) "REVOKE: User '%s' does not have ROLE '%s'", grantee, role);
611 }
612 m->session->tr->schema_updates++;
613 return NULL;
614}
615
616sqlid
617sql_find_auth(mvc *m, str auth)
618{
619 sqlid res = -1;
620 oid rid;
621 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
622 sql_table *auths = find_sql_table(sys, "auths");
623 sql_column *auths_name = find_sql_column(auths, "name");
624
625 rid = table_funcs.column_find_row(m->session->tr, auths_name, auth, NULL);
626
627 if (!is_oid_nil(rid)) {
628 sql_column *auths_id = find_sql_column(auths, "id");
629 sqlid *p = (sqlid *) table_funcs.column_find_value(m->session->tr, auths_id, rid);
630
631 if (p) {
632 res = *p;
633 _DELETE(p);
634 }
635 }
636 return res;
637}
638
639sqlid
640sql_find_schema(mvc *m, str schema)
641{
642 sqlid schema_id = -1;
643 oid rid;
644 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
645 sql_table *schemas = find_sql_table(sys, "schemas");
646 sql_column *schemas_name = find_sql_column(schemas, "name");
647
648 rid = table_funcs.column_find_row(m->session->tr, schemas_name, schema, NULL);
649
650 if (!is_oid_nil(rid)) {
651 sql_column *schemas_id = find_sql_column(schemas, "id");
652 sqlid *p = (sqlid *) table_funcs.column_find_value(m->session->tr, schemas_id, rid);
653
654 if (p) {
655 schema_id = *p;
656 _DELETE(p);
657 }
658 }
659 return schema_id;
660}
661
662int
663sql_schema_has_user(mvc *m, sql_schema *s)
664{
665 return(backend_schema_has_user(m, s));
666}
667
668static int
669sql_grantable_(mvc *m, sqlid grantorid, sqlid obj_id, int privs, int sub)
670{
671 oid rid;
672 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
673 sql_table *prvs = find_sql_table(sys, "privileges");
674 sql_column *priv_obj = find_sql_column(prvs, "obj_id");
675 sql_column *priv_auth = find_sql_column(prvs, "auth_id");
676 sql_column *priv_priv = find_sql_column(prvs, "privileges");
677 sql_column *priv_allowed = find_sql_column(prvs, "grantable");
678 int priv;
679
680 (void) sub;
681 for (priv = 1; priv <= privs; priv <<= 1) {
682 if (!(priv & privs))
683 continue;
684 rid = table_funcs.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &grantorid, priv_priv, &priv, NULL);
685 if (!is_oid_nil(rid)) {
686 void *p = table_funcs.column_find_value(m->session->tr, priv_allowed, rid);
687 int allowed = *(int *)p;
688
689 _DELETE(p);
690 /* switch of priv bit */
691 if (allowed)
692 privs = (privs & ~priv);
693 }
694 }
695 if (privs != 0)
696 return 0;
697 return 1;
698}
699
700int
701sql_grantable(mvc *m, sqlid grantorid, sqlid obj_id, int privs, int sub)
702{
703 if (admin_privs(m->user_id) || admin_privs(m->role_id))
704 return 1;
705 return sql_grantable_(m, grantorid, obj_id, privs, sub);
706}
707
708sqlid
709mvc_set_role(mvc *m, char *role)
710{
711 oid rid;
712 sql_schema *sys = find_sql_schema(m->session->tr, "sys");
713 sql_table *auths = find_sql_table(sys, "auths");
714 sql_column *auths_name = find_sql_column(auths, "name");
715 sqlid res = 0;
716
717 if (m->debug&1)
718 fprintf(stderr, "mvc_set_role %s\n", role);
719
720 rid = table_funcs.column_find_row(m->session->tr, auths_name, role, NULL);
721 if (!is_oid_nil(rid)) {
722 sql_column *auths_id = find_sql_column(auths, "id");
723 void *p = table_funcs.column_find_value(m->session->tr, auths_id, rid);
724 sqlid id = *(sqlid *)p;
725
726 _DELETE(p);
727
728 if (m->user_id == id) {
729 m->role_id = id;
730 res = 1;
731 } else {
732 sql_table *roles = find_sql_table(sys, "user_role");
733 sql_column *role_id = find_sql_column(roles, "role_id");
734 sql_column *login_id = find_sql_column(roles, "login_id");
735
736 rid = table_funcs.column_find_row(m->session->tr, login_id, &m->user_id, role_id, &id, NULL);
737 if (!is_oid_nil(rid)) {
738 m->role_id = id;
739 res = 1;
740 }
741 }
742 }
743 return res;
744}
745
746int
747mvc_set_schema(mvc *m, char *schema)
748{
749 int ret = 0;
750 sql_schema *s = find_sql_schema(m->session->tr, schema);
751 char* new_schema_name = _STRDUP(schema);
752
753 if (s && new_schema_name) {
754 if (m->session->schema_name)
755 _DELETE(m->session->schema_name);
756 m->session->schema_name = new_schema_name;
757 m->type = Q_TRANS;
758 if (m->session->tr->active)
759 m->session->schema = s;
760 ret = 1;
761 } else if(new_schema_name) {
762 _DELETE(new_schema_name);
763 }
764 return ret;
765}
766
767char *
768sql_create_user(mvc *sql, char *user, char *passwd, char enc, char *fullname, char *schema)
769{
770 char *err;
771 sqlid schema_id = 0;
772
773 if (!admin_privs(sql->user_id) && !admin_privs(sql->role_id))
774 throw(SQL,"sql.create_user", SQLSTATE(42M31) "Insufficient privileges to create user '%s'", user);
775
776 if (backend_find_user(sql, user) >= 0) {
777 throw(SQL,"sql.create_user", SQLSTATE(42M31) "CREATE USER: user '%s' already exists", user);
778 }
779 if ((schema_id = sql_find_schema(sql, schema)) < 0) {
780 throw(SQL,"sql.create_user", SQLSTATE(3F000) "CREATE USER: no such schema '%s'", schema);
781 }
782 if ((err = backend_create_user(sql, user, passwd, enc, fullname,
783 schema_id, sql->user_id)) != NULL)
784 {
785 /* strip off MAL exception decorations */
786 char *r;
787 char *e = err;
788 if ((e = strchr(e, ':')) == NULL) {
789 e = err;
790 } else if ((e = strchr(++e, ':')) == NULL) {
791 e = err;
792 } else {
793 e++;
794 }
795 r = createException(SQL,"sql.create_user", SQLSTATE(M0M27) "CREATE USER: %s", e);
796 _DELETE(err);
797 return r;
798 }
799 return NULL;
800}
801
802char *
803sql_drop_user(mvc *sql, char *user)
804{
805 sqlid user_id = sql_find_auth(sql, user);
806
807 if (mvc_check_dependency(sql, user_id, OWNER_DEPENDENCY, NULL))
808 throw(SQL,"sql.drop_user",SQLSTATE(M1M05) "DROP USER: '%s' owns a schema", user);
809 if (backend_drop_user(sql,user) == FALSE)
810 throw(SQL,"sql.drop_user",SQLSTATE(M0M27) "%s", sql->errstr);
811 return sql_drop_role(sql, user);
812}
813
814char *
815sql_alter_user(mvc *sql, char *user, char *passwd, char enc, char *schema, char *oldpasswd)
816{
817 sqlid schema_id = 0;
818 /* we may be called from MAL (nil) */
819 if (user != NULL && strcmp(user, str_nil) == 0)
820 user = NULL;
821 /* USER == NULL -> current_user */
822 if (user != NULL && backend_find_user(sql, user) < 0)
823 throw(SQL,"sql.alter_user", SQLSTATE(42M32) "ALTER USER: no such user '%s'", user);
824
825 if (!admin_privs(sql->user_id) && !admin_privs(sql->role_id) && user != NULL && strcmp(user, stack_get_string(sql, "current_user")) != 0)
826 throw(SQL,"sql.alter_user", SQLSTATE(M1M05) "Insufficient privileges to change user '%s'", user);
827 if (schema && (schema_id = sql_find_schema(sql, schema)) < 0) {
828 throw(SQL,"sql.alter_user", SQLSTATE(3F000) "ALTER USER: no such schema '%s'", schema);
829 }
830 if (backend_alter_user(sql, user, passwd, enc, schema_id, oldpasswd) == FALSE)
831 throw(SQL,"sql.alter_user", SQLSTATE(M0M27) "%s", sql->errstr);
832 return NULL;
833}
834
835char *
836sql_rename_user(mvc *sql, char *olduser, char *newuser)
837{
838 if (backend_find_user(sql, olduser) < 0)
839 throw(SQL,"sql.rename_user", SQLSTATE(42M32) "ALTER USER: no such user '%s'", olduser);
840 if (backend_find_user(sql, newuser) >= 0)
841 throw(SQL,"sql.rename_user", SQLSTATE(42M31) "ALTER USER: user '%s' already exists", newuser);
842 if (!admin_privs(sql->user_id) && !admin_privs(sql->role_id))
843 throw(SQL,"sql.rename_user", SQLSTATE(M1M05) "ALTER USER: insufficient privileges to "
844 "rename user '%s'", olduser);
845
846 if (backend_rename_user(sql, olduser, newuser) == FALSE)
847 throw(SQL,"sql.rename_user", SQLSTATE(M1M05) "%s", sql->errstr);
848 return NULL;
849}
850
851int
852sql_create_privileges(mvc *m, sql_schema *s)
853{
854 int pub, p, zero = 0;
855 sql_table *t, *privs;
856 sql_subfunc *f;
857
858 backend_create_privileges(m, s);
859
860 t = mvc_create_table(m, s, "user_role", tt_table, 1, SQL_PERSIST, 0, -1, 0);
861 mvc_create_column_(m, t, "login_id", "int", 32);
862 mvc_create_column_(m, t, "role_id", "int", 32);
863
864 /* all roles and users are in the auths table */
865 t = mvc_create_table(m, s, "auths", tt_table, 1, SQL_PERSIST, 0, -1, 0);
866 mvc_create_column_(m, t, "id", "int", 32);
867 mvc_create_column_(m, t, "name", "varchar", 1024);
868 mvc_create_column_(m, t, "grantor", "int", 32);
869
870 t = mvc_create_table(m, s, "privileges", tt_table, 1, SQL_PERSIST, 0, -1, 0);
871 mvc_create_column_(m, t, "obj_id", "int", 32);
872 mvc_create_column_(m, t, "auth_id", "int", 32);
873 mvc_create_column_(m, t, "privileges", "int", 32);
874 mvc_create_column_(m, t, "grantor", "int", 32);
875 mvc_create_column_(m, t, "grantable", "int", 32);
876
877 /* add roles public and sysadmin and user monetdb */
878 sql_create_auth_id(m, ROLE_PUBLIC, "public");
879 sql_create_auth_id(m, ROLE_SYSADMIN, "sysadmin");
880 sql_create_auth_id(m, USER_MONETDB, "monetdb");
881
882 pub = ROLE_PUBLIC;
883 p = PRIV_SELECT;
884 privs = find_sql_table(s, "privileges");
885
886 t = find_sql_table(s, "schemas");
887 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
888 t = find_sql_table(s, "types");
889 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
890 t = find_sql_table(s, "functions");
891 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
892 t = find_sql_table(s, "args");
893 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
894 t = find_sql_table(s, "sequences");
895 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
896 t = find_sql_table(s, "dependencies");
897 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
898 t = find_sql_table(s, "_tables");
899 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
900 t = find_sql_table(s, "_columns");
901 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
902 t = find_sql_table(s, "keys");
903 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
904 t = find_sql_table(s, "idxs");
905 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
906 t = find_sql_table(s, "triggers");
907 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
908 t = find_sql_table(s, "objects");
909 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
910 t = find_sql_table(s, "tables");
911 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
912 t = find_sql_table(s, "columns");
913 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
914 t = find_sql_table(s, "comments");
915 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
916 t = find_sql_table(s, "user_role");
917 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
918 t = find_sql_table(s, "auths");
919 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
920 t = find_sql_table(s, "privileges");
921 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
922 t = find_sql_table(s, "table_partitions");
923 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
924 t = find_sql_table(s, "range_partitions");
925 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
926 t = find_sql_table(s, "value_partitions");
927 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
928
929 p = PRIV_EXECUTE;
930 f = sql_bind_func_(m->sa, s, "env", NULL, F_UNION);
931
932 table_funcs.table_insert(m->session->tr, privs, &f->func->base.id, &pub, &p, &zero, &zero);
933
934 /* owned by the users anyway
935 s = mvc_bind_schema(m, "tmp");
936 t = find_sql_table(s, "profile");
937 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
938 t = find_sql_table(s, "_tables");
939 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
940 t = find_sql_table(s, "_columns");
941 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
942 t = find_sql_table(s, "keys");
943 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
944 t = find_sql_table(s, "idxs");
945 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
946 t = find_sql_table(s, "triggers");
947 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
948 t = find_sql_table(s, "objects");
949 table_funcs.table_insert(m->session->tr, privs, &t->base.id, &pub, &p, &zero, &zero);
950 */
951
952 return 0;
953}
954