How HANA DB schema should be created and authorization should be assigned?
We have connected to SAP HANA System with database. The database user is having Data Admin OR Modeling or CONTENT_ADMIN authorization.
Listed below are the steps:
SQL console in HANA Studio is opened after getting connected to HANA system or at OS level via hdbsql.
The following SQL should be executed
CREATE SCHEMA mySchemaName;
OR
CREATE SCHEMA mySchemaName OWNED BY userName;
To _SYS_REPO user the authorization is assigned: It is necessary that the following SQL to grant authorization to _SYS_REPO user should be executed when planning to develop any modeling views on this schema, from design time artifacts to create run-time objects; this user is responsible.
GRANT SELECT ON SCHEMA mySchemaName to _SYS_REPO WITH GRANT OPTION;
While activating views created on this schema, errors are received, if _SYS_REPO authorization assignment is skipped.
<info>Create Attribute View failed: SQL: an internal error rolled back the transaction: insufficient privilege: Not authorized
Example:
A schema NAFTA is created here (we will be the owner of this schema by default).
- SCHEMA NAFTA is created;
- WITH GRANT OPTION GRANT SELECT ON SCHEMA NAFTA to _SYS_REPO;
HANA schema(s) types:
- User Defined Schema: HANA db users created this schema.
- System Defined Schema: HANA system created this schema, for various system functionality these schema(s) are used by HANA, for example _SYS_BIC, _SYS_REPO, _SYS_STATISTICS, SYS etc.
- SLT Derived Schema: upon Data Provisioning by SLT process this schema is created.