Monday, 11 May 2015

SAP HANA Data Architecture and Security Concept (Part: 2)

First part of this blog can be found here:


This second part is describing concept of roles and how to use SQL interface to define security objects.

9 Role definitions


Roles should be designed using role hierarchy ability of HANA database. First you should create elementary roles for each schema/package. These can be then directly assigned to users or can be orchestrated together to form higher level roles based on type of the user.

Role can be created using simple syntax:
     CREATE ROLE <role name>

9.1 Elementary roles - SQL privileges


We will design following roles addressing SQL privileges:
  • role DATA_*_READ having privilege to SELECT
  • role DATA_*_WRITE having privilege to INSERT, UPDATE, DELETE and EXECUTE
  • role DATA_*_DDL having privilege to CREATE ANY, ALTER, DROP, INDEX

As result we will need to crate these roles:
  • role DATA_SLT_IDD800_READ
  • role DATA_SLT_IDD800_WRITE
  • role DATA_SLT_IDD800_DDL
  • role DATA_SLT_IDE800_READ
  • role DATA_SLT_IDE800_WRITE
  • role DATA_SLT_IDE800_DDL
  • role DATA_BODS_TESTING_READ
  • role DATA_BODS_TESTING_WRITE
  • role DATA_BODS_TESTING_DDL
  • role DATA_MAN_TESTING_READ
  • role DATA_MAN_TESTING_WRITE
  • role DATA_MAN_TESTING_DDL
  • role DATA_MAN_COPA_READ
  • role DATA_MAN_COPA_WRITE
  • role DATA_MAN_COPA_DDL

Caution:
Only owner (data owner user) should grant SQL privileges. Granting privileges via other user (that has proper grant privilege) is not recommended because all granted privileges are being automatically revoked at the moment when this user is deleted or when his authorizations were removed.

Caution:
Every data owner must grant select privilege with grant option for his schema to technical user _SYS_REPO. Without this step queries to all dependent views will end up with following error: “invalidated view: package/VIEW”. See SAP Note 1612696 - "Invalidated view" error when using analytical views for more information.

Comment:
Of course it is possible to grant access on object level (tables, indexes and views) instead of schema level but this should be done in exceptional cases only. Object level security would cause increased implementation and maintenance effort and low transparency of security concept.

9.2 Elementary roles - Analytic privileges


For each defined analytic privilege(s) within each package we should define separate role. In our example we are having only two packages having analytic privileges therefore we will have following roles:
  • role ANALYTIC_HR_USA
  • role ANALYTIC_HR_CANADA
  • role ANALYTIC_HR_EMEA

These roles will be required for selecting corresponding subsets of data from HR package.

We will also create role for ANALYTIC_TESTING_ALL privilege:
  • role ANALYTIC_TESTING

Caution:
Having analytic privilege is not enough to read the data. Role also must contain SELECT privilege for corresponding automatically generated objects in schema _SYS_BIC.

9.3 Elementary roles - Package privileges


To separate developers based on projects and activities we will create following roles for each defined package:
  • role PACKAGE_*_READ having privilege REPO.READ
  • role PACKAGE_*_EDIT having privilege REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS, REPO.MAINTAIN_NATIVE_PACKAGES, REPO.EDIT_IMPORTED_OBJECTS, REPO.ACTIVATE_IMPORTED_OBJECTS, REPO.MAINTAIN_IMPORTED_PACKAGES

This will result in following roles:
  • role PACKAGE_TESTING_READ
  • role PACKAGE_TESTING_EDIT
  • role PACKAGE_COPA_READ
  • role PACKAGE_COPA_EDIT
  • role PACKAGE_HR_READ
  • role PACKAGE_HR_EDIT

9.4 Elementary roles - System privileges


System privileges are predefined internal authorizations that should not be assigned to regular and application users.

These privileges are intended to create different types of administrative users.

Following administrative roles can be designed (examples):
  • role ADMIN_USER having privilege USER ADMIN, ROLE ADMIN, CREATE SCHEMA
  • role ADMIN_AUDIT having privilege AUDIT ADMIN
  • role ADMIN_BACKUP having privilege BACKUP ADMIN
  • role ADMIN_MONITOR having privilege MONITOR ADMIN
  • role ADMIN_SYSTEM having privilege BACKUP ADMIN, MONITOR ADMIN, SERVICE ADMIN, SESSION ADMIN, TRACE ADMIN

Consideration:
Biggest issue in HANA is that only grantor can revoke the privilege. Problem will arise in case that company will have employee working in HANA security who will grant a lot of roles and then he will leave. In such case his work has to be redone. Same issue happens when such user is on vacation and some role needs to be revoked. Such action is currently not possible. Conclusion of this “feature” is my recommendation to have one security user that is shared between very few individuals. Of course this is making whole security concept vulnerable.

9.5 Special roles


You will also need special roles. For example modeler will need to access list of packages, he will need to activate objects, etc.

Package roles are controlling who can see and edit objects in package. However during object activation there are internal objects created in schema _SYS_BIC. Without proper authorization against this schema you cannot activate objects.

We will create new role having all required privileges for working with packages and analytical objects.
  • role TECHNICAL_PACKAGES

To allow listing all defined packages you need to grant following privilege:
     GRANT EXECUTE ON REPOSITORY_REST TO TECHNICAL_PACKAGES

To enable modelers to activate objects you need to grant them create privileges:
     GRANT CREATE ANY ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

To allow them to re-active objects they need to be able to drop objects:
     GRANT DROP ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES

To enable creation of calculation scenarios and cubes you need to grant following system privilege:
     GRANT CREATE SCENARIO TO TECHNICAL_PACKAGES

Modelers will have authorization to create new privilege but not to activate it or assign to any role (which is very good idea otherwise user can grant himself any authorization).

In case you wish to grant them authorization to activate you need to grant them system privilege CREATE STRUCTURED PRIVILEGE and STRUCTUREDPRIVILEGE ADMIN.

9.6 Composite roles


Based on your needs you can combine elementary roles into bigger logical units. For example we can design role for modelers to give them appropriate privileges into testing schema and package for testing.
  • role COMPOSITE_TESTING

This role will contain following elementary roles:
  • DATA_BODS_TESTING_READ
  • DATA_MAN_TESTING_READ
  • DATA_MAN_TESTING_WRITE
  • DATA_MAN_TESTING_DDL
  • PACKAGE_TESTING_READ
  • PACKAGE_TESTING_EDIT
  • TECHNICAL_PACKAGES
  • ANALYTIC_TESTING

Note: Notice that we intentionally grant only READ access into schema DATA_BODS_TESTING as objects in this schema should not be created manually but exclusively from SAP BusinessObjects Data Services.

10 Deleting objects


Be extremely careful when you delete object from HANA database. I suggest never using CASCADE option because important objects might be deleted. But be careful because during some delete operations (like deleting schema) cascade is always used.

In case you will try to delete user with dependent objects you will have following error:

hdbsql HDB=> DROP USER U_KROJZL
* 417: can't drop without CASCADE specification: U_KROJZL: line 1 col 11 (at pos 10) SQLSTATE: HY000

In such case you should check view "SYS"."OWNERSHIP" to list objects owned by this user.
     SELECT * FROM "SYS"."OWNERSHIP" WHERE OWNER_NAME='U_KROJZL'

Same way other objects can be dependent on these objects. To identify them execute select statement against view "SYS"."OBJECT_DEPENDENCIES".

Unfortunately there is nothing you can do - I am not aware about possibility to transfer ownership.

11 Using SQL


For creating just few users and roles you might use HANA studio. But in case you will need to create more users it is more convenient to use SQL interface.

Below you can see example how to create part of our configuration.

First manually create package prj-testing using user SYSTEM
package.png
Then create analytic privilege ANALYTIC_TESTING_ALL still using user SYSTEM.
analytic1.png
Click Finish (no need to add objects).
analytic2.png
Check option “Applicable to all Information Models” (be sure to fully understand impact of this adjustment) and click Save. Activate object.

Launch SQL interface.

vhost0479:~ # su - hdbadm
vhost0479:/usr/sap/HDB/HDB00> which hdbsql
/usr/sap/HDB/HDB00/exe/hdbsql
vhost0479:/usr/sap/HDB/HDB00> hdbsql

Connect as user SYSTEM.

Note: I would recommend using user SYSTEM (or specifically created user) to create and grant all security related objects (except SQL privileges where owner should grant them). Reason for this rule is fact that privileges are removed if user who granted them is deleted or if corresponding privilege is removed for this user.

Connect as user SYSTEM and execute SQL commands:

hdbsql=> \c -i 00 -u SYSTEM -p <password>
Connected to HDB@vhost0479:30015

CREATE USER DATA_MAN_TESTING IDENTIFIED BY Temp1234
CREATE USER DATA_BODS_TESTING IDENTIFIED BY Temp1234
CREATE USER U_KROJZL IDENTIFIED BY Temp1234
DROP SCHEMA U_KROJZL
CREATE USER A_BODS_TESTING IDENTIFIED BY Temp1234
DROP SCHEMA A_BODS_TESTING
CREATE ROLE DATA_BODS_TESTING_READ
CREATE ROLE DATA_BODS_TESTING_WRITE
CREATE ROLE DATA_BODS_TESTING_DDL
CREATE ROLE DATA_MAN_TESTING_READ
CREATE ROLE DATA_MAN_TESTING_WRITE
CREATE ROLE DATA_MAN_TESTING_DDL
CREATE ROLE PACKAGE_TESTING_READ
CREATE ROLE PACKAGE_TESTING_EDIT
CREATE ROLE TECHNICAL_PACKAGES
CREATE ROLE ANALYTIC_TESTING
CREATE ROLE COMPOSITE_TESTING
GRANT DATA_BODS_TESTING_READ TO COMPOSITE_TESTING
GRANT DATA_MAN_TESTING_READ TO COMPOSITE_TESTING
GRANT DATA_MAN_TESTING_WRITE TO COMPOSITE_TESTING
GRANT DATA_MAN_TESTING_DDL TO COMPOSITE_TESTING
GRANT PACKAGE_TESTING_READ TO COMPOSITE_TESTING
GRANT PACKAGE_TESTING_EDIT TO COMPOSITE_TESTING
GRANT TECHNICAL_PACKAGES TO COMPOSITE_TESTING
GRANT ANALYTIC_TESTING TO COMPOSITE_TESTING
GRANT REPO.READ ON "prj-testing" TO PACKAGE_TESTING_READ
GRANT REPO.EDIT_NATIVE_OBJECTS, REPO.ACTIVATE_NATIVE_OBJECTS,REPO.MAINTAIN_NATIVE_PACKAGES, REPO.EDIT_IMPORTED_OBJECTS, REPO.ACTIVATE_IMPORTED_OBJECTS,REPO.MAINTAIN_IMPORTED_PACKAGES ON "prj-testing" TO PACKAGE_TESTING_EDIT
GRANT EXECUTE ON REPOSITORY_REST TO TECHNICAL_PACKAGES
GRANT CREATE ANY ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES
GRANT DROP ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES
GRANT CREATE SCENARIO TO TECHNICAL_PACKAGES
GRANT SELECT ON SCHEMA _SYS_BIC TO ANALYTIC_TESTING
GRANT COMPOSITE_TESTING TO U_KROJZL

Also create required roles for few power users:

CREATE ROLE ADMIN_BACKUP
GRANT BACKUP ADMIN TO ADMIN_BACKUP
GRANT ADMIN_BACKUP TO U_KROJZL

Now connect as data owner user and finish the security setup:

hdbsql HDB=> \c -i 00 -u DATA_BODS_TESTING -p Temp1234
Connected to HDB@vhost0479:30015

ALTER USER DATA_BODS_TESTING IDENTIFIED BY <new password>
GRANT SELECT ON SCHEMA DATA_BODS_TESTING TO DATA_BODS_TESTING_READ
GRANT INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA DATA_BODS_TESTING TO DATA_BODS_TESTING_WRITE
GRANT CREATE ANY, ALTER, DROP, INDEX ON SCHEMA DATA_BODS_TESTING TO DATA_BODS_TESTING_DDL
GRANT SELECT ON SCHEMA DATA_BODS_TESTING TO _SYS_REPO WITH GRANT OPTION

Repeat same for second data owner:

hdbsql HDB=> \c -i 00 -u DATA_MAN_TESTING -p Temp1234
Connected to HDB@vhost0479:30015

ALTER USER DATA_MAN_TESTING IDENTIFIED BY <new password>
GRANT SELECT ON SCHEMA DATA_MAN_TESTING TO DATA_MAN_TESTING_READ
GRANT INSERT, UPDATE, DELETE, EXECUTE ON SCHEMA DATA_MAN_TESTING TO DATA_MAN_TESTING_WRITE
GRANT CREATE ANY ON SCHEMA _SYS_BIC TO TECHNICAL_PACKAGES
GRANT SELECT ON SCHEMA DATA_MAN_TESTING TO _SYS_REPO WITH GRANT OPTION

If required then after creation of objects in package you can define custom-tailored analytic privileges.

It should be possible to add privileges to role using following SQL syntax but it seems that SQL interpreter is having issue executing the command.

hdbsql HDB=> CALL _SYS_REPO.GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE ('"prj-testing/ANALYTIC_TESTING_ALL"', 'ANALYTIC_TESTING')
* 292: wrong number of arguments: GRANT_ACTIVATED_ANALYTICAL_PRIVILEGE must have three parameters SQLSTATE: 07001

Add privilege to the role manually (using user SYSTEM):
analytic3.png
Save.

Source: © SAP

No comments:

Post a Comment