A key component to a successful data warehouse is the security system needed to protect your data. However, the process of designing and implementing the system often exposes larger problems of data security within the enterprise. An enterprise data security solution is required to ensure that data within all systems, both operational and analytic, is secured appropriately and consistently. The challenge lies in implementing a security model that is both robust and manageable.

The solution should not require extensive effort to administer as there are too many demands for both technical and business resources to sustain a redirection of focus in maintaining data security. The solution also must be able to handle the data security needs of the entire enterprise. A data security model that is targeted only to a specific group of data may be successful at securing that group of data temporarily, but the remainder of the enterprise will still have a fragmented approach, ultimately leading to overall failure.

Data security consists of three major components: a security model, the technical implementation of that model and processes to support both. This article will focus on the security model to support enterprise data. The technical implementation will be dependent upon the specific technologies involved, including the database engine and tools used to manipulate and view the data. Processes to support the security model and technical implementation are also dependent on the technology involved as well as the specific business environment.

Data Security Overview

A data security model should be an ownership model, meaning that it is based on the group that owns the data. Security decisions cannot be made by those not owning the data because that person may not have full knowledge of who should or should not have access to the data. An ownership-based model will also provide a single point of contact for those who request access to portions of the data. The data owner makes the decisions about what data to release and to whom it should be released. Anyone who has questions about the reasons for securing a particular piece of data can contact the data owner, who has full knowledge and authority of that data element.

The data security model must also be role-based. This involves grouping users who perform similar functions into common groups, creatively called “user groups.” The integrity of members in a user group is extremely important. Data owners must be confident that when they grant access a user group to their data, they are granting access to only the appropriate people. If a user group’s membership is not carefully controlled, unauthorized persons may be granted access to confidential data resulting in a loss of confidence on the part of the data owner and leading to the ultimate failure of the entire solution. Therefore, control over membership in a user group must be strictly enforced and periodically audited.

Information Ownership

Each data object must have one and only one owner, known as the information owner. The information owner is responsible for approving access to data he owns. Often, there are too many users and too many pieces of data stored in individual data objects (database tables, columns, views, cubes, reports, etc.) for an information owner to approve access to each data object to each user of that data. Granting access between individual users and data objects results in a process that is impossible to manage or audit, as shown in Figure 1.

A solution is needed to reduce the large number of users and data objects into smaller, more manageable chunks (user groups) that can be administered and maintained as a single unit. Typically, users who perform similar job functions can be combined into user groups. Data objects that have similar security requirements and share an information owner may be combined into common groups called “data groups.”

It is important that only data that shares a common owner be grouped together, so that the data group itself has only a single owner to avoid security compromises stemming from the disagreement of owners with regard to data access. Likewise, it is important that a single user group has only one owner so that there is rigid control of that user group. Both data groups and user groups must be audited periodically to ensure they contain the correct members in each group. Additionally, a user group may need to be audited on demand, as an information owner may require knowledge of the members of a user group before he or she will grant that group access to his data. The simplified model is shown in Figure 2.

User Groups

User groups consist of individual information users, defined as any individual who requires access to information in order to complete his job responsibilities. Accordingly, user groups are a grouping of information users by job function. For example, all financial analysts in a company likely perform similar functions and may be grouped into a single user group. The purpose of a user group is to simplify the granting of access from groups of information users to groups of data.

When initially defining user groups, many will consider grouping the users by information classification; however, this is typically unwise compared to grouping users by job function. information classification is usually specific to a company and is the way a company classifies the security of its information at a high level. For example, assume a company has three levels of information classification: public, internal and confidential. It is tempting to create user groups for each classification, so that there is a user group for public, one for internal and one for confidential. The drawback to this approach is that some users may need access to data within all three classifications. The seemingly obvious solution would be to put the user in three user groups. However, having users belong to more than one user group is problematic, and avoiding this problem represents one of the most important guidelines in forming user groups, as discussed next.

The requirement that a user belong to only one user group may be the most difficult to understand and sell to the business, but it is critically important in order to maintain the integrity of the user groups. If user groups are created by job function, it is typically easy and clear to define them. Someone who is a buyer in the purchasing department has a different function from someone in Marketing, and clearly they can and should belong to different user groups. If someone’s job is to do some things in Purchasing and some things in marketing, then the job function isn’t the same as someone who works only in Purchasing, and they should be in a separate user group, perhaps a group called the “Purchasing/Marketing” group. Failure to limit a user to a single user group could reduce the information owner’s confidence that the user group membership is strictly controlled. If a user may belong to multiple user groups, then it is possible for a user to be assigned to a user group that has access to data that the individual user should not be able to access. That user will also still have access to the data he needs through the proper user group, and nobody will know security has been breached until an audit of the user group is performed. If the user can belong to only one user group, then assigning a user to a user group that is not appropriate will be noticed immediately because the user will not have access to the data he needs to perform his job duties.

Initially there may be some resistance to enforcing the “many users to only one group” rule, but stand firm. If information users who perform different functions are combined into a single user group, potential security exposure will be introduced as well as increasing the cost of administration. In the rare case when an information user might need to be assigned to multiple user groups, the following validation steps must be taken in order to prevent combining information users who perform different job functions in the same user group:

Step 1: Grant data access to the information user’s primary user group. If the function is the same among all users in the user group, why can’t access be granted to data in question to all information users in the information user’s current user group?

Step 2: Move the information user to a new, more appropriate primary user group. As described earlier, if the user truly has a job function that spans two groups such as purchasing and marketing, perhaps a new user group is required. This new group may have very few members in it, but it keeps the integrity of the user group model intact. This should be a relatively rare occurrence.

Step 3: As a last resort, allow the information user to be a member of multiple user groups. This should require senior-level approval and an end date for all user groups to which the user is a member, except for the primary user group. There may be valid reasons for this, such as covering for a colleague who is out of the office or transitioning to a new job function but still temporarily performing his previous job function as well. In all cases, any temporary access to a user group must be accompanied with an end date that will remove membership to that group. Having an end date eliminates the possibility of temporarily adding the user to a group and then forgetting to remove the user later.

Data Groups

Data groups are a collection of data objects that have a common data owner, related attributes and common security requirements that represent the information owner’s security policies. A data object can be any type of object that either stores or accesses data, such as a database table, column, view, report data elements, etc. As with user groups, a data object can exist in one and only one data group. This is required to maintain the integrity of the data object, so that two conflicting security requirements cannot be imposed on a single data object.

Data objects are typically assigned to data groups based on the subject area that contains the data object and the security requirements of the data object. It is uncommon that a data group will consist of data objects in two different subject areas. Data groups also have a relationship with information classification in that a data group will be assigned to one and only one information classification. Typically, the data groups will contain full tables and all columns in those tables, or contain just a few columns of a given table while the remaining columns are assigned to other data groups. This corresponds to handling database security similar to table-level and column-level security, and it is usually implemented physically through database views.

The organization of objects into data groups is part art, part science. Data groups will be created from logical parts of the organization and refined into as much detail as necessary. The challenging part is to know how much detailed separation of data objects is enough. In some organizations, simply creating a single HR is sufficient. Other organizations may require further separation into data groups for personal information, salary information, etc. At this point, it is important to have some idea of how the data will eventually be consumed by the users.

Assigning Data Groups and User Groups

As an example, assume our information classifications are public, internal and confidential. Further, we are implementing security for two different subject areas, HR and clients. A review of the HR data model will group data objects by information classification. We also may want to further group the objects by related elements, such as personal information in one group and compensation information in another group. Both types of data may be classified as confidential but may have two different types of audiences, so we want to narrow the group as much as is reasonable. This requires some forethought into who may need to access the data and if there may be other groups with access to data in the same classification but not necessarily related in the same way. Some users may be interested in the compensation data but are not authorized to view the personal information and vice versa. Access to “confidential” data does not imply that a user has access to all data deemed confidential, even within a single subject area. Combining data objects into data groups may take some time and a few passes through the data model to validate that the data groups are accurate and complete.

In this example, we may have several data groups for our HR subject area. There is likely some data that is accessible by everyone and therefore grouped into the HR public group. This may contain information such as employee name - where if this information were to be made known to the general public there would be no consequences. There may also be a HR internal group that contains information specific to the employee and company such as employee location and employee extension. This data group may even be categorized further if it is desirable to do so. Finally, data may be grouped into HR personal confidential and HR compensation confidential groups as described above. This allows user groups to be assigned to individual data groups at the proper classification and related attributes. Figure 3 illustrates relationships between the information users, user groups, data groups and Data Objects.

Row-Level Security

There is often a question of how row-level security fits within user groups and data groups and how to implement such security. For example, assume a manager is allowed access to view compensation information for employees that report to his organization, but he cannot view compensation information for employees in other organizations. There is often a temptation to create a single user group for each organization in the company, rather than have one user group to which all managers in the company belong. Remember that user groups should be assigned based on similar job function, so if a manager is considered the same job function regardless of the organization that is being managed, then there should be only one user group in which all managers reside.

A data group may contain some data elements with row-level requirements and also contain other data elements that do not have row-level requirements. The data object itself is secured through the data group, but the specific values within that object are secured somewhat differently. Row-level security is accomplished using views and joins to other tables that limit the rows of data returned for a query based on the user who is executing the query. The physical implementation to accomplish this is outside the scope of this, but the resulting database view is considered a data object. This object is assigned to a data group, and the way the rows are returned are contained within the object.

It should be noted that row-level security should be implemented only when absolutely necessary. Organizations often demand row-level security when it is not actually required. It is important to distinguish between securing the data and providing default values based on a user. For example, a sales manager may want to only see data for his region when viewing a report. Does this mean he is not allowed to see data for any other regions? Not necessarily. It may be more convenient for him to simply run a quarterly sales report and the data returned is only for his region, but there may be no corporate or legal policy requiring this. Data security is exactly that – security. It is not intended to provide convenience for individual users; it is to enforce policies that may have competitive and/or legal consequences if the data security is compromised. There is an appropriate place for setting default values for usability purposes, but it is not in the realm of enterprise data security.

Enterprise data security is comprised of a security model, a technical implementation of that model and processes to support the model and technical implementation. The focus of this article is the security model, which itself can be broken down into more detailed elements. Information owners are responsible for granting access to specific pieces of information that fall under their ownership. user groups are information users who consume the information and are grouped according to their job function. Data groups are groups of data objects that have a common information owner, related attributes and common security requirements. When information owners, user groups and data groups are properly identified and defined, they combine to create a data security model that is robust and flexible and a valuable asset to the enterprise.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access