Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor
 
1 ACCEPTED SOLUTION

avatar
Rising Star

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

avatar

I believe this would currently be through Hive views

avatar

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.

avatar
Rising Star

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

avatar

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

avatar
Rising Star

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(); 

avatar
Rising Star

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.

avatar
Master Mentor

@Ronald McCollam

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

avatar

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.

avatar
Rising Star

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