Changeset: 0a177ab017e9 for MonetDB
URL: http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0a177ab017e9
Added Files:
sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.SQL.py
sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.err
sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.out
Modified Files:
sql/test/Users/Tests/All
Branch: default
Log Message:
A test for bug 3765.
diffs (truncated from 389 to 300 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
@@ -11,3 +11,4 @@ test_privs2_p1
test_privs2_p2
dropManyUsers.Bug-3764
grantAndRevokeUserLogedIN.Bug-3476
+grantRevokeAndGrantAgain.Bug-3765
diff --git a/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.SQL.py
b/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.SQL.py
new file mode 100644
--- /dev/null
+++ b/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.SQL.py
@@ -0,0 +1,147 @@
+###
+# Give a privilege to a USER, then remove it and give it again (possible).
+# Assess that by granting one privilege, he only gets that one privilege.
+# Assess that the privilege was indeed removed.
+# Assess that it is possible to regrant the same privilege.
+###
+
+import os, sys
+try:
+ from MonetDBtesting import process
+except ImportError:
+ import process
+
+def client(user, passwd, input=None):
+ clt = process.client(lang='sql', user=user, passwd=passwd,
+ stdin = process.PIPE,
+ stdout = process.PIPE,
+ stderr = process.PIPE,
+ port = int(os.getenv('MAPIPORT')))
+ out, err = clt.communicate(input)
+ sys.stdout.write(out)
+ sys.stderr.write(err)
+
+sql_client = os.getenv('SQL_CLIENT')
+
+
+client('monetdb', 'monetdb', input = """\
+CREATE SCHEMA schemaTest;
+
+CREATE USER user_delete with password 'delete' name 'user can only delete'
schema schemaTest;
+CREATE USER user_insert with password 'insert' name 'user can only insert'
schema schemaTest;
+CREATE USER user_update with password 'update' name 'user can only update'
schema schemaTest;
+CREATE USER user_select with password 'select' name 'user can only select'
schema schemaTest;
+
+CREATE table schemaTest.testTable (v1 int, v2 int);
+
+INSERT into schemaTest.testTable values(1, 1);
+INSERT into schemaTest.testTable values(2, 2);
+INSERT into schemaTest.testTable values(3, 3);
+
+-- Grant delete rights.
+GRANT DELETE on table schemaTest.testTable to user_delete;
+GRANT INSERT on table schemaTest.testTable to user_insert;
+GRANT UPDATE on table schemaTest.testTable to user_update;
+GRANT SELECT on table schemaTest.testTable to user_select;
+
+""")
+
+client('user_delete', 'delete', input = """\
+-- Check delete.
+DELETE FROM testTable where v1 = 2;
+
+-- Check all the other privileges (they should fail).
+SELECT * FROM testTable;
+UPDATE testTable set v1 = 2 where v2 = 7;
+INSERT into testTable values (3, 3);
+""")
+
+client('user_update', 'update', input = """\
+-- Check insert.
+UPDATE testTable set v1 = 2 where v2 = 7;
+
+-- Check all the other privileges (they should fail).
+SELECT * FROM testTable;
+INSERT into testTable values (3, 3);
+DELETE FROM testTable where v1 = 2;
+""")
+
+client('user_insert', 'insert', input = """\
+-- Check insert.
+INSERT into testTable values (3, 3);
+
+-- Check all the other privileges (they should fail).
+SELECT * FROM testTable;
+UPDATE testTable set v1 = 2 where v2 = 7;
+DELETE FROM testTable where v1 = 2;
+""")
+
+client('user_select', 'select', input = """\
+-- Check insert.
+SELECT * FROM testTable;
+
+-- Check all the other privileges (they should fail).
+INSERT into testTable values (3, 3);
+UPDATE testTable set v1 = 2 where v2 = 7;
+DELETE FROM testTable where v1 = 2;
+""")
+
+client('monetdb', 'monetdb', input = """\
+SELECT * FROM schemaTest.testTable;
+
+REVOKE DELETE on schemaTest.testTable from user_delete;
+REVOKE INSERT on schemaTest.testTable from user_insert;
+REVOKE UPDATE on schemaTest.testTable from user_update;
+REVOKE SELECT on schemaTest.testTable from user_select;
+""")
+
+# Next four transitions should not be allowed.
+client('user_delete', 'delete', input = """\
+DELETE from testTable where v2 = 666;
+""")
+
+client('user_insert', 'insert', input = """\
+INSERT into testTable values (666, 666);
+""")
+
+client('user_update', 'update', input = """\
+UPDATE testTable set v1 = 666 where v2 = 666;
+""")
+
+client('user_select', 'select', input = """\
+SELECT * FROM testTable where v1 = 666;
+""")
+#
+
+# Regrant the revoked permissions to the users.
+client('monetdb', 'monetdb', input = """\
+SELECT * from schemaTest.testTable;
+
+-- Grant delete rights.
+GRANT DELETE on table schemaTest.testTable to user_delete;
+GRANT INSERT on table schemaTest.testTable to user_insert;
+GRANT UPDATE on table schemaTest.testTable to user_update;
+GRANT SELECT on table schemaTest.testTable to user_select;
+""")
+
+# Next four transitions should be allowed.
+client('user_delete', 'delete', input = """\
+DELETE from testTable where v1 = 42;
+""")
+
+client('user_insert', 'insert', input = """\
+INSERT into testTable values (42, 42);
+""")
+
+client('user_update', 'update', input = """\
+UPDATE testTable set v1 = 42 where v2 = 42;
+""")
+
+client('user_select', 'select', input = """\
+SELECT * FROM testTable where v1 = 42;
+""")
+
+# XXX: Clean up?
+
+
+
diff --git a/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.err
b/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.err
new file mode 100644
--- /dev/null
+++ b/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.err
@@ -0,0 +1,85 @@
+stderr of test 'grantRevokeAndGrantAgain` in directory 'sql/test/Users` itself:
+
+
+# 10:09:32 >
+# 10:09:32 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=35979" "--set"
"mapi_usock=/var/tmp/mtest-18691/.s.monetdb.35979" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/vera/Desktop/MonetDB/installation/var/MonetDB/mTests_sql_test_Users"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
+# 10:09:32 >
+
+# builtin opt gdk_dbpath =
/home/vera/Desktop/MonetDB/installation/var/monetdb5/dbfarm/demo
+# builtin opt gdk_debug = 0
+# builtin opt gdk_vmtrim = no
+# builtin opt monet_prompt = >
+# builtin opt monet_daemon = no
+# builtin opt mapi_port = 50000
+# builtin opt mapi_open = false
+# builtin opt mapi_autosense = false
+# builtin opt sql_optimizer = default_pipe
+# builtin opt sql_debug = 0
+# cmdline opt gdk_nr_threads = 0
+# cmdline opt mapi_open = true
+# cmdline opt mapi_port = 35979
+# cmdline opt mapi_usock = /var/tmp/mtest-18691/.s.monetdb.35979
+# cmdline opt monet_prompt =
+# cmdline opt mal_listing = 2
+# cmdline opt gdk_dbpath =
/home/vera/Desktop/MonetDB/installation/var/MonetDB/mTests_sql_test_Users
+# cmdline opt mal_listing = 0
+# cmdline opt embedded_r = yes
+# cmdline opt gdk_debug = 536870922
+
+# 10:09:33 >
+# 10:09:33 > "/usr/bin/python2" "grantRevokeAndGrantAgain.SQL.py"
"grantRevokeAndGrantAgain"
+# 10:09:33 >
+
+MAPI = (user_delete) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = SELECT * FROM testTable;
+ERROR = !SELECT: access denied for user_delete to table 'schematest.testtable'
+MAPI = (user_delete) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = UPDATE testTable set v1 = 2 where v2 = 7;
+ERROR = !UPDATE: insufficient privileges for user 'user_delete' to update
table 'testtable' on column 'v1'
+MAPI = (user_delete) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = INSERT into testTable values (3, 3);
+ERROR = !INSERT INTO: insufficient privileges for user 'user_delete' to insert
into table 'testtable'
+MAPI = (user_update) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = SELECT * FROM testTable;
+ERROR = !SELECT: access denied for user_update to table 'schematest.testtable'
+MAPI = (user_update) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = INSERT into testTable values (3, 3);
+ERROR = !INSERT INTO: insufficient privileges for user 'user_update' to insert
into table 'testtable'
+MAPI = (user_update) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = DELETE FROM testTable where v1 = 2;
+ERROR = !DELETE FROM: insufficient privileges for user 'user_update' to delete
from table 'testtable'
+MAPI = (user_insert) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = SELECT * FROM testTable;
+ERROR = !SELECT: access denied for user_insert to table 'schematest.testtable'
+MAPI = (user_insert) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = UPDATE testTable set v1 = 2 where v2 = 7;
+ERROR = !UPDATE: insufficient privileges for user 'user_insert' to update
table 'testtable' on column 'v1'
+MAPI = (user_insert) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = DELETE FROM testTable where v1 = 2;
+ERROR = !DELETE FROM: insufficient privileges for user 'user_insert' to delete
from table 'testtable'
+MAPI = (user_select) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = INSERT into testTable values (3, 3);
+ERROR = !INSERT INTO: insufficient privileges for user 'user_select' to insert
into table 'testtable'
+MAPI = (user_select) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = UPDATE testTable set v1 = 2 where v2 = 7;
+ERROR = !UPDATE: insufficient privileges for user 'user_select' to update
table 'testtable' on column 'v1'
+MAPI = (user_select) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = DELETE FROM testTable where v1 = 2;
+ERROR = !DELETE FROM: insufficient privileges for user 'user_select' to delete
from table 'testtable'
+MAPI = (user_delete) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = DELETE from testTable where v2 = 666;
+ERROR = !DELETE FROM: insufficient privileges for user 'user_delete' to delete
from table 'testtable'
+MAPI = (user_insert) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = INSERT into testTable values (666, 666);
+ERROR = !INSERT INTO: insufficient privileges for user 'user_insert' to insert
into table 'testtable'
+MAPI = (user_update) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = UPDATE testTable set v1 = 666 where v2 = 666;
+ERROR = !UPDATE: insufficient privileges for user 'user_update' to update
table 'testtable' on column 'v1'
+MAPI = (user_select) /var/tmp/mtest-18691/.s.monetdb.35979
+QUERY = SELECT * FROM testTable where v1 = 666;
+ERROR = !SELECT: access denied for user_select to table 'schematest.testtable'
+
+# 10:09:34 >
+# 10:09:34 > "Done."
+# 10:09:34 >
+
diff --git a/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.out
b/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.out
new file mode 100644
--- /dev/null
+++ b/sql/test/Users/Tests/grantRevokeAndGrantAgain.Bug-3765.stable.out
@@ -0,0 +1,134 @@
+stdout of test 'grantRevokeAndGrantAgain` in directory 'sql/test/Users` itself:
+
+
+# 10:09:32 >
+# 10:09:32 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=35979" "--set"
"mapi_usock=/var/tmp/mtest-18691/.s.monetdb.35979" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/vera/Desktop/MonetDB/installation/var/MonetDB/mTests_sql_test_Users"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
+# 10:09:32 >
+
+# MonetDB 5 server v11.22.0
+# This is an unreleased version
+# Serving database 'mTests_sql_test_Users', using 8 threads
+# Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs and 128bit
integers dynamically linked
+# Found 3.746 GiB available main-memory.
+# Copyright (c) 1993-July 2008 CWI.
+# Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved
+# Visit http://www.monetdb.org/ for further information
+# Listening for connection requests on mapi:monetdb://buzu:35979/
+# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-18691/.s.monetdb.35979
+# Start processing logs sql/sql_logs version 52200
+# Finished processing logs sql/sql_logs
+# MonetDB/SQL module loaded
+# MonetDB/R module loaded
+
+Ready.
+# SQL catalog created, loading sql scripts once
+# loading sql script: 09_like.sql
+# loading sql script: 10_math.sql
+# loading sql script: 11_times.sql
+# loading sql script: 12_url.sql
+# loading sql script: 13_date.sql
+# loading sql script: 14_inet.sql
+# loading sql script: 15_querylog.sql
+# loading sql script: 16_tracelog.sql
+# loading sql script: 17_temporal.sql
+# loading sql script: 20_vacuum.sql
+# loading sql script: 21_dependency_functions.sql
+# loading sql script: 22_clients.sql
+# loading sql script: 23_skyserver.sql
+# loading sql script: 24_zorder.sql
+# loading sql script: 25_debug.sql
+# loading sql script: 26_sysmon.sql
+# loading sql script: 27_rejects.sql
+# loading sql script: 39_analytics.sql
+# loading sql script: 39_analytics_hge.sql
+# loading sql script: 40_json.sql
+# loading sql script: 40_json_hge.sql
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
