Changeset: 94068363dd5b for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=94068363dd5b Modified Files: sql/backends/monet5/sql_user.c sql/test/Users/Tests/All sql/test/Users/Tests/userCallFunction.SQL.py Branch: indirect-privs Log Message:
Merged with default diffs (truncated from 1050 to 300 lines): diff --git a/documentation/source/developers_handbook.rst b/documentation/source/developers_handbook.rst --- a/documentation/source/developers_handbook.rst +++ b/documentation/source/developers_handbook.rst @@ -69,20 +69,35 @@ directory where they reside:: [$src_root/sql/test/json/Tests]$ Mtest.py . -Adding a new test +Adding sqllogic test ----------------- -Summarizing the above discussion, to add a new test, you need to write the test -itself, create the stable output and error files, and finally add the test to the -``All`` index. This will make ensure that the test will be picked up by -``Mtest.py`` as part of its group. +See `<https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki/>`_ for detail information +on how to structure sqllogic test if you desire to make one by hand. We have extended the +sqllogic protocol a bit further:: + + skipif <system> + onlyif <system> -To create the correct stable output you can use the ``Mapprove.py`` utility. -First create empty ``.stable.{out,err}`` files and run the test using -``Mtest.py``. Check the output and if it is correct, run ``Mapprove.py`` with -the same arguments. This will add the correct contents to the -``.stable.{out,err}`` files. Commit the changes to the VCS and the test can now -be used by other developers and the nightly testing infrastructure. + statement (ok|ok rowcount|error) [arg] + query (I|T|R)+ (nosort|rowsort|valuesort|python)? [arg] + I: integer; T: text (string); R: real (decimal) + nosort: do not sort + rowsort: sort rows + valuesort: sort individual values + python some.python.function: run data through function (MonetDB extension) + hash-threshold number + halt + +Alternatively ``.sql`` scripts can be converted to sqllogic tests (.test) with ``Mconvert.py``. +For example:: + + $Mconvert.py --auto <module>/Tests <convert_me>.sql + +All new tests need to be placed in the appropriate test folder and their name respectively in the +index ``All`` file. Python tests API ---------------- + +See many of the examples in ``sql/test/Users/Tests``. diff --git a/sql/backends/monet5/sql_user.c b/sql/backends/monet5/sql_user.c --- a/sql/backends/monet5/sql_user.c +++ b/sql/backends/monet5/sql_user.c @@ -35,7 +35,10 @@ monet5_drop_user(ptr _mvc, str user) str err; Client c = MCgetClient(m->clientid); + int grant_user = c->user; + c->user = MAL_ADMIN; err = AUTHremoveUser(c, user); + c->user = grant_user; if (err !=MAL_SUCCEED) { (void) sql_error(m, 02, "DROP USER: %s", getExceptionMessage(err)); _DELETE(err); @@ -161,7 +164,10 @@ monet5_create_user(ptr _mvc, str user, s pwd = passwd; } /* add the user to the M5 authorisation administration */ + int grant_user = c->user; + c->user = MAL_ADMIN; ret = AUTHaddUser(&uid, c, user, pwd); + c->user = grant_user; if (!enc) free(pwd); if (ret != MAL_SUCCEED) diff --git a/sql/jdbc/tests/Tests/All b/sql/jdbc/tests/Tests/All --- a/sql/jdbc/tests/Tests/All +++ b/sql/jdbc/tests/Tests/All @@ -1,39 +1,10 @@ HAVE_JDBCTESTS?JDBC_API_Tester HAVE_JDBCTESTS?SQLcopyinto -HAVE_JDBCTESTS?Test_Cautocommit -HAVE_JDBCTESTS?Test_CisValid -HAVE_JDBCTESTS?Test_Clargequery -HAVE_JDBCTESTS?Test_Cmanycon -HAVE_JDBCTESTS?Test_Creplysize -HAVE_JDBCTESTS?Test_Csavepoints #HAVE_JDBCTESTS?Test_Csendthread # unfortunately has runtime-dependant output -HAVE_JDBCTESTS?Test_Ctransaction -HAVE_JDBCTESTS?Test_Dobjects -HAVE_JDBCTESTS?Test_FetchSize HAVE_JDBCTESTS&HAVE_HGE?Test_Int128 HAVE_JDBCCLIENT_JAR?Test_JdbcClient HAVE_JDBCCLIENT_JAR?bogus-auto-generated-keys -HAVE_JDBCTESTS?Test_PSgeneratedkeys -HAVE_JDBCTESTS?Test_PSgetObject -HAVE_JDBCTESTS?Test_PSlargebatchval -HAVE_JDBCTESTS?Test_PSlargeresponse #HAVE_JDBCTESTS?Test_PSlargeamount # scalabity test which is disabled by default (it takes a long time to run and does not need to be run everytime, only before a new release) -HAVE_JDBCTESTS?Test_PSmanycon -HAVE_JDBCTESTS?Test_PSmetadata -HAVE_JDBCTESTS?Test_PSsomeamount -HAVE_JDBCTESTS?Test_PSsqldata -HAVE_JDBCTESTS?Test_PStimedate -HAVE_JDBCTESTS?Test_PStimezone -HAVE_JDBCTESTS?Test_PStypes -HAVE_JDBCTESTS?Test_CallableStmt -HAVE_JDBCTESTS?Test_Rbooleans -HAVE_JDBCTESTS?Test_Rmetadata -HAVE_JDBCTESTS?Test_Rpositioning -HAVE_JDBCTESTS?Test_Rsqldata -HAVE_JDBCTESTS?Test_Rtimedate -HAVE_JDBCTESTS?Test_Sbatching -HAVE_JDBCTESTS?Test_Smoreresults -HAVE_JDBCTESTS?Test_Wrapper HAVE_JDBCTESTS?BugConcurrent_clients_SF_1504657 HAVE_JDBCTESTS?BugConcurrent_sequences HAVE_JDBCTESTS?BugExecuteUpdate_Bug_3350 diff --git a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.SQL.py b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.SQL.py --- a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.SQL.py +++ b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.SQL.py @@ -1,67 +1,35 @@ -import sys, os, pymonetdb - -db = os.getenv("TSTDB") -port = int(os.getenv("MAPIPORT")) - -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='monetdb', password='monetdb') -cur1 = client1.cursor() -cur1.execute('create user "mydummyuser" with password \'mydummyuser\' name \'mydummyuser\' schema "sys";') -cur1.close() -client1.close() +### +# Check that GRANT a ROLE to a USER once works, but GRANT it a second time is +# properly rejected with "GRANT: User '<usr>' already has ROLE '<role>'", +# which also prevents the related problems described in this bug, i.e. +# duplicate entries are created in sys.user_role and subsequent REVOKE +# doesn't work. +### -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='mydummyuser', password='mydummyuser') -cur1 = client1.cursor() -try: - cur1.execute('set role "sysadmin"; --error') - sys.stderr.write("Exception expected") -except pymonetdb.DatabaseError as e: - if "Role (sysadmin) missing" not in str(e): - sys.stderr.write("Error: Role (sysadmin) missing") -cur1.close() -client1.close() +from MonetDBtesting.sqltest import SQLTestCase -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='monetdb', password='monetdb') -cur1 = client1.cursor() -cur1.execute('select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = \'mydummyuser\');') -if cur1.fetchall() != [(0,)]: - sys.stderr.write('Expected result: [(0,)]') -cur1.execute('grant "sysadmin" to "mydummyuser";') -cur1.close() -client1.close() +with SQLTestCase() as mdb: + mdb.connect(username="monetdb", password="monetdb") + mdb.execute("create user mydummyuser with password 'mydummyuser' name 'mydummyuser' schema sys;").assertSucceeded() + + with SQLTestCase() as tc: + tc.connect(username="mydummyuser", password="mydummyuser") + tc.execute("set role sysadmin;").assertFailed(err_code="42000", err_message="Role (sysadmin) missing") -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='mydummyuser', password='mydummyuser') -cur1 = client1.cursor() -cur1.execute('set role "sysadmin";') -cur1.close() -client1.close() + mdb.execute("select count(*) from user_role where login_id in (select id from sys.auths where name = 'mydummyuser');").assertDataResultMatch([(0,)]) + mdb.execute("grant sysadmin to mydummyuser;").assertSucceeded() + mdb.execute("select count(*) from user_role where login_id in (select id from sys.auths where name = 'mydummyuser');").assertDataResultMatch([(1,)]) + + tc.execute("set role sysadmin;").assertSucceeded() + + mdb.execute("grant sysadmin to mydummyuser;").assertFailed(err_code="M1M05", err_message="GRANT: User 'mydummyuser' already has ROLE 'sysadmin'") + mdb.execute("select count(*) from user_role where login_id in (select id from sys.auths where name = 'mydummyuser');").assertDataResultMatch([(1,)]) -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='monetdb', password='monetdb') -cur1 = client1.cursor() -cur1.execute('select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = \'mydummyuser\');') -if cur1.fetchall() != [(1,)]: - sys.stderr.write('Expected result: [(1,)]') -try: - cur1.execute('grant "sysadmin" to "mydummyuser"; --error') - sys.stderr.write("Exception expected") -except pymonetdb.DatabaseError as e: - if "User 'mydummyuser' already has ROLE 'sysadmin'" not in str(e): - sys.stderr.write("Error: User 'mydummyuser' already has ROLE 'sysadmin'") -cur1.execute('select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = \'mydummyuser\');') -if cur1.fetchall() != [(1,)]: - sys.stderr.write('Expected result: [(1,)]') -cur1.execute('revoke "sysadmin" from "mydummyuser";') -cur1.execute('select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = \'mydummyuser\');') -if cur1.fetchall() != [(0,)]: - sys.stderr.write('Expected result: [(0,)]') -try: - cur1.execute('revoke "sysadmin" from "mydummyuser"; --error') - sys.stderr.write("Exception expected") -except pymonetdb.DatabaseError as e: - if "User 'mydummyuser' does not have ROLE 'sysadmin'" not in str(e): - sys.stderr.write("Error: User 'mydummyuser' does not have ROLE 'sysadmin'") -cur1.execute('select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = \'mydummyuser\');') -if cur1.fetchall() != [(0,)]: - sys.stderr.write('Expected result: [(0,)]') -cur1.execute('drop user "mydummyuser";') -cur1.close() -client1.close() + mdb.execute("revoke sysadmin from mydummyuser;").assertSucceeded() + mdb.execute("select count(*) from user_role where login_id in (select id from sys.auths where name = 'mydummyuser');").assertDataResultMatch([(0,)]) + + mdb.execute("revoke sysadmin from mydummyuser;").assertFailed(err_code="01006", err_message="REVOKE: User 'mydummyuser' does not have ROLE 'sysadmin'") + mdb.execute("select count(*) from user_role where login_id in (select id from sys.auths where name = 'mydummyuser');").assertDataResultMatch([(0,)]) + + # clean up + mdb.execute("drop user mydummyuser;").assertSucceeded() diff --git a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.err b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.err deleted file mode 100644 --- a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.err +++ /dev/null @@ -1,12 +0,0 @@ -stderr of test 'grant-role-not-idempotent.Bug-6660` in directory 'sql/test/BugTracker-2018` itself: - - -# 17:35:19 > -# 17:35:19 > "/usr/bin/python2" "grant-role-not-idempotent.Bug-6660.py" "grant-role-not-idempotent.Bug-6660" -# 17:35:19 > - - -# 17:35:20 > -# 17:35:20 > "Done." -# 17:35:20 > - diff --git a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.out b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.out deleted file mode 100644 --- a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.out +++ /dev/null @@ -1,12 +0,0 @@ -stdout of test 'grant-role-not-idempotent.Bug-6660` in directory 'sql/test/BugTracker-2018` itself: - - -# 17:35:19 > -# 17:35:19 > "/usr/bin/python2" "grant-role-not-idempotent.Bug-6660.py" "grant-role-not-idempotent.Bug-6660" -# 17:35:19 > - - -# 17:35:20 > -# 17:35:20 > "Done." -# 17:35:20 > - diff --git a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py --- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py +++ b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.py @@ -1,61 +1,52 @@ -import sys, os, pymonetdb +### +# Check that when a user is granted (select) access to some columns in a table, +# the user can indeed access those columns. +# In addition, check that after the access to some of the granted columns has +# been revoked, the user can access the remaining columns. +### -db = os.getenv("TSTDB") -port = int(os.getenv("MAPIPORT")) -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='monetdb', password='monetdb') -cur1 = client1.cursor() -cur1.execute(''' -START TRANSACTION; -CREATE schema "myschema"; -CREATE TABLE "myschema"."test" ("id" integer, "name" varchar(20)); -INSERT INTO "myschema"."test" ("id", "name") VALUES (1,'Tom'),(2,'Karen'); -CREATE USER myuser WITH UNENCRYPTED PASSWORD 'Test123' NAME 'Hulk' SCHEMA "myschema"; -GRANT SELECT ON "myschema"."test" TO myuser; -COMMIT; -''') -cur1.execute('SELECT "name" FROM "myschema"."test";') -if cur1.fetchall() != [('Tom',), ('Karen',)]: - sys.stderr.write('Expected result: [(\'Tom\',), (\'Karen\',)]') -cur1.close() -client1.close() +from MonetDBtesting.sqltest import SQLTestCase -client1 = pymonetdb.connect(database=db, port=port, autocommit=True, username='myuser', password='Test123') -cur1 = client1.cursor() -cur1.execute('SELECT "id", "name" FROM "myschema"."test";') -if cur1.fetchall() != [(1,'Tom'),(2,'Karen')]: - sys.stderr.write('Expected result: [(1,\'Tom\'),(2,\'Karen\')]') -cur1.close() -client1.close() +with SQLTestCase() as mdb: + mdb.connect(username="monetdb", password="monetdb") + mdb.execute("CREATE schema myschema;").assertSucceeded() + mdb.execute("CREATE USER myuser WITH UNENCRYPTED PASSWORD 'Test123' NAME 'Hulk' SCHEMA myschema;").assertSucceeded() + mdb.execute("SET SCHEMA myschema;").assertSucceeded() + mdb.execute("CREATE TABLE test (id integer, name varchar(20), address varchar(20));").assertSucceeded() _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list
