There are two broad mechanisms that you can use. One is that some trusted program mediates the user's access to the database. The other is that the data itself is protected.
In the first mechanism, software (such as the database software itself) decides what the user can do with the data in the database. This is the most common form of database security.
The second mechanism is basically encrypting that data. One could argue that this is merely another form of having a program mediate access to the data, but I think it's still different. If data is properly encrypted, it doesn't matter who you give it to. Only the intended recipients can use the data. Note that I've brushed away a lot of potential problems with the word "properly." Algorithm choice, protocol design, key management, forward secrecy and other issues are difficult to solve.
Most systems, however, mediate the user's access to the data in the database. Typically, an access control system defines a set of operations that have to be controlled and how to identify the policy or rules that govern those operations.
For example, the obvious operations are reading and writing. Other operations include creating new data, deleting data and searching data (for example, you might be allowed to ask the database for my record, but not be able to scan across all records of people whose first name starts with J). The operations might be per record or per field. They might also be across types of records. For example, a user might be able to create a new inventory record, but not a new purchase request. Esoteric operations include things like "control" access -- the ability to change the permissions.
Once you have the operations defined, you decide how to manage them. The simplest way is per user. After that, you might add in groups for easy management. That way, for example, you could allow write access of purchase requests to the sales group and read access to the accounting group.
Is the data in the database owned? You might designate that purchase request records are owned by the sales group, but employee records are owned by the HR group.
Next, there has to be some mechanism to express the rules. One way is through access control lists. These are simply a list of small rules. For example, employee records can be read, written, deleted and added by the HR group. They can be read by anyone in the manager group. All other users can only read the "name" and "phone number" fields. Another way to express the rules are through the use of capabilities. A capability is simply a small object that describes a policy or a piece of a policy. We might create the capability to read the name and phone number fields, and hand that to the "employee" group. Then create the capability to read all of the record and hand it to the "manager" group, and the capability for full record access and give it to the "HR" group. Then Alice, who is a sales manager, has both the "employee" capabilities and the "manager" capabilities (as well as the "sales" capabilities that allow her to create purchase requests). Bob, who is in HR, has the "employee" and "HR" capabilities.
Capability-based systems are more modern than access-list based ones. In theory, they are easier to construct and manage. Of course, implementation is everything. A 20 year-old rule-based system may be much easier to use than Version One of a capability-based system.
Lastly, to tie in the encryption parts, there's no reason why the database can't encrypt portions of the database, so even if the rules say you can read it, it's just garbage unless you have the proper key.
I hope this gives you a flavor of all the things that a database constructor would take into account designing a system.
This was first published in February 2002