Solved

Why would root@localhost not have SUPER privileges to begin with?

Posted on 2014-02-20
6
497 Views
Last Modified: 2014-02-23
I recently discovered on a windows based MySQL 5.6.10 version the root@localhost user
did not have SUPER privileges and I would like to know why that would be?

Is there some odd security issue in granting SUPER privileges to the root@localhost user?
0
Comment
Question by:Robert Silver
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 39873652
While mysql is set up by default to have the root user have superuser privileges, it doesn't have to stay that way. You can modify any account you want. In fact, if I were going to try to hack into a mysql server, I'd probably try to hack the root account. Wouldn't it be funny if the root user had no privileges or didn't exist at all?
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39874635
Why are you saying that it doesn't?  What is it you can't do?
0
 
LVL 2

Author Comment

by:Robert Silver
ID: 39875477
No it turns out I was wr ong. I did have to set:
SET GLOBAL log_bin_trust_function_creators = 1;  
What I find rather odd is why is this variable not listed in the 5.6 version MySQL reference
manual?
to enable the creation of a function on a database being replicated.
At one point I thought I could exit out and grant SUPER on *.* to 'localhost'@'root'
however,  that now seems wrong
show grants just shows:
 grant all on *.* with grant option

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*....
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

What does that variable do exactly when set to true ?

I do know that once set I could create functions I could not create prior to  its setting to true
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 39877660
That variable is in the docs. See https://dev.mysql.com/doc/refman/5.6/en/stored-programs-logging.html:

To relax the preceding conditions on function creation (that you must have the SUPER privilege and that a function must be declared deterministic or to not modify data), set the global log_bin_trust_function_creators system variable to 1. By default, this variable has a value of 0,
0
 
LVL 2

Author Closing Comment

by:Robert Silver
ID: 39878513
Okay so when I create a deterministic e.g    f(x)=  sin(x) *6
or
CREATE FUNCTION helloguy (s CHAR(20))
RETURNS CHAR(50)
DETERMINISTIC RETURN CONCAT('Hello, ',s,'!');

The return must be prefixed with DETERMINISTIC  or else that variable needs to be set?

Also do you know what version of MySQL DETERMINISTIC and that variable :
 log_bin_trust_function_creators  was added?

That of course works so now I know if a function is to work with replication it must be
defined by the return statement being proceeded by DETERMINISTIC key word

A definite gotcha!
0
 
LVL 24

Expert Comment

by:mankowitz
ID: 39880868
I agree. It's not immediately obvious, but deterministic functions have totally different implications for replication. If you can reliably predict the output of a function, you can simply store the function in the replication table. If the function applies a random effect to the data, you have to store all of the changed rows.
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
remote mysql 8 53
event logs for application install and uninstall 3 27
Mysql query one to many 11 38
using interface in TLB 3 29
New Windows 7 Installations take days for Windows-Updates to show up and install. This can easily be fixed. I have finally decided to write an article because this seems to get asked several times a day lately. This Article and the Links apply to…
While working, an annoying popup showing below will come and we cannot cancel or close it form the screen. The error message will come again and again.
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
This Micro Tutorial will teach you how to change your appearance and customize your Windows 7 interface to your unique preference. This will be demonstrated using Windows 7 operating system.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question