question Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table in Archives of Support Questions (Read Only)

INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table issue ?!?!

geko — Fri, 16 Sep 2022 11:17:07 GMT

Hi,

I am playing around with Sentry and want to provide "write-only" permission to a user via

grant insert ON default.shipment_test TO ROLE ingester;

But if I connect to hive via beeline and execute an insert statement, I receive the error:

0: jdbc:hive2://quickstart.cloudera:10000/def> insert into shipment_test values (1,'1111');
Error: Error while compiling statement: FAILED: SemanticException No valid privileges
 User writer does not have privileges for QUERY
 The required privileges: Server=server1->Db=default->Table=values__tmp__table__2->Column=tmp_values_col1->action=select; (state=42000,code=40000)

The environment is CDH sandbox 5.8

Linux user 'writer' is member of group 'ingester';

The group 'ingester' is assigned to the proper role in Sentry and was given the INSERT privilege:

grant role sentry_ingester to group ingester;
grant INSERT ON default.shipment_test TO ROLE sentry_ingester;

Why the statement errors out due to a "select" issue on a tmp table ?!?!

Do I have to specify some more privileges, or how do you grant "INSERT"-only permissions to a group ?

Thanks in advance...

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

saranvisa — Mon, 20 Mar 2017 17:48:40 GMT

@geko

You should also consider the permission in folder owner/group where the data will be stored

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

geko — Mon, 20 Mar 2017 19:04:26 GMT

Thanks @saranvisa for replying.

But the HDFS permissions should be adjusted by Sentry itself, since HDFS sync is enabled, right ?

Nevertheless, HDFS permissions look like:

# file: /user/hive/warehouse/shipment_test
# owner: hive
# group: hive
group:ingester:-wx

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

saranvisa — Mon, 20 Mar 2017 20:23:10 GMT

@geko

Until you familiar with available roles/privileges, I would recommend you to use Sentry from Hue. it will auto fill all the available options (or) you just need to choose the available options. It will make your life easier

Pre-request: Make sure your linux users/groups are exactly matches to Hue users/groups

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

mathieu.d — Wed, 22 Mar 2017 15:56:59 GMT

From the error message I understand the issue is more about the temporary table created for selecting the data (and not about read permissions on the folder of the target table).

Not sure "how" sentry is supposed to handle this case. Guess I'll run some test since I'm curious about this.

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

mathieu.d — Wed, 22 Mar 2017 16:40:39 GMT

I have run some tests.

And indeed, the problem seems to be around the temporary table (at least, it is failing here before anything else).

The query you make with the "VALUES" part work like this :

- it create a temporary hive table with the line to be inserted

- it query that temporary table for inserting the data into the target table

From what I see, the temporary table is created. But the user do not have "select" permission on it.

Surprisingly sentry allow for the creation but not the select.

I guess that a workaround would be to grant "SELECT" on the database "default" but this could bring some issues for you (since the user will have read permission on all the tables inside "default").

Seems like a defect to me.

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

geko — Wed, 22 Mar 2017 16:43:10 GMT

Thanks for jumping in here 😉

@mathieu.d , @saranvisa : I did further playaround in this 5.8 sandbox, and even using the Hue=>Security UI produces the same error/issue. What I did:

- created 2 groups in Linux (one for read-only, the other for read-write) and added users to it

- created 2 roles in sentry (again, one for read-only and the other for read-write) and granted corresponding previously created group to it

- created a hive database "sentrydemo" and one table in it

- open 2 terminals with 2 different users (one 'read', and one 'read-write') and connect to hive via beeline

- "select * from hivetablename" works fine

- "insert into hivetablename values (100, 'test entry')" gives me the following error:

 The required privileges: Server=server1->Db=sentrydemo->Table=values__tmp__table__2->Column=tmp_values_col1->action=select; (state=42000,code=40000)

"Enable Sentry synchronisation" is enabled in HDFS config, the "real" table folders have the correct permissions set according to the groups.

Where are those "values__tmp__table..." 's are being stored and why are they not being considered by the Sentry permissions I defined ?!?!

Any ideas ? At the end it is not either just a "INSERT only permission not working", right now it is a general "INSERT" not working issue, since the "read-write" group has privileges "SELECT" & "INSERT"

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

mathieu.d — Wed, 22 Mar 2017 16:51:08 GMT

Yes, not suprised by the outcome of your test since the issue is about "read" permission on the temporary table and not the target table.

If you try the following, it should work for your test :

Give "SELECT" permission on the database and retry. This time it should work and confirm the "problem".

And since you wonder, the problem isn't on the HDFS permission part. It's sentry side permissions issue.

The user that is accessing and writing the data on HDFS is "hive" anyway (since the impersonification should be disabled).

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

geko — Wed, 22 Mar 2017 17:15:31 GMT

thanks for this hint.

I can confirm by providing SELECT privileges on db level, the INSERT works.

From a security perspective this looks a bit weird. Is this a known bug ?!?!

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

mathieu.d — Thu, 23 Mar 2017 09:51:34 GMT

Well, I personaly tends to think this is a small "overlooked" use case.

I mean, this particular query syntax is doing some "weird" things in Hive under the hood (it creates a table and reads it) and sentry seems to not be expecting it.

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

geko — Thu, 23 Mar 2017 12:01:14 GMT

Hi @mathieu.d ,

do you think this needs to be raised as an issue/bug , or is my case that unusual (I don't think so, honestly 😉 )?

Re: INSERT only permission not working : "does not have privileges for QUERY" , _tmp_table

mathieu.d — Fri, 24 Mar 2017 12:48:42 GMT

I do think this is a defect. Not sure how Cloudera will see it.

But to be fair, this particular way of inserting data (with the VALUES syntax) into a table is pretty much limited to small testing.