Re: secure automated access (was Re: Backing up all databases)
Randall Perry wrote: Mysterious. I assume `mysql -p` prompts you for root's password and works as expected, since without -p it says you're not using a password, rather than saying there's something wrong with the password. Yes, that works. When you tried the defaults-file option, did you use the full path? That is, mysql --defaults-file=/private/var/root/.my.cnf Yes. My best guess at this point is that mysql is not finding .my.cnf. Perhaps there's a problem with root's $HOME environment variable? For example, I'm using Mac OS X 10.2.8. Root's $HOME says /var/root, but as /var is a symlink to /private/var, the real home directory is /private/var/root. That's fine, as long as the symlink is there. Without the symlink, I'd get the same thing as you from mysql (and a lot of other stuff wouldn't work). cat $HOME/.my.cnf [client] password=rootpassword /usr/local/mysql/bin/mysql --defaults-file=$HOME/.my.cnf ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Oh. That changes things. The error says Using password: YES. (You told me it said Using password: NO before). That would indicate that mysql did read your .my.cnf file, but doesn't believe it contains the correct password. Double-check you don't have a typo in there. Another possibility might be a special character such as a or # in the password. You can also run mysql --print-defaults to see what mysql thinks you are using. If this is not the case, using `cat $HOME/.my.cnf` should give the same result you got using the full path. Yes You could also simply `echo $HOME` to see if it's set correctly. echo $HOME /var/root The only other thing I can imagine is that mysql is somehow set to ignore the defaults files. Did you build from source or install one of the packages? Which version mysql? Which OS? Mac OS 10.2.3. Maybe Panther has an issue with the build? 10.2.3 is Jaguar, not Panther. Did you mean 10.3.x? As I said, I'm running mysql 4.0.16 on Mac OS 10.2.8, and this works for me. I suppose an issue with Panther is possible, though I'd be surprised if something simple like reading .my.cnf were where it showed up. /usr/local/mysql/bin/mysql -V /usr/local/mysql/bin/mysql Ver 12.21 Distrib 4.0.14, for apple-darwin6.4 (powerpc) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
On Monday, November 17, 2003, at 02:58 AM, Michael Stassen wrote: cat $HOME/.my.cnf [client] password=rootpassword /usr/local/mysql/bin/mysql --defaults-file=$HOME/.my.cnf ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Oh. That changes things. The error says Using password: YES. (You told me it said Using password: NO before). That would indicate that mysql did read your .my.cnf file, but doesn't believe it contains the correct password. Double-check you don't have a typo in there. Another possibility might be a special character such as a or # in the password. There's the problem. Your original advice was to include: In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password This is incorrect, unless the double quotes are part of the password. Removing them so that the entry reads: [client] password=mysql_root_password will correct the problem. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
Mac OS 10.2.3. Maybe Panther has an issue with the build? 10.2.3 is Jaguar, not Panther. Did you mean 10.3.x? As I said, I'm running mysql 4.0.16 on Mac OS 10.2.8, and this works for me. I suppose an issue with Panther is possible, though I'd be surprised if something simple like reading .my.cnf were where it showed up. Sorry, it's 10.3. See my next post for the solution. -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password This is incorrect, unless the double quotes are part of the password. Removing them so that the entry reads: [client] password=mysql_root_password will correct the problem. That was it. Thanks everyone for the help. -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
Ed Leafe wrote: On Monday, November 17, 2003, at 02:58 AM, Michael Stassen wrote: cat $HOME/.my.cnf [client] password=rootpassword /usr/local/mysql/bin/mysql --defaults-file=$HOME/.my.cnf ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Oh. That changes things. The error says Using password: YES. (You told me it said Using password: NO before). That would indicate that mysql did read your .my.cnf file, but doesn't believe it contains the correct password. Double-check you don't have a typo in there. Another possibility might be a special character such as a or # in the password. There's the problem. Your original advice was to include: In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password This is incorrect, unless the double quotes are part of the password. Removing them so that the entry reads: [client] password=mysql_root_password will correct the problem. Randall Perry wrote: That was it. Thanks everyone for the help. I'm glad this worked for you, but now I'm confused. The manual clearly indicates the use of quotes around the password. See http://www.mysql.com/doc/en/Option_files.html. The typical user option file starts with [client] # The following password will be sent to all standard MySQL clients password=my_password On the same page it also says: option=value This is equivalent to --option=value on the command-line. Please note that you must quote an argument using double quotes, if the argument for an option contains a comment character. (Comment characters are # and ;) My reading of this is that quotes, though not always required, should always work. Furthermore, quotes are required in certain circumstances (special characters in password). This is how it works for me. I have quotes around my password in .my.cnf, and it works. (No, my password doesn't contain quotes, though I've tried a password that does contain a quote. It didn't make any difference.) I can remove the quotes and it still works, so long as my password doesn't contain a comment character. On the other hand, Randall's problem was fixed by removing the quotes. Perhaps a certain character (or characters) in a password make quotes not work? If so, the manual should be fixed to reflect this. Ed, you seem certain that quotes are incorrect, contrary to the manual. Perhaps you could elaborate? Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
On Monday, November 17, 2003, at 11:51 AM, Michael Stassen wrote: I'm glad this worked for you, but now I'm confused. The manual clearly indicates the use of quotes around the password. See http://www.mysql.com/doc/en/Option_files.html. The typical user option file starts with [client] # The following password will be sent to all standard MySQL clients password=my_password On the same page it also says: option=value This is equivalent to --option=value on the command-line. Please note that you must quote an argument using double quotes, if the argument for an option contains a comment character. (Comment characters are # and ;) My reading of this is that quotes, though not always required, should always work. Furthermore, quotes are required in certain circumstances (special characters in password). This is how it works for me. I have quotes around my password in .my.cnf, and it works. (No, my password doesn't contain quotes, though I've tried a password that does contain a quote. It didn't make any difference.) I can remove the quotes and it still works, so long as my password doesn't contain a comment character. On the other hand, Randall's problem was fixed by removing the quotes. Perhaps a certain character (or characters) in a password make quotes not work? If so, the manual should be fixed to reflect this. Ed, you seem certain that quotes are incorrect, contrary to the manual.Perhaps you could elaborate? I remember first trying this with quotes as indicated in the manual, and it didn't work. On a hunch I removed the quotes, thinking that it would then resemble the command-line format better. This worked. I left it that way, figuring that the manual was worded incorrectly; i.e., that double-quotes were only to be used when the password contained comment characters. I'm using 4.1.0-alpha running on RH *, if that makes any difference. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
Mysterious. I assume `mysql -p` prompts you for root's password and works as expected, since without -p it says you're not using a password, rather than saying there's something wrong with the password. Yes, that works. When you tried the defaults-file option, did you use the full path? That is, mysql --defaults-file=/private/var/root/.my.cnf Yes. My best guess at this point is that mysql is not finding .my.cnf. Perhaps there's a problem with root's $HOME environment variable? For example, I'm using Mac OS X 10.2.8. Root's $HOME says /var/root, but as /var is a symlink to /private/var, the real home directory is /private/var/root. That's fine, as long as the symlink is there. Without the symlink, I'd get the same thing as you from mysql (and a lot of other stuff wouldn't work). cat $HOME/.my.cnf [client] password=rootpassword /usr/local/mysql/bin/mysql --defaults-file=$HOME/.my.cnf ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) If this is not the case, using `cat $HOME/.my.cnf` should give the same result you got using the full path. Yes You could also simply `echo $HOME` to see if it's set correctly. echo $HOME /var/root The only other thing I can imagine is that mysql is somehow set to ignore the defaults files. Did you build from source or install one of the packages? Which version mysql? Which OS? Mac OS 10.2.3. Maybe Panther has an issue with the build? /usr/local/mysql/bin/mysql -V /usr/local/mysql/bin/mysql Ver 12.21 Distrib 4.0.14, for apple-darwin6.4 (powerpc) Randall Perry wrote: on 11/13/03 3:57 PM, Michael Stassen at [EMAIL PROTECTED] wrote: The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Permissions are correct. Here's a cat of the file with password changed: # cat /private/var/root/.my.cnf [client] password=rootpassword Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? Yes, that's the error. If you don't get in, try mysql --defaults-file=/path/to/.my.cnf That also doesn't work. If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Doesn't work at all. Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? If you don't get in, try mysql --defaults-file=/path/to/.my.cnf If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
on 11/13/03 3:57 PM, Michael Stassen at [EMAIL PROTECTED] wrote: The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Permissions are correct. Here's a cat of the file with password changed: # cat /private/var/root/.my.cnf [client] password=rootpassword Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? Yes, that's the error. If you don't get in, try mysql --defaults-file=/path/to/.my.cnf That also doesn't work. If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Doesn't work at all. Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
Mysterious. I assume `mysql -p` prompts you for root's password and works as expected, since without -p it says you're not using a password, rather than saying there's something wrong with the password. When you tried the defaults-file option, did you use the full path? That is, mysql --defaults-file=/private/var/root/.my.cnf My best guess at this point is that mysql is not finding .my.cnf. Perhaps there's a problem with root's $HOME environment variable? For example, I'm using Mac OS X 10.2.8. Root's $HOME says /var/root, but as /var is a symlink to /private/var, the real home directory is /private/var/root. That's fine, as long as the symlink is there. Without the symlink, I'd get the same thing as you from mysql (and a lot of other stuff wouldn't work). If this is not the case, using `cat $HOME/.my.cnf` should give the same result you got using the full path. You could also simply `echo $HOME` to see if it's set correctly. The only other thing I can imagine is that mysql is somehow set to ignore the defaults files. Did you build from source or install one of the packages? Which version mysql? Which OS? Randall Perry wrote: on 11/13/03 3:57 PM, Michael Stassen at [EMAIL PROTECTED] wrote: The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Permissions are correct. Here's a cat of the file with password changed: # cat /private/var/root/.my.cnf [client] password=rootpassword Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? Yes, that's the error. If you don't get in, try mysql --defaults-file=/path/to/.my.cnf That also doesn't work. If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Doesn't work at all. Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up all databases
Ok, I was confused about the password thing. It works now that I'm passing root's password in the command: /usr/local/mysql/bin/mysqldump --opt --all-databases -prootpassword /usr/local/mysql/data/mysqldump You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up all databases
Be aware that you password is visible (unix anyway with a ps -ef..) when you pass the password like -ppassword. Usually you can enter the password later if you use usr/local/mysql/bin/mysqldump --opt --all-databases -p which avoids the password being visible.. Cormac. On 12-Nov-2003 Randall Perry wrote: Ok, I was confused about the password thing. It works now that I'm passing root's password in the command: /usr/local/mysql/bin/mysqldump --opt --all-databases -prootpassword /usr/local/mysql/data/mysqldump You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- E-Mail: Cormac Tiernan [EMAIL PROTECTED] Date: 12-Nov-2003 Time: 15:01:53 This message was sent by XFMail -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up all databases
Well, this command is running in a cron job -- so that's not an option. Be aware that you password is visible (unix anyway with a ps -ef..) when you pass the password like -ppassword. Usually you can enter the password later if you use usr/local/mysql/bin/mysqldump --opt --all-databases -p which avoids the password being visible.. Cormac. On 12-Nov-2003 Randall Perry wrote: Ok, I was confused about the password thing. It works now that I'm passing root's password in the command: /usr/local/mysql/bin/mysqldump --opt --all-databases -prootpassword /usr/local/mysql/data/mysqldump You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- E-Mail: Cormac Tiernan [EMAIL PROTECTED] Date: 12-Nov-2003 Time: 15:01:53 This message was sent by XFMail -- -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up all databases
you could put a shell script as the actual cron job, and make the file only read-able by root, using an environment variable as the password passed (defined in shell script file), so that way even if someone 'sniffs' the process via 'ps -ef' they don't see the actual password (if they happen to catch the setting of the env var that's another story, but _much_ less likely) -Original Message- From: Randall Perry [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 9:10 AM To: Cormac Tiernan Cc: [EMAIL PROTECTED] Subject: Re: Backing up all databases Well, this command is running in a cron job -- so that's not an option. Be aware that you password is visible (unix anyway with a ps -ef..) when you pass the password like -ppassword. Usually you can enter the password later if you use usr/local/mysql/bin/mysqldump --opt --all-databases -p which avoids the password being visible.. Cormac. On 12-Nov-2003 Randall Perry wrote: Ok, I was confused about the password thing. It works now that I'm passing root's password in the command: /usr/local/mysql/bin/mysqldump --opt --all-databases -prootpassword /usr/local/mysql/data/mysqldump You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- E-Mail: Cormac Tiernan [EMAIL PROTECTED] Date: 12-Nov-2003 Time: 15:01:53 This message was sent by XFMail -- -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan, et al -- ...and then Dan Greene said... % % you could put a shell script as the actual cron job, and make the file only read-able by root, using an environment variable as the password passed (defined in shell script file), so that way even if someone 'sniffs' the process via 'ps -ef' they don't see the actual password (if they happen to catch the setting of the env var that's another story, but _much_ less likely) What, you've never used 'ps aguxwwe' before? Heh. The closest I might come to a better answer is the same sort of scripted setup but to redirect mysql from a file (or a heredoc) containing the password so that it doesn't show up anywhere in ps. But you still have the password right there in the file; that sure sucks! This actually brings up a different question: how can one reliably and securely and yet without human interaction run database queries? I've wondered this for a while, and I think I brought it up on this list some time ago, and there was some talk of X.500 certificates, but I don't recall anything beyond that (including any sort of satisfactory answer). Those of you who store your passwords in the script file for all to see, how do you keep all from seeing them? And those of you who don't, what are you doing to stay secure? And those of you who have a different approach, what is it? TIA HAND :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE/skVoGb7uCXufRwARArBEAKDH8Q5XyrR5yDUioU/sn/gambF/ogCg0rhk UlH37CUM00xzAv0sT6iX3Kk= =L1qa -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up all databases
Then put it in root's .my.cnf file. Randall Perry wrote: Well, this command is running in a cron job -- so that's not an option. Be aware that you password is visible (unix anyway with a ps -ef..) when you pass the password like -ppassword. Usually you can enter the password later if you use usr/local/mysql/bin/mysqldump --opt --all-databases -p which avoids the password being visible.. Cormac. On 12-Nov-2003 Randall Perry wrote: Ok, I was confused about the password thing. It works now that I'm passing root's password in the command: /usr/local/mysql/bin/mysqldump --opt --all-databases -prootpassword /usr/local/mysql/data/mysqldump You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- E-Mail: Cormac Tiernan [EMAIL PROTECTED] Date: 12-Nov-2003 Time: 15:01:53 This message was sent by XFMail -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael David T-G wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Dan, et al -- ...and then Dan Greene said... % % you could put a shell script as the actual cron job, and make the % file only read-able by root, using an environment variable as the % password passed (defined in shell script file), so that way even if % someone 'sniffs' the process via 'ps -ef' they don't see the actual % password (if they happen to catch the setting of the env var that's % another story, but _much_ less likely) What, you've never used 'ps aguxwwe' before? Heh. The closest I might come to a better answer is the same sort of scripted setup but to redirect mysql from a file (or a heredoc) containing the password so that it doesn't show up anywhere in ps. But you still have the password right there in the file; that sure sucks! This actually brings up a different question: how can one reliably and securely and yet without human interaction run database queries? I've wondered this for a while, and I think I brought it up on this list some time ago, and there was some talk of X.500 certificates, but I don't recall anything beyond that (including any sort of satisfactory answer). Those of you who store your passwords in the script file for all to see, how do you keep all from seeing them? And those of you who don't, what are you doing to stay secure? And those of you who have a different approach, what is it? TIA HAND :-D - -- David T-G * There is too much animal courage in -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
That sounds like the solution. Thanks for your help, everyone. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Backing up all databases
Look at GRANT on mysql.com GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFITED BY ''; - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Randall Perry [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 3:05 PM --To: [EMAIL PROTECTED] --Subject: Backing up all databases -- --Am testing this command to backup databases: -- --/usr/local/mysql/bin/mysqldump --opt --all-databases --/usr/local/mysql/data/mysqldump -- --Am running as root, but it only backs up databases root has access too. -- --What's the best way to handle this -- give root full perms on all dbs? -- -- --Randall Perry --sysTame -- --Xserve Web Hosting/Co-location --Website Development/Promotion --Mac Consulting/Sales -- --http://www.systame.com/ -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up all databases
You're joking, right? (Perhaps you thought the original question was a joke, too, as root normally has access to all dbs?) As I understand the manual http://www.mysql.com/doc/en/GRANT.html, that will give root access to every db, from every host except localhost, with no password! I can't imagine that's a good idea. And even so, I don't think this will help, as he's connecting from localhost. If we take the question at face value, it appears he has some dbs that root can't access. (I've never tried it, but I supppose it's possible to revoke root's access to a particular db.) If we assume [EMAIL PROTECTED] has a password we don't want to change, the correct command would be GRANT ALL ON *.* to [EMAIL PROTECTED]; If he wants to change root's password at the same time, he would need to add the IDENTIFIED BY clause GRANT ALL ON *.* to [EMAIL PROTECTED] IDENTIFIED BY 'newpassword'; Am I missing something? Michael Dathan Vance Pattishall wrote: Look at GRANT on mysql.com GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFITED BY ''; - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Randall Perry [mailto:[EMAIL PROTECTED] --Sent: Tuesday, November 11, 2003 3:05 PM --To: [EMAIL PROTECTED] --Subject: Backing up all databases -- --Am testing this command to backup databases: -- --/usr/local/mysql/bin/mysqldump --opt --all-databases --/usr/local/mysql/data/mysqldump -- --Am running as root, but it only backs up databases root has access too. -- --What's the best way to handle this -- give root full perms on all dbs? -- -- --Randall Perry --sysTame -- --Xserve Web Hosting/Co-location --Website Development/Promotion --Mac Consulting/Sales -- --http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
