http://www.perlmonks.org?node_id=251107

outcast has asked for the wisdom of the Perl Monks concerning the following question:

Is there more secure way of using DBI than putting the password in plain text in script?

Replies are listed 'Best First'.
Re: Secure ways to use DBI?
by dws (Chancellor) on Apr 17, 2003 at 02:48 UTC
    Is there more secure way of using DBI than putting the password in plain text in script?

    There are several, depending on your configuration (e.g., your database). You can

    • Read the usename and password out of a file that's not accessible to the web server. This protects against web server exploits that might make the script visible. Such exploits were a problem several years back, and now affect only old, unpatched servers.

    • Configure your database server to only accept requests that come from specific IP addresses (e.g., 127.0.0.1). This limits the damage that might happen if someone hacked your script remotely, but doesn't help if they hack the box.

    • Defer all direct database access to a middle-tier process that prompts for a user at startup. Some high-security shops actually do this, which makes life a nuisance for support staff when the power fails.

    • Defer all direct database access to a middle-tier process that lives on a separate box, and which speaks a narrow, application-specific protocol. This limits the damage if someone hacks your web server box.

    A while back I gave some prior advice to a similar question here. You might find other info in that thread useful.

      In the name of security, I am going to disagree with most of your suggestions. One of the main failings of software developers is that they have no clue what security is. I include myself in this category, although I have personally spent quite a bit of time trying to work on this failing.

      One would assume that DBI was being used from a CGI or mod_perl. Storing the password in a file in a location not directly accessible via HTTP isn't actually better than storing the password in the CGI itself, as the CGI is executed, and is not available in plain text. If anything, moving the password to an alternative location makes it more difficult to control security since there are more paths that have to be accessible that need to be properly maintained.

      Better than configuring your database server to only accept requests from a specific IP address may be only allowing requests from a UNIX local socket. This protects one from the hopefully small windows where port rules or routing rules may not be 100% bullet-proof.

      Directing all database access to a middle-tier process that prompts for a user provides obfuscation, but does not really improve security. Anybody could connect directly to the middle-tier process. Although obfuscation is not to be under-estimated as a method of reducing the probability of an attack, in this case, it comes at the direct cost of efficiency and maintainability of the application, and is not reasonable. In the case where the middle-tier process lives on a separate box, this may be even worse, as it guarantees that the middle-tier process is waiting on an open socket. Unless the middle-tier process is using identd or some other mechanism of testing the privileges on the 'trusted' web server, *any* user from the web server could connect to this off-server process.

      Eventually the only practical solution comes down to controlling access to the web account, and making the CGI read-only to the web account. Also, the SQL server should be configured to limit the abilities of the account hard-coded into the CGI to only those queries specifically necessary. For example, if possible, INSERT might be allowed, but UPDATE or DELETE might not.

        Unless the middle-tier process is using identd or some other mechanism of testing the privileges on the 'trusted' web server, *any* user from the web server could connect to this off-server process.

        Typically there's a firewall between the web server and the middle tier. And speaking an application-specific protocol across this boundary does improve security. The hacker is constrained to used the application-specific protocol, and is blocked from using raw SQL, thus limiting the amount of probing they can do.

        Eventually the only practical solution comes down to controlling access to the web account, and making the CGI read-only to the web account.

        And if you lose the box to some other exploit, the hacker gets a valid database username and login. Even if the pair that the CGI uses is for an INSERT-only account, if the database is on the same box, it's toast.

        I may be misunderstanding your point about storing the password in a file "makes it more difficult to control security".

        We have several hundred pages of CGI that have to access our mySQL database and in our own attempt to make the system more secure (this system is not on a public web, but only available to our employees..but still, it doesn't hurt to be careful) as well as easier to code and manage, we store the mySQL username/password info in a seperate file .

        Here is the code for the file that sets up the mySQL connection

        package Data_config; use Exporter; @ISA = qw(Exporter); @EXPORT = qw( $DBHOST $DBPORT $DBDRIVER $DATABASE $USERNAME $PASSWORD ); ## Database configuration ## our $DBHOST = "localhost"; our $DBPORT = "3306"; our $DBDRIVER = "mysql"; our $DATABASE = "database"; our $USERNAME = "database"; our $PASSWORD = "password";

        We can then make our mySQL setups in each of our CGI scripts with
        ## Create a database handle ## my $DSN = "DBI:$DBDRIVER:database=$DATABASE:host=$DBHOST:port=$DBPORT" +; my $DBH = DBI->connect($DSN, $USERNAME, $PASSWORD, { RaiseError => 1, PrintError => 1 });

        this gives us not only the security of not having the mySQL username/passwords in the CGI but also makes it very easy to change the username/passwords on the server since they are stored in one location.

        ... making the CGI read-only to the web account.

        How does this help if I can read the CGI and it has an embedded password?

        the CGI is executed, and is not available in plain text.
        Yes - so long as the configuration is correct and doesn't have to be touched. But these things do happen, and it would not exactly be a housewarming gift to have the username/password visible in plaintext to the entire world after a server move. In contrast, it is slightly more likely that an external file containing the credentials will initially have too restrictive rather than too permissive access privileges.
        the SQL server should be configured to limit the abilities of the account hard-coded into the CGI
        Ah, but that's what it makes sense to use a middle-tier for: you gain much more finegrained control over the submitted queries than the access control facilities of the database server typically allow. DBI::ProxyServer f.ex enables you to only make some predetermined queries against the proxied database available. This at least severly limits, if not outright eradicates a miscreant's ability to gather information to prepare an attack with.

        Makeshifts last the longest.

      Defer all direct database access to a middle-tier process that prompts for a user at startup.
      I'm in the process of building something like that at the moment. Essentially it's an internal web server that accepts connections from hosts on the local net. This process connects to the Sybase database with a specific user/password that is only allowed to execute stored procedures. Each stored procedure checks and that the remote user/host that wants to execute it is authorized to do so.

      It's probably not completely fool-proof, but it greatly limits the damages that any compromise of the front-end web servers could cause.

      Michael

      Dear Saint dws,

      I am but a lowly monk and I am wondering if my particular
      solution to this problem resembles any of those you have outlined.

      Whenever I need to access a password in a script I pull the password
      out of the database itself. This of course assumes the database
      is itself secure. I use ssh (F-secure) to call a (bash/SQL*plus) script
      that reside on the database server.
      updated
      This script returns the passwords my scripts needs to use.

      I guess what I am asking is, does my script constitute a middle-tier process
      you mentioned in your post?

      Please bestow some wisdom upon me :)
        I use ssh (F-secure) to call a (bash/SQL*plus) script that reside on the database server.

        The question you need to ask yourself is this: "If some wiley hax0r where to gain control of the web server, how difficult would it be for them to get my database password?"

        If they see before them a script that uses ssh, can they then use that script to get the password? If so, you haven't gained yourself much.

        Now if this is all done from a middle tier that the wiley hax0r can't get to, that's another matter.

        Whether what you describe is a "middle-tier process" I don't know. Perhaps.

        Update: What this scheme seems to protect against is losing the password to a sniffer. That works only if you're then using some secure, database-dependent login mechanism, or are using ssh-tunneling to talk to the database.

Re: Secure ways to use DBI?
by zengargoyle (Deacon) on Apr 17, 2003 at 03:21 UTC

    use a better authentication method.

    i'm using PostgreSQL with KerberosV5 authentication.

    package KrbKey; use base 'Class::DBI'; __PACKAGE__->set_db('Main', 'dbi:Pg:dbname=krbkey;host=dbserver.sub.dom' # no user/password ); __PACKAGE__->table('krpass'); __PACKAGE__->columns(All => qw( passkey passval )); 1; #!/usr/bin/perl use strict; use warnings; use KrbKey; sub lookup { my $key = shift; my $pw = KrbKey->retrieve($key); return $pw->passval; } if (@ARGV) { my @pw = map {lookup($_)} @ARGV; print join $/, @pw, ''; } else { print join $/, map({$_->id} KrbKey->retrieve_all), ''; }

    without a valid principal...

    $ getpw Failure while doing '' with '_filter_retrieve_all in KrbKey' Ima::DBI->connect(dbname=krbkey;host=dbserver.sub.dom) failed: Kerbero +s 5 authentication failed at /opt/network/bin/getpw line 17 at /opt/network/bin/getpw line 17

    and with...

    $ kinit Password for me@SUB.DOM: $ getpw cisco.console cisco.enable snmp.ro snmp.rw

    there's also the possibility of using SSL and Certificates for authentication (i think even MySQL can do SSL auth)

Re: Secure ways to use DBI?
by The Mad Hatter (Priest) on Apr 17, 2003 at 02:48 UTC
    If you're using MySQL, take a look at .my.cnf and the other options file (described at the link). I think you can put the password in there and then chmod 600 it.
      And while we are on the subject, here is the corresponding code to use that config file:
      my $dbh = DBI->connect( 'DBI:mysql:database:host:mysql_read_default_file=~/.my.cnf', undef,undef, {RaiseError=>1} );
      Big thanks to gmax for introducing me to the MySQL config file (and that code) a while back. :)

      jeffa

      L-LL-L--L-LL-L--L-LL-L--
      -R--R-RR-R--R-RR-R--R-RR
      B--B--B--B--B--B--B--B--
      H---H---H---H---H---H---
      (the triplet paradiddle with high-hat)
      
Re: Secure ways to use DBI?
by pfaut (Priest) on Apr 17, 2003 at 02:50 UTC

    Many backends or the libraries they interface to will allow specifying connection information in environment variables. Another alternative would be to ask the user. You could also read it from a file, possibly encrypted, but that could easily be subverted by running the code with the debugger.

    90% of every Perl application is already written.
    dragonchild
Re: Secure ways to use DBI?
by aquarium (Curate) on Apr 17, 2003 at 12:26 UTC
    it's a matter of how much time and money you want to spend. if you want to stay on the cheap side, consider kerberos, LDAP, SSL, default config files, etc. if you want top notch security you can setup one time passwords that are retrieved at web system start (activated by a keycard or retina scan) behind a DMZ. Seriously though, a database & web server running with least privileges (non-privileged users) and default database config files (.mycnf for mysql) will provide you with "good enough" security. If it's for a commercial venture, definitely stick the db box behind a DMZ and backup your database often. Chris
      (wow - I really like that kerberos key thing!)
      Another thing to consider is setting reasonable permissions on the user the CGI uses for database connections. If you only need to perform selects, don't give that user the ability to create and drop databases, among other things. This won't necessarily protect you from compromising your password, but it will help mitigate the damamge that can be done if someone gets in.
Re: Secure ways to use DBI?
by Desdinova (Friar) on Apr 17, 2003 at 20:47 UTC
    There have already been some great responses to this question, but taking it froma different angle, One thing I try to do is match the database user to the trust given to the outside web user. 95% of the people that access a dtabase in my apps are doing lookups. so I create the Database user that can only do lookups. that way if the password is given to the whole world they really can't do more than they can from the website.

    If I need acces to things like deltes etc. I will require a user id an password to be input from the site (https page) then the password is not needed to be stored in the script.
Re: Secure ways to use DBI?
by Anonymous Monk on Apr 17, 2003 at 17:08 UTC
    I've seen passwords and usernames stored in environment
    variables. So, unless your box gets cracked, no one 
    that can read the CGI code will be able to see the password.
    
    -bl0rf
    
Re: Secure ways to use DBI?
by outcast (Monk) on Apr 18, 2003 at 18:38 UTC
    I would like to say thanks for eveyone's help. I did not think I would spark a respone like this. Thank you everyone. There is alot of good info in here.