How do I grant full permissions to a user with mysql?

Question

How do I grant full permissions to a mysql user? Im running MySQL 5.1 on Redhat. I tried using cpanel, but when I use jet profiler it says I do not have full permissions.

I am not too good with ssh, so please be detailed.

Answer 1

From the shell connect to MySQL as an administrator:

mysql -u root -p mysql

Now on the mysql prompt type:

> grant all privileges on DATABASE_NAME.* to USERNAME@localhost identified by 'PASSWORD';
> flush privileges;
> \q

You can read more about GRANT's syntax at MySQL's site.

Answer 2

Just insert or update mysql.user with value 'Y' in each column privileges.

mysql> update mysql.user set user='your', host='localhost', .....
mysql> flush privileges;

Answer 3

You mention that you're using Cpanel, which usually indicates that you're on a shared hosting server in which case you won't be able to grant full permissions.

If you're not though, then you need to run a SQL command.

You can do this through PHPMyAdmin or through a MySQL client over SSH.

The query you need to run is:

GRANT ALL PRIVILEGES TO username@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

To do this through PHPMyAdmin, select any database and then click on 'SQL' tab in the main window. You can then type it from there. Although in fact if you're using PHPMyAdmin there's a "Privileges" section that you can use rather than running an SQL query.

If you're using command line, then connect over SSH.

Then, run the following command:

mysql -u [username] -p 

This will drop you into an interactive MySQL prompt and you can paste the queries as above.

Answer 4

Another potential solution is to simply "Add User to Database" using the cPanel's "MySQL Databases" That app provides dropdown lists of existing users and databases. During the process of associating the user to the database will also allow you to assigning user privileges to the database. Selecting "All" privileges will work if that is what is desired.