hive privileges on table vs database.

[#1]

Whats the difference between granting privileges on tables and granting privileges on a databases.

Example:

By granting below privileges on a table for a user, the user would be able to insert, update etc on a table.

grant insert,update,delete,select on table test01 to user user1;

Now, what would be the use of granting below insert, delete etc privileges on a database for a user ?. As in a database, we can only create and delete tables but we can't insert/update in databases.

grant insert,update,delete,select on database db1 to user user1;

And, does by granting insert, delete etc privileges on a database for a user, will the user be getting those privileges on all the objects in that database ?

[#2 · Accepted solution]

@rakesh kumar

I hope this will help you.

if you are an admin, you need to have full access on data base and tables as well to create,drop,update,alter tables. Even if you want to drop DB you should able to do it.

if you are a developer, you will get limited access to do only your work at tables level.

[#3]

@rakesh kumar

You can enforce authorization at any level in hive using Apache Ranger

Please have a look

http://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.5.3/bk_security/content/overview_ranger_ambari_i...

[#4]

@rakesh kumar

Granting insert,update,delete,select on table x to user1 is more granular level security there are usually many application users with different levels of privileges accessing the same table x in a database. I could run

grant select on table x to user2 ;

this user will only have as the statements say ONLY select on tablex, Whereas the

grant insert,update,delete,select on database db1 to user user1; 

Gives user1 the precedent privileges to all tables in database db1.

To limit select ONLY to user2 I would run the below

grant select on database db1 to user user2; 

Having said that database management is easier using roles otherwise management of user privileges can become a headache.

[#5]

granting insert/delete privileges on a database for a User will let the User have similar privilege on all tables under the database. User can perform DML and DDL actions in that database.