char * sql_grant_role(mvc *m, str grantee, str auth /*, grantor?, admin? */ ) { oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_table *roles = find_sql_table(sys, "user_role"); sql_column *auths_name = find_sql_column(auths, "name"); sql_column *auths_id = find_sql_column(auths, "id"); void *auth_id, *grantee_id; rid = table_funcs.column_find_row(m->session->tr, auths_name, grantee, NULL); if (rid == oid_nil) return sql_message("M1M05!GRANT: cannot grant ROLE '%s' to ROLE '%s'", grantee, auth ); grantee_id = table_funcs.column_find_value(m->session->tr, auths_id, rid); rid = table_funcs.column_find_row(m->session->tr, auths_name, auth, NULL); if (rid == oid_nil) { _DELETE(grantee_id); return sql_message("M1M05!GRANT: cannot grant ROLE '%s' to ROLE '%s'", grantee, auth ); } auth_id = table_funcs.column_find_value(m->session->tr, auths_id, rid); table_funcs.table_insert(m->session->tr, roles, grantee_id, auth_id); _DELETE(grantee_id); _DELETE(auth_id); return NULL; }
int mvc_set_role(mvc *m, char *role) { oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_column *auths_name = find_sql_column(auths, "name"); int res = 0; if (m->debug&1) fprintf(stderr, "mvc_set_role %s\n", role); rid = table_funcs.column_find_row(m->session->tr, auths_name, role, NULL); if (rid != oid_nil) { sql_table *roles = find_sql_table(sys, "user_role"); sql_column *role_id = find_sql_column(roles, "role_id"); sql_column *login_id = find_sql_column(roles, "login_id"); sql_column *auths_id = find_sql_column(auths, "id"); void *p = table_funcs.column_find_value(m->session->tr, auths_id, rid); int id = *(int *)p; _DELETE(p); rid = table_funcs.column_find_row(m->session->tr, login_id, &m->user_id, role_id, &id, NULL); if (rid != oid_nil) { m->role_id = id; res = 1; } } return res; }
char * sql_revoke_role(mvc *m, str grantee, str auth) /* grantee no longer belongs the role (auth) */ { oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_table *roles = find_sql_table(sys, "user_role"); sql_column *auths_name = find_sql_column(auths, "name"); sql_column *auths_id = find_sql_column(auths, "id"); sql_column *role_id = find_sql_column(roles, "role_id"); sql_column *login_id = find_sql_column(roles, "login_id"); void *auth_id, *grantee_id; rid = table_funcs.column_find_row(m->session->tr, auths_name, grantee, NULL); if (rid == oid_nil) return sql_message("42M32!REVOKE: no such role '%s' or grantee '%s'", auth, grantee); grantee_id = table_funcs.column_find_value(m->session->tr, auths_id, rid); rid = table_funcs.column_find_row(m->session->tr, auths_name, auth, NULL); if (rid == oid_nil) { _DELETE(grantee_id); return sql_message("42M32!REVOKE: no such role '%s' or grantee '%s'", auth, grantee); } auth_id = table_funcs.column_find_value(m->session->tr, auths_id, rid); rid = table_funcs.column_find_row(m->session->tr, login_id, grantee_id, role_id, auth_id, NULL); table_funcs.table_delete(m->session->tr, roles, rid); _DELETE(grantee_id); _DELETE(auth_id); return NULL; }
static int sql_grantable_(mvc *m, int grantorid, int obj_id, int privs, int sub) { oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *prvs = find_sql_table(sys, "privileges"); sql_column *priv_obj = find_sql_column(prvs, "obj_id"); sql_column *priv_auth = find_sql_column(prvs, "auth_id"); sql_column *priv_priv = find_sql_column(prvs, "privileges"); sql_column *priv_allowed = find_sql_column(prvs, "grantable"); int priv; (void) sub; for (priv = 1; priv < privs; priv <<= 1) { if (!(priv & privs)) continue; rid = table_funcs.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &grantorid, priv_priv, &priv, NULL); if (rid != oid_nil) { void *p = table_funcs.column_find_value(m->session->tr, priv_allowed, rid); int allowed = *(int *)p; _DELETE(p); /* switch of priv bit */ if (allowed) privs = (privs & ~priv); } } if (privs != 0) return 0; return 1; }
static int monet5_drop_user(ptr _mvc, str user) { mvc *m = (mvc *) _mvc; oid rid; sql_schema *sys; sql_table *users; sql_column *users_name; str err; Client c = MCgetClient(m->clientid); err = AUTHremoveUser(c, user); if (err !=MAL_SUCCEED) { (void) sql_error(m, 02, "DROP USER: %s", getExceptionMessage(err)); _DELETE(err); return FALSE; } sys = find_sql_schema(m->session->tr, "sys"); users = find_sql_table(sys, "db_user_info"); users_name = find_sql_column(users, "name"); rid = table_funcs.column_find_row(m->session->tr, users_name, user, NULL); if (!is_oid_nil(rid)) table_funcs.table_delete(m->session->tr, users, rid); /* FIXME: We have to ignore this inconsistency here, because the * user was already removed from the system authorisation. Once * we have warnings, we could issue a warning about this * (seemingly) inconsistency between system and sql shadow * administration. */ return TRUE; }
int sql_trans_disconnect_catalog_ALL(sql_trans *tr) { sql_schema *s = find_sql_schema(tr, "sys"); sql_table *t = find_sql_table(s, "connections"); sql_trans_clear_table(tr, t); return 1; }
int mvc_rollback(mvc *m, int chain, const char *name) { int res = 0; sql_trans *tr = m->session->tr; if (mvc_debug) fprintf(stderr, "#mvc_rollback %s\n", (name) ? name : ""); assert(tr); assert(m->session->active); /* only abort an active transaction */ store_lock(); if (m->qc) qc_clean(m->qc); if (name && name[0] != '\0') { while (tr && (!tr->name || strcmp(tr->name, name) != 0)) tr = tr->parent; if (!tr) { (void)sql_error(m, 010, "ROLLBACK: no such savepoint: '%s'", name); m->session->status = -1; store_unlock(); return -1; } tr = m->session->tr; while (!tr->name || strcmp(tr->name, name) != 0) { /* make sure we do not reuse changed data */ if (tr->wtime) tr->status = 1; tr = sql_trans_destroy(tr); } m->session->tr = tr; /* restart at savepoint */ m->session->status = tr->status; if (tr->name) tr->name = NULL; m->session->schema = find_sql_schema(m->session->tr, m->session->schema_name); } else if (tr->parent) { /* first release all intermediate savepoints */ while (tr->parent->parent != NULL) { tr = sql_trans_destroy(tr); } m->session-> tr = tr; /* make sure we do not reuse changed data */ if (tr->wtime) tr->status = 1; sql_trans_end(m->session); if (chain) sql_trans_begin(m->session); } store_unlock(); m->type = Q_TRANS; if (mvc_debug) fprintf(stderr, "#mvc_rollback %s done\n", (name) ? name : ""); return res; }
static int sql_create_role_id(mvc *m, unsigned int id, str auth, int grantor) { sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_column *auth_name = find_sql_column(auths, "name"); if (table_funcs.column_find_row(m->session->tr, auth_name, auth, NULL) != oid_nil) return FALSE; table_funcs.table_insert(m->session->tr, auths, &id, auth, &grantor); return TRUE; }
str monet5_user_get_def_schema(mvc *m, int user) { oid rid; sqlid schema_id; sql_schema *sys = NULL; sql_table *user_info = NULL; sql_column *users_name = NULL; sql_column *users_schema = NULL; sql_table *schemas = NULL; sql_column *schemas_name = NULL; sql_column *schemas_id = NULL; sql_table *auths = NULL; sql_column *auths_id = NULL; sql_column *auths_name = NULL; void *p = 0; str username = NULL; str schema = NULL; sys = find_sql_schema(m->session->tr, "sys"); auths = find_sql_table(sys, "auths"); auths_id = find_sql_column(auths, "id"); auths_name = find_sql_column(auths, "name"); rid = table_funcs.column_find_row(m->session->tr, auths_id, &user, NULL); if (!is_oid_nil(rid)) username = table_funcs.column_find_value(m->session->tr, auths_name, rid); user_info = find_sql_table(sys, "db_user_info"); users_name = find_sql_column(user_info, "name"); users_schema = find_sql_column(user_info, "default_schema"); rid = table_funcs.column_find_row(m->session->tr, users_name, username, NULL); if (!is_oid_nil(rid)) p = table_funcs.column_find_value(m->session->tr, users_schema, rid); _DELETE(username); assert(p); schema_id = *(sqlid *) p; _DELETE(p); schemas = find_sql_table(sys, "schemas"); schemas_name = find_sql_column(schemas, "name"); schemas_id = find_sql_column(schemas, "id"); rid = table_funcs.column_find_row(m->session->tr, schemas_id, &schema_id, NULL); if (!is_oid_nil(rid)) schema = table_funcs.column_find_value(m->session->tr, schemas_name, rid); if(!stack_set_string(m, "current_schema", schema)) return NULL; return schema; }
str sql_drop_role(mvc *m, str auth) { oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_column *auth_name = find_sql_column(auths, "name"); rid = table_funcs.column_find_row(m->session->tr, auth_name, auth, NULL); if (rid == oid_nil) return sql_message("0P000!DROP ROLE: no such role '%s'", auth); table_funcs.table_delete(m->session->tr, auths, rid); return NULL; }
str sql_create_role(mvc *m, str auth, int grantor) { oid id; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_column *auth_name = find_sql_column(auths, "name"); if (table_funcs.column_find_row(m->session->tr, auth_name, auth, NULL) != oid_nil) return sql_message("0P000!CREATE ROLE: role '%s' already exists", auth); id = store_next_oid(); table_funcs.table_insert(m->session->tr, auths, &id, auth, &grantor); return NULL; }
void SQLtrans(mvc *m) { m->caching = m->cache; if (!m->session->active) { sql_session *s; mvc_trans(m); s = m->session; if (!s->schema) { s->schema_name = monet5_user_get_def_schema(m, m->user_id); assert(s->schema_name); s->schema = find_sql_schema(s->tr, s->schema_name); assert(s->schema); } } }
int mvc_set_schema(mvc *m, char *schema) { int ret = 0; sql_schema *s = find_sql_schema(m->session->tr, schema); if (s) { if (m->session->schema_name) _DELETE(m->session->schema_name); m->session->schema_name = _STRDUP(schema); m->type = Q_TRANS; if (m->session->active) m->session->schema = s; ret = 1; } return ret; }
/*Function to create a connection*/ int sql_trans_connect_catalog(sql_trans *tr, const char *server, int port, const char *db, const char *db_alias, const char *user, const char *passwd, const char *lang) { int id = store_next_oid(), port_l = port; sql_schema *s = find_sql_schema(tr, "sys"); sql_table *t = find_sql_table(s, "connections"); sql_column *c_server = find_sql_column(t, "server"); sql_column *c_db = find_sql_column(t, "db"); sql_column *c_db_alias = find_sql_column(t, "db_alias"); if ((table_funcs.column_find_row(tr, c_server, server, c_db, db, NULL) == oid_nil) && (table_funcs.column_find_row(tr, c_db_alias, db_alias, NULL) == oid_nil)) { table_funcs.table_insert(tr, t, &id, server, &port_l, db, db_alias, user, passwd, lang); return id; } return 0; }
int sql_privilege(mvc *m, int auth_id, int obj_id, int priv, int sub) { oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *privs = find_sql_table(sys, "privileges"); sql_column *priv_obj = find_sql_column(privs, "obj_id"); sql_column *priv_auth = find_sql_column(privs, "auth_id"); sql_column *priv_priv = find_sql_column(privs, "privileges"); int res = 0; (void) sub; rid = table_funcs.column_find_row(m->session->tr, priv_obj, &obj_id, priv_auth, &auth_id, priv_priv, &priv, NULL); if (rid != oid_nil) { /* found priv */ res = priv; } return res; }
sql_schema * mvc_bind_schema(mvc *m, const char *sname) { sql_trans *tr = m->session->tr; sql_schema *s; if (!tr) return NULL; /* declared tables */ if (strcmp(sname, str_nil) == 0) sname = dt_schema; s = find_sql_schema(tr, sname); if (!s) return NULL; if (mvc_debug) fprintf(stderr, "#mvc_bind_schema %s\n", sname); return s; }
/*Function to drop the connection*/ int sql_trans_disconnect_catalog(sql_trans *tr, const char *db_alias) { oid rid = oid_nil; int id = 0; sql_schema *s = find_sql_schema(tr, "sys"); sql_table *t = find_sql_table(s, "connections"); sql_column *col_db_alias = find_sql_column(t, "db_alias"); sql_column *col_id = find_sql_column(t, "id"); rid = table_funcs.column_find_row(tr, col_db_alias, db_alias, NULL); if (rid != oid_nil) { id = *(int *) table_funcs.column_find_value(tr, col_id, rid); table_funcs.table_delete(tr, t, rid); } else { id = 0; } return id; }
int sql_find_auth_schema(mvc *m, str auth) { int res = -1; oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *users = find_sql_table(sys, "db_user_info"); sql_column *users_name = find_sql_column(users, "name"); rid = table_funcs.column_find_row(m->session->tr, users_name, auth, NULL); if (!is_oid_nil(rid)) { sql_column *users_schema = find_sql_column(users, "default_schema"); int *p = (int *) table_funcs.column_find_value(m->session->tr, users_schema, rid); if (p) { res = *p; _DELETE(p); } } return res; }
int sql_find_schema(mvc *m, str schema) { int schema_id = -1; oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *schemas = find_sql_table(sys, "schemas"); sql_column *schemas_name = find_sql_column(schemas, "name"); rid = table_funcs.column_find_row(m->session->tr, schemas_name, schema, NULL); if (rid != oid_nil) { sql_column *schemas_id = find_sql_column(schemas, "id"); int *p = (int *) table_funcs.column_find_value(m->session->tr, schemas_id, rid); if (p) { schema_id = *p; _DELETE(p); } } return schema_id; }
int sql_find_auth(mvc *m, str auth) { int res = -1; oid rid; sql_schema *sys = find_sql_schema(m->session->tr, "sys"); sql_table *auths = find_sql_table(sys, "auths"); sql_column *auths_name = find_sql_column(auths, "name"); rid = table_funcs.column_find_row(m->session->tr, auths_name, auth, NULL); if (rid != oid_nil) { sql_column *auths_id = find_sql_column(auths, "id"); int *p = (int *) table_funcs.column_find_value(m->session->tr, auths_id, rid); if (p) { res = *p; _DELETE(p); } } return res; }
/* release all savepoints up including the given named savepoint * but keep the current changes. * */ int mvc_release(mvc *m, const char *name) { int ok = SQL_OK; int res = Q_TRANS; sql_trans *tr = m->session->tr; assert(tr); assert(m->session->active); /* only release active transactions */ if (mvc_debug) fprintf(stderr, "#mvc_release %s\n", (name) ? name : ""); if (!name) mvc_rollback(m, 0, name); while (tr && (!tr->name || strcmp(tr->name, name) != 0)) tr = tr->parent; if (!tr || !tr->name || strcmp(tr->name, name) != 0) { (void)sql_error(m, 010, "release savepoint %s doesn't exists", name); m->session->status = -1; return -1; } tr = m->session->tr; store_lock(); while (ok == SQL_OK && (!tr->name || strcmp(tr->name, name) != 0)) { /* commit all intermediate savepoints */ if (sql_trans_commit(tr) != SQL_OK) GDKfatal("release savepoints should not fail"); tr = sql_trans_destroy(tr); } tr->name = NULL; store_unlock(); m->session->tr = tr; m->session->schema = find_sql_schema(m->session->tr, m->session->schema_name); m->type = res; return res; }
static void * monet5_schema_user_dependencies(ptr _trans, int schema_id) { rids *A, *U; sql_trans *tr = (sql_trans *) _trans; sql_schema *s = find_sql_schema(tr, "sys"); sql_table *auths = find_sql_table(s, "auths"); sql_column *auth_name = find_sql_column(auths, "name"); sql_table *users = find_sql_table(s, "db_user_info"); sql_column *users_name = find_sql_column(users, "name"); sql_column *users_sch = find_sql_column(users, "default_schema"); /* select users with given schema */ U = table_funcs.rids_select(tr, users_sch, &schema_id, &schema_id, NULL); /* select all authorization ids */ A = table_funcs.rids_select(tr, auth_name, NULL, NULL); /* join all authorization with the selected users */ A = table_funcs.rids_join(tr, A, auth_name, U, users_name); table_funcs.rids_destroy(U); return A; }
static str monet5_create_user(ptr _mvc, str user, str passwd, char enc, str fullname, sqlid schema_id, sqlid grantorid) { mvc *m = (mvc *) _mvc; oid uid = 0; bat bid = 0; str ret; sqlid user_id; str pwd; sql_schema *s = find_sql_schema(m->session->tr, "sys"); sql_table *db_user_info, *auths; Client c = MCgetClient(m->clientid); if (!enc) { pwd = mcrypt_BackendSum(passwd, strlen(passwd)); if (pwd == NULL) { BBPunfix(bid); throw(MAL, "sql.create_user", SQLSTATE(42000) "Crypt backend hash not found"); } } else { pwd = passwd; } /* add the user to the M5 authorisation administration */ ret = AUTHaddUser(&uid, c, user, pwd); if (!enc) free(pwd); if (ret != MAL_SUCCEED) return ret; user_id = store_next_oid(); db_user_info = find_sql_table(s, "db_user_info"); auths = find_sql_table(s, "auths"); table_funcs.table_insert(m->session->tr, db_user_info, user, fullname, &schema_id); table_funcs.table_insert(m->session->tr, auths, &user_id, user, &grantorid); return NULL; }
str monet5_user_set_def_schema(mvc *m, oid user) { oid rid; sqlid schema_id; sql_schema *sys = NULL; sql_table *user_info = NULL; sql_column *users_name = NULL; sql_column *users_schema = NULL; sql_table *schemas = NULL; sql_column *schemas_name = NULL; sql_column *schemas_id = NULL; sql_table *auths = NULL; sql_column *auths_name = NULL; void *p = 0; str schema = NULL; str username = NULL; if (m->debug &1) fprintf(stderr, "monet5_user_set_def_schema " OIDFMT "\n", user); mvc_trans(m); sys = find_sql_schema(m->session->tr, "sys"); user_info = find_sql_table(sys, "db_user_info"); users_name = find_sql_column(user_info, "name"); users_schema = find_sql_column(user_info, "default_schema"); rid = table_funcs.column_find_row(m->session->tr, users_name, username, NULL); if (!is_oid_nil(rid)) p = table_funcs.column_find_value(m->session->tr, users_schema, rid); assert(p); schema_id = *(sqlid *) p; _DELETE(p); schemas = find_sql_table(sys, "schemas"); schemas_name = find_sql_column(schemas, "name"); schemas_id = find_sql_column(schemas, "id"); auths = find_sql_table(sys, "auths"); auths_name = find_sql_column(auths, "name"); rid = table_funcs.column_find_row(m->session->tr, schemas_id, &schema_id, NULL); if (!is_oid_nil(rid)) schema = table_funcs.column_find_value(m->session->tr, schemas_name, rid); /* only set schema if user is found */ rid = table_funcs.column_find_row(m->session->tr, auths_name, username, NULL); if (!is_oid_nil(rid)) { sql_column *auths_id = find_sql_column(auths, "id"); int id; p = table_funcs.column_find_value(m->session->tr, auths_id, rid); id = *(int *) p; _DELETE(p); m->user_id = m->role_id = id; } else { schema = NULL; } if (!schema || !mvc_set_schema(m, schema)) { if (m->session->active) mvc_rollback(m, 0, NULL); return NULL; } /* reset the user and schema names */ if(!stack_set_string(m, "current_schema", schema) || !stack_set_string(m, "current_user", username) || !stack_set_string(m, "current_role", username)) { schema = NULL; } GDKfree(username); mvc_rollback(m, 0, NULL); return schema; }
int mvc_commit(mvc *m, int chain, const char *name) { sql_trans *cur, *tr = m->session->tr; int ok = SQL_OK;//, wait = 0; assert(tr); assert(m->session->active); /* only commit an active transaction */ if (mvc_debug) fprintf(stderr, "#mvc_commit %s\n", (name) ? name : ""); if (m->session->status < 0) { (void)sql_error(m, 010, "40000!COMMIT: transaction is aborted, will ROLLBACK instead"); mvc_rollback(m, chain, name); return -1; } /* savepoint then simply make a copy of the current transaction */ if (name && name[0] != '\0') { sql_trans *tr = m->session->tr; if (mvc_debug) fprintf(stderr, "#mvc_savepoint\n"); store_lock(); m->session->tr = sql_trans_create(m->session->stk, tr, name); store_unlock(); m->type = Q_TRANS; if (m->qc) /* clean query cache, protect against concurrent access on the hash tables (when functions already exists, concurrent mal will build up the hash (not copied in the trans dup)) */ qc_clean(m->qc); m->session->schema = find_sql_schema(m->session->tr, m->session->schema_name); if (mvc_debug) fprintf(stderr, "#mvc_commit %s done\n", name); return 0; } /* first release all intermediate savepoints */ cur = tr; tr = tr->parent; if (tr->parent) { store_lock(); while (tr->parent != NULL && ok == SQL_OK) { tr = sql_trans_destroy(tr); } store_unlock(); } cur -> parent = tr; tr = cur; store_lock(); /* if there is nothing to commit reuse the current transaction */ if (tr->wtime == 0) { if (!chain) sql_trans_end(m->session); m->type = Q_TRANS; if (mvc_debug) fprintf(stderr, "#mvc_commit %s done\n", (name) ? name : ""); store_unlock(); return 0; } /* while (tr->schema_updates && store_nr_active > 1) { store_unlock(); MT_sleep_ms(100); wait += 100; if (wait > 1000) { (void)sql_error(m, 010, "40000!COMMIT: transaction is aborted because of DDL concurrency conflicts, will ROLLBACK instead"); mvc_rollback(m, chain, name); return -1; } store_lock(); } * */ /* validation phase */ if (sql_trans_validate(tr)) { if ((ok = sql_trans_commit(tr)) != SQL_OK) { char *msg = sql_message("40000!COMMIT: transaction commit failed (perhaps your disk is full?) exiting (kernel error: %s)", GDKerrbuf); GDKfatal("%s", msg); _DELETE(msg); } } else { store_unlock(); (void)sql_error(m, 010, "40000!COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead"); mvc_rollback(m, chain, name); return -1; } sql_trans_end(m->session); if (chain) sql_trans_begin(m->session); store_unlock(); m->type = Q_TRANS; if (mvc_debug) fprintf(stderr, "#mvc_commit %s done\n", (name) ? name : ""); return ok; }