Skip to Content

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_451358.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_451359.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_451360.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_451361.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_451362.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 SAP HANA

Check out the Hands-on HANA Development Pre-Conference Session at the ASUG Annual Conference. Click here to learn more.

Session_User vs Current_User

Using this approach you must use the session_user variable, not the current_user variable to filter access. 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.

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Harri Nieminen

    Hi Mark, great post and works like a charm on the columns.

    Could you elaborate a bit on this

    • 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”

    It is possible to build a set of attribute/analytic views and and add those into a calculation view which would then be combined to the SQL view containing this logic to include also the columns to be shown only through the PRIVS? If possible, how would that be done?

    (0) 
    1. Mark Hourani Post author

      Hi Harri,

      Since analytic views and graphical calc views do not offer the MAP function (to the best of my knowledge), I don’t have a recommendation that would work for attribute or analytic views at this time.

      However in SPS09 we hope to offer the ability to encrypt data from an XS engine API. It will be interesting to see what type of solutions will be implemented using that new functionality.

      Best,

      Mark

      (0) 
      1. Aamod Dhople

        Hello Mark,

        We have a requirement to mask confidential data coming from a model. Data coming in certain columns need to be masked and masked columns should be configurable in order for the admin to select the columns. We are currently on SPS9 rev 96. Is there a way to use MAP function in calculated column?

        Thanks,

        Aamod.

        (0) 
        1. Mark Hourani Post author

          Hi Aamod,

          It appears that currently the MAP function cannot be used in a calculated column in the graphical calculation view, but only in a scripted calculation view.

          Perhaps another colleague may comment on other options.

          Best regards,

          Mark

          (0) 

Leave a Reply