Changeset: fe2a746081a7 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=fe2a746081a7
Removed Files:
sql/test/Users/Tests/renameUser.stable.err
sql/test/Users/Tests/renameUser.stable.out
Modified Files:
sql/test/Users/Tests/All
sql/test/Users/Tests/renameUser.SQL.py
Branch: default
Log Message:
clean up and minor extensions
diffs (289 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
@@ -12,6 +12,6 @@ schemaRights
metadataConsistency
createUserRollback
userCallFunction
+renameUser
createSetUp
-renameUser
changeSchemaUser
diff --git a/sql/test/Users/Tests/renameUser.SQL.py
b/sql/test/Users/Tests/renameUser.SQL.py
--- a/sql/test/Users/Tests/renameUser.SQL.py
+++ b/sql/test/Users/Tests/renameUser.SQL.py
@@ -1,81 +1,81 @@
###
-# Let admin rename a user (from A to B) (possible).
-# Create another user with the old name (A)(possible).
-# Verify that the new user (A) cannot make use of the role assign to the
inital user (now B).
-# Verify that a user with no special permissions cannot rename users.
-# Verify that the renamed user (B) still has his rights.
-# Rename a user with an already existing name (not possible).
-# Rename an unexisting user (not possible).
-# Create an user on a non-existing schema (not possible).
-# Create an user with a name of an existing user (not possible).
+# Check various aspects of renaming users
###
from MonetDBtesting.sqltest import SQLTestCase
+import logging
-with SQLTestCase() as tc:
- tc.connect(username="monetdb", password="monetdb")
- tc.execute('ALTER USER "april" RENAME TO "april2";
--succeed').assertSucceeded()
+logging.basicConfig(level=logging.FATAL)
- tc.execute("CREATE USER april with password 'april' name 'second' schema
bank;")
-
- tc.connect(username="april", password="april")
- tc.execute("DELETE from bank.accounts; -- not enough
privelges").assertFailed()
- tc.execute("SET role bankAdmin; -- no such role").assertFailed()
- tc.execute('ALTER USER "april2" RENAME TO "april3"; --not enough
privileges').assertFailed()
+with SQLTestCase() as mdb:
+ with SQLTestCase() as tc2:
+ mdb.connect(username="monetdb", password="monetdb")
+ # prepare a user 'april' as the owner of schema 'bank'
+ mdb.execute("CREATE ROLE bankAdmin;").assertSucceeded()
+ mdb.execute("CREATE SCHEMA bank AUTHORIZATION
bankAdmin;").assertSucceeded()
+ mdb.execute("CREATE USER april WITH PASSWORD 'april' NAME 'april'
SCHEMA bank;").assertSucceeded()
+ mdb.execute("GRANT bankAdmin to april;").assertSucceeded()
+ mdb.execute("CREATE TABLE bank.accounts(nr int, name
VARCHAR(100));").assertSucceeded()
+ # just a sanity check
+ tc2.connect(username="april", password="april")
+ tc2.execute("SET ROLE bankAdmin;").assertSucceeded()
- tc.connect(username='april2', password='april')
- tc.execute("""
- SELECT * from bank.accounts;
- SET role bankAdmin;
- """).assertSucceeded()
-
- tc.connect(username='monetdb', password='monetdb')
- tc.execute('ALTER USER "april2" RENAME TO "april";').assertFailed()
- tc.execute("drop user april;").assertSucceeded()
- tc.execute('ALTER USER "april2" RENAME TO "april";').assertSucceeded()
- tc.execute('ALTER USER "april5" RENAME TO "april2"; -- no such
user').assertFailed()
- tc.execute("drop user april2; --nu such user").assertFailed()
- tc.execute("CREATE USER april2 with password 'april' name 'second april,
no rights' schema library2; --no such schema").assertFailed()
- tc.execute("CREATE USER april with password 'april' name 'second april, no
rights' schema library; --user exsists").assertFailed()
-
-
+ # Check that:
+ # the admin can rename a user (from A to B);
+ # after the rename, user B can log in but not user A
+ mdb.execute("ALTER USER april RENAME TO april2;").assertSucceeded()
+ tc2.connect(username="april", password="april")
+ tc2.execute("SET ROLE bankAdmin;").assertFailed(err_code=None,
err_message="InvalidCredentialsException:checkCredentials:invalid credentials
for user 'april'")
+ tc2.connect(username="april2", password="april")
+ tc2.execute("SET ROLE bankAdmin;").assertSucceeded()
+ # Check that the renamed user (B) still has its rights.
+ tc2.execute("SET role bankAdmin;").assertSucceeded()
+ tc2.execute("DROP TABLE accounts;").assertSucceeded()
+ tc2.execute("CREATE TABLE accounts(nr int, name
VARCHAR(100));").assertSucceeded()
+ tc2.execute("INSERT INTO accounts VALUES (24, 'abc'), (42,
'xyz');").assertRowCount(2)
+ tc2.execute("UPDATE accounts SET nr = 666;").assertRowCount(2)
+ tc2.execute("DELETE FROM accounts WHERE name =
'abc';").assertRowCount(1)
+ tc2.execute("SELECT * from bank.accounts;").assertRowCount(1)
-# import os, sys
-# try:
-# from MonetDBtesting import process
-# except ImportError:
-# import process
-
-# def sql_test_client(user, passwd, input):
-# with process.client(lang="sql", user=user, passwd=passwd,
communicate=True,
-# stdin=process.PIPE, stdout=process.PIPE,
stderr=process.PIPE,
-# input=input, port=int(os.getenv("MAPIPORT"))) as c:
-# c.communicate()
-
-# sql_test_client('monetdb', 'monetdb', input="""\
-# ALTER USER "april" RENAME TO "april2"; --succeed
-# CREATE USER april with password 'april' name 'second' schema bank;
-# """)
+ # Check that:
+ # the admin can create another user with the old name (A);
+ # the new user (A) cannot make use of the role assign to the inital
user (now B).
+ mdb.execute("CREATE USER april with password 'april' name 'second'
schema bank;").assertSucceeded()
+ tc2.connect(username="april", password="april")
+ tc2.execute("SET role bankAdmin;").assertFailed(err_code="42000",
err_message="Role (bankadmin) missing")
+ tc2.execute("DROP TABLE accounts;").assertFailed(err_code="42000",
err_message="DROP TABLE: access denied for april to schema 'bank'")
+ tc2.execute("CREATE TABLE accounts2(nr int, name
VARCHAR(100));").assertFailed(err_code="42000", err_message="CREATE TABLE:
insufficient privileges for user 'april' in schema 'bank'")
+ tc2.execute("INSERT INTO accounts VALUES (24, 'abc'), (42,
'xyz');").assertFailed(err_code="42000", err_message="INSERT INTO: insufficient
privileges for user 'april' to insert into table 'accounts'")
+ tc2.execute("UPDATE accounts SET nr =
666;").assertFailed(err_code="42000", err_message="UPDATE: insufficient
privileges for user 'april' to update table 'accounts' on column 'nr'")
+ tc2.execute("DELETE FROM accounts WHERE name =
'abc';").assertFailed(err_code="42000", err_message="DELETE FROM: insufficient
privileges for user 'april' to delete from table 'accounts'")
+ tc2.execute("SELECT * from
bank.accounts;").assertFailed(err_code="42000", err_message="SELECT: access
denied for april to table 'bank.accounts'")
+ # Check that a user with no special permissions cannot rename users.
+ # FIXME: might need to change the err_message (see issue #7037)
+ tc2.execute("ALTER USER april2 RENAME TO april3;")\
+ .assertFailed(err_code="42M32", err_message="ALTER USER: no such
user 'april2'")
-# # This is the new april, so these operations should fail.
-# sql_test_client('april', 'april', input="""\
-# DELETE from bank.accounts; -- not enough privelges
-# SET role bankAdmin; -- no such role
-# ALTER USER "april2" RENAME TO "april3"; --not enough privileges
-# """)
+ mdb.connect(username='monetdb', password='monetdb')
+ # Check that the admin cannot:
+ # rename a user with an already existing name;
+ mdb.execute('ALTER USER "april2" RENAME TO "april";')\
+ .assertFailed(err_code="42M31", err_message="ALTER USER: user
'april' already exists")
+ mdb.execute("drop user april;").assertSucceeded()
+ mdb.execute('ALTER USER "april2" RENAME TO "april";').assertSucceeded()
+ # rename an unexisting user;
+ mdb.execute('ALTER USER "april5" RENAME TO "april2";')\
+ .assertFailed(err_code="42M32", err_message="ALTER USER: no such
user 'april5'")
+ mdb.execute("drop user april2;")\
+ .assertFailed(err_code="M0M27", err_message="DROP USER: no such
user: 'april2'")
+ # create a user on a non-existing schema;
+ mdb.execute("CREATE USER april2 with password 'april' name 'second
april, no rights' schema library2;")\
+ .assertFailed(err_code="3F000", err_message="CREATE USER: no such
schema 'library2'")
+ # create a user with a name of an existing user.
+ mdb.execute("CREATE USER april with password 'april' name 'second
april, no rights' schema library;")\
+ .assertFailed(err_code="42M31", err_message="CREATE USER: user
'april' already exists")
-# # This is the initial april, so these operations should succeed.
-# sql_test_client('april2', 'april', input="""\
-# SELECT * from bank.accounts;
-# SET role bankAdmin;
-# """)
+ # clean up
+ mdb.execute("DROP TABLE bank.accounts;").assertSucceeded()
+ mdb.execute("DROP USER april;").assertSucceeded()
+ mdb.execute("DROP SCHEMA bank;").assertSucceeded()
+ mdb.execute("DROP ROLE bankAdmin;").assertSucceeded()
-# sql_test_client('monetdb', 'monetdb', input="""\
-# ALTER USER "april2" RENAME TO "april";
-# drop user april;
-# ALTER USER "april2" RENAME TO "april";
-# ALTER USER "april5" RENAME TO "april2"; -- no such user
-# drop user april2; --nu such user
-# CREATE USER april2 with password 'april' name 'second april, no rights'
schema library2; --no such schema
-# CREATE USER april with password 'april' name 'second april, no rights'
schema library; --user exsists
-# """)
diff --git a/sql/test/Users/Tests/renameUser.stable.err
b/sql/test/Users/Tests/renameUser.stable.err
deleted file mode 100644
--- a/sql/test/Users/Tests/renameUser.stable.err
+++ /dev/null
@@ -1,69 +0,0 @@
-stderr of test 'renameUser` in directory 'sql/test/Users` itself:
-
-
-# 23:01:32 >
-# 23:01:32 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=36242" "--set"
"mapi_usock=/var/tmp/mtest-7070/.s.monetdb.36242" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/vera/Desktop/MonetDB/BUILD/var/MonetDB/mTests_sql_test_Users"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
-# 23:01:32 >
-
-# builtin opt gdk_dbpath =
/home/vera/Desktop/MonetDB/BUILD/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 = 36242
-# cmdline opt mapi_usock = /var/tmp/mtest-7070/.s.monetdb.36242
-# cmdline opt monet_prompt =
-# cmdline opt mal_listing = 2
-# cmdline opt gdk_dbpath =
/home/vera/Desktop/MonetDB/BUILD/var/MonetDB/mTests_sql_test_Users
-# cmdline opt mal_listing = 0
-# cmdline opt embedded_r = yes
-# cmdline opt gdk_debug = 536870922
-
-# 23:01:32 >
-# 23:01:32 > "/usr/bin/python2" "renameUser.SQL.py" "renameUser"
-# 23:01:32 >
-
-MAPI = (april) /var/tmp/mtest-7070/.s.monetdb.36242
-QUERY = DELETE from bank.accounts; -- not enough privelges
-ERROR = !DELETE FROM: insufficient privileges for user 'april' to delete from
table 'accounts'
-CODE = 42000
-MAPI = (april) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = SET role bankAdmin; -- no such role
-ERROR = !Role (bankadmin) missing
-CODE = 42000
-MAPI = (april) /var/tmp/mtest-12671/.s.monetdb.35429
-QUERY = ALTER USER "april2" RENAME TO "april3"; --not enough privileges
-ERROR = !ALTER USER: no such user 'april2'
-CODE = 42M32
-MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = ALTER USER "april2" RENAME TO "april";
-ERROR = !ALTER USER: user 'april' already exists
-CODE = 42M31
-MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = ALTER USER "april5" RENAME TO "april2"; -- no such user
-ERROR = !ALTER USER: no such user 'april5'
-CODE = 42M32
-MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = drop user april2; --nu such user
-ERROR = !DROP USER: no such user: 'april2'
-CODE = M0M27
-MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = CREATE USER april2 with password 'april' name 'second april, no
rights' schema library2; --no such schema
-ERROR = !CREATE USER: no such schema 'library2'
-CODE = 3F000
-MAPI = (monetdb) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = CREATE USER april with password 'april' name 'second april, no rights'
schema library; --user exsists
-ERROR = !CREATE USER: user 'april' already exists
-CODE = 42M31
-
-# 23:01:33 >
-# 23:01:33 > "Done."
-# 23:01:33 >
-
diff --git a/sql/test/Users/Tests/renameUser.stable.out
b/sql/test/Users/Tests/renameUser.stable.out
deleted file mode 100644
--- a/sql/test/Users/Tests/renameUser.stable.out
+++ /dev/null
@@ -1,44 +0,0 @@
-stdout of test 'renameUser` in directory 'sql/test/Users` itself:
-
-
-# 23:01:32 >
-# 23:01:32 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=36242" "--set"
"mapi_usock=/var/tmp/mtest-7070/.s.monetdb.36242" "--set" "monet_prompt="
"--forcemito" "--set" "mal_listing=2"
"--dbpath=/home/vera/Desktop/MonetDB/BUILD/var/MonetDB/mTests_sql_test_Users"
"--set" "mal_listing=0" "--set" "embedded_r=yes"
-# 23:01: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:36242/
-# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-7070/.s.monetdb.36242
-# Start processing logs sql/sql_logs version 52200
-# Start reading the write-ahead log 'sql_logs/sql/log.10'
-# Finished reading the write-ahead log 'sql_logs/sql/log.10'
-# Finished processing logs sql/sql_logs
-# MonetDB/SQL module loaded
-# MonetDB/R module loaded
-
-
-# 23:01:32 >
-# 23:01:32 > "/usr/bin/python2" "renameUser.SQL.py" "renameUser"
-# 23:01:32 >
-
-#ALTER USER "april" RENAME TO "april2"; --succeed
-#CREATE USER april with password 'april' name 'second' schema bank;
-#SELECT * from bank.accounts;
-% bank.accounts, bank.accounts # table_name
-% nr, name # name
-% int, varchar # type
-% 1, 0 # length
-#SET role bankAdmin;
-#drop user april;
-#ALTER USER "april2" RENAME TO "april";
-
-# 23:01:33 >
-# 23:01:33 > "Done."
-# 23:01:33 >
-
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
