If someone gets access to your sensitive data, you don't want them to see that data in clear text or in a usable format. You want them to see an encrypted mess like the following:
EnCt2a44c4574a6ad10161e52415457d42af23f940ca9a44c4574a6ad10161e5241545M9wv1Mc5gAlgxUoCVh0y8zVUnDE4g9vIwEmS
On the other hand, you want approved people to have access to the clear text data so that it's usable and they can do their job.
So that's the principle - the data should be visible in clear text for approved users and viewable encrypted for non-approved users.
As an additional consideration, we use encryption when we want to be able to encrypt and decrypt the data. If we only want to render the data unreadable, we use hashing which is a one-way cryptographic function. I'll mention password storage later.
So what do we see in practice, what does it mean and what's our stance from a security perspective?
1. Disk encryption
All the contents on the hard disk are encrypted. A password is used to decrypt the disk or the data can be decrypted in real time on access but essentially, during use of the system all data is available in the clear. The real value of this type of encryption is for media which contains sensitive or private data and is located in insecure environments.
Scenario 1: Disk encryption on a laptop. Your laptop is stolen and the criminal cannot get access to any sensitive data on the disk as it's encrypted using a strong algorithm and the key is also somehow protected on the disk so this is also inaccessible - perfect win!
Scenario 2: Disk encryption on a SAN disk array in a highly secure data centre. The primary control here is the physical security controls in the data centre - disk encryption in this scenario provides little additional security. If the SAN is in a physically shared environment or in unlocked cabinets, there is some additional physical security risk so you could consider locks and/or cages.
The main point here is that disk encryption means the data is encrypted at rest on the disk but when it's in memory it's in the clear. From a user perspective, whether an approved or unapproved user, if they can see the data it is always in clear text. In terms of whether or not disk encryption provides real security, it's worth considering the actual risks such as those in the scenarios above.
The contents of a file are encrypted. We can do this with programs like Microsoft Office, 7-zip and many others. When using file encryption, if we open the file (in a text editor for example), we just see unreadable data. In order to see the data in clear text or to open the file as an unencrypted file, we need a password or passphrase. The security is based around the password - if this is stored in the clear and is generally accessible, there's no real security provided by the file encryption.
Here we have real security provided through the use of encryption which requires restrictive role-based user access controls to be enforced to maintain security.
Here we have two varieties;
Transparent Data Encryption - this generally means the data is encrypted but it's never seen in unreadable format so the encryption is transparent. Anyone who can see the data sees the data in clear text.
Tablespace and column level encryption - generally, the data is encrypted at rest but decrypted on view and available in memory as clear text data. Anyone who can see the data sees the data in clear text.
Usually the application will receive a key from somewhere and use this key to encrypt data and write it to a database or retrieve encrypted data from a database, decrypt it and perform some processing.
The application needs to get the key from somewhere secure as otherwise the key is accessible to others. So usually the data encrypting key will be secured with a key encrypting key. We need to avoid having keys written into the application as the application could be reverse engineered. So we might have key components written to different files on different systems with different access controls and the DEK cryptogram stored in a database which can be decrypted once the application has XOR'd the key components.
Application level encryption can be great as it provides a lot of flexibility. Database users will only see encrypted data. The application can enforce access controls for who can and who cannot view clear text data. The granular control in this implementation can provide a lot of security but careful consideration needs to be given to the secure management of the encryption keys. Note that the application will need to have access to the key for encrypt/decrypt operations so the key is available in memory and will be available if someone dumps the memory.
A HSM is a device designed for performing secure cryptographic operations. Most of the key management procedures are quite manual but a HSM can perform all your cryptographic operations and usually includes crypto accelerators which will perform thousands of cryptographic operations per second. Using a HSM correctly and using it for encryption/decryption processes means your keys are only ever available in clear text on the HSM itself. If you retrieve the key from the HSM to perform cryptographic operations at the application level, the key is still subject to memory dump compromise.
Storing passwords with reversible encryption usually means they are being compared in clear text which is unnecessary or that they're available to decrypt by support staff or that they're available to transmit for confirmation if you forget. Due to numerous compromises of passwords in recent years, passwords are stored as hashes with a salt.
If my password is 1234 and your password is also 1234 and an entity stores both our passwords using the same hashing algorithm, the resultant hashes will be the same. This makes storage of passwords using only a hash vulnerable to rainbow table attacks. Adding a random salt means our same passwords will be different in storage. When I enter a password, my password is hashed with the salt and the result is compared to what is stored in the table against my username. That gives a little information on passwords, hashes and salts!
The above are just a few thoughts as every implementation is different. However, remember that doing encryption alone does not make something secure!