Error while assigning permission in Hive and MySql

Question

Hive version - 3.1.2 SQL connector version - 8.0

Trying to grant permissions like - GRANT all on . to 'hiveuser'@localhost identified by 'hivepassword';

Error - ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'indentified by 'hivepassword'

Please help, I am stuck here! Thanks!

Answer 1

Mysql 8.0 GRANT instruction doesn't support implicit account creation anymore. Therefore the "identified by" option is not part of the instruction anymore. (See 13.7.1.6 GRANT Statement)

So:

• If the user account you are trying to grant doesn't exist yet, you have to create it first with the CREATE USER 'hiveuser'@'localhost' IDENTIFIED BY 'hivepassword'; instruction.
• Then you can grant it with your grant instruction, but without the password definition part: GRANT ALL ON *.* TO 'hiveuser'@'localhost';