Actually, you have THREE problems that you have implied in your question.
- The title talks about data at rest.
- In the question, you talk about access control as well.
- In addition, you then also have a question of data in transit.
The question may have a different answer if you are already using a DB system and introducing encryption in an existing system. Many of the DB systems now support such security features (see below).
Access control and data in transit
Most DB systems support access control from the first day (it's almost a min requirement). However, when you say the such and such system needs to be able to read it, it's really an access control question.
Likewise, data in transit is also a question of the protocols used, many of which are supported by existing DB system(s). For example, SQL Server supports SSL for connections, as does MySQL. (Search for others, they might support it too.)
Encryption at rest
The third is encryption at rest, which solves the issue of if an unauthorized person or system were to get the actual DB file, what do they see. It also comes a related issue of key management, i.e. why can't whoever got your DB file not get the keys?
During the design, it would be prudent to assume that one day the key(s) could be compromised or stolen or, purely from a crypto agility point of view, you will have to change the algorithm and keys (e.g. whoever used DES had to eventually move to AES). Even though it can't be 0 cost, there has to be a path esp. if your DB is going to be a distributed one, to change either the algorithm or the key.
Many DBs now do provide encryption at rest along with some key management solutions. For example SQL Server has supported encryption since 2008. In addition, SQL server has published a key lifecycle management story too with apparently supports symmetric as well as asymmetric keys (via certificates). I believe SQL also supports full DB encryption vs selected fields via queries (such as in your case for SSN).
Likewise MySQL also supports encryption via query functions, which you could utilize for your SSN scenario. You can likewise other DB systems as well that might already support encryption and use those.
If you utilize a system that support built-in encryption, you are likely to avoid many pitfalls associated with doing it your own, as well as get a supported system.
CryptDB is a DB system developed at MIT which encrypts data at rest and also supports running queries over encrypted data. If you look at the page for the system, it lists organizations that are actually using it.
Writing own encryption logic
This is probably more time consuming and more challenging to get it right, but based on your question, it seems that you are contemplating this as an issue. If I were in a similar situation, I would definitely avoid it and go with one of the existing DB systems.
There are many issues. For example, when you encrypt data, the output is somewhat randomized so encrypting the same data with the same key will usually not result in the same cipher text. It might be a bit challenging and you may have to decrease entropy (e.g. by using the same IVs or salts) which might impact the security of your system. And with something as simple like as storing hashes (or even HMACs with a single key), if someone gets the database file(s), they can run brute force to recover the data within weeks, if not days. This is especially true of fields like SSN, unless you were to spend time and always require multiple fields for a query (e.g. SSN and DOB and first three letters of last name, or such combinations), and only store those as hashed but neither of these separately. This will increase entropy and make it harder for someone to find actual values were they to get your DB file.
Other than that, one has to figure out key lifecycle management issues.
EDIT: It's actually a common issue and I had once evaluated encrypting data, when I wrote the initial response, I did not include that here. I have since updated my response to include that, as well as clarify access control, secure connection and data at rest issues.