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

score 3 · Answer 1 · answered Jun 26 '15 at 12:41

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 ...;

score 2 · Accepted Answer · answered Jun 28 '15 at 03:39

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.

Zafar Malik · Answer 3 · 2015-06-26T08:47:09.427

1

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.

edited Jun 26 '15 at 08:47

answered Jun 26 '15 at 06:40

Zafar Malik

6,734
2
19
30

Thanks for the answer. How can I give permission to my ip/ all ips using MySQL? I am using MySQL on a CentOS server. – piku Jun 26 '15 at 06:48
1

I have already given command to assing rights, if you execute given command then you can access mysql from all ips if you put your ip in place of % then you will be able to access only from that ip. this ip means your local system ip from where you are accessing mysql from any gui tool. you can get your ip after searching "what is my ip" in google – Zafar Malik Jun 26 '15 at 06:52
are you executing this command from db server itself ie linux db machine, if so then you have rights and it seems you are putting wrong password. – Zafar Malik Jun 26 '15 at 06:55
Actually, in my case, I am accessing the MySQL at the same linux server where it is installed. So in this case, localhost ip should be same as the server ip. Anyways, I will ask my boss to grant me the access you suggested since I do not have rights to execute GRANT. – piku Jun 26 '15 at 07:07
1

in your case there should not be need of extra permission as you are executing on same db server...are you able to connect mysql on your linux db server by following command "mysql -urajivratn -p" – Zafar Malik Jun 26 '15 at 07:11
and you are inserting data into a table which exist in yahoo database. – Zafar Malik Jun 26 '15 at 07:12
Yes, I am able to connect "mysql -urajivratn -p". Yes, database 'yahoo' is exists and I have already created a table named 'dataset' in this database. I can see the columns of this table as well. I am getting sql-1045 ERROR while loading data to this table from a file. – piku Jun 26 '15 at 07:16
It seems password format issue with your mysql version..check your password length by select user,password from mysql.user where user='rajivratn'; if password length is short then other passwords in this table then you need to reset your password in new format. – Zafar Malik Jun 26 '15 at 07:26
Do you mean the password length of 'somepassword' in GRANT? In my case it is '*4AD47E08DAE2BD4F0977EED5D23DC902349DF618' and its length is same as others. Or you referring to the password of my mysql? – piku Jun 26 '15 at 07:39
just need to add file privileges, please check final grant statements and execute it on server to get related permission. – Zafar Malik Jun 26 '15 at 08:47
:( As I mentioned in the question, I already have FILE privileges. mysql> show grants for rajivratn@'%'; +--------------------------------------+ | Grants for rajivratn@% | +--------------------------------------+ | GRANT FILE ON *.* TO 'rajivratn'@'%' | +--------------------------------------+ – piku Jun 26 '15 at 10:29
either these privileges should be on localhost or provide other privileges also on all ips means %, as to perform your desired action connection required combination of privileges like insert, file etc. Simply give FILE permission to rajivratn@locahost and check... – Zafar Malik Jun 26 '15 at 10:44

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

3 Answers3