Authorizing Access to Analytical Views

Arcadia Enterprise implements authorization for database objects, including for operations on analytical views, through Sentry and Ranger.

The following list summarizes the required authorization levels necessary to perform specific operations through analytical views.

CREATE

Syntax:

CREATE ANALYTICAL VIEW td.av1 STORED AS PARQUET AS
  SELECT y,m,d,max(v) 
  FROM sd.t GROUP BY y,m,d;

Authorization:

(SELECT privilege on base table sd.t OR SELECT COLUMN privilege on y,m,d,v of sd.t) AND ALL privilege on target td

DESCRIBE

Syntax:

DESCRIBE FORMATTED aview_name

Authorization:

Shows all the columns in the analytical view if the user has:

(ANY TABLE level privilege OR SELECT privilege on ANY COLUMN)

DROP

Syntax:

DROP ANALYTICAL VIEW td.av1;

Authorization:

ALL privilege on sd.t; no privilege on td.av1 needed

EXPLAIN SELECT . . . FROM logical view

Syntax:

EXPLAIN SELECT y,m,d,max_v 
  FROM logical_view;

Authorization:

If issued internally by ArcViz: TABLE SELECT on logical_view

If issued BY SQL visual or on command line: (TABLE SELECT on logical_view AND (TABLE SELECT on sd.t OR COLUMN SELECT on y, m, d, v on sd.t))

EXPLAIN SELECT . . . GROUP BY

Syntax:

EXPLAIN SELECT max(v),y,m,d FROM sd.t GROUP BY y,m,d;

Authorization:

(SELECT privilege on sd.t OR SELECT COLUMN privilege on y,m,d,v of sd.t)

REFRESH

Syntax:

REFRESH ANALYTICAL VIEW

Authorization:

ALL privilege on sd.t
SELECT

Syntax:

SELECT y, m, d 
  FROM td.av1;

Authorization:

SELECT privilege on sd.t

For native access to analytical view, must have TABLE level access; COLUMN level privilege to analytical view is not enough

SELECT . . . GROUP BY

Syntax:

SELECT max(v),y,m,d FROM sd.t GROUP BY y,m,d;

Authorization:

Query routes to analytical view if the user has the following:

(SELECT privilege on sd.t OR SELECT COLUMN privilege on y,m,d,v of sd.t)

SELECT FROM logical view

Syntax:

SELECT y,m,d,max_v FROM logical_view;

  CREATE VIEW logical_view AS 
    SELECT max(v) AS max_v,y,m,d 
      FROM sd.t 
      GROUP BY y,m,d

Authorization:

TABLE SELECT on logical_view; no privilege check on matching analytical views required

SHOW

Syntax:

SHOW <EXTENDED> <ALL> ANALYTICAL VIEWS

Authorization:

Some TABLE level privilege on the base tables