Thursday, September 1, 2016

How to create HANA DB schema and assign authorization

Requirement : You want to create a schema in HANA and assign required authorization.

Prerequisite :You are connected to SAP HANA System with database user having  Data Admin OR Modeling or CONTENT_ADMIN authorization.

Steps :

Connect to HANA system and open SQL console in HANA studio or via hdbsql at OS level.
Execute following SQL

CREATE SCHEMA mySchemaName;
OR
CREATE SCHEMA mySchemaName OWNED BY userName;

Assign authorization to _SYS_REPO user : If you are planning to develop any modeling views on this schema then it's necessary to execute following SQL to grant authorization to _SYS_REPO user, this user is responsible for creating run-time objects from your design time artifacts.
GRANT SELECT ON SCHEMA mySchemaName to _SYS_REPO WITH GRANT OPTION;
If you skip this _SYS_REPO authorization assignment then you will get error while activating views created on this schema.
<info>Create Attribute View failed: SQL: transaction rolled back by an internal error: insufficient privilege: Not authorized

Example :
Here we are creating a schema NAFTA (by default we will be owner of this schema).

CREATE SCHEMA NAFTA;
GRANT SELECT ON SCHEMA NAFTA to _SYS_REPO WITH GRANT OPTION;



Types of HANA schema(s):
User Defined Schema : Schema created by HANA db users.
System Defined Schema : Schema created by HANA system, these schema(s) are used by HANA for various system functionality , for example  _SYS_BIC ,  _SYS_REPO ,  _SYS_STATISTICS , SYS etc.
SLT Derived Schema : Schema which are created upon Data Provisioning by SLT process.


1 comment: