Changeset: a9a817016125 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a9a817016125
Modified Files:
sql/test/Users/Tests/copyinto.SQL.py
Branch: default
Log Message:
Cleaned up and extended the test
diffs (130 lines):
diff --git a/sql/test/Users/Tests/copyinto.SQL.py
b/sql/test/Users/Tests/copyinto.SQL.py
--- a/sql/test/Users/Tests/copyinto.SQL.py
+++ b/sql/test/Users/Tests/copyinto.SQL.py
@@ -1,77 +1,60 @@
-import os, sys
+###
+# Check that to execute COPY INTO and COPY FROM, a user does not only need the
+# INSERT and SELECT privileges but also the COPY INTO and COPY FROM
+# privileges.
+###
-TSTSRCBASE = os.environ['TSTSRCBASE']
-SRCDIR = os.path.join(TSTSRCBASE, "sql", "benchmarks", "tpch")
-DATADIR = os.path.join(SRCDIR,"SF-0.01")
-
+import os, sys
from MonetDBtesting.sqltest import SQLTestCase
-with SQLTestCase() as tc:
- tc.connect(username="monetdb", password="monetdb")
- tc.execute("CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL, R_NAME
CHAR(25) NOT NULL, R_COMMENT VARCHAR(152));\n").assertSucceeded()
- tc.execute("COPY 5 RECORDS INTO region from r'%s' USING DELIMITERS '|',
E'|\\n';\n" % os.path.join(DATADIR, 'region.tbl'))
- tc.execute("select count(*) from region;\n")
- tc.execute("CREATE USER copyuser WITH PASSWORD 'copyuser' name 'copyuser'
schema sys;\n")
- tc.execute("GRANT INSERT, SELECT on region to copyuser;\n")
+BASEDIR = os.environ.get('TSTTRGDIR')
+INPUT = os.path.join(BASEDIR, "copyfrom.csv")
+OUTPUT = os.path.join(BASEDIR, "copyinto.csv")
- tc.connect(username="copyuser", password="copyuser")
- tc.execute("COPY 5 RECORDS INTO region from r'%s' USING DELIMITERS '|',
E'|\\n';\n" % os.path.join(DATADIR, 'region.tbl')).assertFailed()
- tc.execute("select count(*) from region;\n").assertSucceeded()
-
- tc.connect(username="monetdb", password="monetdb")
- tc.execute("GRANT COPY FROM, COPY INTO to copyuser;\n").assertSucceeded()
+with open(INPUT, 'w') as csvin:
+ csvin.write("24\n42\n")
- tc.connect(username="copyuser", password="copyuser")
- tc.execute("COPY 5 RECORDS INTO region from r'%s' USING DELIMITERS '|',
E'|\\n';\n" % os.path.join(DATADIR, 'region.tbl')).assertSucceeded()
- tc.execute("select count(*) from region;\n").assertSucceeded()
-
- tc.connect(username="monetdb", password="monetdb")
- tc.execute("REVOKE COPY FROM, COPY INTO from
copyuser;\n").assertSucceeded()
-
-
+with SQLTestCase() as mdb:
+ mdb.connect(username="monetdb", password="monetdb")
+ mdb.execute("CREATE TABLE t (i INT);").assertSucceeded()
+ mdb.execute("COPY 2 RECORDS INTO t FROM r'%s' USING DELIMITERS ',',
E'\\n','\"';" % INPUT).assertSucceeded()
+ mdb.execute("CREATE USER copyuser WITH PASSWORD 'copyuser' name 'copyuser'
schema sys;").assertSucceeded()
-# import os, sys
-# try:
-# from MonetDBtesting import process
-# except ImportError:
-# import process
-
-# TSTSRCBASE = os.environ['TSTSRCBASE']
-# SRCDIR = os.path.join(TSTSRCBASE, "sql", "benchmarks", "tpch")
-# DATADIR = os.path.join(SRCDIR,"SF-0.01")
+ # TODO: add check for COPY <INTO|FROM> STDIN
+ with SQLTestCase() as usr:
+ # The user has no privileges
+ usr.connect(username="copyuser", password="copyuser")
+ usr.execute("SELECT * FROM t;").assertFailed(err_code="42000",
err_message="SELECT: access denied for copyuser to table 'sys.t'")
+ usr.execute("INSERT INTO t VALUES
(888);").assertFailed(err_code="42000", err_message="INSERT INTO: insufficient
privileges for user 'copyuser' to insert into table 't'")
+ usr.execute("COPY 2 RECORDS INTO t FROM r'%s' USING DELIMITERS ',',
E'\\n','\"';" % INPUT).assertFailed(err_code="42000", err_message="COPY INTO:
insufficient privileges for user 'copyuser' to copy into table 't'")
-# with process.client('sql', stdin = process.PIPE, stdout = process.PIPE,
stderr = process.PIPE) as c:
-# c.stdin.write("CREATE TABLE REGION ( R_REGIONKEY INTEGER NOT NULL,
R_NAME CHAR(25) NOT NULL, R_COMMENT VARCHAR(152));\n")
-# c.stdin.write("COPY 5 RECORDS INTO region from r'%s' USING DELIMITERS
'|', E'|\\n';\n" % os.path.join(DATADIR, 'region.tbl'))
-# c.stdin.write("select count(*) from region;\n")
-# c.stdin.write("CREATE USER copyuser WITH PASSWORD 'copyuser' name
'copyuser' schema sys;\n")
-# c.stdin.write("GRANT INSERT, SELECT on region to copyuser;\n")
-# out, err = c.communicate()
-# sys.stdout.write(out)
-# sys.stderr.write(err.replace(DATADIR,
'$DATADIR').replace(DATADIR.replace('\\', r'\\'), '$DATADIR').replace(r'DIR\\',
'DIR/').replace('DIR\\', 'DIR/'))
+ # Check that for COPY INTO <table> FROM <file>, the user needs both
+ # INSERT and COPY FROM privileges
+ mdb.execute("GRANT INSERT ON t TO copyuser;").assertSucceeded()
+ usr.execute("INSERT INTO t VALUES (888);").assertSucceeded()
+ usr.execute("COPY 2 RECORDS INTO t FROM r'%s' USING DELIMITERS ',',
E'\\n','\"';" % INPUT).assertFailed(err_code="42000", err_message="COPY INTO:
insufficient privileges: COPY INTO from file(s) requires database administrator
rights, use 'COPY INTO \"t\" FROM file ON CLIENT' instead")
+ mdb.execute("GRANT COPY FROM TO copyuser;").assertSucceeded()
+ usr.execute("COPY 2 RECORDS INTO t FROM r'%s' USING DELIMITERS ',',
E'\\n','\"';" % INPUT).assertSucceeded()
-# with process.client('sql', user = 'copyuser', passwd = 'copyuser', stdin =
process.PIPE, stdout = process.PIPE, stderr = process.PIPE) as c:
-# c.stdin.write("COPY 5 RECORDS INTO region from r'%s' USING DELIMITERS
'|', E'|\\n';\n" % os.path.join(DATADIR, 'region.tbl'))
-# c.stdin.write("select count(*) from region;\n")
-# out, err = c.communicate()
-# sys.stdout.write(out)
-# sys.stderr.write(err.replace(DATADIR,
'$DATADIR').replace(DATADIR.replace('\\', r'\\'), '$DATADIR').replace(r'DIR\\',
'DIR/').replace('DIR\\', 'DIR/'))
+ mdb.execute("REVOKE INSERT ON t FROM copyuser;\n").assertSucceeded()
+ usr.execute("COPY 2 RECORDS INTO t FROM r'%s' USING DELIMITERS ',',
E'\\n','\"';" % INPUT).assertFailed(err_code="42000", err_message="COPY INTO:
insufficient privileges for user 'copyuser' to copy into table 't'")
+ mdb.execute("GRANT INSERT ON t TO copyuser;").assertSucceeded()
+ mdb.execute("REVOKE COPY FROM FROM copyuser;\n").assertSucceeded()
+ usr.execute("COPY 2 RECORDS INTO t FROM r'%s' USING DELIMITERS ',',
E'\\n','\"';" % INPUT).assertFailed(err_code="42000", err_message="COPY INTO:
insufficient privileges: COPY INTO from file(s) requires database administrator
rights, use 'COPY INTO \"t\" FROM file ON CLIENT' instead")
-# with process.client('sql', stdin = process.PIPE, stdout = process.PIPE,
stderr = process.PIPE) as c:
-# c.stdin.write("GRANT COPY FROM, COPY INTO to copyuser;\n")
-# out, err = c.communicate()
-# sys.stdout.write(out)
-# sys.stderr.write(err.replace(DATADIR,
'$DATADIR').replace(DATADIR.replace('\\', r'\\'), '$DATADIR').replace(r'DIR\\',
'DIR/').replace('DIR\\', 'DIR/'))
+ # Check that for COPY <table> INTO <file>, the user needs both SELECT
+ # and COPY INTO privileges
+ mdb.execute("GRANT SELECT ON t TO copyuser;").assertSucceeded()
+ usr.execute("SELECT * FROM
t;").assertSucceeded().assertDataResultMatch([(24,), (42,), (888,), (24,),
(42,)])
+ usr.execute("COPY SELECT * FROM t INTO r'%s';" %
OUTPUT).assertFailed(err_code="42000", err_message="COPY INTO: insufficient
privileges: COPY INTO file requires database administrator rights, use 'COPY
... INTO file ON CLIENT' instead")
-# with process.client('sql', user = 'copyuser', passwd = 'copyuser', stdin =
process.PIPE, stdout = process.PIPE, stderr = process.PIPE) as c:
-# c.stdin.write("COPY 5 RECORDS INTO region from r'%s' USING DELIMITERS
'|', E'|\\n';\n" % os.path.join(DATADIR, 'region.tbl'))
-# c.stdin.write("select count(*) from region;\n")
-# out, err = c.communicate()
-# sys.stdout.write(out)
-# sys.stderr.write(err.replace(DATADIR,
'$DATADIR').replace(DATADIR.replace('\\', r'\\'), '$DATADIR').replace(r'DIR\\',
'DIR/').replace('DIR\\', 'DIR/'))
+ mdb.execute("GRANT COPY INTO TO copyuser;").assertSucceeded()
+ usr.execute("COPY SELECT * FROM t INTO r'%s';" %
OUTPUT).assertSucceeded()
-# with process.client('sql', stdin = process.PIPE, stdout = process.PIPE,
stderr = process.PIPE) as c:
-# c.stdin.write("REVOKE COPY FROM, COPY INTO from copyuser;\n")
-# out, err = c.communicate()
-# sys.stdout.write(out)
-# sys.stderr.write(err)
+ mdb.execute("REVOKE COPY INTO FROM copyuser;\n").assertSucceeded()
+ usr.execute("COPY SELECT * FROM t INTO r'%s';" %
OUTPUT).assertFailed(err_code="42000", err_message="COPY INTO: insufficient
privileges: COPY INTO file requires database administrator rights, use 'COPY
... INTO file ON CLIENT' instead")
+
+ mdb.execute("GRANT COPY INTO TO copyuser;").assertSucceeded()
+ mdb.execute("REVOKE SELECT ON t FROM copyuser;\n").assertSucceeded()
+ usr.execute("COPY SELECT * FROM t INTO r'%s';" %
OUTPUT).assertFailed(err_code="42000", err_message="SELECT: access denied for
copyuser to table 'sys.t'")
+
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
