Changeset: a5ea61888e68 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/a5ea61888e68
Added Files:
sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.test
sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.test
sql/test/BugTracker-2021/Tests/remote-table-large.Bug-7178.test.in
sql/test/SQLancer/Tests/sqlancer19.test.in
sql/test/SQLancer/Tests/sqlancer20.test.in
sql/test/SQLancer/Tests/sqlancer21.test.in
Removed Files:
sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.py
sql/test/BugTracker-2021/Tests/remote-table-large.Bug-7178.SQL.py
sql/test/SQLancer/Tests/sqlancer19.SQL.py
sql/test/SQLancer/Tests/sqlancer20.SQL.py
sql/test/SQLancer/Tests/sqlancer21.SQL.py
Modified Files:
testing/Mtest.py.in
Branch: Sep2022
Log Message:
Converted some tests to .test format.
diffs (truncated from 2476 to 300 lines):
diff --git a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.test
rename from sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
rename to sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.test
--- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
+++ b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.test
@@ -1,52 +1,134 @@
-###
-# Check that when a user is granted (select) access to some columns in a table,
-# the user can indeed access those columns.
-# In addition, check that after the access to some of the granted columns has
-# been revoked, the user can access the remaining columns.
-###
+statement ok
+CREATE schema myschema
+
+statement ok
+CREATE USER myuser WITH UNENCRYPTED PASSWORD 'Test123' NAME 'Hulk' SCHEMA
myschema
+
+statement ok
+SET SCHEMA myschema
+statement ok
+CREATE TABLE test (id integer, name varchar(20), address varchar(20))
+
+statement ok
+INSERT INTO test (id, name,address) VALUES (1,'Tom', 'planet'),(2,'Karen',
'earth')
+
+# 'myuser' cannot SELECT before GRANT and after REVOKE, can SELECT after GRANT
+@connection(id=tc, username=myuser, password=Test123)
+statement error 42000!SELECT: access denied for myuser to table 'myschema.test'
+select * from test
+
+statement ok
+GRANT SELECT ON test TO myuser
-from MonetDBtesting.sqltest import SQLTestCase
+@connection(id=tc)
+query ITT rowsort
+select * from test
+----
+1
+Tom
+planet
+2
+Karen
+earth
+
+statement ok
+REVOKE SELECT ON test FROM myuser
+
+@connection(id=tc)
+statement error 42000!SELECT: access denied for myuser to table 'myschema.test'
+select * from test
+
+# 'myuser' can SELECT test(id)
+statement ok
+GRANT SELECT (id) ON test TO myuser
-with SQLTestCase() as mdb:
- mdb.connect(username="monetdb", password="monetdb")
- mdb.execute("CREATE schema myschema;").assertSucceeded()
- mdb.execute("CREATE USER myuser WITH UNENCRYPTED PASSWORD 'Test123' NAME
'Hulk' SCHEMA myschema;").assertSucceeded()
- mdb.execute("SET SCHEMA myschema;").assertSucceeded()
- mdb.execute("CREATE TABLE test (id integer, name varchar(20), address
varchar(20));").assertSucceeded()
- mdb.execute("INSERT INTO test (id, name,address) VALUES (1,'Tom',
'planet'),(2,'Karen', 'earth');").assertSucceeded()
+@connection(id=tc)
+query I rowsort
+select id from test
+----
+1
+2
+
+@connection(id=tc)
+statement error 42000!SELECT: identifier 'name' unknown
+select name from test
+
+@connection(id=tc)
+statement error 42000!SELECT: identifier 'address' unknown
+select address from test
+
+@connection(id=tc)
+query I rowsort
+select * from test
+----
+1
+2
- with SQLTestCase() as tc:
- tc.connect(username="myuser", password="Test123")
- # 'myuser' cannot SELECT before GRANT and after REVOKE, can SELECT
after GRANT
- tc.execute("select * from test").assertFailed(err_code='42000',
err_message="SELECT: access denied for myuser to table 'myschema.test'")
- mdb.execute("GRANT SELECT ON test TO myuser;").assertSucceeded()
- tc.execute("select * from test").assertRowCount(2)
- mdb.execute("REVOKE SELECT ON test FROM myuser;").assertSucceeded()
- tc.execute("select * from test").assertFailed(err_code='42000',
err_message="SELECT: access denied for myuser to table 'myschema.test'")
+# 'myuser' can SELECT test(id, address)
+statement ok
+GRANT SELECT (address) ON test TO myuser
+
+@connection(id=tc)
+query I rowsort
+select id from test
+----
+1
+2
+
+@connection(id=tc)
+statement error 42000!SELECT: identifier 'name' unknown
+select name from test
- # 'myuser' can SELECT test(id)
- mdb.execute("GRANT SELECT (id) ON test TO myuser;").assertSucceeded()
- tc.execute("select id from
test").assertSucceeded().assertDataResultMatch([(1,),(2,)])
- tc.execute("select name from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'name' unknown")
- tc.execute("select address from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'address' unknown")
- tc.execute("select * from
test").assertSucceeded().assertDataResultMatch([(1,),(2,)])
+@connection(id=tc)
+query T rowsort
+select address from test
+----
+earth
+planet
+
+@connection(id=tc)
+query IT rowsort
+select * from test
+----
+1
+planet
+2
+earth
- # 'myuser' can SELECT test(id, address)
- mdb.execute("GRANT SELECT (address) ON test TO
myuser;").assertSucceeded()
- tc.execute("select id from
test").assertSucceeded().assertDataResultMatch([(1,),(2,)])
- tc.execute("select name from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'name' unknown")
- tc.execute("select address from
test").assertSucceeded().assertDataResultMatch([('planet',),('earth',)])
- tc.execute("select * from
test").assertSucceeded().assertDataResultMatch([(1,'planet'),(2,'earth')])
+# 'myuser' can only SELECT test(address)
+statement ok
+REVOKE SELECT (id) ON test FROM myuser
+
+@connection(id=tc)
+statement error 42000!SELECT: identifier 'id' unknown
+select id from test
+
+@connection(id=tc)
+statement error 42000!SELECT: identifier 'name' unknown
+select name from test
- # 'myuser' can only SELECT test(address)
- mdb.execute("REVOKE SELECT (id) ON test FROM
myuser;").assertSucceeded()
- tc.execute("select id from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'id' unknown")
- tc.execute("select name from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'name' unknown")
- tc.execute("select address from
test").assertSucceeded().assertDataResultMatch([('planet',),('earth',)])
- tc.execute("select * from
test").assertSucceeded().assertDataResultMatch([('planet',),('earth',)])
+@connection(id=tc)
+query T rowsort
+select address from test
+----
+earth
+planet
- # clean up
- mdb.execute("SET SCHEMA sys;").assertSucceeded()
- mdb.execute("DROP USER myuser;").assertSucceeded()
- mdb.execute("DROP SCHEMA myschema CASCADE;").assertSucceeded()
+@connection(id=tc)
+query T rowsort
+select * from test
+----
+earth
+planet
+
+# clean up
+statement ok
+SET SCHEMA sys
+
+statement ok
+DROP USER myuser
+
+statement ok
+DROP SCHEMA myschema CASCADE
+
diff --git a/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.py
b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.test
rename from sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.py
rename to sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.test
--- a/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.py
+++ b/sql/test/BugTracker-2020/Tests/revokeRoleUserLoggedIN.Bug-7026.test
@@ -1,43 +1,77 @@
-###
-# 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.
-###
+# Create a user, schema and role
+statement ok
+CREATE SCHEMA s1
+
+statement ok
+CREATE USER bruce WITH PASSWORD 'bruce' name 'willis' schema s1
-from MonetDBtesting.sqltest import SQLTestCase
+statement ok
+CREATE TABLE s1.test(d int)
+statement ok
+CREATE ROLE role1
+
+statement ok
+GRANT ALL ON s1.test to role1
-with SQLTestCase() as tc1:
- with SQLTestCase() as tc2:
- tc1.connect(username="monetdb", password="monetdb")
- # Create a user, schema and role
- tc1.execute("""
- CREATE SCHEMA s1;
- CREATE USER bruce WITH PASSWORD 'bruce' name 'willis' schema s1;
- CREATE TABLE s1.test(d int);
- CREATE ROLE role1;
- GRANT ALL ON s1.test to role1;
- GRANT role1 TO bruce;""").assertSucceeded()
+statement ok
+GRANT role1 TO bruce
+
+# Login as `bruce` and use `role1`
+@connection(id=bruce, username=bruce, password=bruce)
+statement ok
+SET role role1
+
+@connection(id=bruce)
+statement ok
+INSERT INTO test VALUES (24), (42)
+
+@connection(id=bruce)
+statement ok
+UPDATE test SET d = 42 WHERE d <> 42
+
+@connection(id=bruce)
+statement ok
+DELETE FROM test WHERE d = 42
- # Login as `bruce` and use `role1`
- tc2.connect(username="bruce", password="bruce")
- tc2.execute('SET role role1;').assertSucceeded()
- tc2.execute('INSERT INTO test VALUES (24),
(42);').assertSucceeded().assertRowCount(2)
- tc2.execute('UPDATE test SET d = 42 WHERE d <>
42;').assertSucceeded().assertRowCount(1)
- tc2.execute('DELETE FROM test WHERE d =
42;').assertSucceeded().assertRowCount(2)
- tc2.execute('SELECT * FROM
test;').assertSucceeded().assertRowCount(0).assertDataResultMatch([])
+@connection(id=bruce)
+query I nosort
+SELECT * FROM test
+----
+
+# Revoke `role1` from `bruce`
+statement ok
+REVOKE role1 FROM bruce
- # Revoke `role1` from `bruce`
- tc1.execute('REVOKE role1 FROM bruce;').assertSucceeded()
+# `bruce` should still be able to access `test`
+@connection(id=bruce)
+statement ok
+INSERT INTO test VALUES (24), (42)
+
+@connection(id=bruce)
+statement ok
+UPDATE test SET d = 42 WHERE d <> 42
- # `bruce` should not be able to access `test` again:
- tc2.execute('INSERT INTO test VALUES (24),
(42);').assertSucceeded().assertRowCount(2)
- tc2.execute('UPDATE test SET d = 42 WHERE d <>
42;').assertSucceeded().assertRowCount(1)
- tc2.execute('DELETE FROM test WHERE d =
42;').assertSucceeded().assertRowCount(2)
- tc2.execute('SELECT * FROM
test;').assertSucceeded().assertRowCount(0).assertDataResultMatch([])
- tc2.execute('SET ROLE role1; -- verifies role1 is
gone').assertFailed(err_message="Role (role1) missing")
+@connection(id=bruce)
+statement ok
+DELETE FROM test WHERE d = 42
+
+@connection(id=bruce)
+query I nosort
+SELECT * FROM test
+----
- tc1.execute("""
- DROP USER bruce;
- DROP SCHEMA s1 CASCADE;
- DROP ROLE role1""").assertSucceeded()
+#verify role1 is gone
+@connection(id=bruce)
+statement error Role (role1) missing
+SET ROLE role1
+
+statement ok
+DROP USER bruce
+
+statement ok
+DROP SCHEMA s1 CASCADE
+
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
