Changeset: e3f15abf9470 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e3f15abf9470
Removed Files:
sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
Modified Files:
sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
Branch: default
Log Message:
converted, cleaned up, extended
diffs (145 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.py
--- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
+++ b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py
@@ -1,61 +1,52 @@
-import sys, os, pymonetdb
+###
+# 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.
+###
-db = os.getenv("TSTDB")
-port = int(os.getenv("MAPIPORT"))
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='monetdb', password='monetdb')
-cur1 = client1.cursor()
-cur1.execute('''
-START TRANSACTION;
-CREATE schema "myschema";
-CREATE TABLE "myschema"."test" ("id" integer, "name" varchar(20));
-INSERT INTO "myschema"."test" ("id", "name") VALUES (1,'Tom'),(2,'Karen');
-CREATE USER myuser WITH UNENCRYPTED PASSWORD 'Test123' NAME 'Hulk' SCHEMA
"myschema";
-GRANT SELECT ON "myschema"."test" TO myuser;
-COMMIT;
-''')
-cur1.execute('SELECT "name" FROM "myschema"."test";')
-if cur1.fetchall() != [('Tom',), ('Karen',)]:
- sys.stderr.write('Expected result: [(\'Tom\',), (\'Karen\',)]')
-cur1.close()
-client1.close()
+from MonetDBtesting.sqltest import SQLTestCase
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='myuser', password='Test123')
-cur1 = client1.cursor()
-cur1.execute('SELECT "id", "name" FROM "myschema"."test";')
-if cur1.fetchall() != [(1,'Tom'),(2,'Karen')]:
- sys.stderr.write('Expected result: [(1,\'Tom\'),(2,\'Karen\')]')
-cur1.close()
-client1.close()
+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()
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='monetdb', password='monetdb')
-cur1 = client1.cursor()
-cur1.execute('''
-REVOKE SELECT ON "myschema"."test" FROM myuser;
-GRANT SELECT ("name") ON "myschema"."test" TO myuser;
-''')
-cur1.close()
-client1.close()
+ 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)
+ mdb.execute("GRANT SELECT (id) ON test TO myuser;").assertSucceeded()
+ tc.execute("select id from test").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").assertDataResultMatch([(1,),(2,)])
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='myuser', password='Test123')
-cur1 = client1.cursor()
-try:
- cur1.execute('SELECT "id", "name" FROM "myschema"."test";')
- sys.stderr.write("Exception expected")
-except pymonetdb.DatabaseError as e:
- if "identifier 'id' unknown" not in str(e):
- sys.stderr.write("Error: identifier 'id' unknown expected")
-cur1.execute('SELECT "name" FROM "myschema"."test";')
-if cur1.fetchall() != [('Tom',), ('Karen',)]:
- sys.stderr.write('Expected result: [(\'Tom\',), (\'Karen\',)]')
-cur1.close()
-client1.close()
+ # 'myuser' can SELECT test(id, address)
+ mdb.execute("GRANT SELECT (address) ON test TO
myuser;").assertSucceeded()
+ tc.execute("select id from test").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").assertDataResultMatch([('planet',),('earth',)])
+ tc.execute("select * from
test").assertDataResultMatch([(1,'planet'),(2,'earth')])
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='monetdb', password='monetdb')
-cur1 = client1.cursor()
-cur1.execute('''
-DROP USER myuser;
-DROP SCHEMA "myschema" CASCADE;
-''')
-cur1.close()
-client1.close()
+ # '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").assertDataResultMatch([('planet',),('earth',)])
+ tc.execute("select * from
test").assertDataResultMatch([('planet',),('earth',)])
+
+ # clean up
+ mdb.execute("SET SCHEMA sys;").assertSucceeded()
+ mdb.execute("DROP USER myuser;").assertSucceeded()
+ mdb.execute("DROP SCHEMA myschema CASCADE;").assertSucceeded()
diff --git
a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
deleted file mode 100644
--- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
+++ /dev/null
@@ -1,12 +0,0 @@
-stderr of test 'grant-select-column.Bug-6765` in directory
'sql/test/BugTracker-2019` itself:
-
-
-# 11:35:12 >
-# 11:35:12 > "/usr/bin/python3" "grant-select-column.Bug-6765.py"
"grant-select-column.Bug-6765"
-# 11:35:12 >
-
-
-# 11:35:12 >
-# 11:35:12 > "Done."
-# 11:35:12 >
-
diff --git
a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
deleted file mode 100644
--- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
+++ /dev/null
@@ -1,12 +0,0 @@
-stdout of test 'grant-select-column.Bug-6765` in directory
'sql/test/BugTracker-2019` itself:
-
-
-# 11:34:49 >
-# 11:34:49 > "/usr/bin/python3" "grant-select-column.Bug-6765.py"
"grant-select-column.Bug-6765"
-# 11:34:49 >
-
-
-# 11:34:49 >
-# 11:34:49 > "Done."
-# 11:34:49 >
-
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
