Changeset: 85ea3faf5d24 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=85ea3faf5d24
Modified Files:
sql/test/Users/Tests/view_privs.SQL.py
Branch: default
Log Message:
Extended the VIEW access tests to check that GRANT SELECT on <view> also works
properly on individual columns of a view
diffs (52 lines):
diff --git a/sql/test/Users/Tests/view_privs.SQL.py
b/sql/test/Users/Tests/view_privs.SQL.py
--- a/sql/test/Users/Tests/view_privs.SQL.py
+++ b/sql/test/Users/Tests/view_privs.SQL.py
@@ -1,6 +1,7 @@
###
# Check indirect VIEW privilege:
# A user with only(!) SELECT privilege on VIEWs of tables can use the VIEWs.
+# A user can view the columns of a VIEW to which it has been granted access.
# Check that if a user has CREATE VIEW privilege in one schema (i.e. is an
# schema owner) but not in another schema, then the user can CREATE VIEWs
# on tables in this schema but not in the other schema.
@@ -27,7 +28,6 @@ with SQLTestCase() as mdb:
mdb.execute("SELECT * FROM
your_view;").assertSucceeded().assertDataResultMatch([('alice', 1980,
'********A'), ('bob', 1970, '********0')])
# grant indirect view right to my_user
mdb.execute("GRANT SELECT on your_view to my_user;").assertSucceeded()
- mdb.execute("SET SCHEMA sys;").assertSucceeded()
with SQLTestCase() as tc:
@@ -47,11 +47,28 @@ with SQLTestCase() as mdb:
# my_user cannot create VIEWs on your_table
tc.execute("CREATE VIEW your_view AS SELECT * FROM
your_schema.your_table;").assertFailed(err_code="42000", err_message="SELECT:
access denied for my_user to table 'your_schema.your_table'")
+ # Check that we can revoke select from a whole view, but regrant select
+ # on some columns of the view
+ mdb.execute("REVOKE SELECT on your_view FROM
my_user;").assertSucceeded()
+ tc.execute("SELECT * FROM your_schema.your_view;")\
+ .assertFailed(err_code="42000", err_message="SELECT: access denied
for my_user to view 'your_schema.your_view'")
+ mdb.execute("GRANT SELECT (name) on your_view to
my_user;").assertSucceeded()
+ tc.execute("SELECT name FROM
your_schema.your_view;").assertSucceeded()\
+ .assertDataResultMatch([('alice',), ('bob',)])
+ tc.execute("SELECT * FROM your_schema.your_view;").assertSucceeded()\
+ .assertDataResultMatch([('alice',), ('bob',)])
+ mdb.execute("GRANT SELECT (ssn) on your_view to
my_user;").assertSucceeded()
+ tc.execute("SELECT name, ssn FROM
your_schema.your_view;").assertSucceeded()\
+ .assertDataResultMatch([('alice', '********A'), ('bob',
'********0')])
+ tc.execute("SELECT * FROM your_schema.your_view;").assertSucceeded()\
+ .assertDataResultMatch([('alice', '********A'), ('bob',
'********0')])
+ tc.execute("SELECT yr FROM your_schema.your_view;")\
+ .assertFailed(err_code="42000", err_message="SELECT: identifier
'yr' unknown")
+
# clean up
- mdb.execute("DROP VIEW your_schema.your_view;").assertSucceeded()
- mdb.execute("DROP TABLE your_schema.your_table;").assertSucceeded()
+ mdb.execute("SET SCHEMA sys;").assertSucceeded()
mdb.execute("DROP USER my_user;").assertSucceeded()
mdb.execute("DROP ROLE my_role;").assertSucceeded()
- mdb.execute("DROP SCHEMA my_schema;").assertSucceeded()
- mdb.execute("DROP SCHEMA your_schema;").assertSucceeded()
+ mdb.execute("DROP SCHEMA my_schema CASCADE;").assertSucceeded()
+ mdb.execute("DROP SCHEMA your_schema CASCADE;").assertSucceeded()
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
