question Ranger: Access denied for user 'rangeradmin'@'localhost' in Support Questions

Ranger: Access denied for user 'rangeradmin'@'localhost'

ameyasakhalkar — Thu, 27 Jul 2017 12:56:47 GMT

I followed the instructions (https://cwiki.apache.org/confluence/display/RANGER/Ranger+Installation+Guide) to install Ranger admin and usersync for evaluation. Ranger admin UI is up.

Also followed the instructions at https://docs.hortonworks.com/HDPDocuments/HDP2/HDP-2.6.1/bk_security/content/configuring_mysql_for_ranger.html to setup MySQL server and create/grant permissions to necessary users. MySQL is running on the same machine as Ranger admin and usersync.

Granted the same permissions to user rangeradmin as well.

However, I see a flurry of following errors in /usr/local/mysql/data/mysqld.local.err:

2017-07-27T05:50:37.759668Z 1389 [Note] Access denied for user 'rangeradmin'@'localhost' (using password: YES)

2017-07-27T05:50:37.759669Z 1390 [Note] Access denied for user 'rangeradmin'@'localhost' (using password: YES)

2017-07-27T05:50:38.762447Z 1392 [Note] Access denied for user 'rangeradmin'@'localhost' (using password: YES)

[...]

I am able to connect to MySQL as 'rangeradmin' using MySQL's command line client. Not sure why Ranger admin is not able to connect.

Also, I am _not_ able to login to Ranger admin UI. It displays "Unable to connect to DB..." error. Any pointers to debug?

Thanks.

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

jsensharma — Thu, 27 Jul 2017 13:04:56 GMT

@Ameya Sakhalkar

Looks like the grant is still not set correctly. Can you please share the output of the following SQL:

# mysql -u rangeradmin -p -h localhost
mysql> select user, host from user where user like 'ranger%' ;

Or connect to Mysql as root and then run.

mysql> SHOW GRANTS FOR 'rangeradmin'@'localhost';
mysql> GRANT ALL ON *.* TO 'rangeradmin'@'localhost' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

ameyasakhalkar — Thu, 27 Jul 2017 13:12:37 GMT

Sure. Please find it below.

mysql> use mysql;
Database changed
mysql> select user, host from user where user like 'ranger%' ;
+-------------+-----------+
| user        | host      |
+-------------+-----------+
| rangeradmin | %         |
| rangerdba   | %         |
| rangeradmin | localhost |
| rangerdba   | localhost |
+-------------+-----------+
4 rows in set (0.00 sec)

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

dsharma — Thu, 27 Jul 2017 13:26:54 GMT

can you please try following command on your database:

GRANT ALL ON *.* TO 'rangeradmin'@'localhost' identified by "<rangeradmin_password>" WITH GRANT OPTION;

flush privileges;

and then restart the ranger

Note: another thing it is better to provide root credentials , then at the time of installation it will connect through root and will take care of doing creation of rangeradmin database and configuring it

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

ameyasakhalkar — Thu, 27 Jul 2017 15:57:06 GMT

Thanks @Deepak Sharma. Followed the steps, however, still getting "Access denied" error.

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

dsharma — Thu, 27 Jul 2017 18:43:01 GMT

did you replace the correct password and host in the command?

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

Shelton — Thu, 27 Jul 2017 22:34:19 GMT

@Ameya Sakhalkar

Just run this it should work while logged on as root MySQL,make sure you are connected to the ranger database

grant all on rangeradmin.* to ranger;
Flush privileges;

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

ameyasakhalkar — Sat, 29 Jul 2017 18:15:13 GMT

@Deepak Sharma, yes I did. Is there anything else I need to try out?

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

ameyasakhalkar — Sat, 29 Jul 2017 18:16:58 GMT

Thanks @Geoffrey Shelton Okot, but its failing in spite of this

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

Shelton — Sun, 30 Jul 2017 06:09:11 GMT

@Ameya Sakhalkar

Don't worry you can create a new Ranger database as follow change username and password to suit your environment.

mysql -u root -p{rootpassword}

CREATE USER 'ranger'@'localhost' IDENTIFIED BY 'ranger_password';
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'localhost';
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'rangerDBhost';
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'%';
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'rangerDBhost' IDENTIFIED BY 'ranger' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'localhost' IDENTIFIED BY 'ranger_password' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'ranger'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# mysql -u ranger -pranger
CREATE DATABASE ranger;

################ simpler version ###################
create database ranger;
create user ranger identified by 'ranger';
grant all on ranger.* to ranger;
Flush privileges;

Let me know if that helps

Re: Ranger: Access denied for user 'rangeradmin'@'localhost'

ameyasakhalkar — Mon, 31 Jul 2017 23:02:44 GMT

Thanks a ton @Geoffrey Shelton Okot. It worked.