How to list all Hive databases that I have access to?

PauloNeves — Thu, 17 Dec 2020 23:34:58 GMT

The command show databases list all databases in a Hive instance. The command list databases that I have and don't have access to. When I try to list the tables in a DB that I don't have access to, using the command show tables from forbidden_db, it returns an empty list.

Which command would list all databases that I have access to at least one table?

Re: How to list all Hive databases that I have access to?

GangWar — Mon, 28 Dec 2020 14:04:02 GMT

@PauloNeves You have to login with your user ID in my opinion and that should have a valid kerberos ticket if kerberos is enabled and then you can only list the tables you have access that should come using list tables. You can refer this cheat seat if this helps.

http://hortonworks.com/wp-content/uploads/2016/05/Hortonworks.CheatSheet.SQLtoHive.pdf

Re: How to list all Hive databases that I have access to?

Shelton — Sun, 03 Jan 2021 20:44:47 GMT

@PauloNeves

Yes, the command show databases will list all databases in a Hive instance whether you are authorized to access it or not. I am sure this is cluster devoid of Ranger or Sentry which are the 2 authorization tools in Cloudera!!!

Once the ranger plugin is enabled then authorization is delegated to Ranger to provide fine-grained data access control in Hive, including row-level filtering and column-level masking. This is the recommended setting to make your database administration easier as it provides a centralized security administration, access control, and detailed auditing for user access within the Hadoop, Hive, HBase, and other components in the ecosystem.

Unfortunately, I had already enabled the Ranger plugin for hive on my cluster but all the same, it confirms what I wrote above. Once the ranger plugin is enabled for a component ie. hive,HBase or Kafka then the authorization is managed exclusively through Ranger

Database listing before Ranger
Below is what happens if my user sheltong has not explicitly been given authorization through Ranger, see [screenshots]

I see no database though I have over 8 databases

See the output of the hive user who has explicit access to all the tables due to the default policy he could see the databases.

Database listing after Ranger

After creating a policy explicitly giving the user sheltong access to the 3 databases

Policy granting explicit access to 3 databases

Now when I re-run the show databases bingo!

Back to your question show tables from forbidden_db, it returns an empty list, this can be true especially if the database is empty! has not table like the screenshot below though I have access to the database it's empty

Now I create a table and re-run the select now I am able to see the table

I hope this demonstrates the power of Ranger and explains maybe what you are encountering, I am also thinking if your cluster has Ranger hive plugin enabled you could have select on the databases but you will need explicit minimum select or the following permission on the underlying database tables to be able to see them.

Happy Hadooping

question Re: How to list all Hive databases that I have access to? in Support Questions

How to list all Hive databases that I have access to?

Re: How to list all Hive databases that I have access to?

Re: How to list all Hive databases that I have access to?