GRANTing a ROLE is not idempotent

[monetdb-team]

Date: 2018-11-09 19:20:38 +0100
From: Till W <<till.wollenberg>>
To: SQL devs <>
Version: 11.31.11 (Aug2018-SP1)
CC: @njnes, till.wollenberg

Last updated: 2019-01-14 17:29:06 +0100

Comment 26676

Date: 2018-11-09 19:20:38 +0100
From: Till W <<till.wollenberg>>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:63.0) Gecko/20100101 Firefox/63.0
Build Identifier:

While trying to GRANT the sysadmin role to a (formerly) unprivileged user I noticed that if I execute the GRANT statement more than once I get unexpected results:

Step 1: before having the ROLE GRANTed

(as user monetdb)
sql>select * from user_role;
+----------+---------+
| login_id | role_id |
+==========+=========+
+----------+---------+
0 tuples

(as the unprivileged user)
sql>set role sysadmin;
SQLException:sql.update_var:42000!Role (sysadmin) missing

Outcome: doesn't work which is expected

Step 2: GRANing the role

(as user monetdb)
sql> grant sysadmin to mydummyuser;
operation successful
sql>select * from user_role;
+----------+---------+
| login_id | role_id |
+==========+=========+
| 7227 | 2 |
+----------+---------+
1 tuple

(as the unprivileged user)
sql>set role sysadmin;
operation successful

Outcome: does work which is expected

Step 3 GRANTing the role again:

(as user monetdb)
sql> grant sysadmin to mydummyuser;
operation successful
sql>select * from user_role;
+----------+---------+
| login_id | role_id |
+==========+=========+
| 7227 | 2 |
| 7227 | 2 |
+----------+---------+
2 tuples

(as the unprivileged user)
sql>set role sysadmin;
SQLException:sql.update_var:42000!Role (sysadmin) missing

Outcome: does not work which is not expected plus sys.user_role has redundant entries

Also, revoking the role does not work in this state either:

(as user monetdb)
sql>revoke sysadmin from vuser;
operation successful
sql>select * from user_role;
+----------+---------+
| login_id | role_id |
+==========+=========+
| 7227 | 2 |
| 7227 | 2 |
+----------+---------+
2 tuples

Outcome: revoking the role is no longer possible, which is not expected.

I ended up executing 'delete from user_role where login_id=7227;' and re-grating the role to mydummyuser as a workaround.

Reproducible: Always

Comment 26680

Date: 2018-11-15 17:49:55 +0100
From: MonetDB Mercurial Repository <>

Changeset ef7a07ab3143 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=ef7a07ab3143

Changeset description:

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.