CODEWITHSUNDEEP
mysqlsql-grant
Nullbyte
Nullbyte

Reputation: 251

Grant privileges to user in MySQL

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?

Upvotes: 0

Views: 8016

Answers (3)

Chiuna Magesa
Chiuna Magesa

Reputation: 1

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

GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'localhost';

Upvotes: 0

Palec
Palec

Reputation: 13581

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:

Upvotes: 1

bruno2c
bruno2c

Reputation: 11

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. (...)

Upvotes: 0

Related Questions