Sfrgttk

Building an exterior wall within an exterior wall for insulation

Why did the villain in the first Men in Black movie care about Earth's Cockroaches?

Why does magnet wire need to be insulated?

What happens when the wearer of a Shield of Missile Attraction is behind total cover?

A starship is travelling at 0.9c and collides with a small rock. Will it leave a clean hole through, or will more happen?

Definition of "atomic object"

The effect of fishing on total land area needed to feed an island settlement

I have trouble understanding this fallacy: "If A, then B. Therefore if not-B, then not-A."

Why don't key signatures indicate the tonic?

A Missing Symbol for This Logo

How to deal with possible delayed baggage?

What is a good reason for every spaceship to carry a weapon on board?

What is the difference between rolling more dice versus fewer dice?

Current across a wire with zero potential difference

Is there a verb that means to inject with poison?

What will happen if Parliament votes "no" on each of the Brexit-related votes to be held on the 12th, 13th and 14th of March?

After checking in online, how do I know whether I need to go show my passport at airport check-in?

systemd service won't start nodejs

Explanation of a regular pattern only occuring for prime numbers

Why is it that Bernie Sanders is always called a "socialist"?

Square Root Distance from Integers

Why would space fleets be aligned?

Why does PHOTOREC keep finding files?

What is the difference between "...", '...', $'...', and $"..." quotes?

Difference of write authorization on file system with mySQL

how to use a different tmp dir for INTO OUTFILE select with MySQL?MySQL query with an OUTFILE to csv not working when called from a batch fileMoving datadir OK start, but socket error for mysql clientMySQL InnoDB Configuration with 60Gb of MemoryCannot overwrite file: /var/lib/mysql/ibdata1 when using Percona xtrabackupImporting a mysql database after drop filling up limited / in /tmp deleted file - A problem?service mongod cant start, permission denied mongod.lock when changing dbpathmysql db backup and restore withought mysqldump. only tar?MySQL cannot create file (permission denied). even my folder permission is 1777 (Ubuntu)MySQL: initialize the Data Directory (--data-dir) for Operating System user other than mysql

2

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this:

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE 'key01_en.html'

By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default.

Now, I want the file to be saved in my user let say /home/userone/dump_html folder

If I say this (I launch the mysql command from the shell, and logged as "userone":

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

It makes an error that the process does not have enough rights to write there.

But now still from the mysql> prompt, if I make:

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/tmp/key01_en.html'

;



-- and then

system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html

;

It works without any issue.

It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why?
(The discussion started on this point from this answer on SO)

mysql permissions command-line access-control

share|improve this question

edited May 23 '17 at 11:33

Community♦

1

asked May 9 '14 at 6:29

рüффпрüффп

1115

bumped to the homepage by Community♦ 13 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mysql prompt is run with user right , where SELECT in database has process rights. In this case mysqld (i think) see top or 'ps aux | grep mysql' and you see user which own mysql service
  
  – simplexio
  May 9 '14 at 6:56
  

  
  
  
  

add a comment | 

2

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this:

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE 'key01_en.html'

By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default.

Now, I want the file to be saved in my user let say /home/userone/dump_html folder

If I say this (I launch the mysql command from the shell, and logged as "userone":

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

It makes an error that the process does not have enough rights to write there.

But now still from the mysql> prompt, if I make:

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/tmp/key01_en.html'

;



-- and then

system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html

;

It works without any issue.

It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why?
(The discussion started on this point from this answer on SO)

mysql permissions command-line access-control

share|improve this question

edited May 23 '17 at 11:33

Community♦

1

asked May 9 '14 at 6:29

рüффпрüффп

1115

bumped to the homepage by Community♦ 13 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  mysql prompt is run with user right , where SELECT in database has process rights. In this case mysqld (i think) see top or 'ps aux | grep mysql' and you see user which own mysql service
  
  – simplexio
  May 9 '14 at 6:56
  

  
  
  
  

add a comment | 

On my local mySQL (on CentOS 6 VM) database I wanted to export some data from the DB to a file like this:

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE 'key01_en.html'

By doing this, the file will be saved in the /var/lib/mysql/mydbschema/ by default.

Now, I want the file to be saved in my user let say /home/userone/dump_html folder

If I say this (I launch the mysql command from the shell, and logged as "userone":

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

It makes an error that the process does not have enough rights to write there.

But now still from the mysql> prompt, if I make:

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/tmp/key01_en.html'

;



-- and then

system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html

;

It works without any issue.

It looks strange to me the SQL cannot write on my disk but the system command can. Any idea why?
(The discussion started on this point from this answer on SO)

mysql permissions command-line access-control

share|improve this question

edited May 23 '17 at 11:33

Community♦

1

asked May 9 '14 at 6:29

рüффпрüффп

1115

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE 'key01_en.html'

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/home/userone/dumphtml/key01_en.html'

SELECT html

FROM mytable

WHERE lang = 'en' and id='KEY01'

INTO OUTFILE '/tmp/key01_en.html'

;



-- and then

system mv /tmp/key01_en.html /home/userone/dumphtml/key01_en.html

;

share|improve this question

edited May 23 '17 at 11:33

Community♦

1

asked May 9 '14 at 6:29

рüффпрüффп

1115

share|improve this question

edited May 23 '17 at 11:33

Community♦

1

asked May 9 '14 at 6:29

рüффпрüффп

1115

edited May 23 '17 at 11:33

Community♦

1

edited May 23 '17 at 11:33

Community♦

1

asked May 9 '14 at 6:29

рüффпрüффп

1115

asked May 9 '14 at 6:29

рüффпрüффп

1115

1 Answer
1

active

oldest

votes

0

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.

share|improve this answer

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

answered Dec 7 '14 at 22:15

turagittechturagittech

1213

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I understand the rights limitation of the mySQL user but in the same "shell" I can use the system command and these command have the right to write in my home folder. Your answer even correct does not explain why there is such difference between commands privileges.
  
  – рüффп
  Oct 30 '16 at 10:42
  
  
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f64809%2fdifference-of-write-authorization-on-file-system-with-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.

share|improve this answer

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

answered Dec 7 '14 at 22:15

turagittechturagittech

1213

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I understand the rights limitation of the mySQL user but in the same "shell" I can use the system command and these command have the right to write in my home folder. Your answer even correct does not explain why there is such difference between commands privileges.
  
  – рüффп
  Oct 30 '16 at 10:42
  
  
  

  
  
  
  

add a comment | 

0

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.

share|improve this answer

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

answered Dec 7 '14 at 22:15

turagittechturagittech

1213

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I understand the rights limitation of the mySQL user but in the same "shell" I can use the system command and these command have the right to write in my home folder. Your answer even correct does not explain why there is such difference between commands privileges.
  
  – рüффп
  Oct 30 '16 at 10:42
  
  
  

  
  
  
  

add a comment | 

Your problem is that MySQL cannot write to your home directory, as by default it will run as the MySQL user, or as some other user you defined when you installed and configured it, if not using the default rpm or other install package. Users are not, by default, able to write to another user's home directory as it is each account's private space.

/tmp is by default writable by all users. As such MySQL can write there. Depending on user groups and umask on your system you may be able to read that output as your user.

If you don't want to use /tmp you could create another directory where your user and MySQL can write and make sure your user and MySQL are members of the same group.

share|improve this answer

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

answered Dec 7 '14 at 22:15

turagittechturagittech

1213

share|improve this answer

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

answered Dec 7 '14 at 22:15

turagittechturagittech

1213

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

edited Dec 7 '14 at 23:11

Michael Green

14.7k83060

answered Dec 7 '14 at 22:15

turagittechturagittech

1213

answered Dec 7 '14 at 22:15

turagittechturagittech

1213