Release - HDP2.5 Background:
Before this feature was released, Ranger allowed User to create only access policy for hive, that can provide security till column level, but folks might be interested in enabling cell level security then this is the solution they should opt. Introduction: This is a newly introduced feature,allows to create new type of ranger-hive policies that help administrator to restrict User from accessing some specific rows in table based on the filter condition in policy or mask the data that is sensitive , this masking can be full or partial. Lets go in details for both policy type:
Row Filter policy: Row filter policy allows ranger to specify filter expression in the hive policy, so that users sees only some specifc rows in the table that belong to him. eg: if user belong to US, and query the employee table and we want to restrict him to see only those employee who belong to US, then filter expression will be location = 'US'. there is a new tab we can see HDP2.5 onwards on hive policy page for 'Row filter', we need to provide database and table name in resource, and in condition enter the row filter expression that need to be used for filtering out the result of the hive query run by user part of row filter policy condition. in filter expression must be a valid WHERE clause , even inner condition are accepted. -Note:
1) there is no column option provide in this policy type because it does not make sense for it. Column Masking Policy: Column masking policy allows ranger to specify masking condition in hive policy to mask the sensitive data for specific users, eg. in Bank account no. & cvv is sensitive data of a customer, now in ranger you can create masking policy to mask a column data partial or full for specific user or group.there is a new tab we can see HDP2.5 onwards on hive policy page for 'column masking', we need to provide database,table and column name in the resource and in condition select the masking condition. following masking conditions are supported currently: 1) Redact:
2) Partial Mask: show last 4 3) partial Mask: show first 4 4) Hash 5) Nullify 6) Unmasked(retain original value) 7) Date: show only year 😎 custom
-Note: 1) wildcards are not allowed in both of the policies type 2) these policies can be created on table or view both. 3) Mask and filter are evaluated while execution of query based on the order they are listed in the policy. Example: Now lets take a example and try a row filter and each column masking technique:
lets say we have a table called "customer" in "Bank" database: ----+--------------------+--+
| customer.id | customer.name | customer.account | customer.cvv | customer.dob | customer.location |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| 432 | Amit | 898981931313131 | 432 | 1975-04-01 | Delhi |
| 493 | John | 79898193128931 | 234 | 1985-09-11 | Bangalore |
| 683 | nisar | 69598193128931 | 121 | 1965-09-11 | Bangalore |
| 532 | rohan | 198981931313131 | 402 | 1995-04-01 | Delhi |
| 400 | Rahul | 69898193128931 | 159 | 1985-09-10 | Bangalore |
| 809 | nisar | 59598193128931 | 096 | 1979-09-11 | Bangalore | Lets create row filter and colum masking policy, and run "select * from customer;" and we will see the difference in results: 1) Row filter policy example: create a row filter policy with filter expression:
location = 'Bangalore' for user1
a) result if query executed by user1, "select * from customer;": +--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| customer.id | customer.name | customer.account | customer.cvv | customer.dob | customer.location |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| 493 | John | 79898193128931 | 234 | 1985-09-11 | Bangalore |
| 683 | nisar | 69598193128931 | 121 | 1965-09-11 | Bangalore |
| 400 | Rahul | 69898193128931 | 159 | 1985-09-10 | Bangalore |
| 809 | nisar | 59598193128931 | 096 | 1979-09-11 | Bangalore |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
4 rows selected (0.864 seconds) b) result if query executed by user2, "select * from customer;", since it is not part of policy so it will get all the results: +--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| customer.id | customer.name | customer.account | customer.cvv | customer.dob | customer.location |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| 432 | Amit | 898981931313131 | 432 | 1975-04-01 | Delhi |
| 493 | John | 79898193128931 | 234 | 1985-09-11 | Bangalore |
| 683 | nisar | 69598193128931 | 121 | 1965-09-11 | Bangalore |
| 532 | rohan | 198981931313131 | 402 | 1995-04-01 | Delhi |
| 400 | Rahul | 69898193128931 | 159 | 1985-09-10 | Bangalore |
| 809 | nisar | 59598193128931 | 096 | 1979-09-11 | Bangalore |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
2) column masking policy example: create a column masking policy on table customer, column account with masking condition: location = 'Bangalore' for user1 a) result if query executed by user1, "select * from customer;" , since it is part of policy so it will get masked account number and will show only last 4 numbers in it: +--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| customer.id | customer.name | customer.account | customer.cvv | customer.dob | customer.location |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| 432 | Amit | xxxxxxxxxxx3131 | 432 | 1975-04-01 | Delhi |
| 493 | John | xxxxxxxxxx8931 | 234 | 1985-09-11 | Bangalore |
| 683 | nisar | xxxxxxxxxx8931 | 121 | 1965-09-11 | Bangalore |
| 532 | rohan | xxxxxxxxxxx3131 | 402 | 1995-04-01 | Delhi |
| 400 | Rahul | xxxxxxxxxx8931 | 159 | 1985-09-10 | Bangalore |
| 809 | nisar | xxxxxxxxxx8931 | 096 | 1979-09-11 | Bangalore |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
6 rows selected (0.841 seconds) b) result if query executed by user2, "select * from customer;", since it is not part of policy so it will get unmasked results: +--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| customer.id | customer.name | customer.account | customer.cvv | customer.dob | customer.location |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
| 432 | Amit | 898981931313131 | 432 | 1975-04-01 | Delhi |
| 493 | John | 79898193128931 | 234 | 1985-09-11 | Bangalore |
| 683 | nisar | 69598193128931 | 121 | 1965-09-11 | Bangalore |
| 532 | rohan | 198981931313131 | 402 | 1995-04-01 | Delhi |
| 400 | Rahul | 69898193128931 | 159 | 1985-09-10 | Bangalore |
| 809 | nisar | 59598193128931 | 096 | 1979-09-11 | Bangalore |
+--------------+----------------+-------------------+---------------+---------------+--------------------+--+
6 rows selected (0.649 seconds) same way we can try out other masking types too. There are some good use cases listed on the following wiki page please refer it too: https://cwiki.apache.org/confluence/display/RANGER/Row-level+filtering+and+column-masking+using+Apache+Ranger+policies+in+Apache+Hive please comment for any question.
... View more
