Dear All,

I am facing a strange issue with Linux users (non root). Let me explain this in a bit detail.

I have mysql 5.1.47 running and I need to give read only access so few users who can query mysql database and generate CSV on their home directory, and that user must have read/write access to that directory. For example I created a user 'testing' and directory is created like /home/testing. Now I have to do chmod 777 /home/testing in order to enable mysql to generate CSV in this directory. But issue is that any other user come to this directory (/home/testing) through its FTP login can delete any file. Whereas this directory is home directory of 'testing' user and only he/she must have full access and all other non root users must not able to delete any file in this directory. How to achieve this?

Thanking you in advance

No you don't. And chmodding something 777 is always lets-not-think-about-the-consequences bad advice: you could for instance let the user produces the CSV file to a temporary location ('mktemp'), chown it to a group the FTP user can access, then let the FTP user 'install' it with the required access rights.

Dear unSpawn,

Actually that doesn't fulfill my requirement. Let me try to explain in simple way. I have a production system where MySQL Database is running. I want to give read only access(through mysql read only user) to MySQL where a person can connect to mysql and generate CSVs from MySQL Queries somewhere. So I need to create OS user as well where user can generate CSVs using MySQL Query. Now problem is here, MySQL don't generate CSVs in directory where its not chmod 777 but when I do chmod 777 on user home directory mysql start generating CSVs but all other OS users (non root) also got full access to that directory. I only want to give write access to MySQL and that os User onky.

I hope it will be clear now and you can suggest me its solution as it is driving me crazy.

Do you understand what making the directory permissions 777 does? The permissions are set up according to owner/group/other with 3 bits for read,write, and execute; 777 allows everybody ALL of these abilities which is why everyone can access each others folders! MySQL does not require 777 access, it requires write (and possibly execute on directories) access.

A much safer approach would be to either create a GROUP (the middle permission) that allows MySQL access to that directory and to play both the MySQL system user and the desired user(s) in that group. Similarly, each user has a group associated with them. You could add MySQL to their group, though this is a little less secure.

Unspawn's suggestion is similar, in that it uses group permissions to solve the 777 issue and it addresses the mysql having write access to home folders by creating a common area for the creation of the CSV files where each user can transfer their data from. You could expand upon this to create subdirectories for each user where MySQL and ONLY THAT USER could access. This would achieve all of your stated goals while maximizing the security.

How about setting up something like phpMySQL? That would let them create CSV files directly on their own machines with no need for an OS user. You would have to make sure that phpMySQL was locked down tightly, but it might be a more secure approach than having 777 directories.

<edit>

Actually, why don't you open up MySQL for TCP/IP connections and let the people access it that way. The more I think about it the less I think you need to have people logged into the MySQL server to do their work. MySQL was designed to be accessed remotely, so you should think about doing that.
</edit>

Dear Noway2,

I tried the same but no luck. I make a group and added mysql user and other os user to that group. Now I change group of the directory to new one and tried to generate CSV through MySQL but it still gives me Error # 13. I would be very thankful if you can give me commands to implement that.

Thanks you very much for continued support.

Dear Hangdog42,

Thanks for your reply. I can't install PHPMySQL as it is not under my control.

People already accessing MySQL remotely but to generate CSV, MySQL generate it on the same server where it is installed. Thats why I need to create OS user so that MySQL generates CSV in user home directory and user can login through FTP and Picks the files and no other user can have access to that directory.

Post output of these commands:

cat /etc/group

root:x:0:root
bin:x:1:root,bin,daemon
daemon:x:2:root,bin,daemon
sys:x:3:root,bin,adm
adm:x:4:root,adm,daemon
tty:x:5:
disk:x:6:root
lp:x:7:daemon,lp
mem:x:8:
kmem:x:9:
wheel:x:10:root
mail:x:12:mail
news:x:13:news
uucp:x:14:uucp
man:x:15:
games:x:20:
gopher:x:30:
dip:x:40:
ftp:x:50:
lock:x:54:
nobody:x:99:
users:x:100:
utmp:x:22:
utempter:x:35:
rpc:x:32:
mailnull:x:47:
smmsp:x:51:
nscd:x:28:
floppy:x:19:
vcsa:x:69:
rpcuser:x:29:
nfsnobody:x:65534:
sshd:x:74:
screen:x:84:
pcap:x:77:
slocate:x:21:
ntp:x:38:
stapdev:x:101:
stapusr:x:102:
dbus:x:81:
haldaemon:x:68:
avahi:x:70:
xfs:x:43:
avahi-autoipd:x:103:
gdm:x:42:
sabayon:x:86:
advcomm:x:500:
mysql:x:104:
zaka:x:501:
zaeem:x:502:
csv:x:504:testing,mysql
testing:x:503:


# ls -ld /home/testing
drwx------ 4 testing csv 4096 Oct 30 10:18 /home/testing

Group csv has no access to this directory

Dear eSelix,

After doing chmod 0770 I am still unable to generate CSV file and error is mentioned below.

ERROR 1 (HY000): Can't create/write to file '/home/testing/books.csv' (Errcode: 13)

I believe that Error 13 is a permissions issue. It looks like the directory permissions should have been taken care of with the last step. Do you perchance have another security application like Apparmor blocking it?

Did you restart mysql and your other programs after you added mysql to the the csv group?

Thanks a lot guys. After rebooting MySQL issue is resolved. I really appreciate your help