Changeset: ef7a07ab3143 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ef7a07ab3143 Added Files: sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.py sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.err sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.out Modified Files: sql/backends/monet5/sql.c sql/server/sql_privileges.c sql/test/BugTracker-2018/Tests/All Branch: Aug2018 Log Message:
Added test and fixes for bug 6660 (i.e check if user has a role before granting it). The same happens for revoking. A role is revoked only when the user has it. diffs (285 lines): diff --git a/sql/backends/monet5/sql.c b/sql/backends/monet5/sql.c --- a/sql/backends/monet5/sql.c +++ b/sql/backends/monet5/sql.c @@ -582,13 +582,8 @@ setVariable(Client cntxt, MalBlkPtr mb, #else lng sgn = val_get_number(src); #endif - if ((msg = sql_update_var(m, varname, src->val.sval, sgn)) != NULL) { - snprintf(buf, BUFSIZ, "%s", msg); - if (strlen(msg) > 6 && msg[5] == '!') - return msg; - _DELETE(msg); - throw(SQL, "sql.setVariable", SQLSTATE(42100) "%s", buf); - } + if ((msg = sql_update_var(m, varname, src->val.sval, sgn)) != NULL) + return msg; if(!stack_set_var(m, varname, src)) throw(SQL, "sql.setVariable", SQLSTATE(HY001) MAL_MALLOC_FAIL); } else { diff --git a/sql/server/sql_privileges.c b/sql/server/sql_privileges.c --- a/sql/server/sql_privileges.c +++ b/sql/server/sql_privileges.c @@ -505,7 +505,7 @@ sql_grant_role(mvc *m, str grantee, str rid = table_funcs.column_find_row(m->session->tr, auths_name, role, NULL); if (is_oid_nil(rid)) - throw(SQL, "sql.grant_role", SQLSTATE(M1M05) "Cannot grant ROLE '%s' to ROLE '%s'", role, grantee); + throw(SQL, "sql.grant_role", SQLSTATE(M1M05) "GRANT: Cannot grant ROLE '%s' to user '%s'", role, grantee); val = table_funcs.column_find_value(m->session->tr, auths_id, rid); role_id = *(int*)val; _DELETE(val); @@ -513,13 +513,16 @@ sql_grant_role(mvc *m, str grantee, str if (backend_find_user(m, role) >= 0) throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: '%s' is a USER not a ROLE", role); if (!admin_privs(grantor) && !role_granting_privs(m, rid, role_id, grantor)) - throw(SQL,"sql.grant_role", SQLSTATE(0P000) "Insufficient privileges to grant ROLE '%s'", role); + throw(SQL,"sql.grant_role", SQLSTATE(0P000) "GRANT: Insufficient privileges to grant ROLE '%s'", role); rid = table_funcs.column_find_row(m->session->tr, auths_name, grantee, NULL); if (is_oid_nil(rid)) - throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "Cannot grant ROLE '%s' to ROLE '%s'", role, grantee); + throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: Cannot grant ROLE '%s' to user '%s'", role, grantee); val = table_funcs.column_find_value(m->session->tr, auths_id, rid); grantee_id = *(int*)val; _DELETE(val); + rid = table_funcs.column_find_row(m->session->tr, find_sql_column(roles, "login_id"), &grantee_id, find_sql_column(roles, "role_id"), &role_id, NULL); + if (!is_oid_nil(rid)) + throw(SQL,"sql.grant_role", SQLSTATE(M1M05) "GRANT: User '%s' already has ROLE '%s'", grantee, role); table_funcs.table_insert(m->session->tr, roles, &grantee_id, &role_id); if (admin) { @@ -567,10 +570,14 @@ sql_revoke_role(mvc *m, str grantee, str rid = table_funcs.column_find_row(m->session->tr, roles_login_id, &grantee_id, roles_role_id, &role_id, NULL); if (!is_oid_nil(rid)) table_funcs.table_delete(m->session->tr, roles, rid); + else + throw(SQL,"sql.revoke_role", SQLSTATE(42M32) "REVOKE: User '%s' does not have ROLE '%s'", grantee, role); } else { rid = sql_privilege_rid(m, grantee_id, role_id, PRIV_ROLE_ADMIN, 0); if (!is_oid_nil(rid)) table_funcs.table_delete(m->session->tr, roles, rid); + else + throw(SQL,"sql.revoke_role", SQLSTATE(42M32) "REVOKE: User '%s' does not have ROLE '%s'", grantee, role); } m->session->tr->schema_updates++; return NULL; diff --git a/sql/test/BugTracker-2018/Tests/All b/sql/test/BugTracker-2018/Tests/All --- a/sql/test/BugTracker-2018/Tests/All +++ b/sql/test/BugTracker-2018/Tests/All @@ -91,3 +91,4 @@ create_table_empty_column_name.Bug-6653 select_where_true.Bug-6654 alter-sequence-subquery.Bug-6657 quantile-crash.Bug-6658 +grant-role-not-idempotent.Bug-6660 diff --git a/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.py b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.py new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.py @@ -0,0 +1,57 @@ +import sys +try: + from MonetDBtesting import process +except ImportError: + import process + + +def client(input, user, passwd): + c = process.client('sql', user=user, passwd=passwd, stdin=process.PIPE, stdout=process.PIPE, stderr=process.PIPE) + out, err = c.communicate(input) + sys.stdout.write(out) + sys.stderr.write(err) + + +script1 = '''\ +create user "mydummyuser" with password 'mydummyuser' name 'mydummyuser' schema "sys"; +''' + +script2 = '''\ +set role "sysadmin"; --error +''' + +script3 = '''\ +select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +grant "sysadmin" to "mydummyuser"; +''' + +script4 = '''\ +set role "sysadmin"; +''' + +script5 = '''\ +select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +grant "sysadmin" to "mydummyuser"; --error +select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +revoke "sysadmin" from "mydummyuser"; +select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +revoke "sysadmin" from "mydummyuser"; --error +select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +drop user "mydummyuser"; +''' + + +def main(): + s = process.server(stdin=process.PIPE, stdout=process.PIPE, stderr=process.PIPE) + client(script1, 'monetdb', 'monetdb') + client(script2, 'mydummyuser', 'mydummyuser') + client(script3, 'monetdb', 'monetdb') + client(script4, 'mydummyuser', 'mydummyuser') + client(script5, 'monetdb', 'monetdb') + out, err = s.communicate() + sys.stdout.write(out) + sys.stderr.write(err) + + +if __name__ == '__main__': + main() 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 new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.err @@ -0,0 +1,41 @@ +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 > + +MAPI = (mydummyuser) /var/tmp/mtest-4265/.s.monetdb.32462 +QUERY = set role "sysadmin"; --error +ERROR = !Role (sysadmin) missing +CODE = 42000 +MAPI = (monetdb) /var/tmp/mtest-30651/.s.monetdb.34282 +QUERY = grant "sysadmin" to "mydummyuser"; --error +ERROR = !GRANT: User 'mydummyuser' already has ROLE 'sysadmin' +CODE = M1M05 +MAPI = (monetdb) /var/tmp/mtest-30651/.s.monetdb.34282 +QUERY = revoke "sysadmin" from "mydummyuser"; --error +ERROR = !REVOKE: User 'mydummyuser' does not have ROLE 'sysadmin' +CODE = 42M32 +# builtin opt gdk_dbpath = /home/ferreira/repositories/MonetDB-Aug2018/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 = 34282 +# cmdline opt mapi_usock = /var/tmp/mtest-30651/.s.monetdb.34282 +# cmdline opt monet_prompt = +# cmdline opt gdk_dbpath = /home/ferreira/repositories/MonetDB-Aug2018/BUILD/var/MonetDB/mTests_sql_test_BugTracker-2018 +# cmdline opt gdk_debug = 553648138 + +# 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 new file mode 100644 --- /dev/null +++ b/sql/test/BugTracker-2018/Tests/grant-role-not-idempotent.Bug-6660.stable.out @@ -0,0 +1,99 @@ +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 > + +#create user "mydummyuser" with password 'mydummyuser' name 'mydummyuser' schema "sys"; +#select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +% sys.L5 # table_name +% L5 # name +% bigint # type +% 1 # length +[ 0 ] +#grant sysadmin to mydummyuser; +#set role sysadmin; +#select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +% sys.L5 # table_name +% L5 # name +% bigint # type +% 1 # length +[ 1 ] +#select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +% sys.L5 # table_name +% L5 # name +% bigint # type +% 1 # length +[ 1 ] +#revoke "sysadmin" from "mydummyuser"; +#select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +% sys.L5 # table_name +% L5 # name +% bigint # type +% 1 # length +[ 0 ] +#select count(*) from "user_role" where "login_id" in (select "id" from "sys"."auths" where "name" = 'mydummyuser'); +% sys.L5 # table_name +% L5 # name +% bigint # type +% 1 # length +[ 0 ] +#drop user "mydummyuser"; +# MonetDB 5 server v11.31.12 +# This is an unreleased version +# Serving database 'mTests_sql_test_BugTracker-2018', using 8 threads +# Compiled for x86_64-pc-linux-gnu/64bit with 128bit integers +# Found 15.492 GiB available main-memory. +# Copyright (c) 1993 - July 2008 CWI. +# Copyright (c) August 2008 - 2018 MonetDB B.V., all rights reserved +# Visit https://www.monetdb.org/ for further information +# Listening for connection requests on mapi:monetdb://wired-142.cwi.nl:34282/ +# Listening for UNIX domain connection requests on mapi:monetdb:///var/tmp/mtest-30651/.s.monetdb.34282 +# MonetDB/GIS module loaded +# SQL catalog created, loading sql scripts once +# loading sql script: 09_like.sql +# loading sql script: 10_math.sql +# loading sql script: 11_times.sql +# loading sql script: 12_url.sql +# loading sql script: 13_date.sql +# loading sql script: 14_inet.sql +# loading sql script: 15_querylog.sql +# loading sql script: 16_tracelog.sql +# loading sql script: 17_temporal.sql +# loading sql script: 18_index.sql +# loading sql script: 20_vacuum.sql +# loading sql script: 21_dependency_views.sql +# loading sql script: 22_clients.sql +# loading sql script: 23_skyserver.sql +# loading sql script: 25_debug.sql +# loading sql script: 26_sysmon.sql +# loading sql script: 27_rejects.sql +# loading sql script: 39_analytics.sql +# loading sql script: 39_analytics_hge.sql +# loading sql script: 40_geom.sql +# loading sql script: 40_json.sql +# loading sql script: 40_json_hge.sql +# loading sql script: 41_md5sum.sql +# loading sql script: 45_uuid.sql +# loading sql script: 46_profiler.sql +# loading sql script: 51_sys_schema_extension.sql +# loading sql script: 60_wlcr.sql +# loading sql script: 72_fits.sql +# loading sql script: 74_netcdf.sql +# loading sql script: 75_lidar.sql +# loading sql script: 75_shp.sql +# loading sql script: 75_storagemodel.sql +# loading sql script: 80_statistics.sql +# loading sql script: 80_udf.sql +# loading sql script: 80_udf_hge.sql +# loading sql script: 85_bam.sql +# loading sql script: 90_generator.sql +# loading sql script: 90_generator_hge.sql +# loading sql script: 99_system.sql +# MonetDB/SQL module loaded + +# 17:35:20 > +# 17:35:20 > "Done." +# 17:35:20 > + _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list