Introduction
We will explore the creation of virtual tables that mirror tables in a remote data source. While a newly generated HDI user comes with default roles and privileges, they are unable to initiate the virtual table creation process without prior assignment. This assignment must be completed before the user can proceed. You can carry out the process in many ways such as directly assigning privileges to the user and then proceeding with the creation. However, in this blog, I will dive into the concept of maintaining a generic user who acts as a grantor, responsible for granting privileges across multiple projects.
Content
- Create a technical user ( GRANTOR USER)
- Create a ROLE
- Create a Remote Source
- Assign the privileges to the Role ( HDI_MAIN_ROLE)
- Add the user HDI_GRANTOR in User Provided Service Instance (UPS)
- Create a new project in Business Application Studios
- Add Artifacts
Create a technical user ( GRANTOR USER)
Technical user that creates the objects in the HDI container needs certain privileges and roles assigned to them, in order to create virtual table on top of the remote source. As an approach, you can create the HDI container and then grant the necessary privileges to the owner of HDI container (#OO) which leads to manual & repetitive efforts. Instead, use a grantor service as self service which is nothing but a technical user which has the privileges to grant required privileges to other users such as container owners. so lets create a technical user or grantor user. This grantor user will not be used for single projects but can be used as self service as grantors for multiple projects.
You can create the user either in HANA Cockpit or via SQL.
Open the Database Explorer > copy the below SQL script.
CREATE USER HDI_GRANTOR PASSWORD Password1
NO FORCE_FIRST_PASSWORD_CHANGE
VALID UNTIL FOREVER
SET USERGROUP DEFAULT;Note : In this scenario, i have used options such as NO FORCE_FIRST_PASSWORD_CHANGE & VALID UNTIL FOREVER to overrule the setting of the password policy. I have also chosen a weak password (Password1) for example purpose only. Read more on Create User ( SAP HELP)
You can also view the HDI_GRANTOR user in the user management in the SAP HANA Cockpit.
Now that I have the HDI_GRANTOR user ready, I can assign the necessary privileges and get the self service user ready to use. But lets try and wrap those privileges in a role and then its just a single role which could be assigned to the technical users for multiple projects.
Create a ROLE
SQL to create role is as follows:
CREATE ROLE HDI_MAIN_ROLE;Create Role - SAP HELP
Alternatively, you can create the role in the HANA Cockpit, you can go to the Role Management > Create Role.
Create a Remote Source
Remote sources are connections to other databases. Virtual tables use a remote source to create a local table that points to data stored in another database.
In the SQL console, enter the below SQL statement
CREATE REMOTE SOURCE HC_DL_1 ADAPTER "hanaodbc"
CONFIGURATION 'Driver=libodbcHDB.so;ServerNode=<remote_tenant_endpoint>;
sslTrustStore="<digi_certificate_string>";encrypt=TRUE;'
WITH CREDENTIAL TYPE 'PASSWORD' USING 'user=<user_name>;password=<password>';
-- Replace the user and password to the technical user details.
-- Where <digi_certificate_string> is the contents of the certificate entered as a string on a single line.
Alternatively, you can create the remote source manually. In the DBX > Catalog > Remote Source > Add Remote Source.
Read more on how to create a remote source
Also check out this tutorial on Access Remote Sources with SAP HANA Database Explorer
Assign the privileges to the Role ( HDI_MAIN_ROLE)
Now that we have the remote source ready, we can assign the privileges to the role on top of the remote source.
GRANT <object_privilege>[,...] ON <object_name> TO <grantee>
GRANT CREATE VIRTUAL TABLE,
CREATE VIRTUAL PROCEDURE,
CREATE VIRTUAL FUNCTION,
ALTER,
REMOTE EXECUTE
ON REMOTE SOURCE HC_DL_1 TO HDI_MAIN_ROLE;
Learn more on SAP Help GRANT Statement
In the HANA cockpit, go to role HDI_MAIN_ROLE > Assign Privileges > Object Privileges
Select the privileges such as Create Virtual Table, Create Virtual Procedure & Function, Alter, Execute.
Once you save it, the selected roles will be reflected in the HDI_MAIN_ROLE.
Assign the User HDI_GRANTOR to the role HDI_MAIN_ROLE
In the SAP HANA Cockpit, go to Role Management > Assigned Users > Assign Users
Search for the user ( HDI_GRANTOR )
Also make it grantable to others and Save.
Add the user HDI_GRANTOR in User Provided Service Instance (UPS)
User Provided Service is required in HANA to use the services that are not listed in the service marketplace. Lets maintain this user in Cloud Foundry in the UPS so that it can be used as self service by developers. Go to your SAP BTP Cockpit > Go to your space > Click on the dropdown near create > Select User Provided Service Instance.
Add the instance name, copy the below JSON and add it in the field
{
"password": "Password1",
"tags": [
"hana"
],
"user": "HDI_GRANTOR"
}Once the UPS is created, this can be used to assign the necessary privileges and roles to the newly created container owner.
Create a new project in Business Application Studios
On the dashboard, Select New project from template > SAP HANA Database Project > Choose a name and keep everything else to the defaults > Create
Once the project is created, you can see that the it is bound. lets bind the existing user provided service instance which we already created in the previous steps. Click at the '+' located beside the database connections and choose the HDI_UPS and add it.
Now, lets go ahead and deploy to make sure that we have the base ready.
Once deployed successfully, lets create a new folder on the db level. in that folder create a new .hdbgrants file.
Add Artifacts
We are going to maintain the instructions for deployer that it needs to use the grantor service to grant certain privileges i.e. HDI_MAIN_ROLE. Lets add the below JSON to the HDI_VT.hdbgranta file.
{
"ServiceName_1":{
"object_owner": {
"global_roles": [
{
"roles": [
"HDI_MAIN_ROLE"
]
}
]
},
"application_user": {
"global_roles": [
{
"roles": [
"HDI_MAIN_ROLE"
]
}
]
}
}
}Note :ît is not recommended that application user has the role assigned because this means any generated application user will be able to create virtual table directly which is not a good practice.
Deploy and observe in the log that our project is using the grantor service which has the alias ServiceName_1 and also the .hdbgrants file.
Once deployed successfully, lets go ahead and create the Virtual Table in src ( src > VT_HDI.hdbvirtualtable). Using the value help, choose the Remote Source, Schema Name & Object Name.
Deploy the project. You should be able to view the virtual table and also be able to access the same. You can open the database explorer via BAS by clicking on icon shown below.
Conclusion:
Summarizing the above steps, we have now a remote source, grantor user with the right set of privileges to create a virtual table and the artifacts added. In addition to this, this will also be useful in scenarios such as Remote Table Replicas (RTR) and extend the above scenario.
Please share your valuable feedback.
- SAP Managed Tags:
2 Comments
