Create a SQL user with access to multiple spaces in SAP Datasphere

Suppose you want to access two SAP Datasphere spaces using the same SQL user, how do you create such a user? That’s what we will go through in this blog post.

As an example, we have two spaces as the below screenshot shows. We will create a SQL user that can access both of these spaces. For this we take the following steps.

1. Create a Database User Group including an admin user;
   
2. Create an SQL user with the new admin user;
   
3. Create a Database User for each space;
   
4. Grant read access to the views that you want the SQL user to have access to.

Step 1 – Create a Database User Group including an admin user

In the side navigation area of SAP Datasphere, choose System > Configuration > Database Access > Database User Groups. Here, create a Database User Group. This will provide you right away with administrator credentials. If you need documentation on the creation process, go to this help page. In there, there is also a link to the SAP HANA Cloud documentation that describes the Database User Group Concept.

Step 2: Create an SQL user with the new admin user

After the Database User Group is created, go to your preferred database client tool to log on with that user. In this blog post we use the SAP Database Explorer. With the admin user, we create a new SQL user. This will be the user getting read access to the two SAP Datasphere spaces.

CREATE USER DWCDBGROUP#READ_SPACES#BOBBY PASSWORD "<A1a etc.>" SET USERGROUP DWCDBGROUP#READ_SPACES

Now log on with the newly created SQL user. Tip: in DB Explorer, you can right click on the existing instance and choose “Add Database with Different User” – then it adds a new user while re-using the instance details, which saves you some time.

When we check the views in the two spaces that this user has access to, we see that currently no views are accessible.

Step 3: Create a Database User for each space

To grant read access from the two spaces to this SQL user, you first have to create a Database User in each of the spaces. You do this under Space Management > Database Access > Database Users for each of the spaces. Make sure you tick the checkboxes for Enable Read Access (SQL) and With Grant Option. Deploy the space to activate the user and fetch the credentials.

Step 4: Grant read access to the views that you want the SQL user to have access to

Using the database users created in step 3 – one for each space – we go through the following for each of the spaces.

Check to which views the database user has access. The following statement gives you a list of the views that are exposed for consumption. If you want to add more views, you’ll have to expose them for consumption in the SAP Datasphere view editor.

select OBJECT_NAME from effective_privileges
where SCHEMA_NAME IN ('SEFAN_SALES','SEFAN_HIERARCH2') AND GRANTEE_TYPE = 'USER' AND IS_GRANTABLE = 'TRUE' AND user_name = current_user;

As you can see, this gives us a the list of views exposed for consumption in our space.

The space database user also has grant select privileges, because we made that setting when creating the user. Therefore, we can fire GRANT SELECT statements and provide our SQL user with read access to the views. You cannot grant access on the entire schema, only on individual objects. SAP Datasphere does not provide you with any user that has full schema access with a grant option. Below we grant the SQL user access to one view.

GRANT SELECT ON SEFAN_SALES."vBusinessPartner" TO DWCDBGROUP#READ_SPACES#BOBBY

When now checking again the list of views with the SQL user, you can see that this view popped up. Below screenshot is made after we repeated all of the above step also for our other space.

Build a procedure to grant access to all consumable views of a space at once

Granting read access to all individual views in a space can be a bit cumbersome, but of course you can automate this by coding a procedure that loops over all consumable views for a space and then grants read access to each view. Below code creates such procedure. Make sure to create the procedure with the Database User Group Admin, and then grant access to that procedure to the database users of the space. These can then execute this procedure.

--execute with ADM user
CREATE OR REPLACE PROCEDURE grant_select_on_objects (
    IN IP_SPACE NVARCHAR(256),
    IN IP_SQL_USER NVARCHAR(256)
)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    -- Declare a cursor for the SELECT statement
    DECLARE CURSOR C FOR 
        SELECT SCHEMA_NAME, OBJECT_NAME 
        FROM effective_privileges 
        WHERE SCHEMA_NAME = :IP_SPACE
          AND GRANTEE_TYPE = 'USER'
          AND IS_GRANTABLE = 'TRUE'
          AND USER_NAME = CURRENT_USER;

    -- Variables to hold the fetched data
    DECLARE v_schema_name NVARCHAR(256);
    DECLARE v_object_name NVARCHAR(256);
    DECLARE v_sql NVARCHAR(5000);

    -- Loop through the result set
    FOR cur_row AS C DO
        v_schema_name = cur_row.SCHEMA_NAME;
        v_object_name = cur_row.OBJECT_NAME;

        -- Construct and execute the GRANT statement
        v_sql = 'GRANT SELECT ON "' || v_schema_name || '"."' || v_object_name || '" TO "' || :IP_SQL_USER || '"';
        EXECUTE IMMEDIATE :v_sql;
            
    END FOR;

END;

GRANT EXECUTE ON grant_select_on_objects TO SEFAN_SALES#READ_SPACES;
GRANT EXECUTE ON grant_select_on_objects TO SEFAN_HIERARCH2#READ_SPACES

When the procedure is created, execute it with the space database users as follows:

CALL DWCDBGROUP#READ_SPACES#ADM.grant_select_on_objects('SEFAN_HIERARCH2', 'DWCDBGROUP#READ_SPACES#BOBBY');
CALL DWCDBGROUP#READ_SPACES#ADM.grant_select_on_objects('SEFAN_SALES', 'DWCDBGROUP#READ_SPACES#BOBBY');

As you can see below, the SQL user now has access to the views from both spaces.

Because the access is for individual objects, when you want to add views that have been newly created or exposed since, you’ll have to run the procedure or the individual statements again.