You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Anup Tiwari <an...@gmail.com> on 2018/09/11 13:51:42 UTC
How to Grant All Privileges for All Databases except one in Hive SQL
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
Re: How to Grant All Privileges for All Databases except one in Hive SQL
Posted by Alan Gates <al...@gmail.com>.
If I needed to set the permissions for every table in every database and
there were many, I'd write a shell script that first fetched all the
databases and tables (using show databases, use database, and show tables)
and then generated a "grant select on x" for each table. Assuming you
don't want to grant every table to every user you'll probably need to
incorporate some filtering in your script.
Alan.
On Mon, Sep 17, 2018 at 11:31 AM Anup Tiwari <an...@gmail.com> wrote:
> Hive doesn't have a "grant select on db.*" option, which is what I think
> you're looking for here.
>
> Yes i am looking something like this only and since it is not available,
> does that mean i have to go for each table ?
>
> I am asking because we have many DBs and a lot of tables within each DB so
> is there any other way ?
>
> Regards,
> Anup Tiwari
>
>
> On Mon, Sep 17, 2018 at 8:48 PM Alan Gates <al...@gmail.com> wrote:
>
>> What you are seeing is correct behavior. Select on the database means
>> the user can see objects in the database (ie, tables, views). To see
>> contents of those objects you have to grant access on those objects. Hive
>> doesn't have a "grant select on db.*" option, which is what I think you're
>> looking for here.
>>
>> Alan.
>>
>> On Mon, Sep 17, 2018 at 5:50 AM Anup Tiwari <an...@gmail.com>
>> wrote:
>>
>>> 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 <al...@gmail.com>
>>> 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 <an...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> Can someone reply on this?
>>>>>
>>>>> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <an...@gmail.com> 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
>>>>>>
>>>>>
Re: How to Grant All Privileges for All Databases except one in Hive SQL
Posted by Anup Tiwari <an...@gmail.com>.
Hive doesn't have a "grant select on db.*" option, which is what I think
you're looking for here.
Yes i am looking something like this only and since it is not available,
does that mean i have to go for each table ?
I am asking because we have many DBs and a lot of tables within each DB so
is there any other way ?
Regards,
Anup Tiwari
On Mon, Sep 17, 2018 at 8:48 PM Alan Gates <al...@gmail.com> wrote:
> What you are seeing is correct behavior. Select on the database means the
> user can see objects in the database (ie, tables, views). To see contents
> of those objects you have to grant access on those objects. Hive doesn't
> have a "grant select on db.*" option, which is what I think you're looking
> for here.
>
> Alan.
>
> On Mon, Sep 17, 2018 at 5:50 AM Anup Tiwari <an...@gmail.com>
> wrote:
>
>> 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 <al...@gmail.com> 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 <an...@gmail.com>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> Can someone reply on this?
>>>>
>>>> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <an...@gmail.com> 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
>>>>>
>>>>
Re: How to Grant All Privileges for All Databases except one in Hive SQL
Posted by Alan Gates <al...@gmail.com>.
What you are seeing is correct behavior. Select on the database means the
user can see objects in the database (ie, tables, views). To see contents
of those objects you have to grant access on those objects. Hive doesn't
have a "grant select on db.*" option, which is what I think you're looking
for here.
Alan.
On Mon, Sep 17, 2018 at 5:50 AM Anup Tiwari <an...@gmail.com> wrote:
> 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 <al...@gmail.com> 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 <an...@gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> Can someone reply on this?
>>>
>>> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <an...@gmail.com> 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
>>>>
>>>
Re: How to Grant All Privileges for All Databases except one in Hive SQL
Posted by Anup Tiwari <an...@gmail.com>.
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 <al...@gmail.com> 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 <an...@gmail.com>
> wrote:
>
>> Hi,
>>
>> Can someone reply on this?
>>
>> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <an...@gmail.com> 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
>>>
>>
Re: How to Grant All Privileges for All Databases except one in Hive SQL
Posted by Alan Gates <al...@gmail.com>.
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 <an...@gmail.com> wrote:
> Hi,
>
> Can someone reply on this?
>
> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <an...@gmail.com> 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
>>
>
Re: How to Grant All Privileges for All Databases except one in Hive SQL
Posted by Anup Tiwari <an...@gmail.com>.
Hi,
Can someone reply on this?
On Tue, 11 Sep 2018 19:21 Anup Tiwari, <an...@gmail.com> 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
>