<p>I've recently had to dust off my Perl and shell script skills to help out some colleagues. The colleagues in question have been tasked with providing some reports from an internal application with a large Oracle database backend, and they simply don't have the skills to do this. While some might question whether I have those skills either (grin), apparently enough people think I do to mean I can't weasel out of it.</p>

<p>So to my question - in order to extract the reports from the database, my script is obviously having to connect and run queries. I haven't thus far managed to come up with a good solution for where to store the username and password for the database so it is currently being stored as plaintext in the script.</p>

<p>Is there a good solution for this that someone else has already written, perhaps as a CPAN module? Or is there something else that's better to do - like keep the user / password combo in a completely separate file that's hidden away somewhere else on the filesystem? Or should I be keeping them trivially encrypted to just avoid them being pulled out of my scripts with a system-wide grep?</p>

<p>Edit:
The Oracle database sits on an HP-UX server.<br>
The Application server (running the shell scripts) is Solaris.<br>
Setting the scripts to be owned by just me is a no-go, they have to be owned by a service account that multiple support personnel have access to.<br> 
The scripts are intended to be run as cron jobs.<br>
I'd love to go with public-key authentication, but am unaware of methods to make that work with Oracle - if there is such a method - enlighten me!</p>

[{'answer_id': 80630, 'author': 'moritz', 'author_id': 14132, 'author_profile': 'https://Stackoverflow.com/users/14132', 'pm_score': 1, 'selected': False, 'text': "<p>There is no good solution. You can obfuscate the passwords a bit, but you can't secure them.</p>\n\n<p>If you have control over your DB setup, you could try to connect by a named pipe (at least mysql supports that) without a password and let the OS handle the permissions.</p>\n\n<p>You could also store the credentials in a file with restrictive permissions.</p>\n"}, {'answer_id': 80637, 'author': 'Redbeard', 'author_id': 14977, 'author_profile': 'https://Stackoverflow.com/users/14977', 'pm_score': 4, 'selected': True, 'text': '<p>Best practice, IMHO, would be to NOT hold any passwords in a shell / Perl script. That is what public key authentication is for.</p>\n'}, {'answer_id': 80647, 'author': 'Paul Hargreaves', 'author_id': 5330, 'author_profile': 'https://Stackoverflow.com/users/5330', 'pm_score': 1, 'selected': False, 'text': '<p>Since you\'ve tagged ksh &amp; bash I\'m going to assume Linux.</p>\n\n<p>Most of the problem is that if the user can read the script and locate the method you used to hide / encrypt the file then they will also be able to do the same thing manually.</p>\n\n<p>A better way may be do the following:</p>\n\n<ol>\n<li>Make your script so it can only be seen/read/opened by you. chmod 700 it. Hardcode passwords away.</li>\n<li>Have a "launcher" script that is executable by the user and does a sudo .</li>\n</ol>\n\n<p>This way the user can see your launcher script, examine it to see it only has the single command line. They can run it and it works, but they don\'t have permissions to read the source for the script that is sudo\'d.</p>\n'}, {'answer_id': 80652, 'author': 'paxdiablo', 'author_id': 14860, 'author_profile': 'https://Stackoverflow.com/users/14860', 'pm_score': 0, 'selected': False, 'text': "<p>In UNIX, I always make these scripts setuid and store the user and password info in a file that's heavily protected (the entire directory tree is non-readable/searchable by regular users and the file itself is readable only by the owner of the script).</p>\n"}, {'answer_id': 80672, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>Keep them in a separate file, trivially encrypted, and make a separate user in the database with read only access to necessary tables. If you think the file has been read, then you can shut off access to just that user.</p>\n\n<p>If you want to get fancy, a SUID program could check the /proc//exe and cmdline (in Linux), and only then release the username.</p>\n'}, {'answer_id': 80695, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 3, 'selected': False, 'text': '<p>If the script is running remotely from the server.</p>\n\n<ol>\n<li>Make your reports views</li>\n<li>Give the user you are logging into ONLY access to select on the report views</li>\n<li>Just store the password.</li>\n</ol>\n\n<p>That way, all that the user can do, is select the data for its report. Even if someone happened to get the password, they would be limited as to what they could do with it.</p>\n'}, {'answer_id': 81416, 'author': 'Mark Nold', 'author_id': 4134, 'author_profile': 'https://Stackoverflow.com/users/4134', 'pm_score': 1, 'selected': False, 'text': '<p>I\'m not sure what version of Oracle you are running. On older version of Oracle (pre 9i Advanced Security) some DBA\'s would <code>CREATE USER OPS$SCOTT IDENTIFIED BY EXTERNALLY</code> and set <code>REMOTE_OS_AUTHENT</code> to true.</p>\n\n<p>This would mean that your remote sun machine could authenticate you as SCOTT and then your Oracle DB would accept that authentication.</p>\n\n<p><strong>This is a bad idea.</strong></p>\n\n<p>As you could image any Windows XP with a local user of SCOTT could then log into your DB without a password.</p>\n\n<p>Unfortunately it\'s the only option that i know of Oracle 9i DBs to not store username/passwords in your script or somewhere else accessible by the client machine.</p>\n\n<p>What ever your solution it\'s worthwhile having a look through Oracle\'s <a href="http://www.oracle.com/technology/pub/articles/project_lockdown/project-lockdown.pdf" rel="nofollow noreferrer">Project Lockdown</a> before committing.</p>\n'}, {'answer_id': 82097, 'author': 'Jonathan Bourke', 'author_id': 8361, 'author_profile': 'https://Stackoverflow.com/users/8361', 'pm_score': 0, 'selected': False, 'text': '<p>I have / had a similar issue with developers deploying SQL code to MSSQL (in fact to any database on that MSSQL server, so role had to be SysAdmin) using ANT from a Solaris server. Again I did not want to store the username and password in the ANT build.xml files so my solution, which I know is not ideal, is as follows:</p>\n\n<ol>\n<li>Store name / value pairs for username and password in a plain text file</li>\n<li>Encrypt file (on Solaris) and use a pass phrase only known to certain admins</li>\n<li>Leave only the encrypted file on the Solaris system</li>\n<li>ANT build.xml runs a sudo decrypt and prompts for pass phrase, which is entered by admin</li>\n<li>ANT sources decrypted file loading username and password as variables for the SQL string</li>\n<li>ANT immediately deleted the plaintext file</li>\n<li>ANT deploys code and exits</li>\n</ol>\n\n<p>This all happens in a matter of seconds, and the sql username and password is never visibly accessible on the server. As the code is deployed by allowed admins in production, the developers never need to include it in their code.</p>\n\n<p>I am sure it could be better, but...</p>\n\n<p>JB </p>\n'}, {'answer_id': 82860, 'author': 'Barbie', 'author_id': 12647, 'author_profile': 'https://Stackoverflow.com/users/12647', 'pm_score': 2, 'selected': False, 'text': '<p>Personally I hold passwords in configuration files which are then distributed independently of the application, and can be changed to the specific machine/environment. In shell scripts you can source these within the main script.</p>\n\n<p>However, in Perl there are a variety of approaches. You may wish to investigate <a href="http://search.cpan.org/perldoc?Getopt::Long" rel="nofollow noreferrer">Getopt::Long</a> for command line options (and additionally <a href="http://search.cpan.org/perldoc?Getopt::ArgvFile" rel="nofollow noreferrer">Getopt::ArgvFile</a> to store those in a simple configuration file), or look at something like <a href="http://search.cpan.org/perldoc?Config::IniFiles" rel="nofollow noreferrer">Config::IniFiles</a> for something with a little more power behind it. These are the two types I generally use, but there are other configuration file modules available.</p>\n'}, {'answer_id': 84984, 'author': 'Chris Madden', 'author_id': 16331, 'author_profile': 'https://Stackoverflow.com/users/16331', 'pm_score': 1, 'selected': False, 'text': '<p>For storing passwords you could do a two step encryption routine, first with a hardcoded key in your script itself, and optionally a 2nd time with a key stored in a file (which is set using file permissons to have restricted access). </p>\n\n<p>In a given situation you can then either use a key file (+ key from script), or if the situation requirements aren\'t that great he can just use the encyrption using the key is hardcoded in the script.  In both cases the password would be encrypted in the config file.  </p>\n\n<p>There is no perfect solution because somehow you have to be able to decrypt and obtain the cleartext password...and if you can do it someone else can too if they have the right info.  </p>\n\n<p>Especially in the situation where we give them a perl script (vs. an exe) they can easily see how you do the encryption (and the hardcoded key)...which is why you should allow the option to use a keyfile (that can be protected by filesystem permissions) as well.</p>\n\n<p>Some practical examples for how to implement is <a href="http://www.perl.com/pub/a/2001/07/10/crypto.html" rel="nofollow noreferrer" title="here.">here</a></p>\n'}, {'answer_id': 989158, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<p>It\'s a shame I never saw this thread before -- it looks very interesting. I\'ll add my two cents for anyone coming upon the thread in the future.</p>\n\n<p>I\'d recommend using OS authentication on the db server itself -- REMOTE_OS_AUTHENT is still FALSE.</p>\n\n<p>If you\'re invoking the script from another machine, setup a phrase-less SSH key and use SSH to get there. You can then pipe back the SQL results to the calling machine and it can process this information further.</p>\n\n<p>Doing this avoids having to code a password anywhere. Of course, if a malicious administrator were to hijack the phrase-less key and use it, he or she could also access the user account on the DB host and could then do any operations the OS authenticated DB user could. To mitigate this you could reduce the database permissions for that OS user to the bare minimum -- let\'s say "read only".</p>\n\n<p>Ingo</p>\n'}, {'answer_id': 1124535, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': '<blockquote>\n  <p>On windows create a Folder and a File within it containing the passwords in clear text. \n  Set the user who would run the scheduled job(script or batch) as the only person with read/write access to this folder and file. (remove even administrator). \n  To all other scripts, add code to read the clear text password from this restricted file.</p>\n</blockquote>\n\n<p>This should suffice for few.</p>\n\n<p>Keywords: Password HardCoding</p>\n'}, {'answer_id': 47402279, 'author': 'Kexin Z', 'author_id': 4677830, 'author_profile': 'https://Stackoverflow.com/users/4677830', 'pm_score': 0, 'selected': False, 'text': '<p>There are commercial or more advance solutions such as cyberark AIM can do it better, but doing it for free and out of box, I have been piggy back the SSH public/private key because for one, SSH key pairs most likely already created conform the security policy; secondly, SSH key pairs are already have a set of standard protocol to protect the keys by the file permission, continuous system hardening (like tripwire), or key rotation.</p>\n\n<p>This is how I did it:</p>\n\n<ol>\n<li><p>Generate the ssh key pairs if not yet. The key pairs and directory will be protected by default system protocol/permission.\nssh-keygen –t rsa –b 2048</p></li>\n<li><p>use the ssh public key to encrypt the string and stored in same .ssh directory\n$ echo "secretword" | openssl rsautl -encrypt -inkey ~/.ssh/id_rsa.pub -pubin -out ~/.ssh/secret.dat</p></li>\n<li><p>use ssh private key to decrypt the key, and pass the parameters to scripts/AP in the realtime. The script/programe to include the line to decrypt in realtime:\nstring=<code>openssl rsautl -decrypt -inkey ~/.ssh/id_rsa -in ~/.ssh/secret.dat</code></p></li>\n</ol>\n\n<p>PS - I have been experimenting CYBERARK AIM agentless solution. it\'s sort of pain requires significant changes/API changes for the API/script. will keep you posted how that goes. </p>\n'}]
['https://Stackoverflow.com/questions/80612', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/6756/']