How to give superuser privileges to MySQL?

Question

I have a MySQL database which is hosted in Azure, and I'm accessing it through MySQL Workbench in my laptop. The point is that when I want to execute some commands I get error message saying I don't have enough privileges. I tried to access the Users and Privileges section in MySQL Workbench, but I got the message saying:

The account you are currently using does not have sufficient privileges to make changes to MySQL users and privileges.

Where can I give superuser privileges, so that I can execute every command from my MySQL Workbench?

Answer 1

1. The privileges is only related to the user, the client you use has nothing to do with it, so whether you use a workbench or a CLI, it does not matter.

2. In MySQL privileges are arranged to different "user", and "user" are composed by "username" and "host" (from where you login the mysql), so basically, a user in mysql who own specific privilege looks like:

   'foo'@'192.16.22.231', 'bar'@'10.3.243.%' ..
   

3. How to improve all the privileges to a specific user? do this as a super user:

   GRANT ALL PRIVILEGES ON \*.* TO YOUR_USER
   

   super user is usually 'root'@'127.0.0.1', since you have to grant to your specific 'user', you have to know the IP address from which you login

If you think above is a little complicated and your mysql is just fast-installed and simple configured, you can just try this and maybe it helps:

1. login as 'root' or mysql

2. execute this:

   GRANT ALL PRIVILEGES ON \*.* TO 'your_user'@'%';
   

Answer 2

For example, you can create the superuser john which can do everything same as the default user root with GRANT as shown below. *MySQL has SUPER privilege but it is not the one to create the superuser and you probably need to log in with the user root:

GRANT ALL ON *.* TO 'john'@'localhost' WITH GRANT OPTION;

Or:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost' WITH GRANT OPTION;

And, you can give all privileges on all the tables in all databases (*.*) to the user john with GRANT as shown below, but this doesn't create the superuser which can do everything same as the default user root:

GRANT ALL ON *.* TO 'john'@'localhost';

Or:

GRANT ALL PRIVILEGES ON *.* TO 'john'@'localhost';

In addition, you can give all privileges on only person table in only apple database (apple.person) to the user john with GRANT as shown below:

GRANT ALL ON apple.person TO 'john'@'localhost';

Or:

GRANT ALL PRIVILEGES ON apple.person TO 'john'@'localhost';

Answer 3

1. Execute SELECT * FROM mysql.user WHERE user = 'your account'\G in your client. If All the priv column is 'Y', your account has superuser privileges.

2. You can also try UPDATE mysql.user. Then, execute flush privileges; to make your changes effective.

3. Execute GRANT ALL PRIVILEGES ON \*.* TO 'your_user'@'%'; to add a new superuser privilege account.

4. If all the above operations are not allowed, please call the Azure support. In cloud database, some system databases may be not be allowed to access.

Answer 4

I had solved this issue by doing:

UPDATE mysql.user SET Grant_priv = 'Y' WHERE User = 'root';

This can be "dangerous" if you do not know what are you doing ;-)