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

[#1]

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

[#2]

@geko

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

[#3]

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

[#4]

@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

[#5]

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.

[#6 · Accepted solution]

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.

[#7]

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"

[#8]

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

[#9]

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

[#10]

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.