Solved

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

Posted on 2014-02-20
6
476 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
  • 3
  • 2
6 Comments
 
LVL 24

Expert Comment

by:mankowitz
Comment Utility
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 82

Expert Comment

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

Author Comment

by:Robert Silver
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Hi Friends, These registry tweaks will help you optimizing your Windows 7 system for any VDI. This will improve the machine performanance and can be used on normal systems also. These are few registry tweaks which will add value by enhancing the …
So many times I have seen the words written in a question "if only I could show you" or " I know how hard it is for you since you can't see it" in any zone. That has inspired me to write about this tool in windows 7 called "Problem Steps Recorder…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
This Micro Tutorial will give you a basic overview of Windows Live Photo Gallery and show you various editing filters and touches to photos you can apply. This will be demonstrated using Windows Live Photo Gallery on Windows 7 operating system.

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now