Changeset: a11af30ab8bc for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a11af30ab8bc
Added Files:
sql/test/Users/Tests/view_privs.SQL.py
Modified Files:
sql/test/Users/Tests/All
Branch: indirect-privs
Log Message:
Added test for `GRANT SELECT on <view>`
diffs (73 lines):
diff --git a/sql/test/Users/Tests/All b/sql/test/Users/Tests/All
--- a/sql/test/Users/Tests/All
+++ b/sql/test/Users/Tests/All
@@ -2,6 +2,7 @@ function_privs
role
table
table_privs
+view_privs
unknown_user
createSetUp
withGrantOption
diff --git a/sql/test/Users/Tests/view_privs.SQL.py
b/sql/test/Users/Tests/view_privs.SQL.py
new file mode 100644
--- /dev/null
+++ b/sql/test/Users/Tests/view_privs.SQL.py
@@ -0,0 +1,57 @@
+###
+# Check indirect VIEW privilege:
+# A user with only(!) SELECT privilege on VIEWs of tables can use the VIEWs.
+# 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.
+###
+
+from MonetDBtesting.sqltest import SQLTestCase
+
+with SQLTestCase() as mdb:
+ mdb.connect(username="monetdb", password="monetdb")
+
+ # my_user is the owner of my_schema, hence has all privileges here
+ mdb.execute("CREATE ROLE my_role;").assertSucceeded()
+ mdb.execute("CREATE SCHEMA my_schema AUTHORIZATION
my_role;").assertSucceeded()
+ mdb.execute("CREATE USER my_user WITH PASSWORD 'p1' NAME 'my_user' SCHEMA
my_schema;").assertSucceeded()
+ mdb.execute("GRANT my_role to my_user;").assertSucceeded()
+
+ # someone else's schema, to parts of which my_user only has access when
+ # granted
+ mdb.execute("CREATE SCHEMA your_schema;").assertSucceeded()
+ mdb.execute("SET SCHEMA your_schema;").assertSucceeded()
+ mdb.execute("CREATE TABLE your_table (name VARCHAR(10), birthday DATE, ssn
CHAR(9));").assertSucceeded()
+ mdb.execute("INSERT INTO your_table VALUES ('alice', '1980-01-01',
'AAAAAAAAA'), ('bob', '1970-01-01', '000000000');").assertRowCount(2)
+ mdb.execute("CREATE VIEW your_view AS SELECT name, EXTRACT(YEAR FROM
birthday) as yr, '********'||substring(ssn,9,9) as ssn FROM
your_table;").assertSucceeded()
+ 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:
+ tc.connect(username="my_user", password="p1")
+
+ # my_user can create tables, views in its own schema. Just a sanity
check
+ tc.execute("SET ROLE my_role;").assertSucceeded()
+ tc.execute("CREATE TABLE my_schema.my_table (name VARCHAR(10), i
INT);").assertSucceeded()
+ tc.execute("CREATE VIEW my_schema.my_view AS SELECT * FROM
my_schema.my_table;").assertSucceeded()
+ mdb.execute("DROP VIEW my_schema.my_view;").assertSucceeded()
+ mdb.execute("DROP TABLE my_schema.my_table;").assertSucceeded()
+
+ # my_user can only indirectly select from your_view
+ tc.execute("SELECT * FROM
your_schema.your_table;").assertFailed(err_code="42000", err_message="SELECT:
access denied for my_user to table 'your_schema.your_table'")
+ tc.execute("SELECT * FROM your_schema.your_view;").assertSucceeded()\
+ .assertDataResultMatch([('alice', 1980, '********A'), ('bob',
1970, '********0')])
+ # 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'")
+
+ # clean up
+ mdb.execute("DROP VIEW your_schema.your_view;").assertSucceeded()
+ mdb.execute("DROP TABLE your_schema.your_table;").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()
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
