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
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.
Lower maintenance due to less views
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.
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);
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);
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.
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.
When I am logged in as USER2, I see nulls for the SSN data when I query the view.
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.
When I am logged in as USER2, I see only the last four digits of the SSN data when I query the view.
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
Check out the Hands-on HANA Development Pre-Conference Session at the ASUG Annual Conference. Click here to learn more.
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.
More on the map function in the SAP HANA SQL Script Help Guide.
-> 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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
38 | |
19 | |
13 | |
13 | |
11 | |
10 | |
10 | |
10 | |
8 | |
8 |