Changeset: dd451aadf1df for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=dd451aadf1df
Modified Files:
sql/test/BugTracker-2020/Tests/All
Branch: mtest
Log Message:
merged from default
diffs (219 lines):
diff --git a/sql/server/rel_updates.c b/sql/server/rel_updates.c
--- a/sql/server/rel_updates.c
+++ b/sql/server/rel_updates.c
@@ -894,7 +894,7 @@ rel_update(mvc *sql, sql_rel *t, sql_rel
sql_exp *
update_check_column(mvc *sql, sql_table *t, sql_column *c, sql_exp *v, sql_rel
*r, char *cname, const char *action)
{
- if (!table_privs(sql, t, PRIV_UPDATE) && !sql_privilege(sql,
sql->user_id, c->base.id, PRIV_UPDATE))
+ if (!table_privs(sql, t, PRIV_UPDATE) && sql_privilege(sql,
sql->user_id, c->base.id, PRIV_UPDATE) < 0)
return sql_error(sql, 02, SQLSTATE(42000) "%s: insufficient
privileges for user '%s' to update table '%s' on column '%s'", action,
get_string_global_var(sql, "current_user"), t->base.name, cname);
if (!v || (v = exp_check_type(sql, &c->type, r, v, type_equal)) == NULL)
return NULL;
diff --git a/sql/server/sql_privileges.c b/sql/server/sql_privileges.c
--- a/sql/server/sql_privileges.c
+++ b/sql/server/sql_privileges.c
@@ -119,7 +119,7 @@ sql_grant_global_privs( mvc *sql, char *
if (grantee_id <= 0)
throw(SQL,"sql.grant_global",SQLSTATE(01007) "GRANT: User/role
'%s' unknown", grantee);
/* first check if privilege isn't already given */
- if ((sql_privilege(sql, grantee_id, GLOBAL_OBJID, privs)))
+ if ((sql_privilege(sql, grantee_id, GLOBAL_OBJID, privs) >= 0))
throw(SQL,"sql.grant_global",SQLSTATE(01007) "GRANT: User/role
'%s' already has this privilege", grantee);
sql_insert_priv(sql, grantee_id, GLOBAL_OBJID, privs, grantor, grant);
tr->schema_updates++;
@@ -168,13 +168,13 @@ sql_grant_table_privs( mvc *sql, char *g
throw(SQL,"sql.grant_table", SQLSTATE(01007) "GRANT: User/role
'%s' unknown", grantee);
/* first check if privilege isn't already given */
if ((privs == all &&
- (sql_privilege(sql, grantee_id, t->base.id, PRIV_SELECT) ||
- sql_privilege(sql, grantee_id, t->base.id, PRIV_UPDATE) ||
- sql_privilege(sql, grantee_id, t->base.id, PRIV_INSERT) ||
- sql_privilege(sql, grantee_id, t->base.id, PRIV_DELETE) ||
- sql_privilege(sql, grantee_id, t->base.id, PRIV_TRUNCATE))) ||
- (privs != all && !c && sql_privilege(sql, grantee_id, t->base.id,
privs)) ||
- (privs != all && c && sql_privilege(sql, grantee_id, c->base.id,
privs))) {
+ (sql_privilege(sql, grantee_id, t->base.id, PRIV_SELECT) >= 0 ||
+ sql_privilege(sql, grantee_id, t->base.id, PRIV_UPDATE) >= 0 ||
+ sql_privilege(sql, grantee_id, t->base.id, PRIV_INSERT) >= 0 ||
+ sql_privilege(sql, grantee_id, t->base.id, PRIV_DELETE) >= 0 ||
+ sql_privilege(sql, grantee_id, t->base.id, PRIV_TRUNCATE) >= 0)) ||
+ (privs != all && !c && sql_privilege(sql, grantee_id, t->base.id,
privs) >= 0) ||
+ (privs != all && c && sql_privilege(sql, grantee_id, c->base.id,
privs) >= 0)) {
throw(SQL, "sql.grant", SQLSTATE(01007) "GRANT: User/role '%s'
already has this privilege", grantee);
}
if (privs == all) {
@@ -215,7 +215,7 @@ sql_grant_func_privs( mvc *sql, char *gr
if (grantee_id <= 0)
throw(SQL, "sql.grant_func", SQLSTATE(01007) "GRANT: User/role
'%s' unknown", grantee);
/* first check if privilege isn't already given */
- if (sql_privilege(sql, grantee_id, f->base.id, privs))
+ if (sql_privilege(sql, grantee_id, f->base.id, privs) >= 0)
throw(SQL,"sql.grant", SQLSTATE(01007) "GRANT: User/role '%s'
already has this privilege", grantee);
sql_insert_priv(sql, grantee_id, f->base.id, privs, grantor, grant);
tr->schema_updates++;
@@ -428,7 +428,7 @@ int
sql_privilege(mvc *m, sqlid auth_id, sqlid obj_id, int priv)
{
oid rid = sql_privilege_rid(m, auth_id, obj_id, priv);
- int res = 0;
+ int res = -1;
if (!is_oid_nil(rid)) {
/* found priv */
@@ -513,7 +513,7 @@ role_granting_privs(mvc *m, oid role_rid
owner_id = table_funcs.column_find_sqlid(m->session->tr, auths_grantor,
role_rid);
if (owner_id == grantor_id)
return true;
- if (sql_privilege(m, grantor_id, role_id, PRIV_ROLE_ADMIN))
+ if (sql_privilege(m, grantor_id, role_id, PRIV_ROLE_ADMIN) ==
PRIV_ROLE_ADMIN)
return true;
/* check for grant rights in the privs table */
return false;
diff --git a/sql/test/BugTracker-2020/Tests/All
b/sql/test/BugTracker-2020/Tests/All
--- a/sql/test/BugTracker-2020/Tests/All
+++ b/sql/test/BugTracker-2020/Tests/All
@@ -40,3 +40,4 @@ savepoints_crash_mserver5_2.Bug-7021
transaction_with_unreleased_savepoint.Bug-7022
view_with_aggr_column.Bug-7023
delete-transaction-loose-inserts.Bug-7024
+revokeRoleUserLoggedIN.Bug-7026
diff --git
a/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.SQL.py
b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.SQL.py
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.SQL.py
@@ -0,0 +1,65 @@
+###
+# Assess that when the role of a user, who is currently logged in and has
+# assumed that role, has been revoked, the user immedately lose all
+# privileges associated with that role.
+###
+
+import sys, time, pymonetdb, os
+
+def connect(username, password):
+ return pymonetdb.connect(database = os.getenv('TSTDB'),
+ hostname = 'localhost',
+ port = int(os.getenv('MAPIPORT')),
+ username = username,
+ password = password,
+ autocommit = True)
+
+def query(conn, sql):
+ print(sql)
+ cur = conn.cursor()
+ try:
+ cur.execute(sql)
+ except pymonetdb.OperationalError as e:
+ print("!", e)
+ return
+ r = cur.fetchall()
+ cur.close()
+ print(r)
+
+def run(conn, sql):
+ print(sql)
+ try:
+ r = conn.execute(sql)
+ except pymonetdb.OperationalError as e:
+ print("!", e)
+ return
+ print('# OK')
+
+
+c1 = connect('monetdb', 'monetdb')
+# Create a user, schema and role
+run(c1, 'CREATE SCHEMA s1;')
+run(c1, 'CREATE USER bruce WITH PASSWORD \'bruce\' name \'willis\' schema s1;')
+run(c1, 'CREATE TABLE s1.test(d int);')
+run(c1, 'CREATE ROLE role1;')
+run(c1, 'GRANT ALL ON s1.test to role1;')
+run(c1, 'GRANT role1 TO bruce;')
+
+# Login as `bruce` and use `role1`
+c2 = connect('bruce', 'bruce')
+run(c2, 'SET role role1;')
+run(c2, 'INSERT INTO test VALUES (24), (42);')
+run(c2, 'UPDATE test SET d = 42 WHERE d <> 42;')
+run(c2, 'DELETE FROM test WHERE d = 42;')
+query(c2, 'SELECT * FROM test;')
+
+# Revoke `role1` from `bruce`
+run(c1, 'REVOKE role1 FROM bruce;')
+
+# `bruce` should not be able to access `test` again:
+run(c2, 'INSERT INTO test VALUES (24), (42);')
+run(c2, 'UPDATE test SET d = 42 WHERE d <> 42;')
+run(c2, 'DELETE FROM test WHERE d = 42;')
+query(c2, 'SELECT * FROM test;')
+query(c2, 'SET role role1; -- verifies role1 is gone')
+
diff --git
a/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.err
b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.err
@@ -0,0 +1,12 @@
+stderr of test 'revokeRoleUserLoggedIN.Bug-7026` in directory
'sql/test/BugTracker-2020` itself:
+
+
+# 14:34:36 >
+# 14:34:36 > "/usr/bin/python3.8" "revokeRoleUserLoggedIN.Bug-7026.SQL.py"
"revokeRoleUserLoggedIN.Bug-7026"
+# 14:34:36 >
+
+
+# 14:34:37 >
+# 14:34:37 > "Done."
+# 14:34:37 >
+
diff --git
a/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.out
b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.out
@@ -0,0 +1,47 @@
+stdout of test 'revokeRoleUserLoggedIN.Bug-7026` in directory
'sql/test/BugTracker-2020` itself:
+
+
+# 14:34:36 >
+# 14:34:36 > "/usr/bin/python3.8" "revokeRoleUserLoggedIN.Bug-7026.SQL.py"
"revokeRoleUserLoggedIN.Bug-7026"
+# 14:34:36 >
+
+CREATE SCHEMA s1;
+# OK
+CREATE USER bruce WITH PASSWORD 'bruce' name 'willis' schema s1;
+# OK
+CREATE TABLE s1.test(d int);
+# OK
+CREATE ROLE role1;
+# OK
+GRANT ALL ON s1.test to role1;
+# OK
+GRANT role1 TO bruce;
+# OK
+SET role role1;
+# OK
+INSERT INTO test VALUES (24), (42);
+# OK
+UPDATE test SET d = 42 WHERE d <> 42;
+# OK
+DELETE FROM test WHERE d = 42;
+# OK
+SELECT * FROM test;
+[]
+REVOKE role1 FROM bruce;
+# OK
+INSERT INTO test VALUES (24), (42);
+# OK
+UPDATE test SET d = 42 WHERE d <> 42;
+# OK
+DELETE FROM test WHERE d = 42;
+# OK
+SELECT * FROM test;
+[]
+SET role role1; -- verifies role1 is gone
+! 42000!Role (role1) missing
+
+
+# 14:34:37 >
+# 14:34:37 > "Done."
+# 14:34:37 >
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
