What are alternatives to regulate access privacy information in databases—specifically column level data security?

Option 1—Multiple views and roles

Creating multiple views with different column sets and providing access to appropriate users through roles:

Logic:

• Create 2 views for base table
  • all_vw has all columns
  • restricted_vw doesn't have privacy information columns
• Create 2 database roles,
  • unrestricted_role—Mainly used to access all columns including privacy information columns
  • restricted_role—Mainly used to access non-privacy information columns
• Grant access on all_vw to unrestricted_role
• Grant access on restricted_vw to restricted_role
• Users can now be associated to appropriate roles based on the business needs

Pros and cons:

• Easy to implement at database.
• Different sets of views need to be maintained based on the sensitivity of data element. For example, Private Health Information has 3 categories based on the data sensitivity. So we may end up creating 4 views—3 for different PHI categories and 1 common view excluding all PHI data elements.
• It is not dynamic.

Option 2—Application context views

Build logic returning the runtime access rights for user and include as part of view; associate the logic to specific sensitive column. Then based on the role associated at the runtime, column data will be displayed or the data will be masked. So if the user has access to restricted columns, data will be displayed otherwise masked.

Logic:

• Create 2 database roles,
  • restricted_role—Mainly used to access, data excluding privacy information columns
  • unrestricted_role—Mainly used to access all columns, including privacy information columns
• Build logic/function to return the role/access rights at runtime,
• Create views with the logic/function associated to the columns with restricted data
• When user queries the view, based on the associated role, column data will be displayed or masked

Teradata example:

1. Create 2 roles restricted_role and unrestricted_role

2. Add logic to the view to fetch the role at runtime,

(Select RoleName as testrole
From DBC.UserRoleRights
where Databasename = 'testdb' ) test

3. Use the returned value from logic/function to evaluate the CASE statement on the
sensitive columns in SELECT statement of view,

select CASE
WHEN test.testrole = 'unrestricted_role'
THEN ssn_no
ELSE '1111'
END As ssn ,
ename ,
sal
from emp,
(Select RoleName as testrole
From DBC.UserRoleRights
where Databasename = 'testdb' ) test ;

4. Data is returned for ssnno , if unrestricted_role is matched else '1111' is returned,

Pros and cons:

• There is only one set of views; the application or user access layer need not include any special logic.
• Initial implementation will take more time based on the privacy information that needs to be secured.
• Performance impacts need to be considered at the database level, though this frees the application or user access layer from building logic.

Data security using macros and database procedures

There are two more options to secure data in Teradata using macros and database procedures. Users are granted access to macros or procedures by the owner. The macros/procedures execute using the privileges of the owner of macros/procedures. So the runtime user does not need to be concerned about the underlying database access. This does not directly address the column level security, but if Option 1 or 2 is set up as discussed, it can be effectively behind the scenes using macros and procedures.

Enhancements:

Option 2 can be fine-tuned so the role information is encapsulated in a macro or procedure and then calling macro/procedure as part of the SQL query by passing appropriate parameters, which returns the role associated.

Conclusion:

The article provides general guidelines for using the database options to secure column level data with specific examples. Option 1 is the most traditional approach for implementing the column level security. Option 2 is more dynamic and some database vendors are already supporting this as security feature.

References:

Teradata - Database Administration (B035-1093-115A)
Oracle Database 10g - Application Context Support