Security and Privacy of Data

This is an excerpt from Data Strategy by Sid Adelman, Larissa Moss and Majid Abai, published by Addison Wesley.

This article will only deal with security and privacy as it relates to data. It does not cover the extensive concerns of security and privacy of the web or wireless issues. It also does not deal with viruses and worms.

The attention to data security has become more and more important as employees often keep their own copies of highly sensitive data on their laptops, home computers, and even PDAs (Employees will often download company data to their laptops or home computers to allow them to work from home. Employees are able to copy extensive amounts of data to CDs and DVDs. Companies rarely have any idea who is keeping this data and the implications to security and privacy.

Fraud or misappropriation of sensitive organization data – especially when that fraud or misappropriate is reported in the Wall Street Journal – is often the catalyst for organizations taking action to address their security and privacy issues. It’s not just that the horse is already out of the barn; but there are quite a few other horses ready to bolt. Management should now realize their exposures and should now be ready to listen and approve the money and people resources that it will take to build and support security and privacy policies.

Technology has made security and privacy even more of an issue as critical data can now walk out the door on a removable disk the size of a key fob. The senior management of your organization and the board of directors want to know what you are doing to protect the security of their important and sensitive data and the privacy of their customers.

Data Identification for Security/Privacy

Security relates to the protection of data against unauthorized disclosure, alteration, restriction, or destruction. Privacy is the right of your customers, suppliers, and partners to be sure that the confidential information you maintain on them is being controlled and protected from unauthorized access or distribution.

User Role

Role-based security would assign privileges to create, update, delete, or read (access) specific data, based upon the user’s role or position in the organization. For example, an employee in HR with the responsibility for entering new employees would have the privilege to create a new employee record but would not be allowed to change salary information. A manager would have access and update capability to employee data only for his or her department.

Roles and Responsibilities

It’s not enough to have security and privacy policies, nor is it enough to have tools to identify problems, intercept aberrant access, or identify potential exposures. Without the appropriate roles and responsibilities identified, authorized, and staffed, information about breaches and potential problems will never be addressed in a timely manner.

Security Officer

The primary responsibility of the security officer is to establish security and privacy policies for the organization. In order to establish appropriate and applicable policies, the security officer needs to know the security and privacy requirements of your organization. He or she would be the driver of security activities, including identification of the gaps and exposures, and would know what needs to be accomplished to close the gaps and mitigate the exposures. The security officer would draft the security and privacy policies and have these policies validated by internal and/or external auditors and by senior management. He or she would work with those who administer the security and privacy processes, such as the DBAs and system administrators, to assure that the policies are being executed. The security officer would test the implemented processes, and would validate that the security and privacy policies of the organization were being implemented. In case of a discovered policy violation, he or she would determine what remediation efforts should be used to correct any exposures. The security officer would also determine how and where user ids and passwords are to be encrypted, where they should be stored, and who should hold the keys for encrypting and decrypting. The security officer is responsible for policing and enforcing the security and privacy requirements for the data.

Data Owner

This position is variously called the data owner, data steward, or information steward. We will call this role the data owner. The data owner is in the best position to understand the exposures if the data is compromised, lost, stolen, or misused. The data owner is the one who knows the value of the information and can best determine the benefits of either tight or loose security policies on the data under his or her control. The data owner is usually the line-of-business manager in whose department the data originates. For example, the manager responsible for purchasing would own supplier data and would determine the importance of certain fields (columns) within the Supplier Database. The manager responsible for insurance claims would own the Claims Database and the Human Resource (HR) manager would own the Human Resource Database. In cooperation with the security officer, the data owner should determine the sensitivity of the data and should help to establish policies for the data they own.

The data owner should be made aware of any access to sensitive data including who is accessing the data and for what purpose. In some cases, the data owner would want to approve any unusual requests for access.

System Administrator

The system administrators (sometimes called sysadmins) are the ones who manage the computer systems. They are responsible for keeping the computers up and running, for maintaining the networks, for backup and recovery of the computers (not the databases which are the responsibility of the DBAs), for installing and testing new software, for making sure the system is not corrupted by viruses, for monitoring the system’s performance, for allocating new storage, and – the role that’s most relevant to this chapter – the system administrator is responsible for creating and deleting user accounts. In creating and deleting the user accounts, the system administrator is also responsible for assigning, administering and maintaining passwords. This maintenance includes manually changing passwords that have been forgotten or compromised.

Regulatory Compliance

While there are always new laws passed affecting the governance of information, there are some valuable lessons to be learned from the following example:

The Health Insurance Portability and Accountability Act (HIPAA) mandates that specific administrative and technological policies and procedures must be in place to ensure the security of health data that is stored electronically. It also requires that role-based security be implemented identifying who is authorized to access patient data and for what purpose. HIPAA also makes clear the rights of patients to keep their medical information private.

The Gramm-Leach-Bliley Act enacted in 1999 specifies that any financial institution that provides financial products or services to consumers must comply with certain privacy restrictions. If an organization provides financial products or services to individuals and if those products or services are to be used primarily for their personal, family, or household purposes, you are dealing with consumers and must comply with this law.

Sarbanes-Oxley requires that the CEO and the CFO verify and certify that the financial results they report are accurate and that the creation of that information has followed proper and documented internal control procedures.

California SB 1386 Identity Protection Bill requires companies to encrypt personal data and that individuals whose identities have been compromised must be notified of the violation. All organizations that have California customers, not just California companies, must comply with SB 1386.

The Family Educational Rights and Privacy Act (FERPA) requires that student information cannot be revealed and that aggregated student information cannot be disclosed for groupings of students numbering less than ten for fear that this could lead to individual identification of the student.

International rules including those of the European Union and Canada are more stringent than those in the United States. Organizations with multi-national operations must either keep their domestic databases separate, or they must conform to the more demanding international rules. European Union data protection laws apply only to data that directly or indirectly can identify an individual.

Such data would include a person’s name, address, phone number, credit card number, and tax identification number (or equivalent). These laws do not apply to statistical data as long as that data
cannot be traced back to the individuals.

Auditing Procedures

Auditing procedures should be established to detect purposeful or accidental attempts by either internal or external intruders to access secured data. Many of the DBMSs and third-party tools provide the capability to capture access requests for data including the columns, views, and tables accessed. The tools should also provide information on when the data was accessed, how often and how many times, who accessed it, how often and how many times, what access products were used, for example a business intelligence (BI) product or SQL, and whether the access was successful. Some of these auditing tools can capture and highlight abnormal use and present aberrant access patterns to those responsible for security. These abnormal and deviant situations are often the first indication that something troublesome is happening. Active auditing utilities can impact the system’s performance, and so they should be intelligently and selectively implemented.

Security Audit

A security audit should be carried out periodically to validate the organization’s security and privacy policies and to verify that the implementation of those policies work and are scrupulously followed. A security audit would include the following:

  • Review all controls relating to the use of the data.
  • Review and validate the assignment of user ids, passwords, and their administration.
  • Review how data flows, how it is accessed, security or privacy exposures, etc.
  • Review the use and implementation of the security capabilities of the DBMS.
  • Review processes to monitor data usage including create, update, delete and access.
  • Review the policies and processes of vendors, outsourcers, business partners, and supply chain participants.
  • Review the security capabilities of the application packages and how they are implemented.
  • Review the assignment and roles of the data owners, their level of involvement, and how complete their categorization of the data in their area of responsibility.
  • Review active auditing reports.

If you outsource, ask the outsourcing organization what they do to audit their own security and privacy processes. Include your required policies and procedures in your contract with the outsourcer. Review the documented policies and procedures used by the outsourcing organization and, depending on your security and privacy requirements, require an independent audit of their processes.

External Users of Your Data

You may be selling your data; you may be providing the data to partners, suppliers and wholesale customers as part of a supply chain program. You may be sending your data to an outsourcing organization and that organization might be offshore. Your industry may be regulated and the terms of the regulation may require you to divulge certain of your data to the regulators, for example clinical pharmaceutical trial results must be provided to the Food and Drug Administration. As data leaves your secure environment, the level of exposure increases significantly. There are exposures in the data transmission process. The receiving organization may not have the same level of security and privacy policies or they may have the policies but those policies may not be enforced. Any sensitive data should be encrypted before transmission. The policies of the receiving organization should be reviewed and, if the data is especially sensitive, you will want to conduct security and privacy audits to be sure that your data has not been compromised.

Design Solutions

Security is always harder to retrofit than to implement at the outset. This means that security and privacy should be designed into every application that warrants the effort.

If a database contains keys that can uniquely identify a customer, a patient, or a supplier, the data is exposed to some extent especially when these keys are required in performing joins with other tables. The solution is to use system-generated keys so that the entity remains anonymous.

Database Controls

All databases provide for a certain degree of security control using VIEWs to limit what tables, what rows within those tables, and what columns within those rows a user can see. Some organizations never allow raw access to any tables and only allow access through the views. The DBA can also restrict some access by establishing partitions and limit access by partition. For example, partitions could be established for each country where the organization does business. This could satisfy some country-specific laws that prohibit the mixing of their data with that of other countries

Security Databases

Many organizations have specialized security databases that maintain roles, user ids, and authorization for access. These databases are used in conjunction with the VIEW capabilities of the DBMS. The security databases themselves must be very tightly controlled and include the use of active auditing tools as these security databases also need to be monitored.

Test and Production Data

Application developers need to have test data. Unfortunately, creating sanitized data or data that have been made anonymous is fairly difficult. Thus, many organizations use either a sample or a complete copy of the production database. This may not present a problem at the level of an employee changing his or her salary grade in his or her test data, but it does present a privacy exposure when the test data includes other employees’ salary or performance information. The exposure would obviously be the discovery of a fellow employee’s salary or performance ranking. If the test data should include customer numbers or credit card numbers, the exposure would be that a customer list could be stolen and compromised. If there are exposures and they are deemed to have enough sensitivity, production data must be anonymized before they can be used in a test environment.

Data Encryption

Encryption takes some overhead and should only be used on data that is transferred from one platform to another or it should only be used on sensitive data. Most DBMSs have encryption wizards and other encryption capability. Be sure to research the encryption options and understand how bulletproof those options are (since they can be broken, find out what it takes to break the codes).

Understand the DBA effort involved in encrypting the data and any exposures involved with your RDBMS encryption, such as the loss of some data. Also understand any performance implications of using encryption as well as any other issues such as backup and recovery, reorganization, and archiving of encrypted data. The performance costs are related to how and where the encryption takes place.

Standards for Data Use

Most organizations have customers of one stripe or another. Your organization should have standards of how this customer data is stored, how it’s encrypted, how it’s secured, and how it’s connected to various internal applications.

Some security and privacy standards may come from the legal department, while others may come from the various business units, such as sales and marketing, or compliance and risk.

Impact of the Data Warehouse

The data warehouse presents some new security challenges especially as some portions of the data warehouse may eventually be made available to others outside the organization. The plethora of new
security and privacy laws will require a more complete definition and understanding of data in the data warehouse. The new data laws require an understanding of who is using what internal data.

Laws, regulations, and policies impact and cross most departments. Data disciplines need to be treated as organizational compliance issues and not just be the responsibility of an individual, a
department, or the security office.

At minimum, the organization needs to be aware of concerns relating to potential for insider trading on financial data warehouse data, and any laws related to the use of tax identification numbers – California has a very strict law. The organization must be aware of the Sarbanes-Oxley requirements, including data timeliness and retention, and other regulatory security and privacy laws. They must incorporate the appropriate functions into every project in order to support these requirements.

Applications that use customer information, most notably customer relationship management (CRM) applications, may overstep the line into a person’s private life. This has significant implications to organizations that dearly want to optimize their marketing efforts while not offending and annoying their existing customer base.

As data is examined and defined, the security and privacy requirements of the data and the regulatory sensitivity of the data should be included in the metadata. The notion of user entitlement (role-based access control) should be institutionalized to limit data access by the type of user who should be only be able to look at specifically authorized data elements.

Vendor Issues

The software you purchase and the external data you bring in have security and privacy implications. As you evaluate these offerings, be sure to have someone on the team who can ask appropriate questions and be able to evaluate the answers and capabilities and exposures from these vendors.


Most software products, application packages and ERPs have security capabilities built in, but they vary greatly in their effectiveness as well as how closely they match your own needs for security and privacy. As tools are evaluated, their security and privacy capabilities must be researched not just for the function they provide but also for the effort involved in administration – some administration is very labor intensive. Because they want to be DBMS independent, some application packages override the capabilities of the DBMS by implementing their own security and bypassing the security functions of the DBMS. This could result in a major exposure to your data.

Provide the vendor with your architecture, a description of your business, your environment including your other major software products, and your requirements for security and privacy. Ask the following questions:

  1. Please describe the security and privacy features of your product. Indicate where your product protects the system, e.g., front-end, back-end.
  2. How does your software support our security and privacy requirements and policies?
  3. How does your software interface with the products we now use?
  4. How does your product use or not use the security capabilities of the DBMS?
  5. If we use your software, how would you describe the responsibilities of the person who will be administering security and privacy?
  6. Please provide the names and titles of references who are working with your security and privacy capabilities.

External data

External data has a myriad of exposures both for your organization as well as the company that provided the external data. Most sales and marketing applications need data that goes beyond the data that is generated internally. To get a better understanding of your customers, it would be helpful to have demographic data that can be purchased. The demographic data could include income, age, ethnicity, buying habits, credit information, and education. Some of this data would be considered private and the use of the data could violate your own privacy policies, if not government regulations. The marketing department, on the other hand, would be very eager to use this data as they create marketing programs, as well as sales leads. The marketing department needs to know that, just because the data can be purchased, certain parts of it should not be used.

Best Practices/Worst Practices

  • As an organization develops its own security and privacy policies, it’s best to conform to the most stringent laws, rather than trying to do the minimum.
  • Include a “privacy indicator” in customer record that describes each customer’s privacy preferences. This customer record must be linked to all customer usage, especially when marketing to the customer.
  • If you outsource, send only the data that is absolutely needed to the outsourcing organization (even more important if it’s being offshored). For example, if the outsource vendor is evaluating a customer’s credit, they do not need to know the customer’s name, tax identification number, address, phone, employer, or anything else that could specifically identify the customer.
  • Conduct vulnerability scans to detect exposures. Monitor any unusual patterns in data access.
  • Conduct security risk assessments whenever new systems are implemented or whenever existing systems are significantly modified.
  • Encrypt sensitive data. This is especially important if that data is being transmitted either inside or outside the organization.
  • Establish a standard procedure that restricts access when employees are terminated. This should be obvious, but a surprisingly high percentage of companies are slow to cut off access.
  • If an outside organization, such as a governmental entity, an external auditor or a consultant requests customer data, supplier data, or any other data that is deemed to be sensitive, the
    request should first go to the legal department to determine if honoring the request violates the organization’s security and privacy policies. Even if the request does not violate your security
    and privacy policies, the executive committee may want to review the request.
  • If an outside entity such as an external auditor or a consultant requests customer data, supplier data, or any other data that is deemed to be sensitive, the request should be fully understood
    and the right to access the data should be given only in exceptional situations, and only under a Non-Disclosure Agreement (NDA).
  • Look at the cost and benefit of protecting the data – What is the data worth? Don’t spend $1 to secure information that’s worth 50 cents.


The effort to create and administer security and privacy policies is not trivial. Proper implementation does nothing for the organization’s bottom line. The best way to look at the work and budget required is to equate it with insurance. If you don’t have proper policies and if they are not properly executed, your company is exposed for substantial fines for violating governmental regulations, suits for violating customer privacy, and terrible public relations when the media exposes your slipshod ways.

Knowing how far to go with security and privacy policies is difficult. Depending on your industry, and in which countries you do business, governmental regulations force you to comply, and comply at very specific levels for some but not all of your data so don’t waist money and people resources on data that does not have to be protected.

Share this post

Sid Adelman

Sid Adelman

SidÊis aÊPrincipal at Sid Adelman & Associates, an organization specializing in planning and implementing data warehouses and in establishing effective data architecturesÊand strategies.

scroll to top
We use technologies such as cookies to understand how you use our site and to provide a better user experience. This includes personalizing content, using analytics and improving site operations. We may share your information about your use of our site with third parties in accordance with our Privacy Policy. You can change your cookie settings as described here at any time, but parts of our site may not function correctly without them. By continuing to use our site, you agree that we can save cookies on your device, unless you have disabled cookies.
I Accept