In some circumstances, peppers can be helpful.
As a typical example, let's say you're building a web application. It consists of webapp code (running in some webapp framework, ASP.NET MVC, Pyramid on Python, doesn't matter) and a SQL Database for storage. The webapp and SQL DB run on different physical servers.
The most common attack against the database is a successful SQL Injection Attack. This kind of attack does not necessarily gain access to your webapp code, because the webapp runs on a different server & user-ID.
You need to store passwords securely in the database, and come up with something on the form of:
$hashed_password = hash( $salt . $password )
where $salt is stored in plaintext in the database, together with the $hashed_password representation and randomly chosen for each new or changed password.
The most important aspect of every password hashing scheme is that hash is a slow cryptographically secure hash function, see https://security.stackexchange.com/a/31846/10727 for more background knowledge.
The question is then, given that it is almost zero effort to add a constant value to the application code, and that the application code will typically not be compromised during an SQL Injection Attack, is the following then substantially better than the above?
$hashed_password = hash( $pepper . $salt . $password )
where $salt is stored in plaintext in the database, and $pepper is a constant stored in plaintext in the application code (or configuration if the code is used on multiple servers or the source is public).
Adding this $pepper is easy -- you're just creating a constant in your code, entering a large cryptographically secure random value (for example 32byte from /dev/urandom hex or base64 encoded) into it, and using that constant in the password hashing function. If you have existing users you need a migration strategy, for example rehash the password on the next login and store a version number of the password hashing strategy alongside the hash.
Answer:
Using the $pepper does add to the strength of the password hash if compromise of the database does not imply compromise of the application. Without knowledge of the pepper the passwords remain completely secure. Because of the password specific salt you even can't find out if two passwords in the database are the same or not.
The reason is that hash($pepper . $salt . $password) effectively build a pseudo random function with $pepper as key and $salt.$password as input (for sane hash candidates like PBKDF2 with SHA*, bcrypt or scrypt). Two of the guarantees of a pseudo random function are that you cannot deduce the input from the output under a secret key and neither the output from the input without the knowledge of the key. This sounds a lot like the one-way property of hash functions, but the difference lies in the fact that with low entropy values like passwords you can effectively enumerate all possible values and compute the images under the public hash function and thus find the value whose image matches the pre-image. With a pseudo random function you cannot do so without the key (i.e. without the pepper) as you can't even compute the image of a single value without the key.
The important role of the $salt in this setting comes into play if you have access to the database over a prolonged time and you can still normally work with the application from the outside. Without the $salt you could set the password of an account you control to a known value $passwordKnown and compare the hash to the password of an unknown password $passwordSecret. As hash($pepper . $passwordKnown)==hash($pepper . $passwordSecret) if and only if $passwordKnown==$passwordSecret you can compare an unknown password against any chosen value (as a technicality I assume collision resistance of the hash function). But with the salt you get hash($pepper . $salt1 . $passwordKnown)==hash($pepper . $salt2 . $passwordSecret) if and only if $salt1 . $passwordKnown == $salt2 . $passwordSecret and as $salt1 and $salt2 were randomly chosen for $passwordKnown and respectively $passwordSecret the salts will never be the same (assuming large enough random values like 256bit) and you can thus no longer compare password against each other.