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:


Table Access Example

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


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


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


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:


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:


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


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


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


Row Filters

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


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


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:


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:


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


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.


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


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


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