question Re: Is it possible to enable column level security at Hive View level? in Support Questions

Is it possible to enable column level security at Hive View level?

nsabharwal — Mon, 19 Aug 2019 09:26:59 GMT

HDP 2.3.2

Sandbox

hive> CREATE TABLE page_view(viewTime INT, userid BIGINT,

> page_url STRING, referrer_url STRING,

> ip STRING COMMENT 'IP Address of the User')

> COMMENT 'This is the page view table'

> PARTITIONED BY(dt STRING, country STRING)

> STORED AS SEQUENCEFILE;

Time taken: 14.39 seconds

hive> CREATE VIEW onion_referrers(url COMMENT 'URL of Referring page')

> COMMENT 'Referrers to The Onion website'

> AS

> SELECT DISTINCT referrer_url

> FROM page_view

> WHERE page_url='http://www.theonion.com';

Time taken: 3.484 seconds

Ranger policy

Restricting access on ip column - Table

Restricting access on url column - View

0: jdbc:hive2://localhost:10000> select * from page_view;

Error: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [hive] does not have [SELECT] privilege on [default/page_view/country,dt,ip,page_url,referrer_url,userid,viewtime] (state=42000,code=40000)

0: jdbc:hive2://localhost:10000> select * from onion_referrers;

INFO : Session is already open

INFO :

INFO : Status: Running (Executing on YARN cluster with App id application_1454947053064_0004)

INFO : Map 1: -/-Reducer 2: 0/1

INFO : Map 1: -/-Reducer 2: 0(+1)/1

INFO : Map 1: -/-Reducer 2: 1/1

+----------------------+--+

| onion_referrers.url |

+----------------------+--+

No rows selected (3.695 seconds)

0: jdbc:hive2://localhost:10000>

@bdurai @sneethiraj @bganesan

****Update****

I added base table column in exclude list and view can access the column

Re: Is it possible to enable column level security at Hive View level?

nsabharwal — Mon, 19 Aug 2019 09:26:51 GMT

0: jdbc:hive2://localhost:10000> select * from onion_referrers;

INFO : Session is already open

INFO :

INFO : Status: Running (Executing on YARN cluster with App id application_1454947053064_0004)

INFO : Map 1: -/-Reducer 2: 0/1

INFO : Map 1: -/-Reducer 2: 0(+1)/1

INFO : Map 1: -/-Reducer 2: 1/1

+----------------------+--+

| onion_referrers.url |

+----------------------+--+

No rows selected (4.381 seconds)

0: jdbc:hive2://localhost:10000>

Re: Is it possible to enable column level security at Hive View level?

pminovic — Thu, 11 Feb 2016 17:16:39 GMT

Hi @Neeraj Sabharwal, your view is using referrer_url and page_url from the page_view table which have no access permissions. Not sure is this the reason you get no output from your view. Can you start by setting Hive columns to "*" and then trying to reduce permissions.

Re: Is it possible to enable column level security at Hive View level?

nsabharwal — Thu, 11 Feb 2016 18:16:15 GMT

@Predrag Minovic I checked with the security team and it's known issue.

Re: Is it possible to enable column level security at Hive View level?

nsabharwal — Thu, 11 Feb 2016 18:18:19 GMT

There will be a new feature eventually to take care of this. "Being able to control access to view columns, without having to grant access to the underlying table column(s), might be a desired security feature."