CODEWITHSUNDEEP
mysql
Manish Malviya
Manish Malviya

Reputation: 871

how to add super privileges to mysql database?

I am trying to execute query in mysql.

SET GLOBAL log_bin_trust_function_creators =1;

Error:

SQL query:
SET GLOBAL log_bin_trust_function_creators =1
MySQL said:
#1227 - Access denied; you need the SUPER privilege for this operation

I want to know that how do i assign SUPER privileges to any database

Upvotes: 68

Views: 406220

Answers (5)

Akash KC
Akash KC

Reputation: 16310

You can add super privilege using phpmyadmin:

Go to PHPMYADMIN > privileges > Edit User > Under Administrator tab Click SUPER. > Go

If you want to do it through Console, do like this:

 mysql> GRANT SUPER ON *.* TO user@'localhost' IDENTIFIED BY 'password';

After executing above code, end it with:

mysql> FLUSH PRIVILEGES;

You should do it with *.* (all databases) because SUPER is not a privilege which applies just to one database, it's global.

Upvotes: 103

Sandeep
Sandeep

Reputation: 1401

On Centos 5 I was getting all sorts of errors trying to make changes to some variable values from the MySQL shell, after having logged in with the proper uid and pw (with root access). The error that I was getting was something like this: 

mysql> -- Set some variable value, for example
mysql> SET GLOBAL general_log='ON';
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

In a moment of extreme serendipity I did the following:

OS-Shell> sudo mysql                          # no DB uid, no DB pw

Kindly note that I did not provide the DB uid and password 

mysql> show variables;
mysql> -- edit the variable of interest to the desired value, for example
mysql> SET GLOBAL general_log='ON';

It worked like a charm

Upvotes: 0

Gustavo A Garcia
Gustavo A Garcia

Reputation: 198

In Sequel Pro, access the User Accounts window. Note that any MySQL administration program could be substituted in place of Sequel Pro.

Add the following accounts and privileges:

GRANT SUPER ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD

Upvotes: 3

dos007
dos007

Reputation: 1

just the query phpmyadmin prints after granting super user. hope help someone with console:

ON $.$ TO-> $=* doesnt show when you put two with a dot between them.

REVOKE ALL PRIVILEGES ON . FROM 'usr'@'localhost'; GRANT ALL PRIVILEGES ON . TO 'usr'@'localhost' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

and the reverse one, removing grant:

REVOKE ALL PRIVILEGES ON . FROM 'dos007'@'localhost'; REVOKE GRANT OPTION ON . FROM 'dos007'@'localhost'; GRANT ALL PRIVILEGES ON . TO 'dos007'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

checked on vagrant should be working in any mysql

Upvotes: 0

Ruwantha
Ruwantha

Reputation: 2653

You can see the privileges here.enter image description here

Then you can edit the user

Upvotes: 9

Related Questions