No, the benefit of salts does not depend on them being secret.
Salts primarily force an attacker to brute-force each stored hash individually, so that the hashes cannot be attacked all at once. Without salts, the attacker can choose a potential password, calculate its hash and then check whether any of the stored hashes match. If that's the case, they've found the password of the corresponding account(s) -- note there may be multiple matches if users have the same password. In contrast, salted hashes prevent an attacker from reusing their computed hashes. They have to choose a potential password, then choose one of the salts, compute the hash and compare the result with the salted hash from the database. In other words, if there are n salted hashes, the attacker has to perform n hash calculations to try a single password. Without salts, they would only have to do one calculation per guess.
Of course this only slows down an attacker significantly if the hash calculations are computationally expensive. You cannot use general-purpose hash algorithms like SHA-256, SHA-512 etc., because those are designed to be fast, allowing an attacker to calculate billions(!) of hashes per second on consumer graphics card. Salts only make sense in specialized hash algorithms that are purposely expensive like bcrypt or the newer Argon2 family.
There are some secondary benefits of salts:
- They prevent the use of precomputed lookup tables. Without salts, it's possible to calculate the hashes of different potential passwords prior to a concrete attack, store the results in a table and then use this table for quick lookups in attacks. You can actually google many unsalted hashes, because there's a good chance somebody has already found the corresponding password and put it on the Internet.
- They hide the fact that multiple accounts share the same password. However, if there are identical passwords, that usually implies the password itself is very simple and can be easily guessed (like a word from the dictionary), so salting doesn't help that much here.
None of those benefits require the salt to be secret. You can use a secret parameter -- often called a pepper -- in addition to salts, but that's an entirely different concept with a different purpose. A pepper forces an attacker to not only gain access to the database but also compromise the target application or server which keeps the pepper.
