Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

GRANT SELECT privilege on a subset of table columns results in access denied error when selecting the same columns from the table #6765

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

Date: 2019-09-26 15:19:03 +0200
From: Frank Groot <>
To: SQL devs <>
Version: 11.35.9 (Nov2019-SP1)
CC: @njnes

Last updated: 2020-02-24 13:21:58 +0100

Comment 27324

Date: 2019-09-26 15:19:03 +0200
From: Frank Groot <>

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.90 Safari/537.36
Build Identifier:

After creating a table with 2 columns and granting a user SELECT privileges on one of the columns, the user cannot perform SELECT queries on any of the table columns.

Reproducible: Always

Steps to Reproduce:

Use a session with user monetdb;
CREATE schema myschema;

Create a table with two columns and add some data
DROP TABLE IF EXISTS "myschema"."test";
CREATE TABLE "myschema"."test"
(
id integer,
name varchar(20)
);
INSERT INTO "myschema"."test" (id,name) VALUES (1,'Tom'),(2,'Karen');

CREATE an arbitrary user
drop user myuser;
CREATE USER myuser WITH UNENCRYPTED PASSWORD 'Test123' NAME 'Hulk' SCHEMA myschema;

Test GRANT permissions on the entire table
--Under session with user monetdb
GRANT SELECT ON myschema.test TO myuser ;

--This under a session with user myuser ************ WORKS!!! ***************
SELECT id, name FROM myschema.test;

--Under session with user monetdb
REVOKE SELECT ON myschema.test FROM myuser

Test GRANT permissions on one column of the table (column "name")
--Under session with user monetdb
GRANT SELECT (name) ON myschema.test TO myuser

--This under a session with user myuser ************ DOESN'T WORK!!! ************
SELECT name FROM myschema.test;

ERROR:
ExampleExceptionFormatter: exception message was: SELECT: access denied for myuser to table 'myschema.test'

--Under session with user monetdb
REVOKE SELECT (name) ON myschema.test FROM myuser

Actual Results:

I received an error with user on selecting just the table columns that were specifically granted in the grant script before.

Expected Results:

I expected to be able to select a subset of table columns that were granted to the user account I was using to run the query.

The functionality is mentioned in bug: 6525 in a comment by Martin van Dinther
(https://www.monetdb.org/bugzilla/show_bug.cgi?id=6525)

Without this fix it's almost impossible to implement a hierarchical security strategy using VIEWs that are allowed to select subsets of tables.

Comment 27333

Date: 2019-09-30 12:08:26 +0200
From: MonetDB Mercurial Repository <>

Changeset ed05003bf33e 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=ed05003bf33e

Changeset description:

Added test for bug #6765

Comment 27419

Date: 2019-11-26 22:36:15 +0100
From: MonetDB Mercurial Repository <>

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

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

Changeset description:

fixed bug #6765, make sure we allow access to columns which the users has
SELECT priviliges on.

Comment 27517

Date: 2020-01-24 16:28:38 +0100
From: Frank Groot <>

We are now testing MonetDb v11.35.9.

Running the testscript results in unexpected message after performing step 5.

Granting the individual column 'name' and subsequent selection of the field results in 'access denied' error:
SELECT: access denied for myuser to table 'myschema.test'

Can you verify this issue?

Kind regards,
Frank

Comment 27578

Date: 2020-02-24 13:21:58 +0100
From: Frank Groot <>

We hadn't updated all of the rpm's. Sorry for any inconvenience!
Kind regards,
Frank

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant