How to securely mask or hide column data using SQL MAP Function in SAP HANA views

Posted by Mark Hourani on May 13, 2014

More by this author

The need for column level security at the database layer can be avoided by masking or hiding data at the application level. However, as a DBA, you may prefer to set up a model where

  1. Only DBAs have access to the physical model
  2. Access to data is only exposed through base views for which DBAs securely manage access. Once the base views are in place and secured for your highly sensitive data, you can have the confidence you need to sleep well at night

SAP HANA offers the ability to manage column security by allowing the creation of additional (secured) views to expose more sensitive columns. In the event that this approach does not fit for your project due to specific administrative requirements, I offer here an alternate approach that may be considered.

Benefit of this approach

Lower maintenance due to less views

Overview

The following is intended to be a simple example demonstrating how to hide or mask column data based on a HANA user’s assigned privileges or roles. We will store sensitive employee data (social security number) in a HANA table that is not directly exposed to users. The employee names and SSNs will be exposed to users by a single view that exposes the SSN to some users and not others.

Step 1: Create schema and employee table

CREATE SCHEMA MYSCHEMA;

CREATE COLUMN TABLE “MYSCHEMA”.“EMPLOYEE”

(“FIRST_NAME” NVARCHAR(32),

“LAST_NAME” NVARCHAR(32),

“SSN” NVARCHAR(12),

“EMPLOYEE_ID” INTEGER NOT NULL,

PRIMARY KEY (“EMPLOYEE_ID”));

insert into “MYSCHEMA”.“EMPLOYEE” values(‘LOU’,‘JOHNSON’,‘456-78-9123’,1);

insert into “MYSCHEMA”.“EMPLOYEE” values(‘BOB’,‘THOMPSON’,‘345-67-8912’,2);

insert into “MYSCHEMA”.“EMPLOYEE” values(‘CINDY’,‘BENSON’,‘234-56-7891’,3);

Step 2: Create privilege table

In this example we create a privilege table for users where a one (1) in the HAS_PRIV column indicates that a user has this privilege. So USER1 has the privilege to access social security numbers.

CREATE COLUMN TABLE “MYSCHEMA”.“PRIVS”

(“USER_ID” NVARCHAR(32) NOT NULL,

“PRIV_NAME” NVARCHAR(32) NOT NULL,

“HAS_PRIV” TINYINT NOT NULL,

PRIMARY KEY (“USER_ID”));

insert into “MYSCHEMA”.“PRIVS” values(‘USER1’, ‘READ_SSN’, 1);

insert into “MYSCHEMA”.“PRIVS” values(‘USER2’, ‘READ_SSN’, 0);

Step 3: Create privilege view

This view uses the SQL MAP function to list the session_user’s granted privileges from the privilege table as columns.

Important Note: You must use the system variable session_user instead of current_user. See the explanation at the end of this post for the reason.

CREATE VIEW “MYSCHEMA”.“V_PRIVS” AS

select

user_id,

MAX(READ_SSN_PRIV) AS READ_SSN_PRIV

from ( select p.user_id,

MAP(p.PRIV_NAME, ‘READ_SSN’, MAP(p.HAS_PRIV, 1, p.HAS_PRIV, NULL), NULL) AS READ_SSN_PRIV

from “MYSCHEMA”.“PRIVS” p

WHERE p.user_id=session_user )

GROUP BY user_id;

When I am logged in as USER1, I see the following privileges when I query the view.

/wp-content/uploads/2014/05/pic1.png

Step 4: Create employee view (option 1)

The employee view will use the privilege view and return a null if the session_user does not have the required privilege granted in the priv tavble.

CREATE VIEW “MYSCHEMA”.“V_EMPLOYEE” AS

select “FIRST_NAME”,

“LAST_NAME”,

“EMPLOYEE_ID”,

MAP(p.READ_SSN_PRIV, 1, e.SSN, NULL) AS SSN

from “MYSCHEMA”.“EMPLOYEE” e,

“MYSCHEMA”.“V_PRIVS” p;

When I am logged in as USER1, I see the complete SSN data when I query the view.

/wp-content/uploads/2014/05/pic2.png

When I am logged in as USER2, I see nulls for the SSN data when I query the view.

/wp-content/uploads/2014/05/pic3.png

Step 5: Create employee view (option 2)

Instead of returning nulls, we could mask the first 5 digits of the SSN and display only the last four digits for users without the required privilege.

CREATE VIEW “MYSCHEMA”.“V_EMPLOYEE_MASK_SSN” ( “FIRST_NAME”,

“LAST_NAME”,

“EMPLOYEE_ID”,

“SSN”) AS

select “FIRST_NAME”,

“LAST_NAME”,

“EMPLOYEE_ID”,

MAP(p.READ_SSN_PRIV, 1, e.SSN, ‘XXX-XX-‘ || SUBSTR(e.SSN, 8)) AS SSN

from “MYSCHEMA”.“EMPLOYEE” e,

“MYSCHEMA”.“V_PRIVS” p;

When I am logged in as USER1, I see the full SSN data when I query the view.

/wp-content/uploads/2014/05/pic4.png

When I am logged in as USER2, I see only the last four digits of the SSN data when I query the view.

/wp-content/uploads/2014/05/pic5.png

Option: Tie the privileges to assigned roles

You can also create a privilege view using the assigned role of a user by querying the sys.granted_roles table and matching the grantee column to the session_user

CREATE VIEW “MYSCHEMA”.“V_PRIVS” AS

select

user_id,

MAX(READ_SSN_PRIV) as READ_SSN_PRIV

from ( select

r.grantee as user_id,

MAP(‘SSN_ROLE’, r.role_name, 1, 0) AS READ_SSN_PRIV

from sys.granted_roles r

WHERE r.grantee = session_user )

group by user_id;

Considerations

  • In this example I show a SQL view. If using a column view you are limited to using a scripted calc view. As such, any dependent column views must also be calc views, as opposed to attribute or analytic views
  • You may have some performance degradation compared to just adding new views for the columns that need to be secured. But depending on your requirements and the size of your data, this should be tolerable. In my example above, I am able to select a single row from the employee view in 23ms where the underlying employee table has 1 million rows
  • This approach only works on tables with primary keys (tables must have a unique identifier)
  • Field names are visible. To completely hide field names, creating additional (secured) views exposing the hidden columns would be the best approach

Learn More about HANA

Be sure to check out the Hands on Development Pre-Conference Session at the ASUG Annual Conference. Click here to learn more.

Session_User vs Current_User

The “current_user” variable returned from HANA column views is not the invoker’s user ID but that of the definer. Even though the definer’s ID is returned as the “current_user”, HANA secures column views based on the invoker of the view.

MAP Function

More on the map function in the SAP HANA SQL Script Help Guide.

http://help.sap.com

-> SAP In-Memory Computing -> SAP HANA Platform -> Reference Information -> SAP HANA SQL and System Views Reference

The MAP function is documented in the SQL Functions-> Miscellaneous Functions section.

Disclaimer

This post describes a possible approach to consider when securing column data in HANA but your specific implementation should be validated by security experts within your organization.

VN:F [1.9.22_1171]
Average User Rating
Rating: 5.0/5 (2 votes cast)
How to securely mask or hide column data using SQL MAP Function in SAP HANA views, 5.0 out of 5 based on 2 ratings

7927 Views