Hive CREATE VIEW fails due to no SELECT access on object. But I can SELECT * on the object...

[#1]

I have been validating the Ranger options. I have select on all columns of a hive table:

SELECT ssn, name, location FROM employee;

works, however the following does not:

CREATE VIEW employee_v AS SELECT ssn, name, location FROM employee;

fails due to not having SELECT on "employee". So confused... thoughts?

org.apache.hive.service.cli.HiveSQLException: Error while compiling 
statement: FAILED: HiveAccessControlException Permission denied: user 
[raj_ops] does not have [SELECT] privilege on [default/employee]

[#2 · Accepted solution]

@Rupert Bailey

Have you given the grant permission to the user "raj_ops" ?

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.0/bk_Ranger_User_Guide/content/user_access_hi...

Example:

GRANT
======
Syntax: grant <permissions> on table <table> to user <user or group>;

For example, to create a policy that grants user1 SELECT permission on the table default-hivesmoke22074, the command is grant select on table default.hivesmoke22074 to user user1;

[#3]

@jss thankyou for the tip. I was indeed required to execute an SQL DSL to GRANT SELECT on the employee TABLE. I was then required to GRANT CREATE on default DATABASE to USER. Which obeys ANSI I guess, but it seems adding the resource level privilege by Ranger is not sufficient (and I expect not with Atlas either with a tag). Interestingly from a SQL perspective even though the user creates the view, he needs to be explicitly provided GRANT SELECT on the object they create, which you don't need to do in Teradata SQL.

[#4]

I take it from this that there is a difference between Table level selection and All column level selection. Perhaps that acts a little bit like an "SELECT ... WITH GRANT" on the object (Teradata SQL).