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.

Tags : HANA Schema , _SYS_REPO schema authorization.