Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

New Contributor

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]
1 ACCEPTED SOLUTION

Accepted Solutions

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

@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 REPLIES 3

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

@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;
Highlighted

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

New Contributor

@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.

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

New Contributor

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).

Don't have an account?
Coming from Hortonworks? Activate your account here