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?

Maybe this post will help: stackoverflow.com/questions/12111760/… — Grasshopper
– Grasshopper, Commented Nov 14, 2014 at 20:09
@Grasshopper I'm already connecting to the database through MySQL Workbench, but I don't have super user privileges I guess, cause I cannot execute some of the commands. — tett
– tett, Commented Nov 14, 2014 at 20:45

techraf · Accepted Answer · 2016-12-01 03:43:01Z

4

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.
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.%' ..
```
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:

login as 'root' or mysql

execute this:

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

edited Dec 1, 2016 at 3:43

techraf

69.2k30 gold badges211 silver badges215 bronze badges

answered Nov 15, 2014 at 3:12

Fengya Li

4,8791 gold badge17 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tot Zam · Accepted Answer · 2016-12-01 06:40:33Z

0

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.
You can also try UPDATE mysql.user. Then, execute flush privileges; to make your changes effective.
Execute GRANT ALL PRIVILEGES ON \*.* TO 'your_user'@'%'; to add a new superuser privilege account.
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.

edited Dec 1, 2016 at 6:40

Tot Zam

8,84411 gold badges55 silver badges81 bronze badges

answered Dec 1, 2016 at 4:08

songlei.wang

3813 silver badges6 bronze badges

Comments

mzalazar · Accepted Answer · 2017-05-08 18:00:20Z

0

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

answered May 8, 2017 at 18:00

mzalazar

6,6363 gold badges39 silver badges33 bronze badges

Comments

Super Kai - Kazuya Ito · Accepted Answer · 2023-11-10 17:26:36Z

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

Collectives™ on Stack Overflow

How to give superuser privileges to MySQL?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related