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.
- Create a Database User Group including an admin user;
- Create an SQL user with the new admin user;
- Create a Database User for each space;
- Grant read access to the views that you want the SQL user to have access to.
Figure 1: Two spaces for which we will create one SQL user with read access to both spaces
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.
Figure 2: Dialog for creating a Database User Group
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.
Figure 3: Add new Database Instance entry with the newly created SQL user
When we check the views in the two spaces that this user has access to, we see that currently no views are accessible.
Figure 4: The newly created SQL user has no privileges yet on either of the two spaces
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.
Figure 5: Dialog to create a Database User for a given space
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.
Figure 6: List of the views that the space database user has access to
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.
Figure 7: Views in the SAP Datasphere space that the SQL user has read access to
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.
Figure 8: Views in the SAP Datasphere space that the SQL user has read access to
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.
Conclusion
Now you have a SQL user with read access to two spaces. Are you happy now?
Bluesky
