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. Want to know more about what has changed? Check out the Community News blog.

Hive authorisation not working for few AD users

Hive authorisation not working for few AD users

New Contributor

Some of the AD group users(testing and development team) are unable to access Hive objects. They are able to access HDFS files, which means that file ACLs are working fine and they are members of relevant groups.

 

It is the problem with Hive/Sentry where they are able to see only "default" database from Hue and they get below error whenever they try to access Hive DB/tables:

 

 

 

 

I checked Hiveserver2 logs and saw below messages when the user tried to access Hive.

 

1)

 

2018-04-11 11:25:38,091 INFO org.apache.hadoop.hive.ql.log.PerfLogger: [HiveServer2-Handler-Pool: Thread-124]: </PERFLOG method=compile start=1523409938014 end=1523409938091 duration=77 from=org.apache.hadoop.hive.ql.Driver>
2018-04-11 11:25:38,092 INFO org.apache.hadoop.hive.ql.log.PerfLogger: [HiveServer2-Handler-Pool: Thread-124]: </PERFLOG method=compile start=1523409938014 end=1523409938092 duration=78 from=org.apache.hadoop.hive.ql.Driver>
2018-04-11 11:25:38,092 INFO org.apache.hadoop.hive.ql.Driver: [HiveServer2-Handler-Pool: Thread-124]: Completed compiling command(queryId=hive_20180411112525_1cd47692-9393-4a62-9531-c3c9009d5b34); Time taken: 0.077 seconds
2018-04-11 11:25:38,092 INFO org.apache.hadoop.hive.ql.log.PerfLogger: [HiveServer2-Handler-Pool: Thread-124]: <PERFLOG method=releaseLocks from=org.apache.hadoop.hive.ql.Driver>
2018-04-11 11:25:38,092 INFO org.apache.hadoop.hive.ql.log.PerfLogger: [HiveServer2-Handler-Pool: Thread-124]: </PERFLOG method=releaseLocks start=1523409938092 end=1523409938092 duration=0 from=org.apache.hadoop.hive.ql.Driver>
2018-04-11 11:25:38,092 INFO org.apache.hive.service.cli.operation.OperationManager: [HiveServer2-Handler-Pool: Thread-124]: Closing operation: OperationHandle [opType=EXECUTE_STATEMENT, getHandleIdentifier()=d0aad5a7-39bd-4a3f-8ad3-14b2adeeddc9]
2018-04-11 11:25:38,092 WARN org.apache.hive.service.cli.thrift.ThriftCLIService: [HiveServer2-Handler-Pool: Thread-124]: Error executing statement:
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException No valid privileges
User cp640136 does not have privileges for SWITCHDATABASE
The required privileges: Server=server1->Db=*->Table=+->Column=*->action=select;Server=server1->Db=*->Table=+->Column=*->action=insert;
at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:400)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:187)
at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:271)
at org.apache.hive.service.cli.operation.Operation.run(Operation.java:337)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:439)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatement(HiveSessionImpl.java:405)
at org.apache.hive.service.cli.CLIService.executeStatement(CLIService.java:257)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:501)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hadoop.hive.thrift.HadoopThriftAuthBridge$Server$TUGIAssumingProcessor.process(HadoopThriftAuthBridge.java:762)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:286)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: No valid privileges
User cp640136 does not have privileges for SWITCHDATABASE
The required privileges: Server=server1->Db=*->Table=+->Column=*->action=select;Server=server1->Db=*->Table=+->Column=*->action=insert;
at org.apache.sentry.binding.hive.HiveAuthzBindingHook.postAnalyze(HiveAuthzBindingHook.java:527)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:561)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1356)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1343)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:185)
... 15 more
Caused by: org.apache.hadoop.hive.ql.metadata.AuthorizationException: User cp640136 does not have privileges for SWITCHDATABASE
at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.authorize(HiveAuthzBinding.java:320)

 

 

 

2) 

 

 

Caused by: org.apache.hadoop.hive.ql.parse.SemanticException: No valid privileges
User cp553001 does not have privileges for SWITCHDATABASE
The required privileges: Server=server1->Db=sit1_es_dds_consumer->Table=*->Column=*->action=select;Server=server1->Db=sit1_es_dds_consumer->Table=*->Column=*->action=insert;
at org.apache.sentry.binding.hive.HiveAuthzBindingHook.postAnalyze(HiveAuthzBindingHook.java:527)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:561)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1356)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1343)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:185)
... 15 more
Caused by: org.apache.hadoop.hive.ql.metadata.AuthorizationException: User cp553001 does not have privileges for SWITCHDATABASE
at org.apache.sentry.binding.hive.authz.HiveAuthzBinding.authorize(HiveAuthzBinding.java:320)
at org.apache.sentry.binding.hive.HiveAuthzBindingHook.authorizeWithHiveBindings(HiveAuthzBindingHook.java:727)
at org.apache.sentry.binding.hive.HiveAuthzBindingHook.postAnalyze(HiveAuthzBindingHook.java:512)
... 19 more
2018-04-11 11:45:03,683 INFO org.apache.hive.service.cli.thrift.ThriftCLIService: [HiveServer2-Handler-Pool: Thread-95]: Session disconnected without closing properly, close it now
2018-04-11 11:45:03,683 INFO org.apache.hive.service.CompositeService: [HiveServer2-Handler-Pool: Thread-95]: Session closed, SessionHandle [fafbf432-a8f3-432e-adf6-3ec0733b0fa5], current sessions:5
2018-04-11 11:45:03,683 INFO org.apache.hive.service.cli.session.HiveSessionImpl: [HiveServer2-Handler-Pool: Thread-95]: Operation log session directory is deleted: /var/log/hive/operation_logs/fafbf432-a8f3-432e-adf6-3ec0733b0fa5

 

 

I checked on the host with "id <user_name> and validated that they are members of relevant group and we have no  problem there.

 

Please share your suggestions

 

 

 

3 REPLIES 3

Re: Hive authorisation not working for few AD users

Champion

@nandakumar

 

it looks like sentry issue, have you recently added/enabled the sentry service? if so, you can try this

 

then you may have to grant the necessary access of your dbs to user group. this can be done via hue or you can login to hive as admin and try the below commands

 

Ex:

Consider your user belongs to <my_group>

 

## role creation:
create role <my_role>;

 

## grant access to my_role
grant all on database <my_db1> to role <my_role>;
grant select on database <my_db2> to role <my_role>;

 

## grant role to group
grant <my_role> to group <my_group>;

Re: Hive authorisation not working for few AD users

New Contributor
Hi Saranvisa,

This is done already and was working fine before.

It still works for several users who are under the same AD group.

I am unable to isolate the issue, since it is working for several users and not working for others.

How can i verify if a "user_account" is a part of the role/AD group in Hive/Sentry?

I checked it already on my hosts with "id <username>" they are all members of the AD group.

Re: Hive authorisation not working for few AD users

Champion

@nandakumar

 

you can use adquery commands 

adquery <user>

adquery <group>

etc