how to add super privileges to mysql database?
MysqlMysql Problem Overview
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
Mysql Solutions
Solution 1 - Mysql
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 in on *.* because SUPER is not the privilege that applies just to one database, it's global.
Solution 2 - Mysql
You can see the privileges here.
Then you can edit the user
Solution 3 - Mysql
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
Solution 4 - Mysql
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
Solution 5 - Mysql
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