Hi Alan,
I have given select access of a database to a role which is attached to a
user but after this also that user is not able to execute select statements
on tables of that database. But if i provide access at table level then
that is working. Can you please help me here ?
Hive Version : 2.3.2
Please find below steps :-
1. Added below confifuration in hive-site.xml
<property>
<name>hive.server2.enable.doAs</name>
<value>false</value>
</property>
<property>
<name>hive.users.in.admin.role</name>
<value>hadoop</value>
</property>
<property>
<name>hive.security.authorization.manager</name>
<value>org.apache.hadoop.hive.ql.security.authorization.plugin.sqlstd.SQLStdHiveAuthorizerFactory</value>
</property>
<property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
</property>
<property>
<name>hive.security.authenticator.manager</name>
<value>org.apache.hadoop.hive.ql.security.SessionStateUserAuthenticator</value>
</property>
2. Restarted Hive Server2.
3. Logged in to hive shell with hadoop user and executed below command
without any error :-
set role admin;
create role readonly;
GRANT ROLE readonly TO USER `user2`;
GRANT SELECT ON DATABASE anup TO ROLE readonly;
4. Logged in to hive shell with user2 and executed below commands :-
select * from anup.t2 limit 5;
*Error :-*
Error: Error while compiling statement: FAILED: HiveAccessControlException
Permission denied: Principal [name=mohan.b, type=USER] does not have
following privileges for operation QUERY [[SELECT] on Object
[type=TABLE_OR_VIEW, name=anup.t2]] (state=42000,code=40000)
show current roles;
+-----------+
| role |
+-----------+
| public |
| readonly |
+-----------+
2 rows selected (0.085 seconds)
SHOW GRANT ROLE `readonly` ON DATABASE anup;
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| database | table | partition | column | principal_name |
principal_type | privilege | grant_option | grant_time | grantor |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
| anup | | | | readonly |
ROLE | SELECT | false | 1537187896000 | hadoop |
+-----------+--------+------------+---------+-----------------+-----------------+------------+---------------+----------------+----------+
Regards,
Anup Tiwari
On Fri, Sep 14, 2018 at 10:50 PM Alan Gates <[email protected]> wrote:
> You can see a full list of what grant supports at
> https://cwiki.apache.org/confluence/display/Hive/SQL+Standard+Based+Hive+Authorization#SQLStandardBasedHiveAuthorization-Grant
>
> There is no "grant x to user on all databases" or regex expressions for
> database names. So you'll have to do the databases one by one.
>
> External security managers such as Apache Ranger (and I think Apache
> Sentry, but I'm not sure) can do blanket policies or default policies.
> This has the added advantage that as new databases are created the policies
> immediately apply.
>
> Alan.
>
> On Thu, Sep 13, 2018 at 10:37 PM Anup Tiwari <[email protected]>
> wrote:
>
>> Hi,
>>
>> Can someone reply on this?
>>
>> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <[email protected]> wrote:
>>
>>> Hi All,
>>>
>>> I have similar requirement as mentioned in the link Link to question
>>> <https://stackoverflow.com/questions/38199021/how-to-grant-all-privileges-for-all-databases-except-one-in-hive-sql>
>>> .
>>>
>>> *Requirement :-*
>>>
>>> I know how to grant privileges on a database to a role in Hive SQL.
>>> For example, GRANT ALL ON DATABASE analyst1 TO ROLE analyst_role;
>>> But there are hundreds of databases on my system, it's almost impossible
>>> to grant one by one.
>>> Is it possible to grant all privileges for all databases ?
>>> Also Is it possible to grant all privileges for all databases except one
>>> database(ex: db.name = temp)?
>>>
>>>
>>> Regards,
>>> Anup Tiwari
>>>
>>
