Dear All,
We had an issue that:
*Environment:*
OS: Centos 7.5 Hive Server 2.3.4 Metastore Mysql Server 5.7.25
*Purpose:*
I want to create a hive table using spark-shell/spark-submit jobs under
account robot.prod and this table can be accessed(select) by beeline
logged with the same account.
*On env B:*
The database owner is set as account robot.prod. Both spark shell and
beeline is logged with account robot.prod in bash.
I created a new table from parquet files with spark-shell
It seems that the hive server created a grant SELECT rule for the newly
created table and I can select from the table using beeline with the same
account.
The rule can be seen in the screenshot below.
We also noticed that for the grant query: the grantor is "*metastore*"
Which is different from the normal grantor "*hive*"
Could you please advise why there is a difference?
[image: image.png]
*On env A:*
The table can be created with spark-shell. It cannot be selected by the
same account using beeline.
The error is :
* Error: Error while compiling statement: FAILED:
HiveAccessControlException Permission denied: Principal [name=robot.prod,
type=USER] does not have following privileges for operation QUERY [[SELECT]
on Object [type=TABLE_OR_VIEW, name=robot_prod. test_user ]]
(state=42000,code=40000) *
It seems that hive server didn't create a grant query for the newly created
table.
1 Could you please advise what could be the problem in env A?
2 Why there isn't any auto grant SELECT query to a newly created table in
env A.
3 The grantor is "*metastore*" Which is different from the normal grantor
"hive". Why there is such a difference?
*Here are the command details:*
1 Spark-shell:
create hive table from a local file using below commands
under robot.prod@test server
#spark-shell
spark.sql("use robot_prod")
val sqlContext = new org.apache.spark.sql.SQLContext(sc)
val df = sqlContext.sql("SELECT * FROM
parquet.`/usr/local/spark/examples/src/main/resources/users.parquet`")
df.write.mode("overwrite").saveAsTable(" robot_prod.test_user")
2 beeline:
login with robot.prod@test_server
#beeline
!connect jdbc:hive2://........:10000 <http://192.168.86.120:10000/>
robot.prod
use robot_prod;
select * from robot_prod.test_user;
Then we have following errors:
Error: Error while compiling statement: FAILED: HiveAccessControlException
Permission denied: Principal [name=robot.prod, type=USER] does not have
following privileges for operation QUERY [[SELECT] on Object
[type=TABLE_OR_VIEW, name=robot_prod. test_user ]] (state=42000,code=40000)
Thanks and regards,
[image: DA_Logo.png] <http://www.digitalalchemy.asia/>
Justin Zhang
Digital Alchemy (Nanjing) Limited Company
T:2046
[image: DA_Blog.jpg] <http://www.digitalalchemy.com.au/blog/>[image:
DA_Linkedin.jpg][image: DA_Twitter.jpg]
<https://www.linkedin.com/company/digital-alchemy>[image: DA_Facebook.jpg]
<https://www.facebook.com/DigitalAlchemyLimited>
[image: CIO_outlook_banners_600x120_v2b.png]
<https://customer-experience-management.apacciooutlook.com/vendor/digital-alchemy-dynamic-decisionpowered-personalized-cem-solutions-cid-2278-mid-119.html>
