Reply
Expert Contributor
Posts: 128
Registered: ‎08-08-2013
Accepted Solution

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

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...

 

 

Posts: 289
Topics: 11
Kudos: 43
Solutions: 25
Registered: ‎09-02-2016

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

@ge-ko

 

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

Expert Contributor
Posts: 128
Registered: ‎08-08-2013

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

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
Highlighted
Posts: 289
Topics: 11
Kudos: 43
Solutions: 25
Registered: ‎09-02-2016

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

@ge-ko

 

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

Posts: 137
Topics: 7
Kudos: 15
Solutions: 14
Registered: ‎07-16-2015

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

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.

 

 

Posts: 137
Topics: 7
Kudos: 15
Solutions: 14
Registered: ‎07-16-2015

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

[ Edited ]

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.

 

 

Expert Contributor
Posts: 128
Registered: ‎08-08-2013

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

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"

Posts: 137
Topics: 7
Kudos: 15
Solutions: 14
Registered: ‎07-16-2015

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

[ Edited ]

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).

Expert Contributor
Posts: 128
Registered: ‎08-08-2013

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

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 ?!?!

Posts: 137
Topics: 7
Kudos: 15
Solutions: 14
Registered: ‎07-16-2015

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

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.

Announcements