3428316 - Integrating SAP Datasphere's data via Database Users/Open SQL Schemas

Details about usage of Database Users (Space Level)/Open SQL Schemas

SAP Datasphere

First of all, let's state a common understanding:

1. Space has the privilege to write data to objects in his own space schema
2. Space has the privilege to read data from open-SQL-schema by default after DB user is created
3. DB user that owns open-SQL-schema has the privileges (read,write) on his own open-SQL-schema
4. DB user that owns open-SQL-schema has no write-privilege on space schema
5. DB user that owns open-SQL-schema has no read-privilege on space schema by default
6. If the checkbox "Enable Read Access (SQL)" in the popup "Edit Privileges" is set, database user that owns open-SQL-schema has the read-privilege on views in space schema.
   See Create a Database User.
7. If the checkbox "Enable Write Access (SQL, DDL, DML)" in the popup "Edit Privileges" is set, Datasphere allows database user that owns open-SQL-schema to connect external tools to user's Open SQL schema to create objects and write data to their open-SQL-schema.
   See Create a Database User.
8. DB user that owns open-SQL-schema has the privilege to grant/revoke the privileges (INSERT,UPDATE,DELETE)on objects, that are stored in open-SQL-schema, to space by executing the procedure "DWC_GLOBAL"."GRANT_PRIVILEGE_TO_SPACE".
9. DB user can only see their own open-SQL-schema and space schema (when Read is enabled).

After executing the procedure, space has the authorization to write (insert, update, delete) data to objects that are stored in open-SQL-schema

The 3 scenarios described in Integrating Data via Database Users/Open SQL Schemas:

Scenario #1 (Inbound direction)

• DB User that owns open-SQL-schema has no write permission on space schema (the point #4 is applied)
• DB User that owns open-SQL-schema reads data from external system and write data to open-SQL-schema (the point #7 is applied)
• Space user reads data from open-SQL-schema (the point #2 is applied)
• Space user write data to his own space schema (the point #1 is applied)
• DB User is needed to connect to external system (#7)
• Because DB User has no write-privilege on space schema (#4), data must be first stored in open-SQL-schema (#7), then space reads data from open-SQL-schema (#2) and write data to space schema (#1)

Scenario #2 (Outbound direction)

• Database user that owns open-SQL-schema reads data from views in space schema (the point #6 is applied)
• The option offers customer the possibility to read data from views in space schema directly
• Here open-SQL-schema user is needed to read data from views in space schema (#6)
• It's not possible to connect to external system from space schema directly

Scenario #3 (Outbound direction)

• Space user has the privilege to write data to tables/views in open-SQL-schema (the point #8 is applied)
• The option offers the possibility to process data first before transferring data to external system by using data flow in space schema 
• Data flow allows customer to create and manage data pipelines, that can be used to perform a variety of tasks to process data
• Processing data before transferring data to external system such as:
  1. Extracting data from different sources
  2. Transforming data
  3. Loading data into target destinations
• After data processing it needs to be stored (#8) in open-SQL-schema to be read
• It's not possible to connect to external system from space schema directly

Permissions and authorizations:

• DB user does not have the permission to write data to space schema as you said
• Datasphere space gets the privilege to read data from open-SQL-schema by default after DB user is created (#2)
• Datasphere space does not automatically get the privilege to write data to open-SQL-schema by default after DB user is created, but only when DB user really wants to grant/revoke (#8)

OpenSQL Schema, OpenSQLSchema , KBA , DS-SM , Space Management , How To