Examples of SQL Authorization of Presto in Ranger

Let us walk through several examples of the Ranger plugin capabilities in this section.

Users and Groups

First let us see the users and groups configuration. We have two groups defined:

  1. Admins
  2. Analysts

There are three users who belong to one of these groups:

  1. Sakshi: username sakshia and belongs to Admins group
  2. Shubham: username stagra and belongs to Analyst group
  3. Sumit: username sumitm and belongs to Analyst group

Here are the above users as they appear in the Ranger Users/Groups page:

../_images/01_Users_and_Groups.png

Table Access Example

We add a policy in Ranger to allow access to the customer table to only stagra:

../_images/02_customer_access_to_shubham.png

With this, Shubham should be able to run any queries on the customer table:

../_images/03_Select_customer_by_shubham_works.png

But any other user, say Sumit, should be unable to access this table:

../_images/04_Select_customer_by_sumit_fails.png

As seen above, the query by sumitm@company.com fails with an AccessDeniedException.

Column Level Authorization

Next, let us configure a policy the the table nation. We give user Shubham full access to this table, and exclude user Sumit from access to the table:

../_images/05a_nation_table_access_to_shubham_not_sumit.png

For user Sumit, we provide access to all but n_regionkey column in this table. Notice the exclusion of this column in the policy below:

../_images/05b_nation_table_access_to_sumit_excluding_n_regionkey.png

With this setup, user Shubham should be able to access all columns of the table nation:

../_images/06_select_nation_by_shubham_works.png

But trying to select n_regionkey by user Sumit should throw an access denied exception:

../_images/07a_select_all_on_nation_by_sumit_fails.png

But, since we have allowed access to other columns of this table for user Sumit, queries on those columns work fine:

../_images/07b_select_n_name_on_nation_by_sumit_works.png

Row Filters

Next, we setup a Row Filter Policy to hide the row with n_name as INDIA from user Shubham:

../_images/08_row_filter_on_nation_hide_INDIA_row_for_shubham.png

With this policy, the user Shubham does not see any rows where the column n_name has the value INDIA:

../_images/09a_nothing_returned_for_INDIA_for_shubham.png

But since the policy does not filter any rows for user Sumit, he should be able to see the rows with n_name having value INDIA:

../_images/09b_row_returned_for_INDIA_for_sumit.png

Data Masking

To demonstrate Data Masking capability, we define a policy for user Shubham to see the n_comment values in table nation truncated to only the first five characters:

../_images/10_Data_mask_on_nation_to_show_only_5_characters_in_comment.png

Selecting n_comment by user Shubham returns the truncated data as follows:

../_images/11_Data_mask_in_action.png

Groups-based Policy

Finally, we will see an example where we set up policy on a group rather than at the user level. In this example, full access is provided to the table promotion to the Admins group. The rest of the users do not have access to it.

../_images/12_policy_to_give_access_to_promotion_to_admins.png

This means that only user Sakshi, who is a part of the Admins group, should be able to access this table:

../_images/15_select_on_promotion_by_sakshi_passes.png

User Shubham should not be able to access the promotion table:

../_images/13_select_on_promotion_by_shubham_fail.png

User Sumit should also be unable to access the promotion table:

../_images/14_select_on_promotion_by_sumit_fail.png