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;
}
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;
}
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;
}
/*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;
}
/*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;
}
sql_column *
mvc_bind_column(mvc *m, sql_table *t, const char *cname)
{
sql_column *c;
(void)m;
c = find_sql_column(t, cname);
if (!c)
return NULL;
if (mvc_debug)
fprintf(stderr, "#mvc_bind_column %s.%s\n", t->base.name, cname);
return c;
}
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
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;
}
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 void
sql_delete_priv(mvc *sql, int auth_id, int obj_id, int privilege, int grantor, int grantable)
{
sql_schema *ss = mvc_bind_schema(sql, "sys");
sql_table *privs = mvc_bind_table(sql, ss, "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");
sql_trans *tr = sql->session->tr;
rids *A;
oid rid = oid_nil;
(void) grantor;
(void) grantable;
/* select privileges of this auth_id, privilege, obj_id */
A = table_funcs.rids_select(tr, priv_auth, &auth_id, &auth_id, priv_priv, &privilege, &privilege, priv_obj, &obj_id, &obj_id, NULL );
/* remove them */
for(rid = table_funcs.rids_next(A); rid != oid_nil; rid = table_funcs.rids_next(A))
table_funcs.table_delete(tr, privs, rid);
table_funcs.rids_destroy(A);
}
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;
}
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
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;
}
