Changeset: b50ce7089da6 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b50ce7089da6
Branch: unlock
Log Message:
merged
diffs (truncated from 370 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
@@ -3,13 +3,13 @@ role
table
table_privs
unknown_user
+withGrantOption
+grantMonetdb
createSetUp
-withGrantOption
columnRights
changePasswordUser
renameUser
changeSchemaUser
-grantMonetdb
copyinto
schemaRights
metadataConsistency
diff --git a/sql/test/Users/Tests/grantMonetdb.SQL.py
b/sql/test/Users/Tests/grantMonetdb.SQL.py
--- a/sql/test/Users/Tests/grantMonetdb.SQL.py
+++ b/sql/test/Users/Tests/grantMonetdb.SQL.py
@@ -1,42 +1,59 @@
###
-# Grant monetdb rights to a user.
-# Verify that the user can assume the monetdb role and CREATE new users, GRANT
privileges and roles.
+# Grant sysadmin rights to a user.
+# Verify that the user can assume the sysadmin role and CREATE new users,
GRANT privileges and roles.
###
from MonetDBtesting.sqltest import SQLTestCase
-with SQLTestCase() as tc:
- tc.connect(username="monetdb", password="monetdb")
- tc.execute("GRANT sysadmin TO alice;").assertSucceeded()
- tc.connect(username="alice", password="alice")
- tc.execute("""
- SET ROLE sysadmin;
- CREATE USER may WITH PASSWORD 'may' NAME 'May' SCHEMA
library;""").assertFailed(err_code='M0M27')
- tc.execute("GRANT ALL ON orders TO april;").assertSucceeded()
- tc.execute("GRANT sysadmin TO april;").assertFailed(err_code='0P000')
+with SQLTestCase() as mdb:
+ mdb.connect(username="monetdb", password="monetdb")
-# import os, sys
-# try:
-# from MonetDBtesting import process
-# except ImportError:
-# import process
+ mdb.execute("CREATE SCHEMA library;").assertSucceeded()
-# 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="""\
-# GRANT sysadmin TO alice;
-# """)
+ mdb.execute("CREATE USER alice WITH PASSWORD 'alice' name 'alice' schema
library;").assertSucceeded()
+ mdb.execute("CREATE USER april WITH PASSWORD 'april' name 'april' schema
library;").assertSucceeded()
-# sql_test_client('alice', 'alice', input="""\
-# SET ROLE sysadmin;
-# CREATE USER may WITH PASSWORD 'may' NAME 'May' SCHEMA library;
-# GRANT ALL ON orders TO april;
-# GRANT sysadmin TO april;
-# """)
+ mdb.execute("CREATE TABLE library.orders(price int, name
VARCHAR(100));").assertSucceeded()
+
+ with SQLTestCase() as tc:
+ tc.connect(username="alice", password="alice")
+ # alice is not a sysadmin yet
+ tc.execute("SET ROLE sysadmin;").assertFailed(err_code='42000',
err_message="Role (sysadmin) missing")
+ tc.execute("CREATE USER may WITH PASSWORD 'may' NAME 'May' SCHEMA
library;").assertFailed(err_code='42M31', err_message="Insufficient privileges
to create user 'may'")
+ tc.execute("GRANT ALL ON library.orders TO
april;").assertFailed(err_code='01007', err_message="GRANT: Grantor 'alice' is
not allowed to grant privileges for table 'orders'")
+ # give alice sysadmin rights
+ mdb.execute("GRANT sysadmin TO alice;").assertSucceeded()
+ tc.execute("SET ROLE sysadmin;").assertSucceeded()
+ # FIXME: this query should probably not fail
+ tc.execute("CREATE USER may WITH PASSWORD 'may' NAME 'May' SCHEMA
library;").assertFailed(err_code='M0M27', err_message="CREATE USER: access
denied for user 'alice'")
+
+ with SQLTestCase() as tc2:
+ # check that april can only SEL/INS/UPD/DEL the table after the
GRANT ALL
+ tc2.connect(username="april", password="april")
+ tc2.execute("INSERT INTO library.orders VALUES (12, 'abc'), (42,
'def');").assertFailed(err_code='42000', err_message="INSERT INTO: insufficient
privileges for user 'april' to insert into table 'orders'")
+ tc2.execute("UPDATE library.orders SET price = price*2 WHERE price
< 42;").assertFailed(err_code='42000', err_message="UPDATE: insufficient
privileges for user 'april' to update table 'orders'")
+ tc2.execute("DELETE FROM library.orders WHERE price =
42;").assertFailed(err_code='42000', err_message="DELETE FROM: insufficient
privileges for user 'april' to delete from table 'orders'")
+ tc2.execute("SELECT * FROM
library.orders;").assertFailed(err_code='42000', err_message="SELECT: access
denied for april to table 'library.orders'")
+ tc.execute("GRANT ALL ON library.orders TO
april;").assertSucceeded()
+ tc2.execute("INSERT INTO library.orders VALUES (12, 'abc'), (42,
'def');").assertRowCount(2)
+ tc2.execute("UPDATE library.orders SET price = price*2 WHERE price
< 42;").assertRowCount(1)
+ tc2.execute("DELETE FROM library.orders WHERE price =
42;").assertRowCount(1)
+ tc2.execute("SELECT * FROM
library.orders;").assertSucceeded().assertDataResultMatch([(24, 'abc')])
+ tc2.execute("DROP TABLE
library.orders;").assertFailed(err_code='42000', err_message="DROP TABLE:
access denied for april to schema 'library'")
+
+ # alice can only GRANT the role to another user if the role was granted
+ # to alice WITH ADMIN OPTION
+ tc.execute("GRANT sysadmin TO april;").assertFailed(err_code='0P000',
err_message="GRANT: Insufficient privileges to grant ROLE 'sysadmin'")
+ mdb.execute("REVOKE sysadmin FROM alice;").assertSucceeded()
+ mdb.execute("GRANT sysadmin TO alice WITH ADMIN
OPTION;").assertSucceeded()
+ tc.execute("GRANT sysadmin TO april;").assertSucceeded()
+
+ # clean up
+ mdb.execute("DROP TABLE library.orders;").assertSucceeded()
+ mdb.execute("DROP SCHEMA library;").assertSucceeded()
+ mdb.execute("DROP USER alice;").assertSucceeded()
+ mdb.execute("DROP USER april;").assertSucceeded()
+
diff --git a/sql/test/Users/Tests/grantMonetdb.stable.err
b/sql/test/Users/Tests/grantMonetdb.stable.err
deleted file mode 100644
--- a/sql/test/Users/Tests/grantMonetdb.stable.err
+++ /dev/null
@@ -1,45 +0,0 @@
-stderr of test 'grantMonetdb` in directory 'sql/test/Users` itself:
-
-
-# 10:44:50 >
-# 10:44:50 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=32584" "--set"
"mapi_usock=/var/tmp/mtest-30723/.s.monetdb.32584" "--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"
-# 10:44:50 >
-
-# 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 = 32584
-# cmdline opt mapi_usock = /var/tmp/mtest-30723/.s.monetdb.32584
-# 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
-
-# 10:44:51 >
-# 10:44:51 > "/usr/bin/python2" "grantMonetdb.SQL.py" "grantMonetdb"
-# 10:44:51 >
-
-MAPI = (alice) /var/tmp/mtest-8540/.s.monetdb.34898
-QUERY = CREATE USER may WITH PASSWORD 'may' NAME 'May' SCHEMA library;
-ERROR = !CREATE USER: access denied for user 'alice'
-CODE = M0M27
-MAPI = (alice) /var/tmp/mtest-30274/.s.monetdb.37685
-QUERY = GRANT sysadmin TO april;
-ERROR = !GRANT: Insufficient privileges to grant ROLE 'sysadmin'
-CODE = 0P000
-
-# 10:44:51 >
-# 10:44:51 > "Done."
-# 10:44:51 >
-
diff --git a/sql/test/Users/Tests/grantMonetdb.stable.out
b/sql/test/Users/Tests/grantMonetdb.stable.out
deleted file mode 100644
--- a/sql/test/Users/Tests/grantMonetdb.stable.out
+++ /dev/null
@@ -1,39 +0,0 @@
-stdout of test 'grantMonetdb` in directory 'sql/test/Users` itself:
-
-
-# 10:44:50 >
-# 10:44:50 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=32584" "--set"
"mapi_usock=/var/tmp/mtest-30723/.s.monetdb.32584" "--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"
-# 10:44:50 >
-
-# 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:32584/
-# Listening for UNIX domain connection requests on
mapi:monetdb:///var/tmp/mtest-30723/.s.monetdb.32584
-# Start processing logs sql/sql_logs version 52200
-# Start reading the write-ahead log 'sql_logs/sql/log.18'
-# Finished reading the write-ahead log 'sql_logs/sql/log.18'
-# Finished processing logs sql/sql_logs
-# MonetDB/SQL module loaded
-# MonetDB/R module loaded
-
-
-# 10:44:51 >
-# 10:44:51 > "/usr/bin/python2" "grantMonetdb.SQL.py" "grantMonetdb"
-# 10:44:51 >
-
-#GRANT monetdb TO alice;
-#SET ROLE monetdb;
-#CREATE USER may WITH PASSWORD 'may' NAME 'May' SCHEMA library;
-#GRANT ALL ON orders TO april;
-#GRANT monetdb TO april;
-
-# 10:44:51 >
-# 10:44:51 > "Done."
-# 10:44:51 >
-
diff --git a/sql/test/Users/Tests/withGrantOption.SQL.py
b/sql/test/Users/Tests/withGrantOption.SQL.py
--- a/sql/test/Users/Tests/withGrantOption.SQL.py
+++ b/sql/test/Users/Tests/withGrantOption.SQL.py
@@ -3,31 +3,46 @@
# Verify that the user can regrant the privilege.
###
-import os, sys
-try:
- from MonetDBtesting import process
-except ImportError:
- import process
+from MonetDBtesting.sqltest import SQLTestCase
+
+with SQLTestCase() as mdb:
+ mdb.connect(username="monetdb", password="monetdb")
-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()
+ mdb.execute("CREATE SCHEMA library;").assertSucceeded()
+ mdb.execute("CREATE SCHEMA bank;").assertSucceeded()
+ mdb.execute("CREATE TABLE bank.loans(nr int, amount
int);").assertSucceeded()
+
+ mdb.execute("CREATE USER alice WITH PASSWORD 'alice' name 'alice' schema
library;").assertSucceeded()
+ mdb.execute("CREATE USER april WITH PASSWORD 'april' name 'april' schema
library;").assertSucceeded()
+
+ mdb.execute("GRANT SELECT ON bank.loans TO april WITH GRANT
OPTION;").assertSucceeded()
+ mdb.execute("GRANT INSERT ON bank.loans TO april WITH GRANT
OPTION;").assertSucceeded()
+ mdb.execute("GRANT UPDATE ON bank.loans TO april WITH GRANT
OPTION;").assertSucceeded()
+ mdb.execute("GRANT DELETE ON bank.loans TO april WITH GRANT
OPTION;").assertSucceeded()
-sql_test_client('monetdb', 'monetdb', input="""\
-GRANT SELECT ON bank.loans TO april WITH GRANT OPTION;
-GRANT INSERT ON bank.loans TO april WITH GRANT OPTION;
-GRANT UPDATE ON bank.loans TO april WITH GRANT OPTION;
-GRANT DELETE ON bank.loans TO april WITH GRANT OPTION;
-""")
-
+ with SQLTestCase() as tc:
+ tc.connect(username="alice", password="alice")
+ # alice doesn't have access to bank.loans yet
+ tc.execute("INSERT INTO bank.loans VALUES (12, 127), (42,
8191);").assertFailed(err_code='42000', err_message="INSERT INTO: insufficient
privileges for user 'alice' to insert into table 'loans'")
+ tc.execute("UPDATE bank.loans SET amount = amount - 100 WHERE nr =
42;").assertFailed(err_code='42000', err_message="UPDATE: insufficient
privileges for user 'alice' to update table 'loans'")
+ tc.execute("DELETE FROM bank.loans WHERE nr =
12;").assertFailed(err_code='42000', err_message="DELETE FROM: insufficient
privileges for user 'alice' to delete from table 'loans'")
+ tc.execute("SELECT * FROM bank.loans;").assertFailed(err_code='42000',
err_message="SELECT: access denied for alice to table 'bank.loans'")
+ # let april grant alice all rights
+ tc.connect(username="april", password="april")
+ tc.execute("GRANT SELECT ON bank.loans TO alice WITH GRANT
OPTION;").assertSucceeded()
+ tc.execute("GRANT INSERT ON bank.loans TO alice WITH GRANT
OPTION;").assertSucceeded()
+ tc.execute("GRANT UPDATE ON bank.loans TO alice WITH GRANT
OPTION;").assertSucceeded()
+ tc.execute("GRANT DELETE ON bank.loans TO alice WITH GRANT
OPTION;").assertSucceeded()
-sql_test_client('april', 'april', input="""\
-GRANT SELECT ON bank.loans TO alice WITH GRANT OPTION;
-GRANT INSERT ON bank.loans TO alice WITH GRANT OPTION;
-GRANT UPDATE ON bank.loans TO alice WITH GRANT OPTION;
-GRANT DELETE ON bank.loans TO alice WITH GRANT OPTION;
-""")
+ tc.execute("INSERT INTO bank.loans VALUES (12, 127), (42,
8191);").assertRowCount(2)
+ tc.execute("UPDATE bank.loans SET amount = amount - 100 WHERE nr =
42;").assertRowCount(1)
+ tc.execute("DELETE FROM bank.loans WHERE nr = 12;").assertRowCount(1)
+ tc.execute("SELECT * FROM
bank.loans;").assertSucceeded().assertDataResultMatch([(42, 8091)])
+ # clean up
+ mdb.execute("DROP TABLE bank.loans;").assertSucceeded()
+ mdb.execute("DROP USER april;").assertSucceeded()
+ mdb.execute("DROP USER alice;").assertSucceeded()
+ mdb.execute("DROP SCHEMA bank;").assertSucceeded()
+ mdb.execute("DROP SCHEMA library;").assertSucceeded()
diff --git a/sql/test/Users/Tests/withGrantOption.stable.err
b/sql/test/Users/Tests/withGrantOption.stable.err
deleted file mode 100644
--- a/sql/test/Users/Tests/withGrantOption.stable.err
+++ /dev/null
@@ -1,37 +0,0 @@
-stderr of test 'withGrantOption` in directory 'sql/test/Users` itself:
-
-
-# 10:30:19 >
-# 10:30:19 > "mserver5" "--debug=10" "--set" "gdk_nr_threads=0" "--set"
"mapi_open=true" "--set" "mapi_port=38464" "--set"
"mapi_usock=/var/tmp/mtest-28429/.s.monetdb.38464" "--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"
-# 10:30:19 >
-
-# 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
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
