There are 2 types of security systems/methods that you will find when developing or evaluating database software.  Good database software anticipates the need for both types.

1. Centralized Security - Also referred to as “Roles-based Security”, this common security structure allows “administrative users” to place other users into groups or “security roles”.  The permissions are granted to the role (not to the users directly).  Users are then assigned to the role and thereby obtain the necessary privileges to do their job.  This allows the administrator to assign a user to multiple roles based on their duties & responsibilities.  For example, your organization may have an accounting department which needs access to financial data that you don’t want other members of the organization viewing/editing.  In this case, an administrator may create an “Accounting Users” security role and assign that role to only the users who work in the accounting department.  I call this “centralized” security because the action of creating the roles and assigning users to those roles is typically done in a “central” location by an administrative user.

2. Distributed Security - Also referred to as “Record-level Security”, this not-so-common security structure allows users to secure individual data records.  As various users are viewing & editing data records (which they have rights to via centralized security), they may come across a particular record which needs to be made “private” due to its sensitive or confidential nature.  Users should be able to “lock down” these records as exceptions to the centralized security governance.  I call this “distributed” security because the action of securing the particular exception records is typically done by the end users as they work with the data and uncover or create information which deems the record sensitive or confidential.

As for the various levels of security, such as “read only”, “edit”, “full control”, etc, this depends on the software application; however, this should typically be pessimistic.  That is, users should not have ANY permissions to the data until it is provided through a security role.  Furthermore, “field-level security” indicates that a particular field (such as a social security number or other sensitive piece of data) be restricted from editing or viewing for a user or group of users.  Regarding field-level security, this should typically be optimistic and assume that users are able to manage all the individual fields in an area of the software unless restricted in some fashion.  From my experience, I have not seen a significant need to ever provide field-level security in a distributed context (a user can edit the social security number in record A, but not record B).  Usually, if you are wanting to hide a particular field from view/editing, this can be done in a centralized manner via a security role as it will most likely apply to all records.

As an additional illustration of “central” versus “distributed” security, consider the computer network you login to at your place of work.  The folders and files on the network that you have permissions to are most likely dictated by security roles to which your user account is assigned.  However, if you change the security settings on a particular file (such as a spreadsheet or document file) that you have rights to you have just demonstrated an example of “distributed” security by making that file an exception to the central security rules.