How to create Ranger Policy to automatically grant access to Hive table owner? HDP-2.5.6, Hive & Ranger

[#1]

I have a database where all members of a group are allowed to create tables. When a user creates a table, only the creator should be allowed access to that table. I will create a new policy to allow other members of the group access to the new table after an approval business process.

HDP 2.6.5, Hive and Ranger (I cannot use Atlas and tag based policies at this time)

This policy allows members of the group to create tables, but the owner has no access after creating.

resources(database:testdb, table:*, column:*)
policyItems (accesses:create,groups:testgroup)

beeline as user1, a member of testgroup

0: jdbc:hive2://localhost:10000> use testdb;
No rows affected (2.509 seconds)

0: jdbc:hive2://localhost:10000> CREATE TABLE testtable (id STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' LOCATION '/hive/data/testtable';
No rows affected (0.669 seconds)

0: jdbc:hive2://localhost:10000> show tables;
+------------+--+
|  tab_name  |
+------------+--+
| testtable  |
+------------+--+
1 row selected (1.029 seconds)

0: jdbc:hive2://localhost:10000> describe testtable;
Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [user1] does not have [SELECT] privilege on[testdb/testtable/*] (state=42000,code=40000)

I tried adding a policy item using the {OWNER} variable but had the same result.

resources(database:testdb, table:*, column:*)
policyItems [(accesses:[select,update,drop,alter,index,lock,read,write] users:{OWNER}), (accesses:create, groups:testgroup)]

[#2 · Accepted solution]

@Eric Richardson By using {USER} instead of {OWNER} you could come up with the following solution:

With the above policy users will be able to:

1. Use database testdb;

2. Users will be able to create tables. With the condition that table names must end in underscore username _{USER}.

2. Show tables that finish with their username only. For example:

show tables;
+---------------+--+
|   tab_name    |
+---------------+--+
| t2_falbani    |
| test_falbani  |
+---------------+--+
## Users wont see any other tables unless another policy grants them permissions

3. Users will have full control over the tables ending in underscore username _{USER} - And won't be able to perform any operations in any other tables unless there is another policy that grants them permissions.

Hopefully this will help you address your requirement. Please let me know if this helps. Also if you found this answer addressed your question, please take a moment to login and click the "accept" link on the answer.

HTH

[#3]

@Eric Richardson {OWNER} is not supported by Hive yet. Please mark the answer if you feel the information provided has helped address your question.

[#4]

Hi @Felix Albani, is it possible to user {USER} even when the naming conventions of username is firstname.lastname?

Would really appreciate your response.

[#5]

I think this solution would work from a technical perspective. However, I am not sure my users will accept the naming pattern limitation.

I have been unable to find a example of anyone using the {OWNER} variable.

@svenkat said how useful it was here https://community.hortonworks.com/questions/118123/hdp-26-allows-user-variable-in-ranger-policies-ar... but didn't provide an example of actually using it.

Does it work?