Solved

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

Posted on 2014-02-20
6
487 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

You may have a outside contractor who comes in once a week or seasonal to do some work in your office but you only want to give him access to the programs and files he needs and keep privet all other documents and programs, can you do this on a loca…
A quick guide on how to use Group Policy to create a custom power plan and set it active on Windows 7.
This Micro Tutorial will give you a basic overview of Windows DVD Burner through its features and interface. This will be demonstrated using Windows 7 operating system.
The viewer will learn how to successfully download and install the SARDU utility on Windows 7, without downloading adware.

831 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