Basically, I have outlined 4 different scenarios that would possibly can exist. in each scenario, there are 5 steps in getting the access setup. Let us talk about briefly about that.
Scenario 1: Accessing classic DB in XSA
It is a typical scenario of accessing objects in XSA from the database of S/4 HANA, BW/4 HANA or old Native HANA DB. The process involves Creating a Custom User provided service to access classic DB(it is like ODBC connection), then a DB role will need to be created in Classic DB. This DB role holds all the required privileges on classic schema. Grantor file will be created in Web IDE with the combination of CUPS + DB role which are created in Step 1 and 2. So with that grantor file now role access has been setup to HDI container owner. Now it is the time to create synonym on classic schema tables, then start using the synonyms in the ETL or modeling objects like calculation views etc.. like a local object to XSA.
Scenario 2: Cross Container access with in a project
It is a good example for HANA DW project scenario, having Staging module/schema and DW module/Schema in a project is very common, though both modules are in same project, containers still isolated. So accessing process involves, firstly the staging module service will be attached to DW module, so that a connection is established, then container role will be created in Staging module, it holds the required privileges on Staging container. In the next step, a hdbgrants file will be created in DW module with role + service created in Step 1 and 2. Thereafter it will be same as in scenario 1, creating synonym and accessing it further.
Scenario 3: Cross container access over multiple projects
Sometimes, it may require to access module which is in a different project. For example, keeping master data in a separate project is very common, when it is to be accessed in other projects sales and finance this procedure will be used. Firstly, an external service will be created in Web IDE to connect to Project 1, then a hdi container role will be created for master table schema and then grantor file will be created in target container with role and service combination. Thereafter it is same as previous scenarios.
Scenario 4: Accessing HDI container objects in Classic DB
This scenario is an opposite to scenario 1, accessing the new developments of XSA in classic DB.
In this scenario, hdbrole will be created in Web IDE with all the required privileges on container, then we can use SAP standard stored procedures to assign this role to classic DB user, or you can also do manual grant of these role to classic user. Running this procedure must be done in HDI Admin Console of DB explorer in Web IDE for HANA. That is it, classic user will get the required access to HDI container objects.
Again, this is only for simple illustration purpose, but there are nice guides already available in documentation.
Please let me know your feedback or comments, I can update the content accordingly. !!! Thanks. !!!