Support Questions
Find answers, ask questions, and share your expertise

hive: SQLStdAuth : grant all on database doesn't give grant on underneath table

Highlighted

hive: SQLStdAuth : grant all on database doesn't give grant on underneath table

New Contributor

hive: SQLStdAuth : grant all on database doesn't give grant on underneath table

1. create database testdb as hive user

2. created table in employee under testdb

3. Created user a123test

4. grant ALL on database testdb to user a123test;

5. Login form beeline as user a123test

6. selct count(*) from employee

above gives me

HiveAccessControlException Permission denied: Principal [name=a123test, type=USER] does not have following privileges for operation QUERY [[SELECT] on Object [type=TABLE_OR_VIEW, name=testdb.employee]] (state=42000,code=40000)

7. If i do

grant insert,update,delete,select on table testdb.employee to user a123test;

then user a123test can select from employee table

My question is if I grant ALL at database level, why i have to give tabe level grant?

Is there any way I can give database level select/insert/Update instead of defining grant at each table level for user/role?

2 REPLIES 2
Highlighted

Re: hive: SQLStdAuth : grant all on database doesn't give grant on underneath table

New Contributor

all document which i read so far gives me indication that we can grant access at database level but i am I am seeing is different behavior

Highlighted

Re: hive: SQLStdAuth : grant all on database doesn't give grant on underneath table

@anuj lal

Under SQL Base Auth, the object_type or privilege level is on Table or on View, it does not honour the Database level.

Refer link

GRANT 
priv_type [, priv_type ] ...
ON table_or_view_name 
TO principal_specification [, principal_specification] ...
[WITH GRANT OPTION];