Changeset: 508bb692ce38 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=508bb692ce38
Added Files:
sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.SQL.py
sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.err
sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.stable.out
Modified Files:
sql/test/BugTracker-2020/Tests/All
Branch: Oct2020
Log Message:
Added test for Bug 7026
diffs (151 lines):
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
@@ -39,3 +39,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,51 @@
+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);
+! 42000!INSERT INTO: insufficient privileges for user 'bruce' to insert into
table 'test'
+
+UPDATE test SET d = 42 WHERE d <> 42;
+! 42000!UPDATE: insufficient privileges for user 'bruce' to update table 'test'
+
+DELETE FROM test WHERE d = 42;
+! 42000!DELETE FROM: insufficient privileges for user 'bruce' to delete from
table 'test'
+
+SELECT * FROM test;
+! 42000!SELECT: access denied for bruce to table 's1.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
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
