Cannot revoke object access

[monetdb-team]

Date: 2014-04-21 23:38:36 +0200
From: Boris Aronshtam <>
To: SQL devs <>
Version: 11.15.15 (Feb2013-SP4)
CC: @njnes, vera.matei

Last updated: 2015-08-28 13:41:40 +0200

Comment 19765

Date: 2014-04-21 23:38:36 +0200
From: Boris Aronshtam <>

The following test-case creates user1 and user2. User1 creates a table u1 and grants some access to it. But after user1 revokes access, user2 is still able to access u1.

-- admin
create role user1_role;
create schema user1_schema authorization user1_role;
create user "user1" with password 'password' name 'User1' schema "user1_schema";
create user "user2" with password 'password' name 'User2' schema "pub";
grant user1_role to user1;
grant pub_role to user2;

-- user1
set role user1_role;
create table u1(c1 int, c2 int);
insert into u1 values(1,101),(2,102);
grant SELECT on u1 to user2; -- ALL | INSERT | DELETE | [ UPDATE | SELECT | REFERENCES ] opt_column_list

-- user2
select * from user1_schema.u1;
insert into user1_schema.u1 values(3,103); -- insufficient privileges

-- user1
grant INSERT on u1 to user2;

-- user2
insert into user1_schema.u1 values(3,103); -- OK now

-- user1
revoke SELECT on u1 from user2;
revoke INSERT on u1 from user2;

-- user2
select * from user1_schema.u1; -- BUG: user2 still can select
insert into user1_schema.u1 values(3,103); -- BUG: user2 still can insert

Comment 20994

Date: 2015-07-13 22:39:32 +0200
From: vera <<vera.matei>>

The revoking takes place only if the the user signs out first. Test has been added to the test suite of the default branch.

Comment 21078

Date: 2015-08-01 15:18:14 +0200
From: MonetDB Mercurial Repository <>

Changeset 30d12a4105a0 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=30d12a4105a0

Changeset description:

Bug fixes

Bug #3759 fixed types of returned bats (ie use timestamps instead of lng) in modules/mal/clients.c
Bug #3769 fixed crash (triggered assert) by properly looking for referenced groupby columns in push aggr down
optimizer in rel_optimizer.c
Bug #3760 fixed correlated subquery handling in rel_select.c, ie merge only distinct expressions.
Bug #3761 fixed by properly adding the zero_or_one aggregation only when needed (ie when the cardinality of
the outer query is a column/table, ie when we expect one value per row for the subquery).
Bug #3364 fixed in sql_privileges.c, ie allow set role when role_id == auth_id (ie default role).
Bug #3365 fixed in sql_privileges.c, the sql_grant_table_privs always added 'all' privileges in one go
	       in rel_schema.c properly pass the login_id or role_id based on the 'from current_{role/user}'
		part of the grant statement
Bug #3476 was already fixed by previous revoke / schema fixes.

in gdk/gdk_select.c fixed problem with anti select and nils (stop before first nil instead of last)

Comment 21182

Date: 2015-08-28 13:41:40 +0200
From: @sjoerdmullender

Jul2015 has been released.