Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things
 
PerlMonks  

Re: Secure ways to use DBI?

by dws (Chancellor)
on Apr 17, 2003 at 02:48 UTC ( #251110=note: print w/replies, xml ) Need Help??


in reply to Secure ways to use DBI?

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.

Replies are listed 'Best First'.
Re: Re: Secure ways to use DBI?
by MarkM (Curate) on Apr 17, 2003 at 03:28 UTC

    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?

        He probably means that only the web account/your account can read/write/execute the file. All others have no rights. This can also mean your personal account on a multi-user machine: Google for cgiwrap and similar solutions.

      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.

        To some degree, the *only* thing being offered is obscurity. Eventually, a password ends up being stored *somewhere*. Whether it is in the CGI, or behind the scenes in a 'proxy server', the password *is* available.

        If somebody wants the password, they will eventually get it. I wish MySQL provided an authentication scheme based on some other sort of credentials than a password. The whole concept of a password is wrong when it comes to automated tasks.

        In any case, I am merely warning that some commonly accepted security techniques are not security techniques at all, but rather, sophisticated evasive attempts at misdirection. How much effort is it worth, when the mere sophistication involved guarantees that the programmers who will maintain the system in the future understand it less, and may completely unintentionally violate the scheme in such a way as to make the system more open after than before.

Re: Re: Secure ways to use DBI?
by mpeppler (Vicar) on Apr 17, 2003 at 15:37 UTC
    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

Re: Re: Secure ways to use DBI?
by LameNerd (Hermit) on Apr 17, 2003 at 17:11 UTC
    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.

        Ah I see.

        My script (an LWP Perl script) that calls the ssh to get a password from the DB,
        does not reside on the web server and is not used by a web server in anyway.

        But this script does need to use a password to connect to a https webpages that requires a user/password.
        The machine the script resides on (M1) is seperated from the DB server with a firewall (FW1) and
        seperated from the "outside world" with another firewall (FW2).

        I think this set up is pretty secure. Here's where I expose my ignorance of
        firewalls
        . FW1 only allows ssh and scp from M1 and FW2 only allows http and https to
        pass through.

        Does that make any sense?

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://251110]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (3)
As of 2021-01-16 01:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Notices?