Setting a permission in hive databases but it does not work

[#1]

help me please, I set the hive permission today, I modify the hive-site.xml as follows,but it does not work,I can not find where it is wrong.

This is a part of my hive-site.xml

<property> <name>hive.security.authorization.enabled</name> <value>true</value> </property> <property> <name>hive.metastore.authorization.storage.checks</name> <value>true</value> <final>true</final> </property> <property> <name>hive.metastore.execute.setugi</name> <value>false</value> </property> <property> <name>hive.users.in.admin.role</name> <value>hive</value> </property> <property> <name>hive.security.authorization.createtable.owner.grants</name> <value>ALL</value> </property>

I create new table "test" use user "hive"(hive has admin role), then I use "useradd mxl" command to add user,and the mxl user can operate the table test, But i don't grant select or insert to user mxl ! Please tell why,thank you very much.

[#2]

Check what is in hive.security.authorization.manager. You will need to set that as well. It is a lot easier if you are using ambari to do it.

Take a look at https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization#SQLStandardBa... for detailed configuration

[#3]

Thanks,i try again use ambari, the property is as fllow <property> <name>hive.security.authorization.manager</name> <value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdConfOnlyAuthorizerFactory</value> </property> ,but it also does not work.

[#4]

Take a look at hiveserver2 logs and post any errors that you see there (if you are using beeline).

[#5]

the hiveserver2.log as follow:

2017-03-15 09:44:51,316 INFO [HiveServer2-Handler-Pool: Thread-70]: thrift.ThriftCLIService (ThriftCLIService.java:OpenSession(313)) - Client protocol version: HIVE_CLI_SERVICE_PROTOCOL_V8 2017-03-15 09:44:51,371 INFO [HiveServer2-Handler-Pool: Thread-70]: session.SessionState (SessionState.java:createPath(642)) - Created local directory: /tmp/dde95c8b-7e9b-4676-b25c-dc2165937ce8_resources 2017-03-15 09:44:51,372 INFO [HiveServer2-Handler-Pool: Thread-70]: session.SessionState (SessionState.java:createPath(642)) - Created HDFS directory: /tmp/hive/hive/dde95c8b-7e9b-4676-b25c-dc2165937ce8 2017-03-15 09:44:51,373 INFO [HiveServer2-Handler-Pool: Thread-70]: session.SessionState (SessionState.java:createPath(642)) - Created local directory: /tmp/hive/dde95c8b-7e9b-4676-b25c-dc2165937ce8 2017-03-15 09:44:51,374 INFO [HiveServer2-Handler-Pool: Thread-70]: session.SessionState (SessionState.java:createPath(642)) - Created HDFS directory: /tmp/hive/hive/dde95c8b-7e9b-4676-b25c-dc2165937ce8/_tmp_space.db 2017-03-15 09:44:51,374 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:setOperationLogSessionDir(264)) - Operation log session directory is created: /tmp/hive/operation_logs/dde95c8b-7e9b-4676-b25c-dc2165937ce8 2017-03-15 09:44:51,823 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:acquireAfterOpLock(332)) - We are setting the hadoop caller context to dde95c8b-7e9b-4676-b25c-dc2165937ce8 for thread HiveServer2-Handler-Pool: Thread-70 2017-03-15 09:44:51,823 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:releaseBeforeOpLock(356)) - We are resetting the hadoop caller context for thread HiveServer2-Handler-Pool: Thread-70 2017-03-15 09:44:51,880 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:acquireAfterOpLock(332)) - We are setting the hadoop caller context to dde95c8b-7e9b-4676-b25c-dc2165937ce8 for thread HiveServer2-Handler-Pool: Thread-70 2017-03-15 09:44:51,880 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:releaseBeforeOpLock(356)) - We are resetting the hadoop caller context for thread HiveServer2-Handler-Pool: Thread-70 2017-03-15 09:44:51,997 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:acquireAfterOpLock(332)) - We are setting the hadoop caller context to dde95c8b-7e9b-4676-b25c-dc2165937ce8 for thread HiveServer2-Handler-Pool: Thread-70 2017-03-15 09:44:51,998 INFO [HiveServer2-Handler-Pool: Thread-70]: session.HiveSessionImpl (HiveSessionImpl.java:releaseBeforeOpLock(356)) - We are resetting the hadoop caller context for thread HiveServer2-Handler-Pool: Thread-70

[#6]

You will need to set the following in the hive-site.xml and restart the HiveServer2.

hive.server2.enable.doAs = false
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 = hive
hive.security.authorization.enabled = true

Note that the permissions are enforced in HS2 session only. So, if hive user created some table and did not explicitly give permissions to user mxl then mxl wouldn't be able to operate on it. Example

- Login as user hive and in beeline session run the following:
create table test (key string, val int);

- Now login as user mxl and in beeline session run the following:
insert into table test select * from foo;
You will see an error like "Permission denied. name=mxl does not have following privileges. INSERT."

- To explicitly provide permissions, login as user hive in beeline session and run:
grant insert on test to user mxl;

Hope this is what you were looking for, not very sure reading your question.