Support Questions

Find answers, ask questions, and share your expertise

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

avatar
Explorer

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?

2 REPLIES 2

avatar
Master Guru

@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


Cheers!
Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.

avatar
Master Mentor

@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]

01.JPG

I see no database though I have over 8 databases 

02.JPG

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.
04.JPG

Database listing after Ranger

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

03.JPG
Policy granting explicit access to 3 databases

05.JPG

Now when I re-run the show databases bingo!
06.JPG

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

07.JPG

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

08.JPG

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.
09.JPG
Happy Hadooping