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 <alanfga...@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 <anupsdtiw...@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 <alanfga...@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 <anupsdtiw...@gmail.com> >>> wrote: >>> >>>> Hi, >>>> >>>> Can someone reply on this? >>>> >>>> On Tue, 11 Sep 2018 19:21 Anup Tiwari, <anupsdtiw...@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 >>>>> >>>>
