Restricting Column-Level Access

Arcadia Enterprise implements column-level security when using Sentry and Ranger, including authorization when routing queries to analytical views.

When using Sentry or Ranger, you can grant limited SELECT access for specific columns. The following examples demonstrate how we can configure such access restrictions.

Create base table

Create a base table with managed security through Sentry or Ranger, and populate it:

create table employee (
  id int,
  name string,
  salary decimal (9,2),
  ssn string);

insert . . .

Grant limited access to emp_group

Assuming that the we have a defined emp_group security group, we can give it limited access to the employee table by specifically granting access to columns employee.id and employee.name:

grant select(id, name) 
  on table employee 
  to emp_group;

When a user in the emp_group runs the following query, the system returns an Authorization exception:

select * from employee;

AuthorizationException: User ‘emp_user’ does not have privileges 
  to execute 'SELECT' on: default.employee

However, a SELECT query correctly restricted to the first two columns succeeds:

select id, name from employee; 
  -- success

Grant access to salary information to mgr_group

Extending the previous example, we can grant access to the managers for the columns employee.id, employee.name, and employee.salary:

grant select(id, name, salary) 
  on table employee 
  to mgr_group;

As in the previous example, a user in the mgr_group cannot successfully run the query select * from employee;. However, the following query works:

select id, name, salary from employee; 

Grant access to ssn information to hr_group

Similarly, HR personnel can view the employee SSN information. To implement this, we must grant access to all columns: employee.id, employee.name, employee.salary, and employee.ssn:

grant select(id, name, salary, ssn) 
        on table employee 
        to hr_group;

Finally, an hr_group user does not encounter any authorization issues when running this query:

select * from employee;

See Authorizing Access to Analytical Views.