Are 'rajivratn'@'%' and rajivratn@localhost refer to same user in MySQL?

Question

I want load data into a table (dataset) of a database. I am using the standard command

 LOAD DATA INFILE '/home/rajivratn/single_output.tsv' IGNORE INTO TABLE dataset ...

I am getting the following permission error:

ERROR 1045 (28000): Access denied for user 'rajivratn'@'localhost' (using password: YES)

Most of the post suggested that this problem is due to FILE privilege on MySQL and can be fixed by the following GRANT command:

GRANT FILE ON *.* to 'rajivratn'@'%';

I have checked the permission and found the following:

mysql> show grants for 'rajivratn'@'%'
    -> ;
+--------------------------------------+
| Grants for rajivratn@%               |
+--------------------------------------+
| GRANT FILE ON *.* TO 'rajivratn'@'%' |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> show grants;
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for rajivratn@localhost                                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'rajivratn'@'localhost' IDENTIFIED BY PASSWORD 'somepassword'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON `yahoo`.* TO 'rajivratn'@'localhost' |
+------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

However, I am still getting the ERROR 1045 (28000): Access denied for user 'rajivratn'@'localhost' (using password: YES)

Moreover, Why I can not see the FILE permission in Grants for rajivratn@localhost and why it is different from the grants of 'rajivratn'@'%'

Any suggestions to fix this issue?

Thanks

Answer 1

Finally, I have loaded the dataset into the 'yahoo' dataset with the following command using the .csv file:

mysqlimport -u rajivratn -p --local yahoo dataset.csv

Thanks to other two answers since they also clarified many other important concepts related to mysql.

Answer 2

give permission as per below-

GRANT USAGE ON *.* TO 'rajivratn'@'localhost' IDENTIFIED BY PASSWORD 'somepassword'; 
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, FILE ON `yahoo`.* TO 'rajivratn'@'localhost'

Note: Added one FILE permission.

Answer 3

MySQL account names consist of a user name and a host name. This enables creation of accounts for users with the same name who can connect from different hosts.

https://dev.mysql.com/doc/refman/5.6/en/account-names.html

So, no, the two users in question are indeed two different users, with two independent sets of privileges.

Also,

An account name consisting only of a user name is equivalent to 'user_name'@'%'. For example, 'me' is equivalent to 'me'@'%'.

When you connect, the server uses the entries in the mysql.user table, in an internally-sorted order, to determine which user you are.

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific.

The pattern '%' means “any host” and is least specific.

https://dev.mysql.com/doc/refman/5.6/en/connection-access.html

So, when you connect from localhost, you get matched with that user, otherwise, you get matched with the wildcard user.

You will either need to remove the user at localhost, or grant privileges to it, specifically.

mysql> GRANT ... TO rajivratn@localhost ...;