After hive authorization is enabled, users belonging to role public were not able to create tables on databases other than default.
- Everybody is allowed to create tables in default database.
- Only the owner of a database is allowed to create tables in that database.
- Admin role can do anything.
To let other people create tables in databases other than default, we need to change the owner of the database from a user to a role. This can be achieved through alter table command. Then everybody belonging to that role gets owner privileges and can create tables on that database.
SET ROLE admin; ALTER DATABASE <my_db> SET OWNER ROLE <new_role|public>;