Adding permissions to create scheduled queries on Hive failing

user_607567 0
Viewed 87

I am having an issue trying to create some scheduled queries in Hive and I don't find what permission to add, I have tried to grant ALL on the database and nothing is changing.

My command is:

create scheduled query query_1_stream_1 cron '0 */1 * * * ? *' as 
insert into stream_db.kafka_5m partition (st, hr)
...

Error:

Error while compiling statement: FAILED: SemanticException Permission denied: Principal [name=hive, type=USER] does not have following privileges for operation CREATE_SCHEDULED_QUERY [ADMIN PRIVILEGE on INPUT, ADMIN PRIVILEGE on OUTPUT]

Any idea what is the specific permission, as I said, I tried to add ALL permissions and the error persists.

hive hadoop
1 Answers

The create_scheduled_query, as well as other similar hive operations, are privileges reserved for the hive's admins, this means that you need to assign the admin role to the user or users that you want to execute those privileged operations.

This is the configuration that you need:

hive.server2.enable.doAs=false
hive.security.authorization.enabled=true
hive.security.authorization.manager=org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory
hive.security.authenticator.manager=org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator
hive.users.in.admin.role=<admin user>

Let's talk about the configuration a little: The hive.server2.enable.doAs will make your queries run as hive, this is the Hive impersonation feature, it is not really required for this role addition, but usually you want the user hive to the admin.

The 2nd, 3rd and 4th are not mandatory and you might have other values in them.

The 5th is the magic one, this indicates which users are admins, you need to list the users that you want to have those privileges here as follows:

hive.users.in.admin.role=hive

Then, open up your hive CLI or beeline and run the following:

set role admin;

This set is required even if your user is admin in the configuration, the hive user is not running as admin automatically never, you need to 1. have the role, and 2. enable the role in the CLI.

At this point, you should be able to create your schedules, alter them, drop them, etc.

user_6075670
Related