Re: Re: Secure ways to use DBI?

[MarkM]

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.

[dws]

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.

[phydeauxarff]

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.

[Anonymous Monk]

... 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?

[crenz]

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.

[Aristotle]

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.

[MarkM]

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.