Unable to grant object privileges while having a non-default current_role

[monetdb-team]

Date: 2013-09-20 23:50:56 +0200
From: Tim H. <<monetdb.bug.reporter>>
To: SQL devs <>
Version: 11.17.9 (Jan2014)
CC: @dnedev, @njnes

Last updated: 2015-08-28 13:42:26 +0200

Comment 19167

Date: 2013-09-20 23:50:56 +0200
From: Tim H. <<monetdb.bug.reporter>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/29.0.1547.66 Safari/537.36
Build Identifier:

If a user has a non-default current_role that is the schema authorization for the current_schema then this user will be unable to grant object privileges on tables in the current_schema.

Reproducible: Always

Steps to Reproduce:

1. Connect as user monetdb and run the following SQL statements:
   create role hr_role;
   create schema hr authorization hr_role;
   create user blake with password 'password' name 'Blake' schema "hr";
   create user clark with password 'password' name 'Clark' schema "hr";
   grant hr_role to blake;

2. Connect as user blake and run the following SQL statements:
   set role hr_role;
   create table employees (id bigint,name varchar(20));
   grant select on employees to clark;
   grant select on employees to clark from current_role;

Actual Results:

operation successful
operation successful
CREATE TABLE: insufficient privileges for user 'blake' in schema 'hr'
CREATE TABLE: insufficient privileges for user 'blake' in schema 'hr'

Expected Results:

operation successful
operation successful
CREATE TABLE: insufficient privileges for user 'blake' in schema 'hr'
operation successful

A debugging session reveals that in sql_grant_table_privs, the grantor is set to sql->user_id whether or not "from current_role" is used in the grant command. This suggests an issue in sql_parser.y.

---

The GRANT syntax documentation has errors at http://www.monetdb.org/Documentation/SQLreference/Permissions.

Instead of this:
grant:
GRANT privileges TO grantees [ WITH GRANT OPTION ] [ WITH ADMIN grantor ]
| GRANT authid_list TO grantees [ WITH ADMIN OPTION ] [ WITH ADMIN grantor ]

It should read this:
grant:
GRANT privileges TO grantees [ WITH GRANT OPTION ] [ FROM grantor ]
| GRANT authid_list TO grantees [ WITH ADMIN OPTION ] [ FROM grantor ]

Also, the following should be on a new line:
grantees: [ PUBLIC | authid ] ','...

Comment 21074

Date: 2015-08-01 15:17:56 +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 21094

Date: 2015-08-03 17:18:58 +0200
From: MonetDB Mercurial Repository <>

Changeset fa8ff2df52b2 made by Vera Matei vera.matei@monetdbsolutions.com in the MonetDB repo, refers to this bug.

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

Changeset description:

Added the test case (from bugzilla) for bug #3365.

Comment 21212

Date: 2015-08-28 13:42:26 +0200
From: @sjoerdmullender

Jul2015 has been released.