Changeset: 2cb3d7b98aeb for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2cb3d7b98aeb
Branch: unlock
Log Message:
merged
diffs (truncated from 447 to 300 lines):
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);
@@ -76,7 +79,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/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()
+ mdb.execute("INSERT INTO test (id, name,address) VALUES (1,'Tom',
'planet'),(2,'Karen', 'earth');").assertSucceeded()
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='monetdb', password='monetdb')
-cur1 = client1.cursor()
-cur1.execute('''
-REVOKE SELECT ON "myschema"."test" FROM myuser;
-GRANT SELECT ("name") ON "myschema"."test" TO myuser;
-''')
-cur1.close()
-client1.close()
+ with SQLTestCase() as tc:
+ tc.connect(username="myuser", password="Test123")
+ # 'myuser' cannot SELECT before GRANT and after REVOKE, can SELECT
after GRANT
+ tc.execute("select * from test").assertFailed(err_code='42000',
err_message="SELECT: access denied for myuser to table 'myschema.test'")
+ mdb.execute("GRANT SELECT ON test TO myuser;").assertSucceeded()
+ tc.execute("select * from test").assertRowCount(2)
+ mdb.execute("REVOKE SELECT ON test FROM myuser;").assertSucceeded()
+ tc.execute("select * from test").assertFailed(err_code='42000',
err_message="SELECT: access denied for myuser to table 'myschema.test'")
+
+ # 'myuser' can SELECT test(id)
+ mdb.execute("GRANT SELECT (id) ON test TO myuser;").assertSucceeded()
+ tc.execute("select id from test").assertDataResultMatch([(1,),(2,)])
+ tc.execute("select name from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'name' unknown")
+ tc.execute("select address from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'address' unknown")
+ tc.execute("select * from test").assertDataResultMatch([(1,),(2,)])
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='myuser', password='Test123')
-cur1 = client1.cursor()
-try:
- cur1.execute('SELECT "id", "name" FROM "myschema"."test";')
- sys.stderr.write("Exception expected")
-except pymonetdb.DatabaseError as e:
- if "identifier 'id' unknown" not in str(e):
- sys.stderr.write("Error: identifier 'id' unknown expected")
-cur1.execute('SELECT "name" FROM "myschema"."test";')
-if cur1.fetchall() != [('Tom',), ('Karen',)]:
- sys.stderr.write('Expected result: [(\'Tom\',), (\'Karen\',)]')
-cur1.close()
-client1.close()
+ # 'myuser' can SELECT test(id, address)
+ mdb.execute("GRANT SELECT (address) ON test TO
myuser;").assertSucceeded()
+ tc.execute("select id from test").assertDataResultMatch([(1,),(2,)])
+ tc.execute("select name from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'name' unknown")
+ tc.execute("select address from
test").assertDataResultMatch([('planet',),('earth',)])
+ tc.execute("select * from
test").assertDataResultMatch([(1,'planet'),(2,'earth')])
-client1 = pymonetdb.connect(database=db, port=port, autocommit=True,
username='monetdb', password='monetdb')
-cur1 = client1.cursor()
-cur1.execute('''
-DROP USER myuser;
-DROP SCHEMA "myschema" CASCADE;
-''')
-cur1.close()
-client1.close()
+ # 'myuser' can only SELECT test(address)
+ mdb.execute("REVOKE SELECT (id) ON test FROM
myuser;").assertSucceeded()
+ tc.execute("select id from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'id' unknown")
+ tc.execute("select name from test").assertFailed(err_code='42000',
err_message="SELECT: identifier 'name' unknown")
+ tc.execute("select address from
test").assertDataResultMatch([('planet',),('earth',)])
+ tc.execute("select * from
test").assertDataResultMatch([('planet',),('earth',)])
+
+ # clean up
+ mdb.execute("SET SCHEMA sys;").assertSucceeded()
+ mdb.execute("DROP USER myuser;").assertSucceeded()
+ mdb.execute("DROP SCHEMA myschema CASCADE;").assertSucceeded()
diff --git
a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
deleted file mode 100644
--- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.err
+++ /dev/null
@@ -1,12 +0,0 @@
-stderr of test 'grant-select-column.Bug-6765` in directory
'sql/test/BugTracker-2019` itself:
-
-
-# 11:35:12 >
-# 11:35:12 > "/usr/bin/python3" "grant-select-column.Bug-6765.py"
"grant-select-column.Bug-6765"
-# 11:35:12 >
-
-
-# 11:35:12 >
-# 11:35:12 > "Done."
-# 11:35:12 >
-
diff --git
a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
b/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
deleted file mode 100644
--- a/sql/test/BugTracker-2019/Tests/grant-select-column.Bug-6765.stable.out
+++ /dev/null
@@ -1,12 +0,0 @@
-stdout of test 'grant-select-column.Bug-6765` in directory
'sql/test/BugTracker-2019` itself:
-
-
-# 11:34:49 >
-# 11:34:49 > "/usr/bin/python3" "grant-select-column.Bug-6765.py"
"grant-select-column.Bug-6765"
-# 11:34:49 >
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
