0

From the control panel of my website I have created a new MySQL(5) database Test and a new user admin with password 123. I have tried assigning privileges to the user admin using:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'

or

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'Test'

I keep getting the following error:

#1045 - Access denied for user 'admin'@'%' (using password: YES)

I need the following privileges for the user admin:

CREATE, ALTER, DELETE, INSERT, SELECT, UPDATE, LOCK TABLES

How do I make that in a query from phpMyAdmin?

Palec
  • 12,743
  • 8
  • 69
  • 138
Nullbyte
  • 231
  • 1
  • 6
  • 16
  • Have you flushed privileges? – ʰᵈˑ Jan 31 '14 at 16:44
  • You can also manually edit your privileges in `mysql.user` – ʰᵈˑ Jan 31 '14 at 16:44
  • @h. I never used MYSQl before. I am not a coder. What should I do? I made some search but I am not coming right. – Nullbyte Jan 31 '14 at 16:45
  • run `FLUSH PRIVILEGES;` and see if privileges have taken effect then. If not, reply, and I'll help you further. – ʰᵈˑ Jan 31 '14 at 16:47
  • @h.Ok, I tried. This is the error I get: #1227 - Access denied; you need (at least one of) the RELOAD privilege(s) for this operation – Nullbyte Jan 31 '14 at 16:50
  • @Palec As I said in my question there is only one user, "admin". I understand what you say but I need those priviledges for the user "admin" otherwhise the website does not function. – Nullbyte Jan 31 '14 at 16:54
  • @Palec I checked the priviledges for admin and I got this: GRANT ALL PRIVILEGES ON `Test`.* TO 'admin'@'%' which I suppose includes all priviledges I am looking for in my answer. Is that correct? – Nullbyte Jan 31 '14 at 16:59

3 Answers3

1

I guess you are trying to change privileges of 'admin'@''%' being logged in as that user. This is strange.

You can display which user you are logged in as using

SELECT USER();

Then check grants that account already has:

SHOW GRANTS FOR 'admin'@''%';

We came to the conclusion you have

GRANT ALL PRIVILEGES ON `Test`.* TO 'admin'@'%'

That says you already have all privileges on all tables in database Test. You cannot further grant those privileges to other users, though (otherwise there would be WITH GRANT OPTION).

During the installation of MySQL, root user is always created. Use it to grant privileges to other accounts.

More info in manual:

Palec
  • 12,743
  • 8
  • 69
  • 138
0

After run these statements try to execute FLUSH:

FLUSH PRIVILEGES;

From MYSQL Reference Manual :

(...) If you change the grant tables directly but forget to reload them, your changes have no effect until you restart the server. This may leave you wondering why your changes do not seem to make any difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. (...)

bruno2c
  • 11
  • 2
0

Login as a root user then grant all privileges to admin user.

GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost';
Valentin Michalak
  • 2,089
  • 1
  • 14
  • 27
Chiuna Magesa
  • 1
  • 2