Support Questions
Find answers, ask questions, and share your expertise

What is the best way to implement row-based security in Hive?

New Contributor
 
1 ACCEPTED SOLUTION

Contributor

Row-level security can be achieved by defining views with hard-coded permissions in Ranger.

An alternative available since Hive 1.2.0 is to filter dynamically based on the current user, with the current_user() function. This provides row-by-row security. One option to define the ACLs is via a permission table:

create table permission( username string, driverid string);

For example to secure the driver(driverid, drivername) table, you could create the following permission:

insert into permission values( jsmith, 25 );

Finally define the view by joining against it:

create view secure_driver AS select d.* from driver d inner join permissions p on d.driverid=p.driverid where username = current_user(); 

View solution in original post

9 REPLIES 9

I believe this would currently be through Hive views

Master Collaborator

Row based security can be achieved through SQL Standard Based Hive Authorization.You can create a view with the filter from the original table and then GRANT permissions to role or individual user.

Contributor

We would recommend customers to use Ranger with Hive, rather than SQL std authorization. The solution recommend by JP would work

Just furthering adding to what Deepesh and Ali said - Create Hive views that would filter out rows and then use Ranger to grant that user access to that View (and not the underlying table).

Contributor

Row-level security can be achieved by defining views with hard-coded permissions in Ranger.

An alternative available since Hive 1.2.0 is to filter dynamically based on the current user, with the current_user() function. This provides row-by-row security. One option to define the ACLs is via a permission table:

create table permission( username string, driverid string);

For example to secure the driver(driverid, drivername) table, you could create the following permission:

insert into permission values( jsmith, 25 );

Finally define the view by joining against it:

create view secure_driver AS select d.* from driver d inner join permissions p on d.driverid=p.driverid where username = current_user(); 

Contributor

Though views are not a scalable model, this would be the best recommended solution till the time we have support for inserting predicate or filtering row through UDF in Hive.

@Ronald McCollam

Hive view and if we look into other technology stacks then Row level security is based on views.

I know... I'm the FIFTH person to say create a View and secure permissions on it, and the backing table, appropriately. 😉

That said, I've got a simple little demo posted at https://github.com/HortonworksUniversity/Essentials/blob/master/demos/ranger/README.md along with a video recording of it linked there in case anyone might find that useful.

Explorer

Ranger now supports row filtering where you can specify a condition to filter the results by even without creating a new hive view.

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.0/bk_security/content/ranger_row_level_filter...

; ;