hive with SQL Standard-based Authorization

[#1]

hi,

im trying to setup SQL Standard-based Authorization based on the

https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.3.2/bk_dataintegration/content/hive-013-feature...

but apparently it DOESNT work.

These are the values what are recommended.

-hiveconf hive.metastore.uris

''(a space inside single quotation marks)

-hiveconf hive.security.authorization.manager

org.apache.hadoop.hive.ql.security. authorization. MetaStoreAuthzAPIAuthorizerEmbedOnly

My ambari hive setup has other values...

hive.metastore.uristhrift://blabla.com:9083

and

hive.security.authorization.manager

org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdConfOnlyAuthorizerFactory

Right now when i try to

show roles; for the user which was defined in

hive.users.in.admin.role

i got the error below:

[08S01]: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Current user : adf_admin is not allowed to list roles. User has to belong to ADMIN role and have it as current role, for this action.

Thank you

[#2]

Hi @John Smith,

At first glance, I am wondering if you granted the appropriate admin privilege to the adf_admin user. There are a few more details on setting up SQL standard auth in a SysAdmin guider here. Notice the step where the admin user must grant themselves admin rights.

[#3]

hello @Brandon Wilson , im wondering how can i do this? im googling and i cant see anything related to the setting admin privs to the adf_admin user or whatever user. I thought i can do this putting the user name into conf variable "

hive.users.in.admin.role "

pls let me know. thank you

[#4]

In the link I posted abouve there is a step requiring the admin user to grant admin privileges to itself:

GRANT admin TO USER adf_admin;

[#5]

I tried

GRANT admin TO USER adf_admin;

and got error below

[08S01]: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Current user : adf_admin is not allowed to grant role. User has to belong to ADMIN role and have it as current role, for this action. Otherwise, grantor need to have ADMIN OPTION on role being granted and have it as a current role for this action.

[#6]

I think you should grant this role to the user BEFORE restarting HS2 with the new Authorization enforced. I would revert your Hive configuration back to the original settings. Then execute the GRANT. Then re-enable the SQL authorization via the configs and service restart.

[#7]

hi,

not really its same issue....

[08S01]: Error while processing statement: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. Current user : adf_admin is not allowed to grant role. User has to belong to ADMIN role and have it as current role, for this action. Otherwise, grantor need to have ADMIN OPTION on role being granted and have it as a current role for this action.

[#8]

Hi @lenovomi, Did you got the solution to your problem? I am facing the same issue. 😞

[#9]

For enabling the SQL Std Auth manually you would need to set the following settings in the hive-site.xml before restarting the HiveServer2.

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>

In the above <admin user> would be replaced by the user you want as admin. Note that you can perform admin operations after you connect to your beeline session as the admin user and then run the following command:

set role ADMIN;

For an Ambari controlled cluster recommendation would be to enable it through toggle on the Hive configs page.